catch-img

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

Oracle技術情報

Oracle SQLチューニング

　今回から、「NULL処理構文の理解と効率的なSQLの作成」と題しまして、７回シリーズでお話しさせていただきます。第一回は、概要と「NULL処理関数の理解」というテーマでお届けします。それでは、始めましょう。

　NULLとは、列に値が存在しないことを意味します。 NOT NULL制約またはPrimary Key制約がかかっていない列にはNULLデータを含めることができます。 NULLは値自体が存在しないもので、他の値と演算を行うと、誤った結果が抽出される危険があります。たとえば、NULL + 100 = NULLになるので、NULL値を持つ列のデータと操作するときは常に注意する必要があります

　このようなNULLデータを持つ列が式演算に正常に使われるためには、NULLデータを任意の値（例えば0）に変更して処理するようにしてくれれば、誤った式演算は避けることができるのです。この場合、NULLデータを式演算に活用できるようにサポートする関数がNVLです。ここで、NVLは式演算とNULL値を文字列に変換することもでき、使用用途が多様です。ただし、NVLを誤って使用する場合は、SQLが効率的ではない方法で実行される可能性があるため、NVLを乱用しないでください。

　もしそうなら、NULLを含むSQLの適切な解釈と効率的に処理するNVLの使い方をテストを通して調べましょう。

　まず、テストを実行できるようにテストスクリプトを実行しましょう。


Script. NULL処理構文関連テスト用

DROP TABLE NULL_T PURGE ;
DROP TABLE NULL_T1 PURGE ;
DROP TABLE NULL_T2 PURGE ;
DROP TABLE NULL_T3 PURGE ;


DROP TABLE NULL_T4 PURGE ;
DROP TABLE NULL_T5 PURGE ;
DROP TABLE NOT_NULL_T1 PURGE ;
DROP TABLE NOT_NULL_T2 PURGE ;

< NULL_T >

■テーブル生成

CREATE TABLE NULL_T (C1 NUMBER NOT NULL, C2 DATE, C3 VARCHAR2(10), C4 NUMBER ) ;

INSERT INTO NULL_T
SELECT  LEVEL AS C1
       ,DECODE(MOD(LEVEL,10),0,NULL,TO_DATE('20110101','YYYYMMDD')+LEVEL) AS C2
       ,DECODE(MOD(LEVEL,10),0,NULL,TO_DATE('20110101','YYYYMMDD')+LEVEL) AS C3
       ,DECODE(MOD(LEVEL,10),0,NULL,LEVEL) AS C4
  FROM DUAL
CONNECT BY LEVEL <= 100000 ;

COMMIT ;

■各カラムにインデックス生成および統計情報収集

CREATE INDEX NULL_T_IDX_01 ON NULL_T ( C1 ) ;
CREATE INDEX NULL_T_IDX_02 ON NULL_T ( C3 ) ;
CREATE INDEX NULL_T_IDX_03 ON NULL_T ( C2 ) ;

EXEC dbms_stats.gather_table_stats(OWNNAME=>'SCOTT',TABNAME=>'NULL_T',CASCADE=>TRUE,ESTIMATE_PERCENT=>100) ;

< NULL_T1 >

■テーブル生成

CREATE TABLE NULL_T1
AS
SELECT LEVEL AS C1, CHR(65+MOD(LEVEL,26)) AS C2, LEVEL+99999 AS C3
  FROM DUAL
CONNECT BY LEVEL <= 100000 ;



■各カラムにインデックス生成および統計情報収集

CREATE INDEX NULL_T1_IDX_01 ON NULL_T1 ( C2 ) ;

EXEC dbms_stats.gather_table_stats(OWNNAME=>'SCOTT',TABNAME=>'NULL_T1',CASCADE=>TRUE,ESTIMATE_PERCENT=>100) ;

< NULL_T2 >

■テーブル生成


