오라클 23ai 신기능 group by all 구문

OS 환경 : Oracle Linux 8.4 (64bit)

 

DB 환경 : Oracle Database 23.9.0.25.07 ai for Oracle Cloud and Engineered Systems

 

방법 : 오라클 23ai 신기능 group by all 구문

오라클 23ai 23.9 버전부터 group by all 기능이 새롭게 도입되었음
이 기능은 select 문에서 집계 함수(예: sum, count)를 제외한 나머지 비집계 컬럼들을 자동으로 그룹 기준으로 사용하도록 해주는 기능임
기존에는 비집계 컬럼을 모두 group by 절에 수동으로 나열해야 했지만, group by all을 사용하면 이를 생략할 수 있어 쿼리를 더 간단하고 오류 없이 작성할 수 있음
빠르게 쿼리를 작성하거나, 컬럼 수가 많고 반복 작업이 많은 상황에서 특히 유용함
단, rollup, cube, grouping sets 등 다른 그룹핑 기능과는 함께 사용할 수 없으며, 집계 함수나 서브쿼리 등 일부 표현식은 자동 그룹 기준에 포함되지 않음

 

 

사용 예제
1. 기존 방식(group by에 모든 비집계 컬럼을 명시해야 함)
2. group by all 방식1
3. group by all 방식2
4. 10053 트레이스 확인

 

 

사용 예제
1. 기존 방식

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18

SQL> select deptno, job, count(*) as emp_count from emp group by deptno, job;       DEPTNO JOB        EMP_COUNT ---------- --------- ----------         10 PRESIDENT          1         30 MANAGER            1         10 MANAGER            1         20 MANAGER            1         20 ANALYST            2         20 CLERK              2         30 SALESMAN           4         30 CLERK              1         10 CLERK              1   9 rows selected.

group by에 모든 비집계 컬럼을 명시해줘야함

 

 

2. group by all 방식1

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18

SQL> select deptno, job, count(*) as emp_count from emp group by all;       DEPTNO JOB        EMP_COUNT ---------- --------- ----------         10 PRESIDENT          1         30 MANAGER            1         10 MANAGER            1         20 MANAGER            1         20 ANALYST            2         20 CLERK              2         30 SALESMAN           4         30 CLERK              1         10 CLERK              1   9 rows selected.

deptno, job은 count(*)와 같은 집계 함수가 아니기 때문에 자동으로 그룹핑 대상이 됨

 

 

3. group by all 방식2

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21

SQL> select deptno, job, sal, count(*) as emp_count, max(hiredate) as latest_hire from emp group by all;       DEPTNO JOB              SAL  EMP_COUNT LATEST_HIRE ---------- --------- ---------- ---------- ------------------         10 PRESIDENT       5000          1 17-NOV-81         30 MANAGER         2850          1 01-MAY-81         10 MANAGER         2450          1 09-JUN-81         20 MANAGER         2975          1 02-APR-81         20 ANALYST         3000          2 13-JUL-87         20 CLERK            800          1 17-DEC-80         30 SALESMAN        1600          1 20-FEB-81         30 SALESMAN        1250          2 28-SEP-81         30 SALESMAN        1500          1 08-SEP-81         20 CLERK           1100          1 13-JUL-87         30 CLERK            950          1 03-DEC-81         10 CLERK           1300          1 23-JAN-82   12 rows selected.

deptno, job, sal은 비집계 컬럼이므로 자동 그룹핑 대상임
count(*), max(hiredate)는 집계 함수이므로 제외됨

 

 

4. 10053 트레이스 확인
아래 쿼리 수행하여 10053 트레이스 확인

1 2 3 4

SQL>  select deptno, job, max(hiredate) as latest_hire from emp group by all;

 

 

결과 확인

1 2 3 4

$ vi 10053.trc  qb SEL$1 (#0):******* UNPARSED QUERY IS ******* SELECT "EMP"."DEPTNO" "DEPTNO","EMP"."JOB" "JOB",MAX("EMP"."HIREDATE") "LATEST_HIRE" FROM "IMSI"."EMP" "EMP" GROUP BY "EMP"."DEPTNO","EMP"."JOB" query block SEL$1 (#0) unchanged

기존 group by 구문처럼 all 대신 컬럼들이 삽입되는것을 볼수 있음

 

 

결론 :
oracle 23ai의 group by all 기능은 복잡한 select 문에서 반복적인 group by 컬럼 나열을 생략할 수 있게 해주는 실용적인 기능임
특히 컬럼 수가 많거나, 쿼리를 빠르게 작성해야 하는 상황에서 생산성을 높이고 오류를 줄일 수 있음
실행 계획 트레이스를 통해 확인한 결과, oracle은 내부적으로 group by all을 기존 방식의 명시적 컬럼 리스트로 자동 변환하여 처리함
성능이나 동작 방식은 기존과 동일하면서도 사용자 입장에서는 훨씬 간결하고 직관적인 쿼리 작성이 가능해짐
하지만 rollup, cube, grouping sets 같은 고급 그룹핑 기능과는 함께 사용할 수 없기 때문에 사용 시 주의가 필요함

 

 

참조 : 

https://apex.oracle.com/pls/apex/features/r/dbfeatures/features?feature_id=2061
https://docs.oracle.com/en/database/oracle/oracle-database/23/nfcoa/ru_23_9.html#GUID-102576-1
https://docs.oracle.com/en/database/oracle/oracle-database/23/sqlrf/SELECT.html#SQLRF-GUID-CFA006CA-6FF1-4972-821E-6996142A51C6
https://tuna.tistory.com/211