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

<Oracle 11g検証 隠れた新機能検証 その2>
ペンネーム: ぽっちゃりメタボン

皆様、ごぶさたしています。ぽっちゃりメタボンです。
久しぶりの登場となりますがはりきっていきましょう。
先週から、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を作成しておけば、データの分析、
確認などもお手軽に実行できますよね。

以上、2回にわたり分析関数PIVOT、UNPIVOTについて検証してきました。

次回は仮想列について検証を行う予定です。
お楽しみに!!

では、またお会いする日まで。

暑くもなく、寒くもなく快眠ができる 茅ヶ崎より