三種連結方式的區別

1 nested loops join
--我們用設定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
未完待續：
2 hash join
使用限制最多，不支援>、<、<>、like
3 merge sort join
支援>、<不支援<>、like