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

2025-06-09 05:57Oracle技術情報

　こんにちは。「NULL処理構文の理解と効率的なSQLの作成」の４回目は、「NULLABLEカラム使用による非効率COUNT関数処理」についてのお話です。それでは、始めましょう。

７.４　NULLABLEカラム使用による非効率COUNT関数処理

[性能問題SQL] - DB Version:9.2.0.4

SELECT a.gname,
       COUNT( a.ukey ) AS cnt
FROM imsi.addrgrp a
WHERE a.userid = :b1
GROUP BY a.gname ;

  SELECT STATEMENT CHOOSE-Cost : N/A 
    SORT GROUP BY 
     TABLE ACCESS BY INDEX ROWID IMSI.ADDRGRP(1)
      INDEX RANGE SCAN IMSI.IDX_ADDRGRP_02(NU) (USERID,GNAME)
[インデックス情報]

  INDEX_NAME       COLUMN LIST
  ---------------  -------------
  IDX_ADDRGRP_01   UKEY
  IDX_ADDRGRP_02   USERID, GNAME
  IDX_ADDRGRP_03   USERID, UKEY

　[性能問題SQL]は、特定のユーザー（USERID）が持つデータをGNAME別にカウントするSQLです。ユーザーごとにデータ件数が大きな差を見せており、データが多い特定のユーザーでCountingを行う場合には、テーブルにアクセスする回数が多く、Random I/Oが過度に発生してパフォーマンス問題を招いています。

　ところでSQLとインデックス情報を確認してみれば格別な問題が見らません。明らかにCOUNT関数で使われたカラム(UKEY)を除いたすべてのカラムがインデックスで構成されていて、COUNT関数で使われたカラムを既存インデックスに追加してテーブルアクセスをしないようにすべてのカラムをインデックスで構成するならば簡単に問題を解決することができるはずです。
　しかし、もし該当テーブルのサイズがあまり大きくて直ちにインデックスを変更することが難しいならば、該当SQLの改善法案があっても適用できない状況が発生することになることが考えられます。

　ところで[性能問題SQL]のCOUNT関数に使われたUKEYカラムが不必要に使われたとすればどうでしょうか?。COUNT(A.UKEY)の代わりにカラムを使わないCOUNT()で処理すれば、SQLにあるすべてのカラムがすでに生成されているインデックスIDX_ADDRGRP_02に含まれているので、Random I/Oによる問題は解消されるでしょう。すなわち、[性能問題SQL]はインデックス変更なしでSQL変更だけで改善が可能を意味します。 COUNT(A.UKEY)をCOUNT()に変更が可能なのかのどうかの判断はCOUNT関数のデータ処理方式を理解してこそ判断することがでます。

　先立って“グループ関数(COUNT,SUM,MAX,MIN)とNVL処理”の結果とともにCOUNT関数にカラムを利用する場合のCounting処理は該当カラムのNOT NULLであるデータだけCOUNTを実行することになりました。したがってUKEYカラムにNOT NULL制約条件が存在するならばCOUNT(*)やCOUNT(1)に変更が可能です。カラムの情報を確認した結果UKEYカラムはNOT NULL制約条件が関わっているカラムであるから[改善後SQL]のようにCOUNT関数の部分を変更すればSQLの性能を改善することができます。

[改善後SQL]

SELECT a.gname ,
       --count(a.ukey) AS cnt --->変更前
       COUNT(*) AS cnt --->変更後
  FROM imsi.addrgrp a
 WHERE a.userid = :b1
 GROUP BY a.gname ;

  SELECT STATEMENT CHOOSE-Cost : N/A 
   SORT GROUP BY 
    INDEX RANGE SCAN IMSI.IDX_ADDRGRP_02(NU) (USERID,GNAME)

-インデックス情報

  INDEX_NAME       COLUMN LIST
  ---------------  -------------
  IDX_ADDRGRP_01   UKEY
  IDX_ADDRGRP_02   USERID, GNAME
  IDX_ADDRGRP_03   USERID, UKEY

　それではテストデータを生成して、COUNT関数がNULLデータをどのように処理するのかに対して調べてみることにしましょう。

Script. テストデータ生成用

DROP TABLE NULL_T4 ;

