/*
 结论： 排序合并连接中，两表都是只被访问0次或者1次，和HASH 连接一样）
*/

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


--Merge Sort Join中 T2表只会被访问1次或0次(驱动表访问1次，被驱动表访问1次）
set linesize 1000
SELECT  /*+ leading(t1) use_merge(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.09 |    1012 |       |       |          |
|   1 |  MERGE JOIN         |      |      1 |    100 |    100 |00:00:00.09 |    1012 |       |       |          |
|   2 |   SORT JOIN         |      |      1 |    100 |    100 |00:00:00.01 |       7 | 11264 | 11264 |10240  (0)|
|   3 |    TABLE ACCESS FULL| T1   |      1 |    100 |    100 |00:00:00.01 |       7 |       |       |          |
|*  4 |   SORT JOIN         |      |    100 |    100K|    100 |00:00:00.09 |    1005 |  9266K|  1184K| 8236K (0)|
|   5 |    TABLE ACCESS FULL| T2   |      1 |    100K|    100K|00:00:00.02 |    1005 |       |       |          |
-----------------------------------------------------------------------------------------------------------------
   4 - access("T1"."ID"="T2"."T1_ID")
       filter("T1"."ID"="T2"."T1_ID")

--Merge Sort Join中T2表被访问0次的情况
set linesize 1000
SELECT /*+ leading(t1) use_merge(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 |  MERGE JOIN         |      |      1 |      1 |      0 |00:00:00.01 |       7 |       |       |          |
|   2 |   SORT JOIN         |      |      1 |      1 |      0 |00:00:00.01 |       7 |  1024 |  1024 |          |
|*  3 |    TABLE ACCESS FULL| T1   |      1 |      1 |      0 |00:00:00.01 |       7 |       |       |          |
|*  4 |   SORT JOIN         |      |      0 |    100K|      0 |00:00:00.01 |       0 |  7250K|  1073K|          |
|   5 |    TABLE ACCESS FULL| T2   |      0 |    100K|      0 |00:00:00.01 |       0 |       |       |          |
-----------------------------------------------------------------------------------------------------------------
   3 - filter("T1"."N"=999999999)
   4 - access("T1"."ID"="T2"."T1_ID")
       filter("T1"."ID"="T2"."T1_ID")
       
--Merge Sort Join中T1和T2表都访问0次的情况
set linesize 1000
SELECT /*+ leading(t1) use_merge(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 |   MERGE JOIN         |      |      0 |    100 |      0 |00:00:00.01 |       |       |          |
|   3 |    SORT JOIN         |      |      0 |    100 |      0 |00:00:00.01 | 73728 | 73728 |          |
|   4 |     TABLE ACCESS FULL| T1   |      0 |    100 |      0 |00:00:00.01 |       |       |          |
|*  5 |    SORT JOIN         |      |      0 |    100K|      0 |00:00:00.01 |  7250K|  1073K|          |
|   6 |     TABLE ACCESS FULL| T2   |      0 |    100K|      0 |00:00:00.01 |       |       |          |
--------------------------------------------------------------------------------------------------------
   1 - filter(NULL IS NOT NULL)
   5 - access("T1"."ID"="T2"."T1_ID")
       filter("T1"."ID"="T2"."T1_ID")