2.30_PC*_(Pivot_Conversion)
2011.10.12 05:59
2.30 PC* ( Pivot Conversion ) :
Pivot 절을 Case + Group By 로 변환하라.
* 과거
일별 월별 실적을 위해 Group by + decode 혹은 Case 문 활용
*Pivot
기본적으로 새로를 가로로 만들기 위한 기법
SELECT channel_id,
COUNT(CASE WHEN TO_CHAR (time_id,'YYYY' ) = '1998' THEN quantity_sold END ) SALES_1998,
COUNT(CASE WHEN TO_CHAR (time_id,'YYYY' ) = '1999' THEN quantity_sold END ) SALES_1999,
COUNT(CASE WHEN TO_CHAR (time_id,'YYYY' ) = '2000' THEN quantity_sold END ) SALES_2000,
COUNT(CASE WHEN TO_CHAR (time_id,'YYYY' ) = '2001' THEN quantity_sold END ) SALES_2001
FROM
sales
GROUP BY channel_id;
------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | A-Rows | A-Time | Buffers Used-Mem |
------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 4 |00:00:02.81 | 5052 |
| 1 | HASH GROUP BY | | 1 | 4 |00:00:02.81 | 5052 1783K (0)|
| 2 | PARTITION RANGE ALL| | 1 | 918K|00:00:02.15 | 5052 |
| 3 | TABLE ACCESS FULL | SALES | 28 | 918K|00:00:00.78 | 5052 |
------------------------------------------------------------------------------------------
해당 SQl 은 SALES 테이블에서 채널별 년도별로 판매건수를 집게한 것이다.
하지만 년도별이 아닌 월별로 보고 싶다면 Group by + Case 문을 12번 이나 작성 해야 한다.
또한 위에서 세로를 가로로 바꾸는 기법을 모르는 개발자들의 경우 각각의 COUNT 함수 대신에 스칼라 서브쿼리를 매번 반복적으로 사용하여 성능을 저하 시킨다.
이런 비효율적 Coding 작업과 성능이 느린 SQL 을 위하여 11g 에 Pivot 절이 탄생 하였다.
예 )
SELECT *
FROM ( SELECT channel_id,quantity_sold,TO_CHAR(time_id,'YYYY' ) as year
FROM sales )
PIVOT ( COUNT (QUANTITY_SOLD ) FOR YEAR IN ( '1998' AS SALES_1998,
'1999' AS SALES_1999,
'2000' AS SALES_2000,
'2001' AS SALES_2001 ));
------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | A-Rows | A-Time | Buffers | Used-Mem |
------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 4 |00:00:02.45 | 5052 | |
| 1 | HASH GROUP BY PIVOT | | 1 | 4 |00:00:02.45 | 5052 | 1778K (0)|
| 2 | PARTITION RANGE ALL| | 1 | 918K|00:00:02.23 | 5052 | |
| 3 | TABLE ACCESS FULL | SALES | 28 | 918K|00:00:00.80 | 5052 | |
------------------------------------------------------------------------------------------
최초의 SQL Plan 과 Pivot 을 적용한 Plan 이 대동소이함을 알 수 있다.
실제로 10053 Trace 분석해 보면 아래와 같은 SQL을 볼 수 있다.
**************************
Predicate Move-Around (PM)
**************************
OPTIMIZER INFORMATION
******************************************
----- Current SQL Statement for this session (sql_id=gm359ftfc7t09) -----
SELECT channel_id,
COUNT(CASE WHEN TO_CHAR (time_id,'YYYY' ) = '1998' THEN quantity_sold END ) SALES_1998,
COUNT(CASE WHEN TO_CHAR (time_id,'YYYY' ) = '1999' THEN quantity_sold END ) SALES_1999,
COUNT(CASE WHEN TO_CHAR (time_id,'YYYY' ) = '2000' THEN quantity_sold END ) SALES_2000,
COUNT(CASE WHEN TO_CHAR (time_id,'YYYY' ) = '2001' THEN quantity_sold END ) SALES_2001
FROM
sales
GROUP BY channel_id
*******************************************
Transformer 가 Pivot 을 위의 SQL 처처럼 Group by + Case 문으로 바꾼 것이다.
Pivot 을 Control 하는 파라미터는 _pivot_implementation_method 이다 .
해당 파라미터의 값은 Pivot1,Pivot2,Choose 중에 선택할 수 있으며 default 값은 Choose 이다.
위의 SQL은 파라미터가 Choose 인 상태에서 수행 되었지만 Pivot1이 적용된 것이다.
이제 파라미터를 Pivot2 로 바꿔보자.
얘 )
SELECT *FROM ( SELECT channel_id,quantity_sold,TO_CHAR(time_id,'YYYY' ) as year
FROM sales )
PIVOT ( COUNT (QUANTITY_SOLD ) FOR YEAR IN ( '1998' AS SALES_1998,
'1999' AS SALES_1999,
'2000' AS SALES_2000,
'2001' AS SALES_2001 ));
--------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | A-Rows | A-Time | Buffers | Used-Mem |
--------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 4 |00:00:01.19 | 5052 | |
| 1 | VIEW | | 1 | 4 |00:00:01.19 | 5052 | |
| 2 | TRANSPOSE | | 1 | 4 |00:00:01.19 | 5052 | |
| 3 | SORT GROUP BY PIVOT | | 1 | 13 |00:00:01.19 | 5052 | 2048 (0)|
| 4 | PARTITION RANGE ALL| | 1 | 918K|00:00:02.09 | 5052 | |
| 5 | TABLE ACCESS FULL | SALES | 28 | 918K|00:00:00.74 | 5052 | |
--------------------------------------------------------------------------------------------
파라미터 pivot1 이적용된 sql에 비하여 수행시간이 2배가량 향상 되었고 Plan 상에 Transpose 라는 Operatoin 과정이 추가 되었다.
하지만 Buffers 항목의 수치는 동일하다.
Opreration Transpose와 파라미터 Pivot2의 비밀은 무엇일까.?
대용량 테이블을 Select 하는 개발자라면 Case 문이나 Decode 의 부하가 상당함을 알 것이다. 이럴 경우 Group by 를 먼저처리 한 후 에 Decode 나 Case 를 적용하면 부하를 획기적으로 줄일 수 있다.
오라클 Transformer 는 Gropu By 먼저 수행하여결과 건수를 획기적으로줄인 후에 Case문을 적용한다.
이것이 바로 Group By + Transpose 과정이다.
예 )
SELECT /*+GATHER_PLAN_STATISTICS*/
channel_id,
NVL(SUM(CASE WHEN num =1 THEN cnt END),0)sales_1998,
NVL(SUM(CASE WHEN num =2 THEN cnt END),0)sales_1999,
NVL(SUM(CASE WHEN num =3 THEN cnt END),0)sales_2000,
NVL(SUM(CASE WHEN num =4 THEN cnt END),0)sales_2001
FROM ( SELECT /*+NO_USE_HASH_AGGREGATION*/
channel_id,
CASE WHEN ( TO_CHAR(time_id,'YYYY' ) ='1998' ) THEN 1
WHEN ( TO_CHAR(time_id,'YYYY' ) ='1999' ) THEN 2
WHEN ( TO_CHAR(time_id,'YYYY' ) ='2000' ) THEN 3
WHEN ( TO_CHAR(time_id,'YYYY' ) ='2000' ) THEN 4
END AS num,
COUNT(*) cnt
FROM sales
GROUP BY channel_id,TO_CHAR(TIME_ID,'YYYY') )
GROUP BY channel_id;
--------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | A-Rows | A-Time | Buffers | Used-Mem |
--------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 4 |00:00:01.18 | 5052 | |
| 1 | SORT GROUP BY NOSORT | | 1 | 4 |00:00:01.18 | 5052 | |
| 2 | VIEW | | 1 | 13 |00:00:01.18 | 5052 | |
| 3 | SORT GROUP BY | | 1 | 13 |00:00:01.18 | 5052 | 2048 (0)|
| 4 | PARTITION RANGE ALL| | 1 | 918K|00:00:02.08 | 5052 | |
| 5 | TABLE ACCESS FULL | SALES | 28 | 918K|00:00:00.73 | 5052 | |
--------------------------------------------------------------------------------------------
수행시간 뿐만 아니라 Scan 한 블럭수 (Buffers),PGA메모리 사용량도 대동 소이 함을 알 수있다.
NO_USE_HASH_AGGREGATOIN 힌트를 사용한 이유는 파라미터 Pivot2 를 적용한 Plan 과 똑같은 상황을 만들기 위해서 Hash Group By 를 피하기 위한 목적으로 사용되었다.
11gR1에서는 10053 Event
Trace에 UNPARSED QUERY가 나타나지 않았다. 오라클 11gR2 환경에서 10053 Trace를
분석하면 Query Transformation이 종료된 상태의 SQL을 볼 수 있다. 아래의 Trace 내용을
보자.
Final query after transformations:******* UNPARSED QUERY IS *******
SELECT CHANNEL_ID,
SALES_1998,
SALES_1999,
SALES_2000,
SALES_2001
FROM (SELECT CHANNEL_ID CHANNEL_ID,
NVL(SYS_OP_PIVOT(COUNT(*),1),0) SALES_1998,
NVL(SYS_OP_PIVOT(COUNT(*),2),0) SALES_1999,
NVL(SYS_OP_PIVOT(COUNT(*),3),0) SALES_2000,
NVL(SYS_OP_PIVOT(COUNT(*),4),0) SALES_2001
FROM SALES
GROUP BY CHANNEL_ID,
CASE WHEN (TO_CHAR(TIME_ID,'YYYY')='1998') THEN 1
WHEN (TO_CHAR(TIME_ID,'YYYY')='1999') THEN 2
WHEN (TO_CHAR(TIME_ID,'YYYY')='2000') THEN 3
WHEN (TO_CHAR(TIME_ID,'YYYY')='2001') THEN 4
END
);
위의 SQL은 사용자가 실행할 수 없는것이다. 하지만 오라클은 내부적으로 위의 SQL을
사용하고 있다. Case 문 대신에 Sys_Op_Pivot이라는 내부 함수가 사용되었다. 하지만
사용하는 함수가 다르다고 해서 원리가 다른 것이 아니므로 같은 일을 하는 함수 정도로
생각하면 된다. 결론적으로 먼저 Group By 하여 건수가 획기적으로 줄어드는 경우는
Pivot2를 적용하는 것이 효율적임을 알 수 있다.
