SQLチューニング（第27回）「NULL処理構文の理解と効率的なSQLの作成」（3／７回）

「NULL処理構文の理解と効率的なSQLの作成」の三回目である今回は、「グループ関数(COUNT,SUM,MAX,MIN)とNVL処理」と題して、お送りします。では、早速始めましょう。

---

７.３　グループ関数(COUNT,SUM,MAX,MIN)とNVL処理

　グループ関数に対して誤ったNULL処理が原因で誤ったデータを抽出する問題は、たびたび発生します。 NULL演算が間違って発生する可能性のある深刻な問題は、抽出しようとするデータの結果が異なり、データの整合性を損なう可能性があることと、同時にこの点をユーザが誤った結果であると認識することが難しいということです。 今回のケースでは、誤った NULL 処理で結果の値がなぜ変わるのか、そしてなぜ発見するのが難しいのかを調べ、この 2 つの場合の解決策を調べてみましょう。

[改善前SQL]

 SELECT SUM (
        t1.sum_c3
        - ( (SELECT SUM(c3) FROM null_t2 t2 WHERE t2.c1 = t1.c1 AND t2.c2 = t1.c2)
        + (SELECT SUM(c3) FROM null_t3 t3 WHERE t3.c1 = t1.c1 AND t3.c2 = t1.c2) )
      ) AS total_sum
 FROM ( SELECT c1,
            c2,
              SUM(c3) AS sum_c3
         FROM null_t1
        WHERE c2 = 'A'
        GROUP BY c1, c2 ) t1 ;

 TOTAL_SUM
----------
 461381537

テーブルNULL_T2統計情報およびINDEX情報

                            DATA PREC        NUMBER
COLUMN_NAME     DATA_TYPE    LEN SCAL NN   DISTINCT      DENSITY  NUM_NULLS
--------------- ---------- ----- ---- -- ---------- ------------ ----------
C1              NUMBER        22      Y      100000  0.000010000          0
C2              VARCHAR2       4      Y          26  0.038461538          0
C3              VARCHAR2      40      Y           9  0.111111111      10473

INDEX_NAME        COLUMN LIST
---------------  ------------
NULL_T2_IDX01    C1, C2

テーブルNULL_T3統計情報およびINDEX情報

                            DATA PREC        NUMBER
COLUMN_NAME     DATA_TYPE    LEN SCAL NN   DISTINCT      DENSITY  NUM_NULLS
--------------- ---------- ----- ---- -- ---------- ------------ ----------
C1              NUMBER        22      Y      100000  0.000010000          0
C2              VARCHAR2       4      Y          26  0.038461538          0
C3              VARCHAR2      40      Y         100  0.010000000        890
INDEX_NAME        COLUMN LIST
---------------  ------------
NULL_T3_IDX01     C1, C2

[改善後SQL]

SELECT /*+ LEADING(T1) USE_HASH(T1 T2 T3) */
       SUM (  t1.c3_sum - ( t2.c3_sum + t3.c3_sum ) ) AS total_sum
  FROM ( SELECT c1, c2, SUM(c3) AS c3_sum
           FROM NULL_T1
          WHERE c2 = 'A'
          GROUP BY  c1, c2 ) t1,
       (  SELECT c1, c2, SUM(c3) AS c3_sum
            FROM NULL_T2
          GROUP BY c1, c2 ) t2,
       (  SELECT c1, c2, SUM(c3) AS c3_sum
            FROM NULL_T3
          GROUP BY c1, c2 ) t3
 WHERE t1.c1 = t2.c1(+)
   AND t1.c2 = t2.c2(+)
   AND t1.c1 = t3.c1(+)
   AND t1.c2 = t3.c2(+) ;

 TOTAL_SUM
----------
 461381537

　[改善前SQL]のようにスカラーサブクエリを毎度実行して値を抽出した部分を結合(Hash Join)に変更して性能が改善しました。 改善前/後のデータを比較してみればデータもまた完ぺきに一致して性能が改善されたと判断されます。 しかし、悲しいことに改善前/後SQLで抽出されたデータは作成者が意図したのと違ったデータでした。 しかしSQLのどの部分が誤ったデータを抽出するかは発見するのが容易ではありません。

