Oracle NESTED LOOPS ANTI/SEMI被驱动表的执行次数探究

 

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
posted @   PiscesCanon  阅读(563)  评论(0)  编辑  收藏  举报
编辑推荐：
· AI与.NET技术实操系列（二）：开始使用ML.NET
· 记一次.NET内存居高不下排查解决与启示
· 探究高空视频全景AR技术的实现原理
· 理解Rust引用及其生命周期标识（上）
· 浏览器原生「磁吸」效果！Anchor Positioning 锚点定位神器解析
阅读排行：
· DeepSeek 开源周回顾「GitHub 热点速览」
· 物流快递公司核心技术能力-地址解析分单基础技术分享
· .NET 10首个预览版发布：重大改进与新特性概览！
· AI与.NET技术实操系列（二）：开始使用ML.NET
· 单线程的Redis速度为什么快？
点击右上角即可分享
微信分享提示
AI FOR CODE 大赛