오라클 Transformer 는 때때로 반복작업이 일어날경우 같은 테이블에 대한 부하가 심해지므로 TEMP 테이블을 만들어서 저장하고 그 테이블을 이용하여 반복적인 작업을 하게되는 경우가 있다. 대표적인 경우가 GROUPING SETS 를 사용하는 경우이다.
SELECT department_id, JOB_ID, AVG (salary) avg_sal
FROM employee a
GROUP BY GROUPING SETS(department_id, JOB_ID) ;
---------------------------------------------------------------+---------------------------+
| Id | Operation | Name | Rows | Cost | Time |
---------------------------------------------------------------+---------------------------+
| 0 | SELECT STATEMENT | | | 11 | |
| 1 | TEMP TABLE TRANSFORMATION | | | | |
| 2 | LOAD AS SELECT | | | | |
| 3 | TABLE ACCESS FULL | EMPLOYEE | 107 | 3 |
| 4 | LOAD AS SELECT | | | | |
| 5 | HASH GROUP BY | | 1 | 3 |
| 6 | TABLE ACCESS FULL | SYS_TEMP_0FD9D6608_434CFB| 1 | 2 |
| 7 | LOAD AS SELECT | | | | |
| 8 | HASH GROUP BY | | 1 | 3 |
| 9 | TABLE ACCESS FULL | SYS_TEMP_0FD9D6608_434CFB| 1 | 2 |
| 10 | VIEW | | 1 | 2 |
| 11 | TABLE ACCESS FULL | SYS_TEMP_0FD9D6609_434CFB| 1 | 2 |
---------------------------------------------------------------+---------------------------+
위의 PLAN 을 보면 먼저 ID 기준으로 3번에서 employee 테이블을 읽어서 필요한 컬럼만 TEMP 테이블에 저장해두고 그테이블을 반복해서 이용(ID 기준으로 6번, 9번, 11번)하게 되는것이다. 그렇다면 왜 3번이나 반복해서 temp 테이블을 사용하는걸까?
그 해답은 10053 trace 에 있다. id 기준으로 6번에 해당하는 SQL 이 아래와 같다.
SELECT /*+ */ NULL C0, C1 C1, BIN_TO_NUM(1, GROUPING(C1)) D0, COUNT(A0), SUM(A0) FROM "SYS"."SYS_TEMP_0FD9D6608_434CFB" GROUP BY (C1)
위에서 C0, C1 은 TEMP 테이블의 컬럼 ALIAS 이며 각각 department_id, JOB_ID를 의미한다. 또한 BIN_TO_NUM 함수를 사용한 이유는 나중에 ID 기준으로 11번째 에서 Group by 단위를 알아내기 위함이다. COUNT(A0), SUM(A0) 를 select 한이유도 11번에서 AVG (salary) 값을 구하기 위해서이다. 참고로 avg(col1) 함수는 논리적으로 sum(col1)/count(col1) 과 같다.
또한 id 기준으로 9번에 해당하는 SQL 이 아래와 같다.
SELECT /*+ */ C0 C0, NULL C1, BIN_TO_NUM(GROUPING(C0), 1) D0, COUNT(A0), SUM(A0) FROM "SYS"."SYS_TEMP_0FD9D6608_434CFB" GROUP BY (C0)
최종적으로 Transformation이 적용된 SQL은 다음과 같다.
(
SELECT department_id AS C0, JOB_ID AS C1, salary AS A0
FROM employee a
) ,
TEMP1 as
(
SELECT NULL C0, C1, COUNT(salary) AS A1, SUM(salary) AS A0
FROM SYS_TEMP_0FD9D6608_434CFB
GROUP BY C1
) ,
TEMP2 as
(
SELECT C0, NULL C1, COUNT(salary) AS A1, SUM(salary) AS A0
FROM SYS_TEMP_0FD9D6608_434CFB
GROUP BY C0
) ,
SYS_TEMP_0FD9D6609_434CFB AS
(
SELECT TEMP1. * FRPM TEMP1
UNION ALL
SELECT TEMP1. * FRPM TEMP1
)
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 --> 분모가 0 일 경우 처리
FROM SYS_TEMP_0FD9D6609_434CFB ) ;
불만
id 기준으로 6번과 9번에서 각각 job_id 와 department_id 로 group by 해놓고 이것을 id 11 번에서 합쳐서 보여주게 된다. 하지만 필자는 이런 변환에 대하여 불만이 있다. 위의 쿼리는 EMPLOYEE 테이블을 GROUP BY 하지 않은 상태로 TEMP 테이블에 적재한다. 이것은 매우 비효율적이다. EMPLOYEE 테이블을 Temp 테이블에 적재시 미리 Group BY 하여 넣을 수가 있다. 아래의 SQL 처럼 미리 GROUP BY 하여 건수를 미리 줄여놓으면 반복작업시의 부하가 획기적으로 줄어들수 있기 때문이다.
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 a
GROUP BY department_id, JOB_ID )
GROUP BY GROUPING SETS(department_id, JOB_ID) ;
이렇게 수동으로 SQL을 바꾸는 방법은 Grouping Sets의 변환로직이 변하지 않는한 확실한 튜닝방법이 될수 있으므로 반드시 고려되어야 한다.
'Oracle > Optimizer' 카테고리의 다른 글
| MERGE 문과 IN 조건이 만난다면 (8) | 2009.11.26 |
|---|---|
| Transformer - SQL 튜닝의 새로운 패러다임 (11) | 2009.10.15 |
| 스칼라 서브쿼리를 서브쿼리로 변환하라 (11) | 2009.09.10 |
| Semi Join 의 재조명 (5) | 2009.08.31 |
| Group By 를 먼저 수행하고 Join 하라( Group By Placement ) (4) | 2009.08.24 |
