Oracle 11g検証 隠れた新機能検証 その２
＜Oracle 11g検証 隠れた新機能検証 その２＞
ペンネーム： ぽっちゃりメタボン
皆様、ごぶさたしています。ぽっちゃりメタボンです。
久しぶりの登場となりますがはりきっていきましょう。
先週から、Oracle 11gで追加された機能の中でも、大々的には取り上げられ
てはないけど開発者や管理者にとってかゆいところに手が届く機能をピックアップして
その機能を検証しています。先週は開発者にとっても有益なPIVOT関数について取り上げてみました。
第2回目は、こちらも便利なUNPIVOT関数について検証をしてみたいと思います。
■環境
Red Hat Enterprise Linux ES v.4 Update 5
Oracle Database 11g Enterprise Edition Release 11.1.0.6.0 – Production
■UNPIVOTとは？
前回はOracle11gから追加された関数PIVOT関数をご紹介しました。
PIVOTについては動作がイメージしやすいと思いますが、UNPIVOTについては皆様いかがでしょうか。
簡単に説明をしてしまうと、クロス集計されているような複数の値列を単一の列に変更してくれる関数です。
では、前回の検証でピボット化したテーブルを参考にUNPIVOT関数がどのような動きをするのか
確認していくことにしましょう。
■通常のSQLとUNPIVOT関数を使用したSQL
さて、まずはこのデータを前回同様JOBとDNAMEごとに集計した結果を確認しておきます。
1)
SQL> select emp.deptno,dname,job,sum(sal) sum_sal
2 from emp,dept
3 where emp.deptno = dept.deptno
4 group by emp.deptno,dname,job order by emp.deptno,dname,job;
DEPTNO DNAME JOB SUM_SAL
---------- ------------ ---------- ----------
10 ACCOUNTING CLERK 1300
10 ACCOUNTING MANAGER 2450
10 ACCOUNTING PRESIDENT 5000
20 RESEARCH ANALYST 6000
20 RESEARCH CLERK 1900
20 RESEARCH MANAGER 2975
30 SALES CLERK 950
30 SALES MANAGER 2850
30 SALES SALESMAN 5600
前回はこの結果をクロス集計データにピボット化しましたが
これを表として作成、保存しておきます。
2)
SQL> create table pivot as
2 select * from ( select emp.deptno,dname,job,sal from emp,dept
3 where emp.deptno = dept.deptno )
4 pivot ( sum(sal) for job
5 in ('CLERK' as CLERK,'MANAGER' as MANAGER,'PRESIDENT' as PRESIDENT,
6 'ANALYST' as ANALYST,'SALESMAN' as SALESMAN) )
7 ;
3)
SQL> select * from pivot;
DEPTNO DNAME CLERK MANAGER PRESIDENT ANALYST SALESMAN
------ ----------- ------- ---------- ----------- ---------- ----------
10 ACCOUNTING 1300 2450 5000
20 RESEARCH 1900 2975 6000
30 SALES 950 2850 5600
3)の表について自前のSQLでUNPIVOTを行うのであれば以下のSQLで実行が可能です。
※UNPIVOTは1)で確認したクロス集計する前の集計イメージへ再構築します。
4)
SQL> SELECT
2 DEPTNO, DNAME, JOB, SUM_SAL
3 FROM (
4 (SELECT DEPTNO, DNAME, 'CLERK' JOB, CLERK SUM_SAL
5 FROM PIVOT)
6 UNION ALL (SELECT DEPTNO, DNAME, 'MANAGER' JOB, MANAGER SUM_SAL
7 FROM PIVOT)
8 UNION ALL (SELECT DEPTNO, DNAME, 'PRESIDENT' JOB, PRESIDENT SUM_SAL
9 FROM PIVOT)
10 UNION ALL (SELECT DEPTNO, DNAME, 'ANALYST' JOB, ANALYST SUM_SAL
11 FROM PIVOT)
12 UNION ALL (SELECT DEPTNO, DNAME, 'SALESMAN' JOB, SALESMAN SUM_SAL
13 FROM PIVOT)
14 )
DEPTNO DNAME JOB SUM_SAL
---------- --------------- --------------------------- ----------
10 ACCOUNTING ANALYST
10 ACCOUNTING CLERK 1300
10 ACCOUNTING MANAGER 2450
10 ACCOUNTING PRESIDENT 5000
10 ACCOUNTING SALESMAN
20 RESEARCH ANALYST 6000
20 RESEARCH CLERK 1300
20 RESEARCH MANAGER 2975
20 RESEARCH PRESIDENT
20 RESEARCH SALESMAN
30 SALES ANALYST
30 SALES CLERK 950
30 SALES MANAGER 2850
30 SALES PRESIDENT
30 SALES SALESMAN 5600
15行が選択されました。
このように、複数列の値をJOBという単一の列として再構築できました。
列から行に再構築するためには union allで各カラム分のSQLを記述する必要があります。
では、いよいよUNPIVOT関数を実行してみます。
SQL> select * from pivot
2 unpivot include nulls
3 (sal_total for job in (CLERK,MANAGER,PRESIDENT,ANALYST,SALESMAN))
DEPTNO DNAME JOB SUM_SAL
---------- --------------- --------------------------- ----------
10 ACCOUNTING ANALYST
10 ACCOUNTING CLERK 1300
10 ACCOUNTING MANAGER 2450
10 ACCOUNTING PRESIDENT 5000
10 ACCOUNTING SALESMAN
20 RESEARCH ANALYST 6000
20 RESEARCH CLERK 1300
20 RESEARCH MANAGER 2975
20 RESEARCH PRESIDENT
20 RESEARCH SALESMAN
30 SALES ANALYST
30 SALES CLERK 950
30 SALES MANAGER 2850
30 SALES PRESIDENT
30 SALES SALESMAN 5600
UNPIVOT関数を使用することにより非常にシンプルにSQLを記述でき、
同様の結果を得ることができました。
※1)と同様の結果を抽出する場合には “include nulls” ではなく、
“exclude nulls”と記述します。
これにより、SUM_SALの値がNULLのものを除外して抽出します。
■UNPIVOT関数の内部SQLとは？
UNPIVOT関数は、内部的にどのような処理をしているのでしょうか。
PIVOT関数同様に確認することにしましょう。
ということで、まずはお馴染みのSQLトレースを取得して確認してみることにしましょう。
SQL> alter session set events '10046 trace name context forever,level 12'; Session altered.
※SQLトレースファイル抜粋（TKPROFで整形済み）
select * from pivot
unpivot include nulls
(sal_total for job in (CLERK,MANAGER,PRESIDENT,ANALYST,SALESMAN))
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.00 0 0 0 0
Execute 1 0.00 0.00 0 0 0 0
Fetch 2 0.00 0.00 0 4 0 15
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 4 0.00 0.00 0 4 0 15
Misses in library cache during parse: 0
Optimizer mode: ALL_ROWS
Parsing user id: 81
Rows Row Source Operation
------- ---------------------------------------------------
15 VIEW (cr=4 pr=0 pw=0 time=0 us cost=15 size=615 card=15)
15 UNPIVOT (cr=4 pr=0 pw=0 time=0 us)
3 TABLE ACCESS FULL PIVOT (cr=4 pr=0 pw=0 time=0 us cost=3 size=72 card=3)
今回もUNPIVOTというオペレーションが実行されている事は実行計画から確認
できましたが、どのようなSQLが実行されているかまでは確認できません。
では、こちらも前回同様にUNPIVOT関数実行時のオプティマイザトレースを取得し、
内部ではどのようなSQL文が実行されているのか確認してみましょう。
以下のSQL文を実行します。
SQL> alter session set events '10053 trace name context forever,level 1'; Session altered.
※抜粋
SELECT
"from$_subquery$_002"."DEPTNO" "DEPTNO",
"from$_subquery$_002"."DNAME" "DNAME",
"from$_subquery$_002"."JOB" "JOB",
"from$_subquery$_002"."JOB_TOTAL" "JOB_TOTAL"
FROM (
(SELECT
"PIVOT"."DEPTNO" "DEPTNO",
"PIVOT"."DNAME" "DNAME",
'CLERK' "JOB",
"PIVOT"."CLERK" "JOB_TOTAL"
FROM "SCOTT"."PIVOT" "PIVOT")
UNION ALL (SELECT "PIVOT"."DEPTNO"
"DEPTNO",
"PIVOT"."DNAME" "DNAME",
'MANAGER' "JOB",
"PIVOT"."MANAGER" "JOB_TOTAL"
FROM "SCOTT"."PIVOT" "PIVOT")
UNION ALL (SELECT "PIVOT"."DEPTNO" "DEPTNO",
"PIVOT". "DNAME" "DNAME",
'PRESIDENT' "JOB",
"PIVOT"."PRESIDENT" "JOB_TOTAL"
FROM "SCOTT"."PIVOT" "PIVOT")
UNION ALL (SELECT "PIVOT"."DEPTNO" "DEPTNO",
"PIVOT"."DNAME" "DNAME",
'ANALYST' "JOB",
"PIVOT"."ANALYST" "JOB_TOTAL"
FROM "SCOTT"."PIVOT" "PIVOT")
UNION ALL (SELECT "PIVOT"."DEPTNO" "DEPTNO",
"PIVOT"."DNAME" "DNAME",
'SALESMAN' "JOB",
"PIVOT" ."SALESMAN" "JOB_TOTAL"
FROM "SCOTT"."PIVOT" "PIVOT")
) "from$_subquery$_002"
ふーむ、4)で作成したSQL文と同様のものが実行されてるようです。
指定カラム数分をUNON ALLで連結しているので索引での絞り込みがされないかつ、
指定カラム数が多い場合は、安易にUNPIVOT関数を実行してしまうと負荷が気になりますね。
※上記SQLの実行計画を確認しましたが、FULL SCANがPIVOT表に対して5回実行されます。
Rows Row Source Operation
------- ---------------------------------------------------
15 VIEW (cr=16 pr=2 pw=0 time=0 us cost=15 size=615 card=15)
15 UNION-ALL (cr=16 pr=2 pw=0 time=0 us)
3 TABLE ACCESS FULL PIVOT (cr=4 pr=2 pw=0 time=0 us cost=3 size=48 card=3)
3 TABLE ACCESS FULL PIVOT (cr=3 pr=0 pw=0 time=0 us cost=3 size=48 card=3)
3 TABLE ACCESS FULL PIVOT (cr=3 pr=0 pw=0 time=0 us cost=3 size=42 card=3)
3 TABLE ACCESS FULL PIVOT (cr=3 pr=0 pw=0 time=0 us cost=3 size=42 card=3)
3 TABLE ACCESS FULL PIVOT (cr=3 pr=0 pw=0 time=0 us cost=3 size=42 card=3)
上記のように、Oracle内部ではIN句で指定したカラム数分、
UNION ALLを使用した形でSQLを再構築し、展開されていることが確認できます。
実際の開発の現場や、ちょっとした分析を行いたいユーザにとっては、
簡単に要求を実現できる機能としては待ち望まれていたものかもしれませんね。
かくいう自分も、以前はプログラムで問い合わせ結果を一度配列に格納し、、、再構築。
もしくはSQLを駆使してクロス集計を行っていた口であります。
PIVOT、UNPIVOTを使用したviewを作成しておけば、データの分析、
確認などもお手軽に実行できますよね。
以上、２回にわたり分析関数PIVOT、UNPIVOTについて検証してきました。
次回は仮想列について検証を行う予定です。
お楽しみに！！
では、またお会いする日まで。
暑くもなく、寒くもなく快眠ができる 茅ヶ崎より