2022.04.28
SQLチューニング（第28回）「NULL処理構文の理解と効率的なSQLの作成」（4／７回）
こんにちは。「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 ^^
