データメンテ等でそのキーに紐づくデータがあるか無いか確認したいことがあります。こんな時にどんなSQLで確認したらいいかについて考えてみました。
検証環境
検証用TBL
- STR1 CHAR(8) *ユニークインデックス有り
- STR2 VARCAHR(120)
- データ件数 120件
?全テーブルのCOUNT(*)をチェックする
SELECT 'データは存在しません' FROM(SELECT COUNT(*) AS CNT FROM TEST WHERE STR1 = 'GARA' ) V1, (SELECT COUNT(*) AS CNT FROM TEST WHERE STR1 = 'GARA' ) V2 WHERE V1.CNT = 0 AND V2.CNT = 0
?COUNT(*)の結果をUNIONALLする
SELECT SUM(CNT) FROM (SELECT COUNT(*) AS CNT FROM TEST WHERE STR1 = 'GARA' UNION ALL SELECT COUNT(*) AS CNT FROM TEST WHERE STR1 = 'GARA' )V
?NOTEXISTS句で繋ぐ
SELECT 'データは存在しません' FROM DUAL WHERE NOT EXISTS (SELECT * FROM TEST WHERE STR1 = 'GARA' ) AND NOT EXISTS (SELECT * FROM TEST WHERE STR1 = 'GARA' )
?NOTEXISTS句の中でUNIONする
SELECT 'データは存在しません' FROM DUAL WHERE NOT EXISTS (SELECT * FROM TEST WHERE STR1 = 'GARA' UNION ALL SELECT * FROM TEST WHERE STR1 = 'GARA' )
多分こんなのが思い浮かびます。（2個TESTテーブルを検索しているのはTEST1とTEST2だとでも思ってください）
んでこんなののうちどれが良いのか、実行計画を見てみる。
?全テーブルのCOUNT(*)をチェックする
------------------------------------------------------------------------------ | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | ------------------------------------------------------------------------------ | 0 | SELECT STATEMENT | | 1 | 26 | 0 (0)| 00:00:01 | | 1 | NESTED LOOPS | | 1 | 26 | 0 (0)| 00:00:01 | | 2 | VIEW | | 1 | 13 | 0 (0)| 00:00:01 | |* 3 | FILTER | | | | | | | 4 | SORT AGGREGATE | | 1 | 9 | | | |* 5 | INDEX UNIQUE SCAN| GARA | 1 | 9 | 0 (0)| 00:00:01 | | 6 | VIEW | | 1 | 13 | 0 (0)| 00:00:01 | |* 7 | FILTER | | | | | | | 8 | SORT AGGREGATE | | 1 | 9 | | | |* 9 | INDEX UNIQUE SCAN| GARA | 1 | 9 | 0 (0)| 00:00:01 | ------------------------------------------------------------------------------ Predicate Information (identified by operation id): --------------------------------------------------- 3 - filter(COUNT(*)=0) 5 - access("STR1"='GARA') 7 - filter(COUNT(*)=0) 9 - access("STR1"='GARA') 統計 ---------------------------------------------------------- 136 recursive calls 0 db block gets 21 consistent gets 0 physical reads 0 redo size 281 bytes sent via SQL*Net to client 374 bytes received via SQL*Net from client 1 SQL*Net roundtrips to/from client 6 sorts (memory) 0 sorts (disk) 0 rows processed
テーブル毎に検索をカウント。それをNL結合している。
ソートの発生とrecursive callsの多さに注目
?COUNT(*)の結果をUNIONALLする
------------------------------------------------------------------------------ | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | ------------------------------------------------------------------------------ | 0 | SELECT STATEMENT | | 1 | 13 | 0 (0)| 00:00:01 | | 1 | SORT AGGREGATE | | 1 | 13 | | | | 2 | VIEW | | 2 | 26 | 0 (0)| 00:00:01 | | 3 | UNION-ALL | | | | | | | 4 | SORT AGGREGATE | | 1 | 9 | | | |* 5 | INDEX UNIQUE SCAN| GARA | 1 | 9 | 0 (0)| 00:00:01 | | 6 | SORT AGGREGATE | | 1 | 9 | | | |* 7 | INDEX UNIQUE SCAN| GARA | 1 | 9 | 0 (0)| 00:00:01 | ------------------------------------------------------------------------------ Predicate Information (identified by operation id): --------------------------------------------------- 5 - access("STR1"='GARA') 7 - access("STR1"='GARA') 統計 ---------------------------------------------------------- 1 recursive calls 0 db block gets 2 consistent gets 0 physical reads 0 redo size 422 bytes sent via SQL*Net to client 385 bytes received via SQL*Net from client 2 SQL*Net roundtrips to/from client 0 sorts (memory) 0 sorts (disk) 1 rows processed
テーブル毎に検索をカウント。それをUNIONしてカウントしてる。
実行計画的には?とほぼ同等だが統計としてはconsistent getsが減っており優れている。
通信量が増えSQL*Net roundtripsが2回に増えている。（なんでじゃ？）
?NOTEXISTS句で繋ぐ
---------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | ---------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 1 | | 2 (0)| 00:00:01 | |* 1 | FILTER | | | | | | | 2 | FAST DUAL | | 1 | | 2 (0)| 00:00:01 | |* 3 | INDEX UNIQUE SCAN | GARA | 1 | 9 | 0 (0)| 00:00:01 | |* 4 | INDEX UNIQUE SCAN| GARA | 1 | 9 | 0 (0)| 00:00:01 | ---------------------------------------------------------------------------- Predicate Information (identified by operation id): --------------------------------------------------- 1 - filter( NOT EXISTS (SELECT /*+ */ 0 FROM "TEST" "TEST" WHERE "STR1"='GARA') AND NOT EXISTS (SELECT /*+ */ 0 FROM "TEST" "TEST" WHERE "STR1"='GARA')) 3 - access("STR1"='GARA') 4 - access("STR1"='GARA') 統計 ---------------------------------------------------------- 1 recursive calls 0 db block gets 1 consistent gets 0 physical reads 0 redo size 281 bytes sent via SQL*Net to client 374 bytes received via SQL*Net from client 1 SQL*Net roundtrips to/from client 0 sorts (memory) 0 sorts (disk) 0 rows processed
実行計画がシンプルに。ソートが消えBytesが低下。
統計も良好。
?NOTEXISTS句の中でUNIONする
---------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | ---------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 1 | | 2 (0)| 00:00:01 | |* 1 | FILTER | | | | | | | 2 | FAST DUAL | | 1 | | 2 (0)| 00:00:01 | | 3 | UNION-ALL | | | | | | |* 4 | INDEX UNIQUE SCAN| GARA | 1 | 9 | 0 (0)| 00:00:01 | |* 5 | INDEX UNIQUE SCAN| GARA | 1 | 9 | 0 (0)| 00:00:01 | ---------------------------------------------------------------------------- Predicate Information (identified by operation id): --------------------------------------------------- 1 - filter( NOT EXISTS ( (SELECT /*+ */ 1 FROM "TEST" "TEST" WHERE "STR1"='GARA') UNION ALL (SELECT /*+ */ 1 FROM "TEST" "TEST" WHERE "STR1"='GARA'))) 4 - access("STR1"='GARA') 5 - access("STR1"='GARA') 統計 ---------------------------------------------------------- 1 recursive calls 0 db block gets 1 consistent gets 0 physical reads 0 redo size 281 bytes sent via SQL*Net to client 374 bytes received via SQL*Net from client 1 SQL*Net roundtrips to/from client 0 sorts (memory) 0 sorts (disk) 0 rows processed
?に比べUNION分が増加。増加とはいってもUNIONはソートも何もしないので微増といったころ
しかしfilterがUNIONに対して動くためデータが存在した場合?より非効率。
結論
「?NOTEXISTS句で繋ぐ」が最も優秀である。
ANDの演算は1件FALSEになった時点で評価を取りやめるので一時に比較をする?に勝る。
・・・・あってるはず。。。
間違ってたら突っ込みください。
