catch-img

Oracle SQLチューニング（第30回）「NULL処理構文の理解と効率的なSQLの作成」（6/7回）

Oracle技術情報

Oracle SQLチューニング


「NULL処理構文の理解と効率的なSQLの作成」の６回目は、「IS NOT NULL照会に対する改善方法検索」についてのお話です。それでは、始めましょう。

7.6 IS NOT NULL照会に対する改善方法検索

　IS NOT NULL照会はSQL構成やカラムのインデックス構成情報により多くの性能偏差が発生しますが、最小限IS NOT NULLで問い合わせるカラムを利用したインデックス スキャンが効率的であるなら、IS NOT NULL照会条件をインデックス スキャンが可能な条件に変更する必要があります。 IS NOT NULL条件を変更する時はカラムのデータ タイプにより変更する方法が変わることになります。 今からインデックスを使えるようにIS NOT NULL条件を変更する方法とIS NOT NULL条件によるSQLの性能問題に対して調べてみることにしましょう。

7.6.1 多様なIS NOT NULL処理とSQL性能問題

    CHAR[VARCHAR2]:column > chr(0)[or column > '']
    DATE:column > to_date('19000101','yyyymmdd')
    NUMBER:column >= 0 OR column < 0

7.6.1.1 CHAR(VARCAHR2)タイプであるカラムのIS NOT NULL

　IS NOT NULLはカラムがNULLでないデータだけ抽出することを言います。 したがってCHARタイプであるカラムに対するIS NOT NULL照会はインデックスを使うことはできません。 したがって効率的なインデックス スキャンになることができるように、文字列のうち一番小さい値より大きい値を問い合わせるように変更するならば、インデックスを使用できない非効率は除去することができるようになります。

[IS NOT NULL 照会]

WITH not_null_t AS (  SELECT 'a' AS c1 FROM DUAL
                      UNION ALL 
                      SELECT null FROM DUAL
                      UNION ALL
                      SELECT 'b' FROM DUAL
                      UNION ALL
                      SELECT '0' FROM DUAL )
SELECT *
  FROM not_null_t
 WHERE c1 IS NOT NULL ;

C1
--
A
B
0


[IS NOT NULLと同じ照会SQL]

WITH not_null_t AS (  SELECT 'A' AS c1 FROM DUAL
                      UNION ALL 
                      SELECT null FROM DUAL
                      UNION ALL
                      SELECT 'B' FROM DUAL
                      UNION ALL
                      SELECT '0' FROM DUAL)
SELECT *
  FROM not_null_t
 --WHERE c1 IS NOT NULL
WHERE c1 > CHR(0);--->または、c1 > ' 'も可能.

C1
--
A
B
0

　CHR関数は数値を引数として受け取り、文字を返す関数で、CHR（0）は文字列のうち最も小さい値を意味します。

7.6.1.2 DATEタイプであるカラムのIS NOT NULL

　IS NOT NULLで照会されるDATEタイプであるカラムをインデックス スキャンが可能な構文に変更する方法は、カラムに存在できない一番小さい値を利用して問い合わせることです。

[IS NOT NULL照会]

ALTER SESSION SET nls_date_format = 'yyyy-mm-dd';

WITH not_null_t AS (  SELECT TO_DATE('20110101','yyyymmdd') AS c1 FROM DUAL
                      UNION ALL 
                      SELECT null FROM DUAL
                      UNION ALL
                      SELECT to_date('20111111','yyyymmdd') FROM DUAL )
SELECT *
  FROM not_null_t
 WHERE c1 IS NOT NULL ;

C1
----------
2011-01-01
2011-11-11



[IS NOT NULLと同じ照会SQL]

ALTER SESSION SET nls_date_format = 'yyyy-mm-dd';

WITH not_null_t AS (   SELECT TO_DATE('20110101','yyyymmdd') as c1 FROM DUAL
                       UNION ALL 
                       SELECT null FROM DUAL
                       UNION ALL
                       SELECT TO_DATE('20111111','yyyymmdd') FROM DUAL)
SELECT *
  FROM not_null_t
WHERE c1 > TO_DATE('19000101','yyyymmdd');---> C1カラムに存在できない以前の値
C1
----------
2011-01-01
2011-11-11

7.6.1.3 NUMBERタイプであるカラムのIS NOT NULL

　NUMBERタイプであるカラムのIS NOT NULL照会は0を基点にしてすべてのデータを抽出するように条件を変更すれば良いのです。

[IS NOT NULL照会]

WITH t1 AS (   SELECT -1 AS c1 FROM DUAL
               UNION ALL 
               SELECT NULL FROM DUAL
               UNION ALL
               SELECT 1 FROM DUAL
               UNION ALL
               SELECT 1.2 FROM DUAL
               UNION ALL
               SELECT 0 FROM DUAL)
SELECT *
FROM t1
WHERE c1 IS NOT NULL ;

           C1
   ----------
        -1
         1
       1.2
         0



[IS NOT NULLと同じ照会SQL]