　それでは、なぜ間違ったデータが抽出されたのでしょうか? その理由に対して調べてみることにしましょう。

　[改善後SQL]は以下のような意図を持って作成しましたが、SQLに正しく反映されず、間違ったデータが抽出されました。

1.	インライン ビューT1ではNULLデータが抽出されない。
2.	T2.C3,T3.C3はNullableカラムで、NULLが抽出された場合にはT1.C3_SUM値をそのまま抽出しなければならない。

　作成意図は上記と同じなのですが改善前/後SQLの場合、T2.C3、T3.C3の値のうちNULLが存在すルトSUM(C3)はNULLを抽出することになります。 これは、SUM関数の処理中にNULLデータに対して処理が行われていないためです。 明らかにSQLの作成意図は、T2.C3、T3.C3の値がNULLであればT1.C3_SUM値のままを抽出することを望みましたが、NULL+T1.SUM(C3)演算によって誤ったTotal Sum値が抽出されてしまいます。 SUM 関数を使用する際、演算対象となるデータの中にNULL 値が含まれる可能性がある場合には、以下のように必ずNVL 関数を利用した処理が追加で必要となります。

[最終改善後SQL]

SELECT /*+ LEADING(T1) USE_HASH(T1 T2 T3) */
     SUM (t1.c3_sum - (NVL(t2.c3_sum、0) + NVL(t3.c3_sum、0)) --> NVL()処理追加
         ) AS total_sum
  FROM ( SELECT c1, c2, SUM(c3) AS c3_sum
           FROM null_t1
          WHERE c2 = 'A'
         GROUP BY c1, c2 ) t1,
         (  SELECT c1, c2, SUM(c3) AS c3_sum
              FROM null_t2
            GROUP BY c1, c2 ) t2,
         (  SELECT c1, c2, SUM(c3) as c3_sum
              FROM null_t3
            GROUP BY c1, c2 ) t3
 WHERE t1.c1 = t2.c1(+)
   AND t1.c2 = t2.c2(+)
   AND t1.c1 = t3.c1(+)
   AND t1.c2 = t3.c2(+) ;

 TOTAL_SUM
----------
576734568 --->脱落したデータが正常抽出される。

　引き続き他のグループ関数であるCOUNT,SUM,MAX,MINのNULL処理がどのように実行されるのか下の例題SQLを通じて調べてみることにしましょう。

SELECT 'Null'    AS Nullable,
       COUNT(c3) AS count_c3,
       SUM(c3) AS sum_c3,
       MAX(c3) AS max_c3,
       MIN(c3) AS min_c3
  FROM NULL_T2
 WHERE c3 IS NULL
   AND c2 = 'A'
UNION ALL
SELECT 'Not Null',
       COUNT(c3),
       SUM(c3),
       MAX(c3),
       MIN(c3)
  FROM NULL_T2
 WHERE c3 IS NOT NULL
   AND c2 = 'A' 
UNION All
SELECT 'ALL',
       COUNT(c3),
       SUM(c3),
       MAX(c3),
       MIN(c3)
  FROM NULL_T2
 WHERE c2 = 'A' ;

NULLABLE   COUNT_C3   SUM_C3     MAX_C3     MIN_C3
---------- ---------- ---------- ---------- ---------
Null       0
Not Null   3077       15386       8          2
ALL        3077       15386       8          2

- COUNT:Number DataType -> NULL Dataを0 (リターン値)
- SUM:Number DataType -> NULL DataをNULL (リターン値)
- MAX:Char DataType -> NULL DataをNULL (リターン値)
- MIN:Char DataType -> NULL DataをNULL (リターン値)

　テスト結果を見れば、COUNTを除いたSUM,MAX,MINはNULLデータをNULLでリターンするということが分かります。 したがって抽出されたデータを利用してまた他の修飾（数式）演算が別に進行されなければならない場合にはNVL処理を必ず実行し、誤った結果値が抽出されることを防ぐことができます。

---

　今回は、ここまでとなります。いかがでしたか？次回は、「NULLABLEカラム使用による非効率COUNT関数処理」です。それでは、次回まで see you ^^

---