CREATE TABLE NULL_T2
AS
SELECT LEVEL AS C1, CHR(65+MOD(LEVEL,26)) AS C2, DECODE(MOD(LEVEL,10),0,NULL,MOD(LEVEL,10)) AS C3
  FROM DUAL
CONNECT BY LEVEL <= 100000 ;

■各カラムにインデックス生成および統計情報収集

CREATE INDEX NULL_T2_IDX_01 ON NULL_T2 ( C1, C2 ) ;

EXEC dbms_stats.gather_table_stats(OWNNAME=>'SCOTT',TABNAME=>'NULL_T2',CASCADE=>TRUE,ESTIMATE_PERCENT=>100) ;

< NULL_T3 >

■テーブル生成

CREATE TABLE NULL_T3
AS
SELECT LEVEL AS C1, CHR(65+MOD(LEVEL,26)) AS C2, DECODE(MOD(LEVEL,100),0,NULL,MOD(LEVEL,100)) AS C3
  FROM DUAL
CONNECT BY LEVEL <= 100000 ;


■各カラムにインデックス生成および統計情報収集

CREATE INDEX NULL_T3_IDX_01 ON NULL_T3 ( C1, C2 ) ;

EXEC dbms_stats.gather_table_stats(OWNNAME=>'SCOTT',TABNAME=>'NULL_T3',CASCADE=>TRUE,ESTIM


ATE_PERCENT=>100) ;


< NULL_T5 >

■テーブル生成

CREATE TABLE NULL_T5 (C1 NUMBER, C2 CHAR(1), C3 CHAR(1)) ;

■データ入力

INSERT INTO NULL_T5
SELECT LEVEL, CHR(65+MOD(LEVEL,26)), DECODE(MOD(LEVEL,10000),9999,NULL,'Y')
  FROM DUAL
CONNECT BY LEVEL <= 100000 ;
COMMIT ;

■各カラムにインデックス生成および統計情報収集

CREATE INDEX NULL_T5_IDX_01 ON NULL_T5 (C3) ;

EXEC dbms_stats.gather_table_stats(OWNNAME=>'SCOTT',TABNAME=>'NULL_T5',CASCADE=>TRUE,ESTIMATE_PERCENT=>100) ;

< NOT_NULL_T1 >

■テーブル生成

CREATE TABLE NOT_NULL_T1 (C1 NUMBER, C2 CHAR(1), C3 CHAR(1)) ;

■データ入力

INSERT INTO NOT_NULL_T1
SELECT LEVEL, CHR(65+MOD(LEVEL,26)), DECODE(MOD(LEVEL,10000),9999,'Y',NULL)
  FROM DUAL
CONNECT BY LEVEL <= 500000 ;


■各カラムにインデックス生成および統計情報収集

CREATE INDEX NOT_NULL_T1_IDX_01 ON NOT_NULL_T1 (C3,C1) ;

EXEC dbms_stats.gather_table_stats(OWNNAME=>'SCOTT',TABNAME=>'NOT_NULL_T1',CASCADE=>TRUE,ESTIMATE_PERCENT=>100) ;

< NOT_NULL_T2 >

■テーブル生成


CREATE TABLE NOT_NULL_T2 (C1 NUMBER, C2 CHAR(1), C3 CHAR(1)) ;

■データ入力

INSERT INTO NOT_NULL_T2
SELECT LEVEL, CHR(65+MOD(LEVEL,26)), DECODE(MOD(LEVEL,10000),9999,'Y',NULL)
  FROM DUAL
CONNECT BY LEVEL <= 500000 ;
COMMIT ;

■各カラムにインデックス生成および統計情報収集

EXEC dbms_stats.gather_table_stats(OWNNAME=>'SCOTT',TABNAME=>'NOT_NULL_T2',CASCADE=>TRUE,ESTIMATE_PERCENT=>100) ;

DROP TABLE null_t4 ;

■テーブル生成

CREATE TABLE null_t4
( 
  login_id      NUMBER NOT NULL, 
  userid  VARCHAR2(10) NOT NULL,
  login_date VARCHAR2(8)
) ;


