2.32_GSTT*_(Grouping_Sets_Using_Temp_Table)
2011.10.11 12:33
■ GROUPING SETS 사용시 TEMP 테이블에 적재후 이를 반복해서 이용하라
-- 개념 : 같은 테이블을 반복해서 이용해야 되는경우 비효율적인 부하가 발생함으로
필요한 항목만 TEMP 테이블에 저장하고 그 테이블을 이용하여 반복적인 작업을 하게 된다.
그렇다면 역시 불필요한 테이블 ACCESS를 줄일수 있다.
ALTER SESSION SET EVENTS '10053 trace name context forever, level 1';
SELECT DEPARTMENT_ID, JOB_ID, AVG (SALARY) AVG_SAL
FROM EMPLOYEE
GROUP BY GROUPING SETS (DEPARTMENT_ID, JOB_ID);
ALTER SESSION SET EVENTS '10053 trace name context off';
--------------------------------------------------------------+-----------------------------------+
| Id | Operation | Name | Rows | Bytes | Cost | Time |
--------------------------------------------------------------+-----------------------------------+
| 0 | SELECT STATEMENT | | | | 12 | |
| 1 | TEMP TABLE TRANSFORMATION | | | | | |
| 2 | LOAD AS SELECT | | | | | |
| 3 | TABLE ACCESS FULL | EMPLOYEE | 107 | 1712 | 4 | 00:00:01 |
| 4 | LOAD AS SELECT | | | | | |
| 5 | HASH GROUP BY | | 1 | 20 | 3 | 00:00:01 |
| 6 | TABLE ACCESS FULL | SYS_TEMP_0FD9D6CB1_EDE99| 1 | 20 | 2 | 00:00:01 |
| 7 | LOAD AS SELECT | | | | | |
| 8 | HASH GROUP BY | | 1 | 26 | 3 | 00:00:01 |
| 9 | TABLE ACCESS FULL | SYS_TEMP_0FD9D6CB1_EDE99| 1 | 26 | 2 | 00:00:01 |
| 10 | VIEW | | 1 | 33 | 2 | 00:00:01 |
| 11 | TABLE ACCESS FULL | SYS_TEMP_0FD9D6CB2_EDE99| 1 | 46 | 2 | 00:00:01 |
--------------------------------------------------------------+-----------------------------------+
-- ID 기준으로 3번에서 EMPLOYEE 테이블을 읽어서 필요한 컬럼만 TEMP 테이블에 저장
-- ID 6, 9, 11번에서 그 테이블을 반복적해서 사용
-- 그렇다면 왜! 3번이나 반복해서 TEMP 테이블을 사용하는걸까? 아래 SQL을 살펴보자
-- C0, C1은 TEMP 테이블의 컬럼 ALIAS 이며 각각 DEPARTMENT_ID, JOB_ID를 의미한다.
WITH
TEMP0 AS
(SELECT /*+ MATERIALIZE */ DEPARTMENT_ID AS C0, JOB_ID AS C1, SALARY AS A0
FROM EMPLOYEE ) ,
TEMP1 AS
(SELECT /*+ MATERIALIZE */ NULL C0, C1, COUNT(A0) AS A1, SUM(A0) AS A0
FROM TEMP0
GROUP BY C1 ) ,
TEMP2 AS
(SELECT /*+ MATERIALIZE */ C0, NULL C1, COUNT(A0) AS A1, SUM(A0) AS A0
FROM TEMP0
GROUP BY C0 ) ,
TEMP3 AS
(SELECT TEMP1. * FROM TEMP1
UNION ALL
SELECT TEMP2. * FROM TEMP2)
SELECT C0 AS DEPARTMENT_ID, C1 AS JOB_ID, A0 AS AVG_SAL
FROM (SELECT C0, C1, DECODE(A0, 0, TO_NUMBER(NULL), A1/A0) AS A0
FROM TEMP3 );
■ 트레이스를 분석해보자
**************************
Predicate Move-Around (PM) --> ID 6번 : TEMP1
**************************
...중간생략
SELECT /*+ */
NULL C0, C1 C1,
BIN_TO_NUM(1, GROUPING(C1)) D0,
--> GROUPING 함수를 사용한 이유는 GROUP BY 단위를 알아내기 위함
--> 즉, C1 혹인 C0 컬럼으로 GROUP BY 된것인지의 여부를 구분해야하는 경우 GROUPING을 적용할수있다.
COUNT(A0), SUM(A0)
--> AVG (SALARY) 을 구하기위함.(논리적으로 SUM(A0)/COUNT(A0))
FROM "SYS"."SYS_TEMP_0FD9D6CB1_EDE99"
GROUP BY (C1)
--> C1으로 GROUP BY
**************************
Predicate Move-Around (PM) --> ID 9번 : TEMP2
**************************
...중간생략
SELECT /*+ */ C0 C0, NULL C1, BIN_TO_NUM(GROUPING(C0), 1) D0, COUNT(A0), SUM(A0)
FROM "SYS"."SYS_TEMP_0FD9D6CB1_EDE99"
GROUP BY (C0)
--> C0으로 GROUP BY
-- 두개의 SQL을 살펴보면 ID 6번, 9번 각각 DEPARTMENT_ID, JOB_ID로 GROUP BY 해놓고
UNION ALL을 적용하게 된다. --> ID 11번 : TEMP3
-- 마지막으로 아래의 트래이스에 반영된 SQL를 확인하면 위의 SQL중
마지막 쿼리가 적용되었다는 것을 확인할 수 있다.
...중간생략
SELECT /*+ CARDINALITY( 107) */
"sys_view_v"."C0" "DEPARTMENT_ID",
"sys_view_v"."C1" "JOB_ID",
"sys_view_v"."A0" "AVG_SAL"
FROM (
SELECT /*+ NO_MERGE NO_PUSH_PRED(v) */ C0, C1, DECODE(A0, 0, TO_NUMBER(NULL), A1/A0) AS A0
FROM (
SELECT "SYS"."SYS_TEMP_0FD9D6CB2_EDE99".*
FROM "SYS"."SYS_TEMP_0FD9D6CB2_EDE99"
) v
) "sys_view_v"
■ 하지만 아쉽다..
-- 위의 쿼리는 EMPLOYEE 테이블을 GROUP BY 하지 않은 상태로 TEMP 테이블에 적재하였다는것이다.
-- 아래처럼 SQL을 미리 GROYP BY하여 건수를 줄이고 반복해서 사용하면 부하가 획기적으로 줄어들수 있기 때문이다.
EXPLAIN PLAN FOR
SELECT DEPARTMENT_ID, JOB_ID, SUM (SUM_SAL) / SUM (CNT) AS AVG_SAL
FROM (SELECT DEPARTMENT_ID, JOB_ID, COUNT (SALARY) CNT,
SUM (SALARY) SUM_SAL
FROM EMPLOYEE
GROUP BY DEPARTMENT_ID, JOB_ID) --> 미리 GROUP BY 하여 건수를 줄임
GROUP BY GROUPING SETS (DEPARTMENT_ID, JOB_ID);
SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY);
-------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 107 | 4922 | 13 (24)| 00:00:01 |
| 1 | TEMP TABLE TRANSFORMATION | | | | | |
| 2 | LOAD AS SELECT | SYS_TEMP_0FD9D6CB7_EDE99 | | | | |
| 3 | HASH GROUP BY | | 107 | 1712 | 5 (20)| 00:00:01 |
| 4 | TABLE ACCESS FULL | EMPLOYEE | 107 | 1712 | 4 (0)| 00:00:01 |
| 5 | LOAD AS SELECT | SYS_TEMP_0FD9D6CB8_EDE99 | | | | |
| 6 | HASH GROUP BY | | 1 | 39 | 3 (34)| 00:00:01 |
| 7 | TABLE ACCESS FULL | SYS_TEMP_0FD9D6CB7_EDE99 | 1 | 39 | 2 (0)| 00:00:01 |
| 8 | LOAD AS SELECT | SYS_TEMP_0FD9D6CB8_EDE99 | | | | |
| 9 | HASH GROUP BY | | 1 | 33 | 3 (34)| 00:00:01 |
| 10 | TABLE ACCESS FULL | SYS_TEMP_0FD9D6CB7_EDE99 | 1 | 33 | 2 (0)| 00:00:01 |
| 11 | VIEW | | 1 | 46 | 2 (0)| 00:00:01 |
| 12 | TABLE ACCESS FULL | SYS_TEMP_0FD9D6CB8_EDE99 | 1 | 46 | 2 (0)| 00:00:01 |
-------------------------------------------------------------------------------------------------------
-- 위의 저자 SQL이랑 아래는 첫 예제 SQL 실행계획 비교
SELECT DEPARTMENT_ID, JOB_ID, AVG (SALARY) AVG_SAL
FROM EMPLOYEE
GROUP BY GROUPING SETS (DEPARTMENT_ID, JOB_ID);
-------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 107 | 3531 | 12 (17)| 00:00:01 |
| 1 | TEMP TABLE TRANSFORMATION | | | | | |
| 2 | LOAD AS SELECT | SYS_TEMP_0FD9D6CB5_EDE99 | | | | |
| 3 | TABLE ACCESS FULL | EMPLOYEE | 107 | 1712 | 4 (0)| 00:00:01 |
| 4 | LOAD AS SELECT | SYS_TEMP_0FD9D6CB6_EDE99 | | | | |
| 5 | HASH GROUP BY | | 1 | 20 | 3 (34)| 00:00:01 |
| 6 | TABLE ACCESS FULL | SYS_TEMP_0FD9D6CB5_EDE99 | 1 | 20 | 2 (0)| 00:00:01 |
| 7 | LOAD AS SELECT | SYS_TEMP_0FD9D6CB6_EDE99 | | | | |
| 8 | HASH GROUP BY | | 1 | 26 | 3 (34)| 00:00:01 |
| 9 | TABLE ACCESS FULL | SYS_TEMP_0FD9D6CB5_EDE99 | 1 | 26 | 2 (0)| 00:00:01 |
| 10 | VIEW | | 1 | 33 | 2 (0)| 00:00:01 |
| 11 | TABLE ACCESS FULL | SYS_TEMP_0FD9D6CB6_EDE99 | 1 | 46 | 2 (0)| 00:00:01 |
-------------------------------------------------------------------------------------------------------
-- 사실.. 기대했다.. 이정도로는 획기적이라기 보다.. 아쉽다.. 대용량이라면 또 얘기가 다르지 않을까 생각이 든다.
-- 아무튼 저자가 원하는 방향으로든 먼저 GROUP BY 하여 건수를 줄이는것도 하나의 튜닝의 방법이라 할수있다.
The Logical Optimizer (bysql.net 2011년 2차 스터디)
작성자: 김범석 (darkbeom)
최초작성일: 2011년 10월 11일
본문서는 bysql.net 스터디 결과입니다 .본 문서를 인용하실때는 출처를 밝혀주세요. http://www.bysql.net
문서의 잘못된 점이나 질문사항은 본문서에 댓글로 남겨주세요. ^^