はじめまして。Re:Qの入江です。

数ヶ月前にOracleの構築案件に携わったのですが、その使用目的がデータウェアハウスのデータマイニング用でした。データマイニングとは？という状態だったこともあり、好奇心からデータウェアハウスおよびデータマイニングについて色々調べてみました。
今日はそのとき見つけた、ちょっとした小技？小ネタ？を紹介したいと思います。

調べていくと、Oracleのマニュアル「Databaseデータ・ウェアハウス・ガイド 11gリリース2(11.2) B56309-03」のROLLUP（GROUP BYの拡張）という関数に目がとまりました。

ROLLUP関数...皆さん、ご存知ですか？

比較的よく使用していたGROUP BYの拡張という言葉にひきつけられ調べてみた結果、ROLLUP関数って便利だな～と感じましたので、DBAの観点からその良さをお伝えします。

ROLLUP関数

 
---------------------------------------------------------------------------
使い方：
  SELECT文のGROUP BY句に追加

用途：
  ①総計（合計）を取得
  ②小計を複数のレベルで取得
---------------------------------------------------------------------------

以下、例を使って説明します。

用途①：総計（合計）を取得

例1）表領域毎のデータファイルサイズ＋全てのデータファイルサイズの合計を調べたい　　
　　（※TEMP表領域を除く）

・ROLLUP関数を使わない場合だと・・・

SQL> SELECT tablespace_name,SUM(bytes)/1024/1024 mb FROM dba_data_files
  2  GROUP BY tablespace_name;

TABLESPACE         MB
---------- ----------
SYSAUX            600
UNDOTBS1          505
USERS               5
SYSTEM            710

と

SQL> SELECT SUM(bytes)/1024/1024 mb FROM dba_data_files;

      MB
--------
    1820

の2つのSQLになります。
(結合して1つのSQLにも可能ですが、文が長くなります。)

そこで、ROLLUP関数を使用すると・・・

SQL> SELECT tablespace_name,SUM(bytes)/1024/1024 mb FROM dba_data_files
  2  GROUP BY ROLLUP(tablespace_name);

TABLESPACE         MB
---------- ----------
SYSAUX            600
SYSTEM            710
UNDOTBS1          505
USERS               5
                 1820　★合計値

上記★にて、tablespace_name列がNullのレコードに合計値が出ていることに気付いたでしょうか。
ROLLUPを使用することにより、プラスアルファで同時に総計（合計）を取得できるのです。

 

用途②：小計を複数のレベルで取得

例2）スキーマ毎、オブジェクトタイプ毎の数を確認し集計したい
　　（※SYSTEM、SYSMANスキーマに限定）

SQL> SELECT owner,object_type,COUNT(*) FROM dba_objects
  2  WHERE owner in ('SYSTEM','SYSMAN')
  3  GROUP BY ROLLUP(owner,object_type);

OWNER          OBJECT_TYPE                                COUNT(*)
-------------- ---------------------------------------- ----------
SYSMAN         LOB                                              75
SYSMAN         TYPE                                            672
SYSMAN         VIEW                                            471
SYSMAN         INDEX                                          1028
SYSMAN         QUEUE                                            12
SYSMAN         TABLE                                           729
SYSMAN         PACKAGE                                         193
SYSMAN         TRIGGER                                          97
SYSMAN         FUNCTION                                         12
SYSMAN         RULE SET                                          4
SYSMAN         SEQUENCE                                         13
SYSMAN         PROCEDURE                                         3
SYSMAN         TYPE BODY                                        49
SYSMAN         PACKAGE BODY                                    193
SYSMAN         MATERIALIZED VIEW                                 1
SYSMAN         EVALUATION CONTEXT                                2
SYSMAN                                                        3554　★1
SYSTEM         LOB                                              25
SYSTEM         TYPE                                              1
SYSTEM         VIEW                                             14
SYSTEM         INDEX                                           241
SYSTEM         QUEUE                                             4
SYSTEM         TABLE                                           161
SYSTEM         PACKAGE                                           1
SYSTEM         SYNONYM                                           8
SYSTEM         TRIGGER                                           2
SYSTEM         SEQUENCE                                         20
SYSTEM         PROCEDURE                                         1
SYSTEM         PACKAGE BODY                                      1
SYSTEM         INDEX PARTITION                                  52
SYSTEM         TABLE PARTITION                                  39
SYSTEM                                                         570　★2
                                                              4124　★3

★1：SYSMANのオブジェクト小計
★2：SYSTEMのオブジェクト小計
★3：SYSTEM、SYSMANスキーマのオブジェクト総計

と小計と総計が取得できたことをお分かりいただけたでしょうか。

例1と2のように、サクッとSQLを実行して、小計や総計も取得して調べたい時、ROLLUP関数を知っておくと便利です。DBAの方も集計を必要な時、ROLLUP関数の使用を検討してみてはいかがでしょうか。

Oracleには隠れた機能がいっぱいあります（むろん知っている人は知っていますが）。
そんなちょっとした機能、小技のノウハウを少しでも紹介していき、活用頂ければ幸いです。