■データ入力

INSERT INTO NULL_T4 VALUES (  1,'user1','20100101') ;
INSERT INTO NULL_T4 VALUES (  2,'admin',NULL) ;
INSERT INTO NULL_T4 VALUES (  3,'user2','20100301') ;
INSERT INTO NULL_T4 VALUES (  6,'user1','20100601') ;
INSERT INTO NULL_T4 VALUES (  7,'admin',NULL) ;
INSERT INTO NULL_T4 VALUES (  8,'user3','20100801') ;
INSERT INTO NULL_T4 VALUES (101,'user1','20100102') ;
INSERT INTO NULL_T4 VALUES (102,'admin',NULL) ;
INSERT INTO NULL_T4 VALUES (103,'user2','20100302') ;
INSERT INTO NULL_T4 VALUES (106,'user1','20100602') ;
INSERT INTO NULL_T4 VALUES (107,'admin',NULL) ;



INSERT INTO NULL_T4 VALUES (108,'user3','20100802') ;
INSERT INTO NULL_T4 VALUES (201,'user1','20100602') ;
INSERT INTO NULL_T4 VALUES (202,'admin',NULL) ;
INSERT INTO NULL_T4 VALUES (203,'user2','20100802') ;
COMMIT ;

７.１　NULL処理関数の理解

７.１.１　NVL ()

Syntax : NVL ( expr1, expr2 )

  • NVLは、NULL値を他の値に置き換えるための関数です。
  • expr1がNULLの場合はexpr2を返し、expr1がNOT NULLの場合はexpr1を返します。
  • expr1とexpr2は複数のデータ型を持つことができますが、異なるデータ型がある場合はexpr1のデータ型に戻ります。

使用例

DATEデータ タイプ

OSのnls_langはAMERICAN_AM
ERICA.AL32UTF8と設定される。

設定変更例)set nls_lang=AMERICAN_AMERICA.AL32UTF8

SQL> SELECT NVL(c2, '01/JAN/2011') as "Date" FROM NULL_T 
     WHERE c2 is null AND rownum <= 1 ;

ERROR at line 1:
ORA-01858: a non-numeric character was found where a numeric was expected



SQL> ALTER SESSION SET nls_date_format = 'DD/MON/YYYY' ;

Session altered.
SQL> SELECT NVL(c2, '01/JAN/2011') as "Date" FROM NULL_T
      WHERE c2 is null AND rownum <= 1 ;

Date
------------
01/JAN/2011

CHARACTERデータ タイプ

SQL> SELECT NVL(c3, 'Null Data') AS "Character" FROM NULL_T 
      WHERE c3 IS NULL AND ROWNUM <= 1 ;

Character
-----------
Null Data

SQL> SELECT NVL(c3, 100) AS "Character" FROM NULL_T 
      WHERE c3 IS NULL AND ROWNUM <= 1 ;

Character
------------
100

NUMBERデータ タイプ

SQL> SELECT NVL(c4, 'Null Data') AS "Number" FROM null_t 
      WHERE c4 IS NULL AND ROWNUM <= 1 ;

ERROR at line 1:
ORA-01722: invalid number

SQL> SELECT NVL(c4, '10') AS "Number" FROM NULL_T
      WHERE c4 is null AND rownum <= 1 ;

   Number
---------
       10

SQL> SELECT NVL(c4, 10) AS "Number" FROM NULL_T WHERE C4 IS NULL AND ROWNUM <= 1 ;
   Number
---------
       10


７.１.２　NVL2 ()

Syntax : NVL2 (expr1, expr2, expr3)

  • NVL2 は、expr1 列のデータが NULL または NOT NULL かどうかに応じて異なる値を抽出したい場合に使用される関数です。
  • NVL2を通じて抽出されるデータは下記のとおりです。
