Oracle 11g検証 隠れた新機能検証 その１
＜Oracle 11g検証 隠れた新機能検証 その１＞
ペンネーム： クリープ
今週から、Oracle11gで新たに追加された機能の中でも、大々的に取り上げられ
てないけど開発者や管理者にとって使えそうな機能をピックアップして、検証
してみようと思います。
まず第1回は、PIVOT関数についてです。
■環境
Red Hat Enterprise Linux ES Update 5
Oracle Database 11g Enterprise Edition Release 11.1.0.6.0 – Production
■PIVOTとは？
Oracle11gから追加された関数の中にPIVOT関数があります。
エクセルのピボットテーブル（PIVOT TABLE）などクロス集計機能でおなじみで
すね。このPIVOTという単語を直訳すると「回転する」という意味で、何かを軸
としてものを動かす、というような意味を持つ言葉です。
では、実際にscottユーザーのempテーブルを例にしてPIVOT関数を使用してみる
ことにしましょう。
■通常のSQLとPIVOT関数を使用したSQL
empテーブルに格納されているデータは以下の通りです。
SQL> select * from emp; EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO ----- ------ ---------- ----- -------- ------ ----- ---------- 7369 SMITH CLERK 7902 80-12-17 800 20 7499 ALLEN SALESMAN 7698 81-02-20 1600 300 30 7521 WARD SALESMAN 7698 81-02-22 1250 500 30 7566 JONES MANAGER 7839 81-04-02 2975 20 7654 MARTIN SALESMAN 7698 81-09-28 1250 1400 30 7698 BLAKE MANAGER 7839 81-05-01 2850 30 7782 CLARK MANAGER 7839 81-06-09 2450 10 7788 SCOTT ANALYST 7566 87-04-19 3000 20 7839 KING PRESIDENT 81-11-17 5000 10 7844 TURNER SALESMAN 7698 81-09-08 1500 0 30 7876 ADAMS CLERK 7788 87-05-23 1100 20 7900 JAMES CLERK 7698 81-12-03 950 30 7902 FORD ANALYST 7566 81-12-03 3000 20 7934 MILLER CLERK 7782 82-01-23 1300 10
さて、このデータをJOBとDNAMEごとに集計したいとします。そこで今までの
通りSQL文を書くと以下のようになります。
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
さらに、このデータをDNAMEの値を項目として指定したクロス集計データにす
る場合、以下のようにSUM関数を使用したSQLを書く必要がありました。
SQL> select emp.deptno,dname
2 ,sum(decode(job,'CLERK' ,sal,null)) "CLERK"
3 ,sum(decode(job,'MANAGER' ,sal,null)) "MANAGER"
4 ,sum(decode(job,'PRESIDENT',sal,null)) "PRESIDENT"
5 ,sum(decode(job,'ANALYST' ,sal,null)) "ANALYST"
6 ,sum(decode(job,'SALESMAN' ,sal,null)) "SALESMAN"
7 from emp,dept
8 where emp.deptno = dept.deptno
9 group by emp.deptno,dname
10 order by emp.deptno,dname;
DEPTNO DNAME CLERK MANAGER PRESIDENT ANALYST SALESMAN
------ ----------- ----- ------- ---------- ------- ----------
10 ACCOUNTING 1300 2450 5000
20 RESEARCH 1900 2975 6000
30 SALES 950 2850 5600
このように、これまでのOracleでクロス集計をしようとすると、項目の記載が
decode関数やsum関数などを利用して、集計する項目をいちいち列記していか
なければなりませんでした。これではSQL自体が複雑になりがちで、集計する
項目が10件、20件と増えてしまうと、それだけでも書く手間もかかりメンテナ
ンスもしづらいものになってしまっていました。
Oracle11gではPIVOT関数を使用することで簡単に同様の結果を抽出することが
できます。
SQL> select * from ( select emp.deptno,dname,job,sal
2 from emp,dept
3 where emp.deptno = dept.deptno )
4 pivot ( sum(sal) for job
5 in ('CLERK','MANAGER','PRESIDENT','ANALYST','SALESMAN') )
6 order by deptno,dname;
DEPTNO DNAME 'CLERK' 'MANAGER' 'PRESIDENT' 'ANALYST' 'SALESMAN'
------ ----------- ------- ---------- ----------- ---------- ----------
10 ACCOUNTING 1300 2450 5000
20 RESEARCH 1900 2975 6000
30 SALES 950 2850 5600
PIVOTの括弧内に集計したい項目、集計関数、INリストに集計したい項目とし
て指定すれば、簡単に集計を行うことができます
但し、INリストの中にサブクエリ等を記載することはできない為、集計する項
目は全て記載する必要があります。さらに、GROUP BY句を指定する必要がない
ので、その点でも書きやすさやメンテナンス性が向上します。
■PIVOT関数の内部SQLとは？
では、このPIVOT関数は、内部的にどのような処理をしているのでしょうか。
PIVOT関数の内部で実行されている集計方法によっては、SUM関数を使用した従
来の書き方の方が高速に処理可能という可能性も考えられ、状況によっては使
い分けする必要があるかもしれません。
ということで、SQLトレースを取得して確認してみることにしましょう。
SQL> alter session set events '10046 trace name context forever,level 12'; Session altered.
※SQLとレースファイル抜粋（TKPROFで整形済み）
select * from ( select emp.deptno,dname,job,sal
from emp,dept
where emp.deptno = dept.deptno )
pivot ( sum(sal) for job
in ('CLERK','MANAGER','PRESIDENT','ANALYST','SALESMAN') )
order by deptno,dname
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ------- ------- ---------- ------
Parse 1 0.13 0.25 0 39 0 0
Execute 1 0.00 0.00 0 0 0 0
Fetch 2 0.02 0.02 0 36 0 3
------- ------ -------- ---------- ------- ------- ---------- ------
total 4 0.16 0.28 0 75 0 3
Misses in library cache during parse: 1
Optimizer mode: ALL_ROWS
Parsing user id: 99
Rows Row Source Operation
---- ---------------------------------------------------
3 SORT GROUP BY PIVOT (cr=36 pr=0 pw=0 time=0 us cost=14 size=756 card=14)
14 HASH JOIN (cr=36 pr=0 pw=0 time=43 us cost=13 size=756 card=14)
4 TABLE ACCESS FULL DEPT (cr=18 pr=0 pw=0 time=7 us cost=6 size=88 card=4)
14 TABLE ACCESS FULL EMP (cr=18 pr=0 pw=0 time=7 us cost=6 size=448 card=14)
Elapsed times include waiting on following events:
Event waited on Times Max. Wait Total Waited
---------------------------------------- Waited ---------- ------------
SQL*Net message to client 2 0.00 0.00
SQL*Net message from client 2 2.28 2.28
SQLトレースでは、本体のSQLのみでOracleの内部で実行されているSQLを確認
することができませんでした。
唯一、実行計画に「SORT GROUP BY PIVOT」という見慣れない記載があるだけ
です。これでは、どのような処理をしているかすらわかりません。
最近のOracleは、SQLトレースでは内部で実行されているSQLが見えないことが
多いようです。
ということで、別のアプローチで情報を取得してみることにしましょう。
以下コマンドを実行した結果を確認してみると。。。
SQL> alter session set events '10053 trace name context forever,level 12'; Session altered.
※一部抜粋
SELECT
"EMP"."DEPTNO" "DEPTNO"
,"DEPT"."DNAME" "DNAME"
,SUM(CASE
WHEN ("EMP"."JOB"='CLERK') THEN "EMP"."SAL"
END ) "'CLERK'"
,SUM(CASE
WHEN ("EMP"."JOB"='MANAGER') THEN "EMP"."SAL"
END ) "'MANAGER'"
,SUM(CASE
WHEN ("EMP"."JOB"='PRESIDENT') THEN "EMP"."SAL"
END ) "'PRESIDENT'"
,SUM(CASE
WHEN ("EMP"."JOB"='ANALYST') THEN "EMP"."SAL"
END ) "'ANALYST'"
,SUM(CASEWHEN ("EMP"."JOB"='SALESMAN') THEN "EMP"."SAL"
END ) "'SALESMAN'"
FROM "SCOTT"."EMP" "EMP"
,"SCOTT"."DEPT" "DEPT"
WHERE "EMP"."DEPTNO"="DEPT"."DEPTNO"
GROUP BY "EMP"."DEPTNO"
,"DEPT"."DNAME"
ORDER BY "EMP"."DEPTNO"
,"DEPT"."DNAME"
上記のように、Oracle内部ではSUM関数とCASE文を使用したSQLが実行されたこ
とがわかります。
このコマンドは、オプティマイザのトレースを取得するコマンドで、オプティ
マイザがどのようにSQLを処理しているのかを確認したい時に実行するコマン
ドです。
つまり、オプティマイザにより最適化を行う際にPIVOT関数を上記SQLに組み替
えているようです。これならどちらを使用してもパフォーマンス的には変わり
ませんし、書きやすさや見やすさからPIVOT関数を使用した方がよいと言えそ
うです。
以上、PIVOT関数について検証しました。
次回はUNPIVOT関数について検証する予定です。