OS 환경 : Oracle Linux 8.4 (64bit)
DB 환경 : Oracle Database 23.4.0.24.05 ai Free
방법 : 오라클 23ai 뉴피처 group by 시 alias 및 position 사용
오라클 23ai 부터 group by 절에도 select 절에 사용한 alias 나 포지션을 그대로 사용할 수 있음
이전 버전에는 order by 절에만 alias 를 사용가능 했지만 이제는 group by 절에도 alias 및 포지션을 사용할수 있음
group by 구문의 공식문서의 Restrictions on the Select List 중 아래 구문이 추가됨
From 23ai you can group by position and alias.
https://docs.oracle.com/en/database/oracle/oracle-database/23/sqlrf/SELECT.html#SQLRF-GUID-CFA006CA-6FF1-4972-821E-6996142A51C6
테스트
기존에 사용 가능했던 구문(order by 절에 alias 사용)
|
1
2
3
4
5
6
7
8
9
10
|
SQL>
select deptno "DNO", sum(sal) from emp
group by deptno
order by DNO;
DNO SUM(SAL)
---------- ----------
10 8750
20 10875
30 9400
|
기존에 사용 불가능했던 구문(group by 절에 alias 및 포지션 사용)
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
|
SQL>
select deptno "DNO", sum(sal) from emp
group by DNO
order by DNO;
*
ERROR at line 2:
ORA-00904: "DNO": invalid identifier
select deptno "DNO", sum(sal) from emp
group by 1
order by 1;
*
ERROR at line 1:
ORA-00979: not a GROUP BY expression
|
에러가 발생함
23ai 에서는 이 구문이 사용 가능함
|
1
2
3
4
5
6
7
8
9
10
|
SQL>
select deptno "DNO", sum(sal) from emp
group by DNO
order by DNO;
DNO SUM(SAL)
---------- ----------
10 8750
20 10875
30 59994
|
select 절에서 함수를 사용 한 alias 를 group by 에 사용
10053 trace도 같이 확인
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
|
SQL> alter session set tracefile_identifier=t2;
Session altered.
SQL> alter session set events '10053 trace name context forever, level 1';
Session altered.
SQL>
select to_char(hiredate, 'yyyy-mm') "YYYYMM", sum(sal) from emp
group by YYYYMM
order by YYYYMM;
YYYYMM SUM(SAL)
------- ----------
1980-12 800
1981-02 19998
1981-04 2975
1981-05 9999
1981-06 2450
1981-09 19998
1981-11 5000
1981-12 12999
1982-01 1300
1987-07 4100
10 rows selected.
SQL> alter session set events '10053 trace name context off';
Session altered.
|
정상적으로 실행됨
트레이스 확인
|
1
2
3
4
5
6
7
8
9
|
$ vi FREE_ora_57233_T2.trc
.
.
Final query after transformations:******* UNPARSED QUERY IS *******
SELECT TO_CHAR("EMP"."HIREDATE",'yyyy-mm') "YYYYMM",SUM("EMP"."SAL") "SUM(SAL)" FROM "SYS"."EMP" "EMP"
GROUP BY TO_CHAR("EMP"."HIREDATE",'yyyy-mm')
ORDER BY TO_CHAR("EMP"."HIREDATE",'yyyy-mm')
.
.
|
group by와 order by 의 alias가 자동으로 to_char로 변환됨
현재 23ai에서 alias는 사용가능 하지만 포지션 번호를 사용하면 에러가 발생함
|
1
2
3
4
5
6
7
8
|
SQL>
select to_char(hiredate, 'yyyy-mm') "YYYYMM", sum(sal) from emp
group by 1
order by 1;
*
ERROR at line 1:
ORA-00979: "HIREDATE": must appear in the GROUP BY clause or be used in an aggregate function
Help: https://docs.oracle.com/error-help/db/ora-00979/
|
포지션까지 사용하고 싶은 경우 group_by_position_enabled 파라미터를 true로 변경해줘야함
|
1
2
3
|
SQL> alter session set group_by_position_enabled = true;
Session altered.
|
포지션 사용 쿼리 재실행
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
|
SQL>
select to_char(hiredate, 'yyyy-mm') "YYYYMM", sum(sal) from emp
group by 1
order by 1;
YYYYMM SUM(SAL)
------- ----------
1980-12 800
1981-02 2850
1981-04 2975
1981-05 2850
1981-06 2450
1981-09 2750
1981-11 5000
1981-12 3950
1982-01 1300
1987-07 4100
10 rows selected.
|
정상적으로 실행됨
group_by_position_enabled 파라미터 정보 확인
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
|
SQL>
col name for a30
col value for a10
col display_value for a10
col default_value for a10
col isses_modifiable for a20
col description for a35
select name
,value
,display_value
,default_value
,isses_modifiable
,description
from v$parameter
where name = 'group_by_position_enabled';
NAME VALUE DISPLAY_VA DEFAULT_VA ISSES_MODIFIABLE DESCRIPTION
------------------------------ ---------- ---------- ---------- -------------------- -----------------------------------
group_by_position_enabled FALSE FALSE FALSE TRUE enable/disable group by position
|
참조 :
https://apex.oracle.com/pls/apex/features/r/dbfeatures/features?feature_id=1710
https://docs.oracle.com/en/database/oracle/oracle-database/23/sqlrf/SELECT.html#SQLRF-GUID-CFA006CA-6FF1-4972-821E-6996142A51C6
https://positivemh.tistory.com/974
https://oracle-base.com/articles/23ai/group-by-and-having-clause-using-column-alias-or-column-position-23ai
http://www.koreaoug.org/c23_free/12430
'ORACLE > Admin' 카테고리의 다른 글
| 오라클 23ai 신기능 from 절 없는 select 문 (0) | 2024.05.09 |
|---|---|
| 오라클 23ai 신기능 tns 에러 메세지 개선 (0) | 2024.05.08 |
| 오라클 23ai 신기능 if [not] exists 구문 (0) | 2024.05.05 |
| 오라클 23ai 신기능 update, delete 시 direct join (0) | 2024.05.04 |
| 오라클 23ai 신기능 pdb 기동순서 조정 (0) | 2024.05.03 |