/*
 结论： NL连接中，驱动表被访问0或者1次，被驱动表也是被访问0次或者1次，绝大部分场景是驱动表和被驱动表被各访问1次）
*/

--环境构造
--研究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;


--Hash Join中 T2表只会被访问1次或0次(驱动表访问1次，被驱动表访问1次）
set linesize 1000
SELECT /*+ leading(t1) use_hash(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 |  OMem |  1Mem | Used-Mem |
----------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |      |      1 |        |    100 |00:00:00.07 |    1019 |       |       |          |
|*  1 |  HASH JOIN         |      |      1 |    100 |    100 |00:00:00.07 |    1019 |   742K|   742K| 1178K (0)|
|   2 |   TABLE ACCESS FULL| T1   |      1 |    100 |    100 |00:00:00.01 |       7 |       |       |          |
|   3 |   TABLE ACCESS FULL| T2   |      1 |    111K|    100K|00:00:00.02 |    1012 |       |       |          |
----------------------------------------------------------------------------------------------------------------
1 - access("T1"."ID"="T2"."T1_ID")




--Hash Join中T2表被访问0次的情况
SELECT /*+ leading(t1) use_hash(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 |  OMem |  1Mem | Used-Mem |
----------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |      |      1 |        |      0 |00:00:00.01 |       7 |       |       |          |
|*  1 |  HASH JOIN         |      |      1 |      1 |      0 |00:00:00.01 |       7 |   676K|   676K|  205K (0)|
|*  2 |   TABLE ACCESS FULL| T1   |      1 |      1 |      0 |00:00:00.01 |       7 |       |       |          |
|   3 |   TABLE ACCESS FULL| T2   |      0 |    111K|      0 |00:00:00.01 |       0 |       |       |          |
----------------------------------------------------------------------------------------------------------------
1 - access("T1"."ID"="T2"."T1_ID")
2 - filter("T1"."N"=999999999)




--Hash Join中T1和T2表都访问0次的情况
SELECT /*+ leading(t1) use_hash(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   |  OMem |  1Mem | Used-Mem |
-------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT    |      |      1 |        |      0 |00:00:00.01 |       |       |          |
|*  1 |  FILTER             |      |      1 |        |      0 |00:00:00.01 |       |       |          |
|*  2 |   HASH JOIN         |      |      0 |    100 |      0 |00:00:00.01 |   732K|   732K|          |
|   3 |    TABLE ACCESS FULL| T1   |      0 |    100 |      0 |00:00:00.01 |       |       |          |
|   4 |    TABLE ACCESS FULL| T2   |      0 |    111K|      0 |00:00:00.01 |       |       |          |
-------------------------------------------------------------------------------------------------------
 1 - filter(NULL IS NOT NULL)
 2 - access("T1"."ID"="T2"."T1_ID")