WITH t1 AS (   SELECT -1 AS c1 FROM DUAL
               UNION ALL
               SELECT NULL FROM DUAL
               UNION ALL
               SELECT 1 FROM DUAL
               UNION ALL
               SELECT 1.2 FROM DUAL
               UNION ALL
               SELECT 0 FROM DUAL )

SELECT *
  FROM t1
 WHERE c1 >= 0 OR c1 < 0 ;

        C1
   ----------
           -1
            1
          1.2
            0

7.6.2 結合処理時IS NOT NULLを活用すること

　テーブル間結合を実行する時、各テーブルのカラムとカラムを比較して処理しますが、NULLを含まない値で比較することになるので、基本的に結合連結カラムはIS NOT NULL条件を有していることになります。

[単一テーブルのカラム比較とIS NOT NULL]

SELECT *
  FROM not_null_t1
 WHERE c3 = c3 ;

--------------------------------------------------------------------------
| Id  | Operation                   | Name               | Rows  | Bytes  |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |                    |    66 |   462  |
|   1 |  TABLE ACCESS BY INDEX ROWID| NOT_NULL_T1        |    66 |   462  |
|*  2 |   INDEX FULL SCAN           | NOT_NULL_T1_IDX_01 |    50 |        |
--------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   2 - filter("C3" IS NOT NULL) ---> C3 = C3はC3 IS NOT NULL条件を含む。

[テーブル結合とIS NOT NULL]

SELECT t1.*, t2.*
  FROM not_null_t1 t1,
       not_null_t2 t2
 WHERE t1.c3 IS NOT NULL
   AND t1.c2 = t2.c2
   AND t1.c3 = t2.c3 ;


---------------------------------------------------------------------------
| Id  | Operation                    | Name               | Rows  | Bytes  |
---------------------------------------------------------------------------
|   0 | SELECT STATEMENT             |                    |    86 |  1204  |
|*  1 |  HASH JOIN                   |                    |    86 |  1204  |
|*  2 |   TABLE ACCESS FULL          | NOT_NULL_T2        |    34 |   238  |
|   3 |   TABLE ACCESS BY INDEX ROWID| NOT_NULL_T1        |    66 |   462  |
|*  4 |    INDEX FULL SCAN           | NOT_NULL_T1_IDX_01 |    50 |        |
---------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   1 - access("T1"."C2"="T2"."C2" AND "T1"."C3"="T2"."C3")
   2 - filter("T2"."C3" IS NOT NULL)
   4 - filter("T1"."C3" IS NOT NULL)

　T2.C3 IS NOT NULLという条件はSQLにはありませんが、t1.c3 = t2.c3条件自体にT1.C3 IS NOT NULLとT2.C3 IS NOT NULL照会条件が含まれています。

　もし、T2.C3列がNULL以外のデータが少ない場合は、T2.C3列に新しい索引を作成し、索引を使用できるように照会条件を変更することで効率的な実行が可能です。

-NOT_NULL_T2.C3に新規インデックスを生成する。

CREATE INDEX not_null_t2_idx_01 ON not_null_t2 (c3) ;

SELECT t1.*, t2.*
  FROM not_null_t1 t1,
       not_null_t2 t2
 WHERE t1.c3 > CHR(0)
   AND t1.c2 = t2.c2
   AND t1.c3 = t2.c3
   AND t2.c3 > CHR(0) ;
---------------------------------------------------------------------------
| Id  | Operation                    | Name               | Rows  | Bytes  |
---------------------------------------------------------------------------
|   0 | SELECT STATEMENT             |                    |    86  |  1204 |
|*  1 |  HASH JOIN                   |                    |    86  |  1204 |
|   2 |   TABLE ACCESS BY INDEX ROWID| NOT_NULL_T2        |    34  |   238 |
|*  3 |    INDEX RANGE SCAN          | NOT_NULL_T2_IDX_01 |    34  |       |
|   4 |   TABLE ACCESS BY INDEX ROWID| NOT_NULL_T1        |    66  |   462 |
|*  5 |    INDEX RANGE SCAN          | NOT_NULL_T1_IDX_01 |    50  |       |
---------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
   1 - access("T1"."C2"="T2"."C2" AND "T1"."C3"="T2"."C3")
   3 - access("T2"."C3">' ')
   5 - access("T1"."C3">' ')



今回は、ここまでとなります。次回は、「'(BLANK)とNULLデータ処理」についてです。それでは、see you next time ^^


前の記事

prev-article-image

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

次の記事

next-article-image

Oracle SQLチューニング（第31回）「NULL処理構文の理解と効率的なSQLの作成」（7/7回）

CONTACT

他社に頼らず自社でデータベースを監視・運用をしませんか？
MaxGaugeがサポートします

お役立ち資料は
こちらから

資料ダウンロード

不明点がある方は、
こちらからお問い合わせください

お問い合わせ

お電話でのお問い合わせはこちら

03-3518-0771

平日 10時～18時

人気記事ランキング

タグ一覧

logo_img
ページトップへ戻る

©EXEM JAPAN Corp. All Rights Reserved.