Oracle12c OPTIMIZER_FEATURESによるNOT EXISTS句の実行計画の違いを確認する その2
概要
OPTIMIZER_FEATURESによるNOT EXISTS句の実行計画の違いを確認します。
その1ではSELECT句をcount(*)のみにしたことで、
12.2.0.1のELIMINATE_JOINにより、tab_bのJOINが省略される実行計画となりましたので、
tab_bのカラムをSELECT句に入れたSQLで確認してみます。
検証のベースとなるSQL
select max(a.cdate) , max(b.cdate) from tab_a a left join tab_b b on b.id = a.id and b.cdate > sysdate - 1/24 where not exists ( select 1 from tab_b b2 where b2.id = a.id and mod( b2.id2, 10 ) = 1 and b2.status is null )
Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production CentOS Linux release 7.3.1611 (Core)
検証
実行計画の確認
12.2.0.1
NAME TYPE VALUE ------------------------------------ ------------ ------------------------------ optimizer_features_enable string 12.2.0.1
MAX(A.CDATE) MAX(B.CDATE) ------------------- ------------------- 2017/09/08 15:02:54 1 row selected. Elapsed: 00:00:04.85 Execution Plan ---------------------------------------------------------- Plan hash value: 2392446093 ---------------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes |TempSpc| Cost (%CPU)| Time | ---------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 1 | 73 | | 4799 (2)| 00:00:01 | | 1 | SORT AGGREGATE | | 1 | 73 | | | | |* 2 | HASH JOIN RIGHT OUTER| | 1301K| 90M| | 4799 (2)| 00:00:01 | |* 3 | TABLE ACCESS FULL | TAB_B | 36 | 792 | | 908 (3)| 00:00:01 | |* 4 | HASH JOIN RIGHT ANTI| | 1301K| 63M| 4192K| 3887 (1)| 00:00:01 | |* 5 | TABLE ACCESS FULL | TAB_B | 104K| 2964K| | 895 (1)| 00:00:01 | | 6 | TABLE ACCESS FULL | TAB_A | 1301K| 27M| | 689 (2)| 00:00:01 | ---------------------------------------------------------------------------------------- Predicate Information (identified by operation id): --------------------------------------------------- 2 - access("B"."ID"(+)="A"."ID") 3 - filter("B"."CDATE"(+)>SYSDATE@!-.0416666666666666666666666666666666666667 ) 4 - access("B2"."ID"="A"."ID") 5 - filter("B2"."STATUS" IS NULL AND MOD("B2"."ID2",10)=1) Note ----- - dynamic statistics used: dynamic sampling (level=2) Statistics ---------------------------------------------------------- 11 recursive calls 0 db block gets 8971 consistent gets 0 physical reads 0 redo size 430 bytes sent via SQL*Net to client 500 bytes received via SQL*Net from client 2 SQL*Net roundtrips to/from client 0 sorts (memory) 0 sorts (disk) 1 rows processed
当然ELIMINATE_JOINは出現せず、ごく当たり前の実行計画となりました。
ですが、全てのテーブルをFULLSCANしてHASHJOINするという、
ちょっと乱暴な実行計画になっているので、ヒント句でNestedLoopにします。
1 select /*+ use_nl(a b) */ 2 max(a.cdate) 3 , max(b.cdate) 4 from tab_a a 5 left join tab_b b 6 on b.id = a.id 7 and b.cdate > sysdate - 1/24 8 where not exists ( select /*+ use_nl(b2) */ 9 1 10 from tab_b b2 11 where b2.id = a.id 12 and mod( b2.id2, 10 ) = 1 13 and b2.status is null 14* ) 15 / MAX(A.CDATE) MAX(B.CDATE) ------------------- ------------------- 2017/09/08 15:02:54 1 row selected. Elapsed: 00:00:06.12 Execution Plan ---------------------------------------------------------- Plan hash value: 2565967754 --------------------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | --------------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 1 | 73 | 3654K (1)| 00:02:23 | | 1 | SORT AGGREGATE | | 1 | 73 | | | | 2 | NESTED LOOPS ANTI | | 1301K| 90M| 3654K (1)| 00:02:23 | | 3 | NESTED LOOPS OUTER | | 1301K| 54M| 2351K (1)| 00:01:32 | | 4 | TABLE ACCESS FULL | TAB_A | 1301K| 27M| 689 (2)| 00:00:01 | |* 5 | TABLE ACCESS BY INDEX ROWID| TAB_B | 1 | 22 | 2 (0)| 00:00:01 | |* 6 | INDEX UNIQUE SCAN | IDX1_TAB_B | 1 | | 1 (0)| 00:00:01 | |* 7 | TABLE ACCESS BY INDEX ROWID | TAB_B | 1 | 29 | 1 (0)| 00:00:01 | |* 8 | INDEX UNIQUE SCAN | IDX1_TAB_B | 1 | | 1 (0)| 00:00:01 | --------------------------------------------------------------------------------------------- Predicate Information (identified by operation id): --------------------------------------------------- 5 - filter("B"."CDATE"(+)>SYSDATE@!-.0416666666666666666666666666666666666667) 6 - access("B"."ID"(+)="A"."ID") 7 - filter("B2"."STATUS" IS NULL AND MOD("B2"."ID2",10)=1) 8 - access("B2"."ID"="A"."ID") Note ----- - dynamic statistics used: dynamic sampling (level=2) Statistics ---------------------------------------------------------- 11 recursive calls 0 db block gets 117577 consistent gets 2091 physical reads 0 redo size 430 bytes sent via SQL*Net to client 500 bytes received via SQL*Net from client 2 SQL*Net roundtrips to/from client 0 sorts (memory) 0 sorts (disk) 1 rows processed
普通にどちらのtab_bもヒント句が効いて、NestedLoopになりました。
まあこれが効かないようでは困ります。
次は、特に理由はありませんが、ヒント句で結合順がNOT EXISTS句が
先にすることが出来るか確認してみます。
1 select /*+ use_nl(a b) leading(a b2 b) */ 2 max(a.cdate) 3 , max(b.cdate) 4 from tab_a a 5 left join tab_b b 6 on b.id = a.id 7 and b.cdate > sysdate - 1/24 8 where not exists ( select /*+ use_nl(b2) */ 9 1 10 from tab_b b2 11 where b2.id = a.id 12 and mod( b2.id2, 10 ) = 1 13 and b2.status is null 14* ) 15 / MAX(A.CDATE) MAX(B.CDATE) ------------------- ------------------- 2017/09/08 15:02:54 1 row selected. Elapsed: 00:00:01.29 Execution Plan ---------------------------------------------------------- Plan hash value: 2649977225 --------------------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | --------------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 1 | 73 | 4703K (1)| 00:03:04 | | 1 | SORT AGGREGATE | | 1 | 73 | | | | 2 | NESTED LOOPS OUTER | | 1301K| 90M| 4703K (1)| 00:03:04 | | 3 | NESTED LOOPS ANTI | | 1301K| 63M| 2351K (1)| 00:01:32 | | 4 | TABLE ACCESS FULL | TAB_A | 1301K| 27M| 689 (2)| 00:00:01 | |* 5 | TABLE ACCESS BY INDEX ROWID| TAB_B | 1 | 29 | 2 (0)| 00:00:01 | |* 6 | INDEX UNIQUE SCAN | IDX1_TAB_B | 1 | | 1 (0)| 00:00:01 | |* 7 | TABLE ACCESS BY INDEX ROWID | TAB_B | 1 | 22 | 2 (0)| 00:00:01 | |* 8 | INDEX UNIQUE SCAN | IDX1_TAB_B | 1 | | 1 (0)| 00:00:01 | --------------------------------------------------------------------------------------------- Predicate Information (identified by operation id): --------------------------------------------------- 5 - filter("B2"."STATUS" IS NULL AND MOD("B2"."ID2",10)=1) 6 - access("B2"."ID"="A"."ID") 7 - filter("B"."CDATE"(+)>SYSDATE@!-.0416666666666666666666666666666666666667) 8 - access("B"."ID"(+)="A"."ID") Note ----- - dynamic statistics used: dynamic sampling (level=2) Statistics ---------------------------------------------------------- 208 recursive calls 6 db block gets 116259 consistent gets 0 physical reads 0 redo size 430 bytes sent via SQL*Net to client 500 bytes received via SQL*Net from client 2 SQL*Net roundtrips to/from client 15 sorts (memory) 0 sorts (disk) 1 rows processed
Predicate Informationを見ると、LEADINGヒント句通りに結合しているのがわかります。
次は、同じSQLで11.2.0.4の実行計画を確認してみます。
alter session set optimizer_features_enable = '11.2.0.4'; NAME TYPE VALUE ------------------------------------ ------------ ------------------------------ optimizer_features_enable string 11.2.0.4
1 select /*+ use_nl(a b) leading(a b2 b) */ 2 max(a.cdate) 3 , max(b.cdate) 4 from tab_a a 5 left join tab_b b 6 on b.id = a.id 7 and b.cdate > sysdate - 1/24 8 where not exists ( select /*+ use_nl(b2) */ 9 1 10 from tab_b b2 11 where b2.id = a.id 12 and mod( b2.id2, 10 ) = 1 13 and b2.status is null 14* ) 15 / MAX(A.CDATE) MAX(B.CDATE) ------------------- ------------------- 2017/09/08 15:02:54 1 row selected. Elapsed: 00:00:01.28 Execution Plan ---------------------------------------------------------- Plan hash value: 2649977225 --------------------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | --------------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 1 | 73 | 4703K (1)| 00:03:04 | | 1 | SORT AGGREGATE | | 1 | 73 | | | | 2 | NESTED LOOPS OUTER | | 1301K| 90M| 4703K (1)| 00:03:04 | | 3 | NESTED LOOPS ANTI | | 1301K| 63M| 2351K (1)| 00:01:32 | | 4 | TABLE ACCESS FULL | TAB_A | 1301K| 27M| 689 (2)| 00:00:01 | |* 5 | TABLE ACCESS BY INDEX ROWID| TAB_B | 1 | 29 | 2 (0)| 00:00:01 | |* 6 | INDEX UNIQUE SCAN | IDX1_TAB_B | 1 | | 1 (0)| 00:00:01 | |* 7 | TABLE ACCESS BY INDEX ROWID | TAB_B | 1 | 22 | 2 (0)| 00:00:01 | |* 8 | INDEX UNIQUE SCAN | IDX1_TAB_B | 1 | | 1 (0)| 00:00:01 | --------------------------------------------------------------------------------------------- Predicate Information (identified by operation id): --------------------------------------------------- 5 - filter("B2"."STATUS" IS NULL AND MOD("B2"."ID2",10)=1) 6 - access("B2"."ID"="A"."ID") 7 - filter("B"."CDATE"(+)>SYSDATE@!-.0416666666666666666666666666666666666667) 8 - access("B"."ID"(+)="A"."ID") Note ----- - dynamic statistics used: dynamic sampling (level=2) Statistics ---------------------------------------------------------- 21 recursive calls 4 db block gets 116063 consistent gets 0 physical reads 0 redo size 430 bytes sent via SQL*Net to client 500 bytes received via SQL*Net from client 2 SQL*Net roundtrips to/from client 0 sorts (memory) 0 sorts (disk) 1 rows processed
12.2.0.1と変わらずでした。何かちょっとつまらない確認になりましたが、
変わらないということを確認することも重要ですね。きっと。