Oracle12c OPTIMIZER_FEATURESによるNOT EXISTS句の実行計画の違いを確認する その1
概要
OPTIMIZER_FEATURESによるNOT EXISTS句の実行計画の違いを確認します。
検証のベースとなるSQL
select count(*) 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
COUNT(*) ---------- 900000 1 row selected. Elapsed: 00:00:00.32 Execution Plan ---------------------------------------------------------- Plan hash value: 3207213458 --------------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes |TempSpc| Cost (%CPU)| Time | --------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 1 | 42 | | 3332 (1)| 00:00:01 | | 1 | SORT AGGREGATE | | 1 | 42 | | | | |* 2 | HASH JOIN RIGHT ANTI| | 1301K| 52M| 4192K| 3332 (1)| 00:00:01 | |* 3 | TABLE ACCESS FULL | TAB_B | 104K| 2964K| | 895 (1)| 00:00:01 | | 4 | TABLE ACCESS FULL | TAB_A | 1301K| 16M| | 688 (1)| 00:00:01 | --------------------------------------------------------------------------------------- Predicate Information (identified by operation id): --------------------------------------------------- 2 - access("B2"."ID"="A"."ID") 3 - filter("B2"."STATUS" IS NULL AND MOD("B2"."ID2",10)=1) Note ----- - dynamic statistics used: dynamic sampling (level=2) Statistics ---------------------------------------------------------- 48 recursive calls 17 db block gets 5966 consistent gets 0 physical reads 3288 redo size 358 bytes sent via SQL*Net to client 500 bytes received via SQL*Net from client 2 SQL*Net roundtrips to/from client 5 sorts (memory) 0 sorts (disk) 1 rows processed
left joinしているtab_bが実行計画上にいません。どのように処理しているのでしょうか。
続いてoptimizer_features_enableを変更して、リリースバージョンによる違いを確認していきます。
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
COUNT(*) ---------- 900000 1 row selected. Elapsed: 00:00:01.21 Execution Plan ---------------------------------------------------------- Plan hash value: 4056045473 --------------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes |TempSpc| Cost (%CPU)| Time | --------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 1 | 64 | | 8831 (1)| 00:00:01 | | 1 | SORT AGGREGATE | | 1 | 64 | | | | |* 2 | HASH JOIN RIGHT ANTI| | 1301K| 79M| 4192K| 8831 (1)| 00:00:01 | |* 3 | TABLE ACCESS FULL | TAB_B | 104K| 2964K| | 895 (1)| 00:00:01 | |* 4 | HASH JOIN OUTER | | 1301K| 43M| 31M| 4834 (1)| 00:00:01 | | 5 | TABLE ACCESS FULL | TAB_A | 1301K| 16M| | 688 (1)| 00:00:01 | |* 6 | TABLE ACCESS FULL | TAB_B | 1049K| 22M| | 908 (3)| 00:00:01 | --------------------------------------------------------------------------------------- Predicate Information (identified by operation id): --------------------------------------------------- 2 - access("B2"."ID"="A"."ID") 3 - filter("B2"."STATUS" IS NULL AND MOD("B2"."ID2",10)=1) 4 - access("B"."ID"(+)="A"."ID") 6 - filter("B"."CDATE"(+)>SYSDATE@!-.041666666666666666666666666666666666666 7) Note ----- - dynamic statistics used: dynamic sampling (level=2) Statistics ---------------------------------------------------------- 32 recursive calls 10 db block gets 9069 consistent gets 0 physical reads 1036 redo size 358 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
11.2.0.4では、まずtab_aとtab_bをOUTER JOINしてから、もう1つのtab_bにANTI結合しています。
うーん、正しいです。非常に正しい。安心しました(?)
もう1つ、12.1.0.2でも確認してみます。
12.1.0.2
alter session set optimizer_features_enable = '12.1.0.2'; NAME TYPE VALUE ------------------------------------ ------------ ------------------------------ optimizer_features_enable string 12.1.0.2
COUNT(*) ---------- 900000 1 row selected. Elapsed: 00:00:00.84 Execution Plan ---------------------------------------------------------- Plan hash value: 4056045473 --------------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes |TempSpc| Cost (%CPU)| Time | --------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 1 | 64 | | 8831 (1)| 00:00:01 | | 1 | SORT AGGREGATE | | 1 | 64 | | | | |* 2 | HASH JOIN RIGHT ANTI| | 1301K| 79M| 4192K| 8831 (1)| 00:00:01 | |* 3 | TABLE ACCESS FULL | TAB_B | 104K| 2964K| | 895 (1)| 00:00:01 | |* 4 | HASH JOIN OUTER | | 1301K| 43M| 31M| 4834 (1)| 00:00:01 | | 5 | TABLE ACCESS FULL | TAB_A | 1301K| 16M| | 688 (1)| 00:00:01 | |* 6 | TABLE ACCESS FULL | TAB_B | 1049K| 22M| | 908 (3)| 00:00:01 | --------------------------------------------------------------------------------------- Predicate Information (identified by operation id): --------------------------------------------------- 2 - access("B2"."ID"="A"."ID") 3 - filter("B2"."STATUS" IS NULL AND MOD("B2"."ID2",10)=1) 4 - access("B"."ID"(+)="A"."ID") 6 - filter("B"."CDATE"(+)>SYSDATE@!-.041666666666666666666666666666666666666 7) Note ----- - dynamic statistics used: dynamic sampling (level=2) - this is an adaptive plan Statistics ---------------------------------------------------------- 433 recursive calls 8 db block gets 9697 consistent gets 527 physical reads 0 redo size 358 bytes sent via SQL*Net to client 500 bytes received via SQL*Net from client 2 SQL*Net roundtrips to/from client 16 sorts (memory) 0 sorts (disk) 1 rows processed
これは11.2.0.4と同じです。
もう少しだけ、12.2.0.1でどのように2つのtab_bを処理しているのか確認します。
12.2.0.1 advanced
select * from table(dbms_xplan.display(null, null, 'advanced'));
Plan hash value: 3207213458 --------------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes |TempSpc| Cost (%CPU)| Time | --------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 1 | 42 | | 3332 (1)| 00:00:01 | | 1 | SORT AGGREGATE | | 1 | 42 | | | | |* 2 | HASH JOIN RIGHT ANTI| | 1301K| 52M| 4192K| 3332 (1)| 00:00:01 | |* 3 | TABLE ACCESS FULL | TAB_B | 104K| 2964K| | 895 (1)| 00:00:01 | | 4 | TABLE ACCESS FULL | TAB_A | 1301K| 16M| | 688 (1)| 00:00:01 | --------------------------------------------------------------------------------------- Query Block Name / Object Alias (identified by operation id): ------------------------------------------------------------- 1 - SEL$085E11BF 3 - SEL$085E11BF / B2@SEL$3 4 - SEL$085E11BF / A@SEL$1 Outline Data ------------- /*+ BEGIN_OUTLINE_DATA SWAP_JOIN_INPUTS(@"SEL$085E11BF" "B2"@"SEL$3") USE_HASH(@"SEL$085E11BF" "B2"@"SEL$3") LEADING(@"SEL$085E11BF" "A"@"SEL$1" "B2"@"SEL$3") FULL(@"SEL$085E11BF" "B2"@"SEL$3") FULL(@"SEL$085E11BF" "A"@"SEL$1") OUTLINE(@"SEL$1") OUTLINE(@"SEL$2") ANSI_REARCH(@"SEL$1") OUTLINE(@"SEL$8812AA4E") ANSI_REARCH(@"SEL$2") OUTLINE(@"SEL$948754D7") MERGE(@"SEL$8812AA4E" >"SEL$948754D7") OUTLINE(@"SEL$2BFA4EE4") OUTLINE(@"SEL$3") ELIMINATE_JOIN(@"SEL$2BFA4EE4" "B"@"SEL$1") OUTLINE(@"SEL$56824FA9") UNNEST(@"SEL$3") OUTLINE_LEAF(@"SEL$085E11BF") ALL_ROWS DB_VERSION('12.2.0.1') OPTIMIZER_FEATURES_ENABLE('12.2.0.1') IGNORE_OPTIM_EMBEDDED_HINTS END_OUTLINE_DATA */ Predicate Information (identified by operation id): --------------------------------------------------- 2 - access("B2"."ID"="A"."ID") 3 - filter("B2"."STATUS" IS NULL AND MOD("B2"."ID2",10)=1) Column Projection Information (identified by operation id): ----------------------------------------------------------- 1 - (#keys=0) COUNT(*)[22] 2 - (#keys=1; rowset=1019) 3 - (rowset=256) "B2"."ID"[NUMBER,22] 4 - (rowset=1019) "A"."ID"[NUMBER,22] Note ----- - dynamic statistics used: dynamic sampling (level=2)
left joinしているtab_bが出てくるのは、下記のELIMINATE_JOINだけです。
ELIMINATE_JOIN(@"SEL$2BFA4EE4" "B"@"SEL$1")
マニュアルにも記載がありませんので推測になりますが、ELIMINATEは省略する、除くの意味で、
tab_aとtab_bはそれぞれTABLE FULL SCANして結合していますから、
もう1回同じテーブルを結合し直さなくても計算出来ますよ、ということなのでしょう。
何だか賢いですね。その2に続きます。