오라클 23ai 신기능 group by 시 alias 및 position 사용

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