對錶的連接的總結

1.嵌套迴圈訪問的次數
SYS@ PROD>create table t1(
id number not null,
n number,
contents varchar2(4000)
);
SYS@ PROD>create table t2(
id number not null,
t1_id number not null,
n number,
contents varchar2(4000)
);
SYS@ PROD>execute dbms_random.seed(0);

SYS@ PROD>insert into t1
select rownum,rownum,dbms_random.string('a',50)
from dual
connect by level<=100
order by dbms_random.random;
SYS@ PROD>insert into t2 select rownum,rownum,rownum,dbms_random.string('b',50) from dualconnect by level<=100000 order by dbms_random.random;
SYS@ PROD>commit;
SYS@ PROD>select count(*) from t1;100
SYS@ PROD>select count(*) from t2;100000
測試：
set linesize 1000
alter session set ststistics_level=all;
select /*+leading(t1) user_nl(t2)*/* from t1,t2 where t1.id=t2.t1_id;
select * from table(dbms_xplan.display_cursor(null,null,'allstats last'));
發現執行計劃裡面Starts列t1表訪問1次，t2表訪問100次
1>SYS@ PROD>select /*+leading(t1) use_nl(t2)*/*
from t1,t2
where t1.id=t2.t1_id
and t1.n in(17,19);
SYS@ PROD>select * from table(dbms_xplan.display_cursor(null,null,'allstats last'));
發現執行計劃裡面Starts列t1表訪問1次，t2表訪問2次
2>SYS@ PROD>select /*+leading(t1) use_nl(t2)*/*
from t1,t2
where t1.id=t2.t1_id
and t1.n=19;
發現執行計劃裡面Starts列t1表訪問1次，t2表訪問1次
3>SYS@ PROD>select /*+leading(t1) use_nl(t2)*/*
from t1,t2
where t1.id=t2.t1_id
and t1.n=999999999;
發現執行計劃裡面Starts列t1表訪問1次，t2表訪問0次
結論：t1表的查詢返回多少條記錄，t2表就訪問多少次。

HINT的含義，其中use_nl表示強制用嵌套迴圈的方式連接，leading(t1)表示強制先訪問t1表，也就是t1表作為驅動表，增加這些HINT
提示的目的只是為了確保我們的SQL語句的執行計劃在做嵌套迴圈連接。在嵌套迴圈連接中，驅動表返回多少條記錄，被驅動表就訪問多少次。
2.哈希連接表的訪問次數
/*leading(*) use_hash(t2)*/*
結論：在HASH連接中，驅動表和被驅動表都只會訪問0次或者1次。
3.排序合併的訪問次數
/*leading(*) use_merge(t2)*/*
結論：排序合併連接根本就沒有驅動和被驅動的概念，而嵌套迴圈和哈希連接要考慮驅動和被驅動情況。

三種訪問順序的比較：
1.嵌套迴圈連接特別要註意驅動表的順序，小的結果集先訪問，大的結果集後訪問，才能保證被驅動表的訪問次數降到最低，從而提升性能。
2.哈希迴圈順序也重要
3.嵌套迴圈連接和哈希連接有驅動順序，驅動表的順序不同將影響連接的性能，而排序合併連接沒有驅動的概念，無論哪張表都無妨。
三種訪問的排序：
嵌套和哈希不需要排序，而排序合併需要排序。

適合連接條件範圍的比較：
1.HASH連接查詢條件不支持<>,大於和小於的寫法，也不支持like的連接方式，如果加了這些連接條件，會走全表掃描。
2.排序合併連接不支持<>,也不支持like的連接條件，但支持>和<之類的連接條件。遇到
3.嵌套迴圈支持所有的sql連接條件的寫法，沒有任何限制。

最適合NL連接的場景
①兩表關聯返回的記錄不多，最佳情況是驅動表結果集僅返回1條，或少量幾條記錄，而被驅動表僅匹配到1條或少量幾條數據
這種情況即便t1表和t2表的記錄奇大無比，也是非常迅速的。
②遇到一些不等值查詢導致哈希和排序合併連接被限制使用，不得不使用NL連接。

排序合併連接上的連接條件雖然沒有檢索功能卻有消除排序的作用。