Oracle 11g検証 隠れた新機能検証 その1

<Oracle 11g検証 隠れた新機能検証 その1>
ペンネーム: クリープ

今週から、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関数について検証する予定です。