结论： NL连接中，驱动表被访问0或者1次，被驱动表被访问0次或者N次，N由驱动表返回的结果集的条数来定）
*/
--环境构造
--研究Nested Loops Join的表访问次数前准备工作
DROP TABLE t1 CASCADE CONSTRAINTS PURGE;
DROP TABLE t2 CASCADE CONSTRAINTS PURGE;
CREATE TABLE t1 (
id NUMBER NOT NULL,
n NUMBER,
contents VARCHAR2(4000)
)
;
CREATE TABLE t2 (
id NUMBER NOT NULL,
t1_id NUMBER NOT NULL,
n NUMBER,
contents VARCHAR2(4000)
)
;
execute dbms_random.seed(0);
INSERT INTO t1
SELECT rownum, rownum, dbms_random.string('a', 50)
FROM dual
CONNECT BY level <= 100
ORDER BY dbms_random.random;
INSERT INTO t2 SELECT rownum, rownum, rownum, dbms_random.string('b', 50) FROM dual CONNECT BY level <= 100000
ORDER BY dbms_random.random;
COMMIT;
select count(*) from t1;
select count(*) from t2;
--我们用设置statistics_level=all的方式来观察如下表连接语句的执行计划：
--T2表被访问100次(驱动表访问1次，被驱动表访问100次）
--这个set linesize 1000对dbms_xplan.display_cursor还是有影响的，如果没有设置，默认情况下的输出，将会少了很多列，如BUFFERS等
Set linesize 1000
alter session set statistics_level=all ;
SELECT /*+ leading(t1) use_nl(t2)*/ *
FROM t1, t2
WHERE t1.id = t2.t1_id;
--略去记录结果
select * from table(dbms_xplan.display_cursor(null,null,'allstats last'));
-------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers |
-------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | 100 |00:00:00.94 | 100K|
| 1 | NESTED LOOPS | | 1 | 100 | 100 |00:00:00.94 | 100K|
| 2 | TABLE ACCESS FULL| T1 | 1 | 100 | 100 |00:00:00.01 | 14 |
|* 3 | TABLE ACCESS FULL| T2 | 100 | 1 | 100 |00:00:00.94 | 100K|
-------------------------------------------------------------------------------------
3 - filter("T1"."ID"="T2"."T1_ID")
---换个语句，这次T2表被访问2次(驱动表访问1次，被驱动表访问2次）
Set linesize 1000
alter session set statistics_level=all ;
SELECT /*+ leading(t1) use_nl(t2) */ *
FROM t1, t2
WHERE t1.id = t2.t1_id
AND t1.n in(17, 19);
select * from table(dbms_xplan.display_cursor(null,null,'allstats last'));
-------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers |
-------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | 2 |00:00:00.02 | 2019 |
| 1 | NESTED LOOPS | | 1 | 2 | 2 |00:00:00.02 | 2019 |
|* 2 | TABLE ACCESS FULL| T1 | 1 | 2 | 2 |00:00:00.01 | 8 |
|* 3 | TABLE ACCESS FULL| T2 | 2 | 1 | 2 |00:00:00.02 | 2011 |
-------------------------------------------------------------------------------------
2 - filter(("T1"."N"=17 OR "T1"."N"=19))
3 - filter("T1"."ID"="T2"."T1_ID")
--继续换个语句，这次T2表被访问1次(驱动表访问1次，被驱动表访问1次）
Set linesize 1000
alter session set statistics_level=all ;
SELECT /*+ leading(t1) use_nl(t2) */ *
FROM t1, t2
WHERE t1.id = t2.t1_id
AND t1.n = 19;
select * from table(dbms_xplan.display_cursor(null,null,'allstats last'));
SQL> select * from table(dbms_xplan.display_cursor(null,null,'allstats last'));
-------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers |
-------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | 1 |00:00:00.01 | 1014 |
| 1 | NESTED LOOPS | | 1 | 1 | 1 |00:00:00.01 | 1014 |
|* 2 | TABLE ACCESS FULL| T1 | 1 | 1 | 1 |00:00:00.01 | 8 |
|* 3 | TABLE ACCESS FULL| T2 | 1 | 1 | 1 |00:00:00.01 | 1006 |
-------------------------------------------------------------------------------------
2 - filter("T1"."N"=19)
3 - filter("T1"."ID"="T2"."T1_ID")
---接下来，T2表居然被访问0次(驱动表访问1次，被驱动表访问0次）
SELECT /*+ leading(t1) use_nl(t2) */ *
FROM t1, t2
WHERE t1.id = t2.t1_id
AND t1.n = 999999999;
select * from table(dbms_xplan.display_cursor(null,null,'allstats last'));
-------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers |
-------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | 0 |00:00:00.01 | 7 |
| 1 | NESTED LOOPS | | 1 | 1 | 0 |00:00:00.01 | 7 |
|* 2 | TABLE ACCESS FULL| T1 | 1 | 1 | 0 |00:00:00.01 | 7 |
|* 3 | TABLE ACCESS FULL| T2 | 0 | 1 | 0 |00:00:00.01 | 0 |
-------------------------------------------------------------------------------------
2 - filter("T1"."N"=999999999)
3 - filter("T1"."ID"="T2"."T1_ID")
---到最后，不只是T2表被访问0次，连T1表也访问0次
SELECT /*+ leading(t1) use_nl(t2) */ *
FROM t1, t2
WHERE t1.id = t2.t1_id
AND 1=2;
select * from table(dbms_xplan.display_cursor(null,null,'allstats last'));
----------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time |
----------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | 0 |00:00:00.01 |
|* 1 | FILTER | | 1 | | 0 |00:00:00.01 |
| 2 | NESTED LOOPS | | 0 | 100 | 0 |00:00:00.01 |
| 3 | TABLE ACCESS FULL| T1 | 0 | 100 | 0 |00:00:00.01 |
|* 4 | TABLE ACCESS FULL| T2 | 0 | 1 | 0 |00:00:00.01 |
----------------------------------------------------------------------------
1 - filter(NULL IS NOT NULL)
4 - filter("T1"."ID"="T2"."T1_ID")
--分析T2表被访问次数不同的原因
---解释T2表为啥被访问100次
select count(*) from t1;
COUNT(*)
----------
100
---解释T2表为啥被访问2次
select count(*) from t1 where t1.n in (17,19);
COUNT(*)
----------
2
---解释T2表为啥被访问1次
select count(*) from t1 where t1.n = 19;
COUNT(*)
----------
1
---解释T2表为啥被访问0次
select count(*) from t1 where t1.n = 999999999;
COUNT(*)
----------
0