Pivot 절의 비밀 - 세로를 가로로?
오라클 2008.12.15 15:26몇 달전에서 세로 데이터를 가로로 출력하는 기법에 대해서 잠깐 논의한 바 있다(아래...)
(한 분이 정확하게도 제목이 거꾸로 되었다는 것을 지적해주셨다)
2008/10/04 - [Advanced Oracle] - 세로 데이터를 가로로 출력하기 - 또 다른 아이디어
Oracle 11g의 신기능인 Pivot 절을 테스트하다가 Pivot의 구현 방식이 내가 위에서 설명한 것과 거의 동일한 것을 알았다. 아래 예제를 보자.
create table t1
as
select
10 - mod(level, 7) as c1,
mod(level, 5)+1 as c2,
(1+abs(mod(dbms_random.random,100000))) as c3
from dual
connect by level <= 1000
;
alter session set statistics_level = all;
Pivot 절을 이용해서 Spread-Sheet 형태의 Report를 얻는다.
*
from t1
pivot (
sum(c3)
for c2
in (1,2,3,4,5)
)
;
C1 1 2 3 4 5
---------- ---------- ---------- ---------- ---------- ----------
6 1316159 1573010 1498494 1288933 1303779
5 1693139 1320481 1293505 1196280 1145482
4 1348538 1560112 1289342 1582098 1229042
8 1431648 1538771 1293812 1295780 1670113
7 1602281 1269428 1291983 1562826 1466569
9 1385877 1437785 1589266 1448629 1320990
10 1500509 1287280 1344653 1305496 1053597
Oracle은 어떻게 이런 결과를 얻을 수 있을까?
결론부터 말하면 간단한 Query Transformation 과정을 거친다.
아래와 같이 ALL 옵션을 이용해서 Plan을 조회하면 Column Projection 정보를 얻을 수 있다.
--------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows |E-Bytes|
--------------------------------------------------------------
| 1 | HASH GROUP BY PIVOT| | 1 | 1000 | 39000 |
| 2 | TABLE ACCESS FULL | T1 | 1 | 1000 | 39000 |
--------------------------------------------------------------
Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------
1 - SEL$58A6D7F6
2 - SEL$58A6D7F6 / T1@SEL$1
Column Projection Information (identified by operation id):
-----------------------------------------------------------
1 - "T1"."C1"[NUMBER,22],
SUM(CASE WHEN ("C2"=1) THEN "C3" END )[22],
SUM(CASE WHEN ("C2"=2) THEN "C3" END )[22],
SUM(CASE WHEN ("C2"=3) THEN "C3" END )[22],
SUM(CASE WHEN ("C2"=4) THEN "C3" END )[22],
SUM(CASE WHEN ("C2"=5) THEN "C3" END )[22]
2 - "T1"."C1"[NUMBER,22], "C2"[NUMBER,22], "C3"[NUMBER,22]
Column Project 정보를 보면 아뿔싸하는 생각이 들 것이다.
Oracle은 Pivot Query을 아래와 같이 변환(Transformation)하는 것이다.
(따라서 Pivot 절을 사용한다고 해서 성능이 더 개선되는 것은 아니라는 것을 짐작할 수 있다)
c1,
sum(case when (c2 = 1) then c3 end) as "1",
sum(case when (c2 = 2) then c3 end) as "2",
sum(case when (c2 = 3) then c3 end) as "3",
sum(case when (c2 = 4) then c3 end) as "4",
sum(case when (c2 = 5) then c3 end) as "5"
from t1
group by c1
;
위의 Query 형태는 내가 블로그에서 소개한 바 있는(하지만 이미 보편적으로 사용되고 있는) 것이다.
Pivot 절이 제공하는 기능을 이용하면 이제 더 이상 지저분한 Dynamic Query는 불필요한가?
기본적으로 그렇다. 단, 가로로 펼칠 Column을 동적으로 구성하려면 다음과 같이 XML 결과를 이용해야 한다는 점이 불편하다.
select *
from t1
pivot xml (
sum(c3)
for c2
in (any)
)
;
select *
from t1
pivot xml (
sum(c3)
for c2
in (select distinct c2 from t1)
)
;
C1
----------
C2_XML
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
4
<PivotSet><item><column name = "C2">1</column><column name = "SUM(C3)">1348538</column></item><item><column name = "C2">2</column><column name = "SUM(C3)">1560112</column></item><item><column name = "
C2">3</column><column name = "SUM(C3)">1289342</column></item><item><column name = "C2">4</column><column name = "SUM(C3)">1582098</column></item><item><column name = "C2">5</column><column name = "SU
M(C3)">1229042</column></item></PivotSet>
5
<PivotSet><item><column name = "C2">1</column><column name = "SUM(C3)">1693139</column></item><item><column name = "C2">2</column><column name = "SUM(C3)">1320481</column></item><item><column name = "
C2">3</column><column name = "SUM(C3)">1293505</column></item><item><column name = "C2">4</column><column name = "SUM(C3)">1196280</column></item><item><column name = "C2">5</column><column name = "SU
M(C3)">1145482</column></item></PivotSet>
6
<PivotSet><item><column name = "C2">1</column><column name = "SUM(C3)">1316159</column></item><item><column name = "C2">2</column><column name = "SUM(C3)">1573010</column></item><item><column name = "
C2">3</column><column name = "SUM(C3)">1498494</column></item><item><column name = "C2">4</column><column name = "SUM(C3)">1288933</column></item><item><column name = "C2">5</column><column name = "SU
M(C3)">1303779</column></item></PivotSet>
...
하지만 Oracle이 제공하는 XML Query 기능을 이용하면 원하는 대로 데이터를 추출할 수 있으므로 큰 문제는 아니라고 할 수 있다. 이제 누구나 XML을 이해하고 사용해야 할 시기가 도래한 것인가.
'오라클' 카테고리의 다른 글
IN (...) 절에 Item을 무제한으로 넣고 싶다. (2) | 2008.12.18 |
---|---|
불필요한 Histogram만 삭제하기 (1) | 2008.12.17 |
Pivot 절의 비밀 - 세로를 가로로? (3) | 2008.12.15 |
Grant와 Cursor Invalidation (0) | 2008.12.11 |
Oracle에 대한 이상한 오해들 (0) | 2008.12.09 |