Oracle NESTED LOOPS ANTI/SEMI被驱动表的执行次数探究
正常的NL中，被驱动表的执行次数为驱动表的结果集行数。
对于NESTED LOOPS ANTI/SEMI的反连接和半连接，从探究实验的结果看，被驱动表的执行次数为驱动表的连接条件的去重后的值。
具体看如下测试。
创建测试表
10:43:26 ZKM@testdb(476)> create table t1 as select * from dba_objects; Table created. Elapsed: 00:00:00.17 10:43:31 ZKM@testdb(476)> create table t2 as select * from dba_objects; Table created. Elapsed: 00:00:00.17
反连接语句：select count(*) from t1 where not exists ( select /*+ nl_aj */ 1 from t2 where t1.owner=t2.owner);
半连接语句： select count(*) from t1 where exists ( select /*+ nl_sj */ 1 from t2 where t1.owner=t2.owner);
反连接/半连接次数：distinct后值为16
10:43:34 ZKM@testdb(476)> select count(distinct owner) from t1; COUNT(DISTINCTOWNER) -------------------- 16 Elapsed: 00:00:00.03 10:43:40 ZKM@testdb(476)> select count(*) from t1 where not exists ( select /*+ nl_aj */ 1 from t2 where t1.owner=t2.owner); COUNT(*) ---------- 0 Elapsed: 00:00:00.07 10:43:44 ZKM@testdb(476)> select * from table(dbms_xplan.display_cursor(null,null,'allstats last')); PLAN_TABLE_OUTPUT ------------------------------------------------------------------------------------------------------ SQL_ID 1wkqm7d8319gw, child number 1 ------------------------------------- select count(*) from t1 where not exists ( select /*+ nl_aj */ 1 from t2 where t1.owner=t2.owner) Plan hash value: 4091582738 ----------------------------------------------------------------------------------------------- | Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers | Reads | ----------------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 1 | | 1 |00:00:00.04 | 2851 | 70 | | 1 | SORT AGGREGATE | | 1 | 1 | 1 |00:00:00.04 | 2851 | 70 | | 2 | NESTED LOOPS ANTI | | 1 | 16486 | 0 |00:00:00.03 | 2851 | 70 | | 3 | TABLE ACCESS FULL| T1 | 1 | 16486 | 19031 |00:00:00.01 | 259 | 0 | |* 4 | TABLE ACCESS FULL| T2 | 16 | 1 | 16 |00:00:00.02 | 2592 | 70 | ----------------------------------------------------------------------------------------------- Predicate Information (identified by operation id): --------------------------------------------------- 4 - filter("T1"."OWNER"="T2"."OWNER") Note ----- - dynamic sampling used for this statement (level=2) 26 rows selected. Elapsed: 00:00:00.02 10:43:48 ZKM@testdb(476)> select count(*) from t1 where exists ( select /*+ nl_sj */ 1 from t2 where t1.owner=t2.owner); COUNT(*) ---------- 19031 Elapsed: 00:00:00.03 10:44:13 ZKM@testdb(476)> select * from table(dbms_xplan.display_cursor(null,null,'allstats last')); PLAN_TABLE_OUTPUT ------------------------------------------------------------------------------------------------------- SQL_ID a96rmsu79t8pk, child number 0 ------------------------------------- select count(*) from t1 where exists ( select /*+ nl_sj */ 1 from t2 where t1.owner=t2.owner) Plan hash value: 2128633509 -------------------------------------------------------------------------------------- | Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers | -------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 1 | | 1 |00:00:00.03 | 2851 | | 1 | SORT AGGREGATE | | 1 | 1 | 1 |00:00:00.03 | 2851 | | 2 | NESTED LOOPS SEMI | | 1 | 12681 | 19031 |00:00:00.03 | 2851 | | 3 | TABLE ACCESS FULL| T1 | 1 | 16486 | 19031 |00:00:00.01 | 259 | |* 4 | TABLE ACCESS FULL| T2 | 16 | 15680 | 16 |00:00:00.01 | 2592 | -------------------------------------------------------------------------------------- Predicate Information (identified by operation id): --------------------------------------------------- 4 - filter("T1"."OWNER"="T2"."OWNER") Note ----- - dynamic sampling used for this statement (level=2) 26 rows selected. Elapsed: 00:00:00.02
反连接/半连接次数：distinct后值为17
10:44:35 ZKM@testdb(476)> update t1 set owner='OTHERNAME' where object_id=35950; 1 row updated. Elapsed: 00:00:00.00 10:44:36 ZKM@testdb(476)> commit; Commit complete. Elapsed: 00:00:00.00 10:44:41 ZKM@testdb(476)> select count(distinct owner) from t1; COUNT(DISTINCTOWNER) -------------------- 17 Elapsed: 00:00:00.01 10:44:44 ZKM@testdb(476)> select count(*) from t1 where not exists ( select /*+ nl_aj */ 1 from t2 where t1.owner=t2.owner); COUNT(*) ---------- 1 Elapsed: 00:00:00.03 10:44:52 ZKM@testdb(476)> select * from table(dbms_xplan.display_cursor(null,null,'allstats last')); PLAN_TABLE_OUTPUT ------------------------------------------------------------------------------------------------------- SQL_ID 1wkqm7d8319gw, child number 1 ------------------------------------- select count(*) from t1 where not exists ( select /*+ nl_aj */ 1 from t2 where t1.owner=t2.owner) Plan hash value: 4091582738 -------------------------------------------------------------------------------------- | Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers | -------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 1 | | 1 |00:00:00.03 | 3110 | | 1 | SORT AGGREGATE | | 1 | 1 | 1 |00:00:00.03 | 3110 | | 2 | NESTED LOOPS ANTI | | 1 | 16486 | 1 |00:00:00.03 | 3110 | | 3 | TABLE ACCESS FULL| T1 | 1 | 16486 | 19031 |00:00:00.01 | 259 | |* 4 | TABLE ACCESS FULL| T2 | 17 | 1 | 16 |00:00:00.02 | 2851 | -------------------------------------------------------------------------------------- Predicate Information (identified by operation id): --------------------------------------------------- 4 - filter("T1"."OWNER"="T2"."OWNER") Note ----- - dynamic sampling used for this statement (level=2) 26 rows selected. Elapsed: 00:00:00.03 10:44:54 ZKM@testdb(476)> select count(*) from t1 where exists ( select /*+ nl_sj */ 1 from t2 where t1.owner=t2.owner); COUNT(*) ---------- 19030 Elapsed: 00:00:00.04 10:45:00 ZKM@testdb(476)> select * from table(dbms_xplan.display_cursor(null,null,'allstats last')); PLAN_TABLE_OUTPUT ------------------------------------------------------------------------------------------------------- SQL_ID a96rmsu79t8pk, child number 0 ------------------------------------- select count(*) from t1 where exists ( select /*+ nl_sj */ 1 from t2 where t1.owner=t2.owner) Plan hash value: 2128633509 -------------------------------------------------------------------------------------- | Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers | -------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 1 | | 1 |00:00:00.03 | 3110 | | 1 | SORT AGGREGATE | | 1 | 1 | 1 |00:00:00.03 | 3110 | | 2 | NESTED LOOPS SEMI | | 1 | 12681 | 19030 |00:00:00.03 | 3110 | | 3 | TABLE ACCESS FULL| T1 | 1 | 16486 | 19031 |00:00:00.01 | 259 | |* 4 | TABLE ACCESS FULL| T2 | 17 | 15680 | 16 |00:00:00.01 | 2851 | -------------------------------------------------------------------------------------- Predicate Information (identified by operation id): --------------------------------------------------- 4 - filter("T1"."OWNER"="T2"."OWNER") Note ----- - dynamic sampling used for this statement (level=2) 26 rows selected. Elapsed: 00:00:00.02
反连接/半连接次数：distinct后值为1
10:46:38 ZKM@testdb(476)> update t1 set owner='OTHERNAME'; 19031 rows updated. Elapsed: 00:00:00.62 10:46:40 ZKM@testdb(476)> commit; Commit complete. Elapsed: 00:00:00.01 10:46:43 ZKM@testdb(476)> select count(distinct owner) from t1; COUNT(DISTINCTOWNER) -------------------- 1 Elapsed: 00:00:00.00 10:46:46 ZKM@testdb(476)> select count(*) from t1 where not exists ( select /*+ nl_aj */ 1 from t2 where t1.owner=t2.owner); COUNT(*) ---------- 19031 Elapsed: 00:00:00.02 10:46:52 ZKM@testdb(476)> select * from table(dbms_xplan.display_cursor(null,null,'allstats last')); PLAN_TABLE_OUTPUT ------------------------------------------------------------------------------------------------------- SQL_ID 1wkqm7d8319gw, child number 1 ------------------------------------- select count(*) from t1 where not exists ( select /*+ nl_aj */ 1 from t2 where t1.owner=t2.owner) Plan hash value: 4091582738 -------------------------------------------------------------------------------------- | Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers | -------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 1 | | 1 |00:00:00.02 | 518 | | 1 | SORT AGGREGATE | | 1 | 1 | 1 |00:00:00.02 | 518 | | 2 | NESTED LOOPS ANTI | | 1 | 16486 | 19031 |00:00:00.02 | 518 | | 3 | TABLE ACCESS FULL| T1 | 1 | 16486 | 19031 |00:00:00.01 | 259 | |* 4 | TABLE ACCESS FULL| T2 | 1 | 1 | 0 |00:00:00.01 | 259 | -------------------------------------------------------------------------------------- Predicate Information (identified by operation id): --------------------------------------------------- 4 - filter("T1"."OWNER"="T2"."OWNER") Note ----- - dynamic sampling used for this statement (level=2) 26 rows selected. Elapsed: 00:00:00.02 10:46:52 ZKM@testdb(476)> select count(*) from t1 where exists ( select /*+ nl_sj */ 1 from t2 where t1.owner=t2.owner); COUNT(*) ---------- 0 Elapsed: 00:00:00.01 10:47:06 ZKM@testdb(476)> select * from table(dbms_xplan.display_cursor(null,null,'allstats last')); PLAN_TABLE_OUTPUT -------------------------------------------------------------------------------------------------------- SQL_ID a96rmsu79t8pk, child number 0 ------------------------------------- select count(*) from t1 where exists ( select /*+ nl_sj */ 1 from t2 where t1.owner=t2.owner) Plan hash value: 2128633509 -------------------------------------------------------------------------------------- | Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers | -------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 1 | | 1 |00:00:00.01 | 518 | | 1 | SORT AGGREGATE | | 1 | 1 | 1 |00:00:00.01 | 518 | | 2 | NESTED LOOPS SEMI | | 1 | 12681 | 0 |00:00:00.01 | 518 | | 3 | TABLE ACCESS FULL| T1 | 1 | 16486 | 19031 |00:00:00.01 | 259 | |* 4 | TABLE ACCESS FULL| T2 | 1 | 15680 | 0 |00:00:00.01 | 259 | -------------------------------------------------------------------------------------- Predicate Information (identified by operation id): --------------------------------------------------- 4 - filter("T1"."OWNER"="T2"."OWNER") Note ----- - dynamic sampling used for this statement (level=2) 26 rows selected. Elapsed: 00:00:00.02
总结如下：
| count(distinct) | 17 | 16 | 1 |
| 驱动表访问次数 | 1 | 1 | 1 |
| 被驱动表访问次数 | 17 | 16 | 1 |