　　　　-  expr1 -&gt; NOT NULL -&gt; Return: expr2
　　　　-  expr1 -&gt;          NULL -&gt; Return: expr3
  • NVL は expr1 列の NULL であるデータのみ expr2 に置き換え、NOT NULL の場合は expr1 列のデータ自体を照会しますが、NVL2 は expr1 列が NOT NULL であっても列のデータではなく特定の値に置き換えることができます。この理由により多方面で活用が可能です。
  • expr2とexpr3のデータ型は、LONGデータ型を除くすべてのデータ型が可能です。
  • expr2とexpr3のデータ タイプが違う場合にはexpr2によってデータ タイプが決定されることになるのでデータ タイプは下記のとおりとなります。
　　　　-  expr2 (CHAR)       vs. expr3 (CHAR)        -&gt; CHAR
　　　　-  expr2 (CHAR)       vs. expr3 (NUMBER)  -&gt; CHAR
　　　　-  expr2 (NUMBER) vs. expr3 (NUMBER) -&gt; NUMBER
　　　　- expr2(NUMBER) vs. expr3(CHAR) -&gt; ERROR(expr3 が '100' 型を除く)


使用例

WITH null_t_temp AS (  SELECT c3
      FROM NULL_T
     WHERE c3 IS NULL AND ROWNUM <= 1
     UNION ALL
    SELECT c3
      FROM NULL_T
     WHERE c3 is not null AND rownum <= 1 )
SELECT c3, NVL2(c3, 'Not Null', 'Null') AS Return_Data
  FROM null_t_temp ;

C3              RETURN_DATA
--------------- -----------
                      Null
2011-01-02        Not Null


WITH null_t_temp AS (  SELECT c3
      FROM NULL_T
     WHERE c3 IS NULL AND ROWNUM <= 1
     UNION ALL
    SELECT c3
      FROM NULL_T
     WHERE c3 is not null AND rownum <= 1 )
SELECT c3, NVL2(c3, 100, 100) AS Return_Data
  FROM null_t_temp ;

C3             RETURN_DATA
-------------- -----------
               100
2011-01-02     100


WITH null_t_temp AS (  SELECT c3
      FROM NULL_T
     WHERE c3 IS NULL AND ROWNUM <= 1
     UNION ALL
    SELECT c3
      FROM NULL_T
     WHERE c3 is not null AND rownum <= 1 )
SELECT c3, NVL2(c3, 100, 'Null') AS Return_Data
FROM null_t_temp ;

10行にエラー:
ORA-01722:数値が適合しません


WITH null_t_temp AS (  SELECT c3
      FROM NULL_T
     WHERE c3 is null AND rownum <= 1
     UNION ALL
    SELECT c3
      FROM NULL_T
     WHERE c3 is not null AND rownum <= 1 )
SELECT c3, NVL2(c3, 100, '95') AS Return_Data
  FROM null_t_temp ;

C3           RETURN_DATA
------------ ------------
             95
2011-01-02   100


WITH null_t_temp AS (   SELECT c3
       FROM NULL_T
      WHERE c3 IS NULL AND ROWNUM <= 1
      UNION ALL
     SELECT c3
       FROM NULL_T
     WHERE c3 is not null AND rownum <= 1 )
SELECT c3, NVL2(c3, 'Not Null', 100) AS Return_Data
  FROM null_t_temp ;

C3              RETURN_DATA
--------------- -----------
                        100
2011-01-02         Not Null



　いかがでしたでしょうか？次回（2/7)は、「NVLの活用」と題してお送りします。では、See You ^^


前の記事

prev-article-image

Oracle SQLチューニング（第24回）「DECODE & CASE WHENの理解および条件ステートメントの処理」(2/2)

次の記事

next-article-image

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

CONTACT

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

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

資料ダウンロード

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

お問い合わせ

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

03-3518-0771

平日 10時～18時

人気記事ランキング

タグ一覧

logo_img
ページトップへ戻る

©EXEM JAPAN Corp. All Rights Reserved.