create table NULL_T2
( 
  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 ;

- NOT NULL & NULLすべてのデータCOUNT

SELECT COUNT(*) 
  FROM null_t4
 WHERE userid = 'admin' ;

   COUNT(*)
----------
         5

SELECT COUNT(1)
  FROM null_t4
 WHERE userid = 'admin' ;

   COUNT(*)
----------
         5

-COUNT()にNOT NULLカラムでCOUNT

SELECT COUNT(login_id)
  FROM null_t4
 WHERE userid = 'admin' ;

   COUNT(*)
----------
         5 ---> NOT NULL属性で作られたカラムであるから、常にすべてのデータをCOUNTする。

-COUNT()にNULLABLEカラムでCOUNT

SELECT COUNT(login_date)
  FROM null_t4
 WHERE userid = 'admin' ;
　

COUNT(LOGIN_DATE)
-----------------
                0 ---> NULLABLEカラムはNULLであるデータを除いたローだけCOUNTする。

　前のテスト結果を見れば、COUNT関数を特定カラムで問い合わせる場合、NULLデータが存在するのかにより結果が変わることが分かります。すなわち、Counting処理時NULLであるデータは含まれません。

　したがってカラムがNOT NULL属性を有しているならばCOUNT関数処理は下記のように修正しても同じ結果値を持つことになるはずです。

    COUNT[1]. COUNT(LOGIN_ID)
    COUNT[2]. COUNT(*)
    COUNT[3]. COUNT(1)

　10g以前のバージョンではCOUNT関数で処理されるカラムを除いたすべてのカラム(Where条件時)がインデックスに含まれていても、COUNT関数で使われるカラムがインデックスに存在しないならばテーブルアクセスが発生するのですが、10g以後からはCOUNT関数で処理されるカラムのNOT NULL制約条件が存在するのか否かによりテーブルアクセスの有無が決定されます。 NOT NULL制約条件がないカラムの場合NULLデータが存在することもできるので、COUNT関数処理時、該当カラムのデータを確認してCountingを実行しなければならないですが、NOT NULL制約条件を持つカラムならば、Counting処理時Where節を満足するすべてのデータが対象になるのでCOUNT関数で使われたカラムを読むためにテーブルをアクセスしません。

　以下のテスト(DBMS Version:10.2.0.5)を見ればNOT NULL制約条件があるカラムである場合にはインデックスだけアクセスしますが、NOT NULL制約条件がなくてNullableカラムである場合にはテーブルアクセスが発生することが分かります。
CREATE INDEX IDX_null_t4_01 ON null_t4(userid) ;

-NOT NULLカラムをCOUNT

SELECT COUNT(login_id)
  FROM NULL_T4
 WHERE userid = 'admin' ;

------------------------------------------------------------------------------------
| Id  | Operation         | Name           | Rows  | Bytes | Cost (%CPU)| Time     |
------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |                |     1 |     7 |     1   (0)| 00:00:01 |
|   1 |  SORT AGGREGATE   |                |     1 |     7 |            |          |
|*  2 |   INDEX RANGE SCAN| IDX_NULL_T4_01 |     5 |    35 |     1   (0)| 00:00:01 |
------------------------------------------------------------------------------------

　インデックスはUSERIDでのみ構成されており、LOGIN_IDでCountingをするのでテーブルアクセスをしなければならないように見えますが、実行計画を見るとインデックスだけを読み、データを処理しているのがわかります。なぜなら、LOGIN_ID列がNOT NULL制約を持っていて、NULLデータを含まないことがわかるので、LOGIN_IDデータを確認する必要なく、単にWhere句を満たすデータだけCountingすればよいからです。

　しかし以下の例のようにNOT NULL制約条件がないカラムでCountingする場合には、該当カラムのデータがNULLなのかどうか確認する作業が必要なのでテーブルアクセスによる負荷を避けることはできません。

- NULLABLEカラムをCOUNT

SELECT COUNT(login_date)
  FROM NULL_T4
 WHERE userid = 'admin' ;

------------------------------------------------------------------------------------
| Id  | Operation                    | Name           | Rows  | Bytes | Cost (%CPU)|
------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT             |                |     1 |    13 |     2   (0)|
|   1 |  SORT AGGREGATE              |                |     1 |    13 |            |
|   2 |   TABLE ACCESS BY INDEX ROWID| NULL_T4        |     5 |    65 |     2   (0)|
|*  3 |    INDEX RANGE SCAN          | IDX_NULL_T4_01 |     5 |       |     1   (0)|
------------------------------------------------------------------------------------

今回は、ここまでとなります。次回は、「IS NULL照会に対する改善方法検索」についてです。それでは、see you next time ^^

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

７.４　NULLABLEカラム使用による非効率COUNT関数処理

前の記事

次の記事

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

人気記事ランキング

タグ一覧

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

７.４ NULLABLEカラム使用による非効率COUNT関数処理

前の記事

次の記事

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

人気記事ランキング

タグ一覧

７.４　NULLABLEカラム使用による非効率COUNT関数処理

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