데이터이야기
DB 노하우, 데이터직무, 다양한 인터뷰를 만나보세요.
GROUP BY 구문
GROUP BY는 해당 칼럼 기준으로 평균, 최댓값, 최솟값, 건 수 등의 통계를 조회하기 위한 목적으로 많이 사용되는 구문이다. 예를 들어, 다음과 같이 Group by 구문을 사용하여 회사에서 각 부서의 직원 수를 조회할 수 있다.
SYS@PROD > conn scott/tiger Connected. SCOTT@PROD > SELECT d.dname, COUNT (empno) empcount 2 FROM dept d, emp e 3 WHERE d.deptno = e.deptno 4 GROUP BY d.dname 5 ORDER BY d.dname;
DNAME EMPCOUNT -------------- ---------- ACCOUNTING 3 RESEARCH 5 SALES 6
위 구문의 실행 계획은 다음과 같다.
SCOTT@PROD > select * from table(dbms_xplan.display_cursor);
Plan hash value: 2970111170
----------------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | ----------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | | | 7 (100)| | | 1 | SORT GROUP BY | | 4 | 64 | 7 (29)| 00:00:01 | | 2 | MERGE JOIN | | 14 | 224 | 6 (17)| 00:00:01 | | 3 | TABLE ACCESS BY INDEX ROWID| DEPT | 4 | 52 | 2 (0)| 00:00:01 | | 4 | INDEX FULL SCAN | PK_DEPT | 4 | | 1 (0)| 00:00:01 | |* 5 | SORT JOIN | | 14 | 42 | 4 (25)| 00:00:01 | | 6 | TABLE ACCESS FULL | EMP | 14 | 42 | 3 (0)| 00:00:01 | -----------------------------------------------------------------------------------------
위의 실행 계획을 살펴보면 GROUP BY와 ORDER BY에서 동일한 dname 칼럼을 사용하였기에 Id 1에서‘SORT GROUP BY’ 오퍼레이션이 사용되었으며, ORDER BY를 위한 ‘SORT ORDER BY’ 정렬 오퍼레이션이 별도로 존재하지 않는다는 점에 유의하자. 그러나 GROUP BY가 항상 ‘SORT GROUP BY’를 사용하여 처리되는 것은 아니다. 10g부터는 보다 성능이 개선된 ‘HASH GROUP BY’ 오퍼레이션을 대개 사용한다. 따라서 SQL에서 정렬된 결과를 원할 경우 항상 명시적으로 ORDER BY 구문을 사용해야 한다는 사실을 절대 잊어버려서는 안 된다. 과거에는 Nested Loop join이나 Group by가 내부적으로 정렬을 사용하는 메커니즘을 이용하여 고의로 ORDER BY를 제거하는 튜닝 기법을 사용하기도 하였지만 더 이상 이러한 트릭을 사용해서는 안 된다. 이는 오라클 9i부터 오라클 매뉴얼에도 명시된 사항이다.
SORT GROUP BY vs HASH GROUP BY
앞서 10g부터는 보다 성능이 개선된 Hash Group by를 사용한다는 사실을 언급하였다. Hash Group by는 기존의 정렬을 사용하는 Sort Group By보다 성능이 뛰어난 대신에 Hash 함수를 사용하기 때문에 결과 집합에 대해 정렬된 순서를 보장하지 않는다.
11g 버전에서 실행한 다음 쿼리를 살펴보자.
JHKIM@PROD > conn scott/tiger Connected. SCOTT@PROD > set autotrace on SCOTT@PROD > SELECT deptno, COUNT (*), avg(sal) 2 FROM emp 3 GROUP BY deptno;
DEPTNO COUNT(*) AVG(SAL) ---------- ---------- ---------- 30 6 1566.66667 <-- DEPTNO가 순서대로 정렬되지 않음. 20 5 2175 10 3 2916.66667
Elapsed: 00:00:00.00
Execution Plan ---------------------------------------------------------- Plan hash value: 4067220884
--------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | --------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 3 | 21 | 4 (25)| 00:00:01 | | 1 | HASH GROUP BY | | 3 | 21 | 4 (25)| 00:00:01 | <<--- Hash 방식 사용함. | 2 | TABLE ACCESS FULL| EMP | 14 | 98 | 3 (0)| 00:00:01 | ---------------------------------------------------------------------------
9i 때와 마찬가지로 정렬된 결과를 원할 경우 명시적으로 ORDER BY를 지정해주어야 한다. ORDER BY를 사용한 다음 결과를 참고 바란다.
SCOTT@PROD > SELECT deptno, COUNT (*), avg(sal) 2 FROM emp 3 GROUP BY deptno 4 order by deptno;
DEPTNO COUNT(*) AVG(SAL) ---------- ---------- ---------- 10 3 2916.66667 <-- DEPTNO가 순서대로 정렬 됨. 20 5 2175 30 6 1566.66667
Elapsed: 00:00:00.00
Execution Plan ---------------------------------------------------------- Plan hash value: 15469362
--------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | --------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 3 | </spanHASH GROUP BY vs. SORT GROUP BY