oracle 表串連，oracle串連

oracle 表串連，oracle串連
一. nested loop 原理

nested loop 串連(迴圈嵌套串連)指的是兩個表串連時, 通過兩層嵌套迴圈來進行依次的匹配, 最後得到返回結果集的表串連方法. 

假如下面的 sql 語句中表 T1 和 T2 的串連方式是迴圈嵌套串連, T1 是驅動表

select *from T1, T2where T1.id = T2.id and T1.name = 'David';

那麼將上述 sql 語句翻譯為偽碼應該如下所示:.

for each row in (select * from T1 where name = 'David') loopfor (select * from T2 where T2.id = outer.id) loopIf match then pass the row on to the next stepIf no match then discard the rowend loopend loop

具體來說, 如果上述 sql 語句執行迴圈嵌套串連的話, 那麼實際的執行過程應該如下所示:
(1) 首先 oracle 會根據一定的規則(根據統計資訊的成本計算或者 hint 強制)決定哪個表是驅動表, 哪個表是被驅動表 (假設 T1 是驅動表)
(2) 查詢驅動表 "select * from T1 where name = 'David'" 然後得到驅動結果集 Q1
(3) 遍曆驅動結果集 Q1 以及被驅動表 T2, 從驅動結果集 Q1 中取出一條記錄, 接著遍曆 T2 並按照串連條件 T2.id = T1.id 去判斷 T2 中是否存在匹配的記錄, 如果能夠匹配則保留, 不能匹配則忽略此行, 然後再從 Q1 中取出下一條記錄, 接著遍曆 T2 進行匹配, 如此下去直到取完 Q1 中的所有記錄

二. nested loop 特性

嵌套迴圈串連有以下特性:

(1) 通常 sql 語句中驅動表只訪問一次, 被驅動表訪問多次
(2) 不必等待處理完成所有行前可以先返回部分已經處理完成的資料
(3) 在限制條件以及串連條件列上建立索引, 能夠提高執行效率
(4) 支援所有類型的串連 (等值串連, 非等值串連, like 等)

構造實驗資料

SQL> CREATE TABLE t1 (  2    id NUMBER NOT NULL,  3    n NUMBER,  4    pad VARCHAR2(4000),  5    CONSTRAINT t1_pk PRIMARY KEY(id)  6  );Table created.SQL> CREATE TABLE t2 (  2    id NUMBER NOT NULL,  3    t1_id NUMBER NOT NULL,  4    n NUMBER,  5    pad VARCHAR2(4000),  6    CONSTRAINT t2_pk PRIMARY KEY(id),  7    CONSTRAINT t2_t1_fk FOREIGN KEY (t1_id) REFERENCES t1  8  );Table created.SQL> CREATE TABLE t3 (  2    id NUMBER NOT NULL,  3    t2_id NUMBER NOT NULL,  4    n NUMBER,  5    pad VARCHAR2(4000),  6    CONSTRAINT t3_pk PRIMARY KEY(id),  7    CONSTRAINT t3_t2_fk FOREIGN KEY (t2_id) REFERENCES t2  8  );Table created.SQL> CREATE TABLE t4 (  2    id NUMBER NOT NULL,  3    t3_id NUMBER NOT NULL,  4    n NUMBER,  5    pad VARCHAR2(4000),  6    CONSTRAINT t4_pk PRIMARY KEY(id),  7    CONSTRAINT t4_t3_fk FOREIGN KEY (t3_id) REFERENCES t3  8  );Table created.SQL> execute dbms_random.seed(0)PL/SQL procedure successfully completed.SQL> INSERT INTO t1 SELECT rownum, rownum, dbms_random.string('a',50) FROM dual CONNECT BY level <= 10 ORDER BY dbms_random.random;10 rows created.SQL> INSERT INTO t2 SELECT 100+rownum, t1.id, 100+rownum, t1.pad FROM t1, t1 dummy ORDER BY dbms_random.random;100 rows created.SQL> INSERT INTO t3 SELECT 1000+rownum, t2.id, 1000+rownum, t2.pad FROM t2, t1 dummy ORDER BY dbms_random.random;1000 rows created.SQL> INSERT INTO t4 SELECT 10000+rownum, t3.id, 10000+rownum, t3.pad FROM t3, t1 dummy ORDER BY dbms_random.random;10000 rows created.SQL> COMMIT;Commit complete.

使用 hint 讓 sql 語句通過 nested loop 串連, 並且指定 t3 為驅動表

SQL> select /*+ leading(t3) use_nl(t4) */ * from t3, t4  2  where t3.id = t4.t3_id and t3.n = 1100;10 rows selected.SQL> select * from table(dbms_xplan.display_cursor(null,null,'allstats last'));PLAN_TABLE_OUTPUT------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------SQL_ID  89hnfwqakjghg, child number 0-------------------------------------select /*+ leading(t3) use_nl(t4) */ * from t3, t4 where t3.id =t4.t3_id and t3.n = 1100Plan hash value: 1907878852-------------------------------------------------------------------------------------| Id  | Operation          | Name | Starts | E-Rows | A-Rows |   A-Time   | Buffers |-------------------------------------------------------------------------------------|   0 | SELECT STATEMENT   |      |      1 |        |     10 |00:00:00.01 |     121 ||   1 |  NESTED LOOPS      |      |      1 |     10 |     10 |00:00:00.01 |     121 ||*  2 |   TABLE ACCESS FULL| T3   |      1 |      1 |      1 |00:00:00.01 |      16 ||*  3 |   TABLE ACCESS FULL| T4   |      1 |     10 |     10 |00:00:00.01 |     105 |-------------------------------------------------------------------------------------Predicate Information (identified by operation id):---------------------------------------------------   2 - filter("T3"."N"=1100)   3 - filter("T3"."ID"="T4"."T3_ID")

在執行計畫中我們可以看到驅動表 T3 訪問一次, 因為驅動表上有謂詞條件 t3.n = 1100, 通過執行謂詞條件後驅動結果集的記錄數為 1, 所以 T4 也只訪問一次(starts 列)

使用 hint 讓 sql 語句通過 nested loop 串連, 並且指定 t4 為驅動表

SQL> select /*+ leading(t4) use_nl(t3) full(t4) full(t3) */ * from t3, t4  2  where t3.id = t4.t3_id and t3.n = 1100;SQL> select * from table(dbms_xplan.display_cursor(null,null,'allstats last'));PLAN_TABLE_OUTPUT--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------SQL_ID  0yxm1muqwrfq2, child number 0-------------------------------------select /*+ leading(t4) use_nl(t3) full(t4) full(t3) */ * from t3, t4where t3.id = t4.t3_id and t3.n = 1100Plan hash value: 3886808168-------------------------------------------------------------------------------------| Id  | Operation          | Name | Starts | E-Rows | A-Rows |   A-Time   | Buffers |-------------------------------------------------------------------------------------|   0 | SELECT STATEMENT   |      |      1 |        |     10 |00:00:00.25 |     150K||   1 |  NESTED LOOPS      |      |      1 |     10 |     10 |00:00:00.25 |     150K||   2 |   TABLE ACCESS FULL| T4   |      1 |  10000 |  10000 |00:00:00.01 |     105 ||*  3 |   TABLE ACCESS FULL| T3   |  10000 |      1 |     10 |00:00:00.21 |     150K|-------------------------------------------------------------------------------------Predicate Information (identified by operation id):---------------------------------------------------   3 - filter(("T3"."N"=1100 AND "T3"."ID"="T4"."T3_ID"))

在執行計畫中我們可以看到驅動表 T4 訪問一次, 因為驅動表上 T4 結果集的記錄數為 10000, 所以 T4 訪問了 10000 次, buffers 和 A-time(實際執行時間) 都比較高.

三. nested loop 最佳化

在 nested loop 被驅動表上的串連列上 (T4 表的 t3_id 列) 建立索引 

SQL> CREATE INDEX t4_t3_id ON t4(t3_id);Index created.SQL> select /*+ leading(t3) use_nl(t4) */ * from t3, t4  2  where t3.id = t4.t3_id and t3.n = 1100;10 rows selected.SQL> select * from table(dbms_xplan.display_cursor(null,null,'allstats last'));PLAN_TABLE_OUTPUT------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------SQL_ID  89hnfwqakjghg, child number 0-------------------------------------select /*+ leading(t3) use_nl(t4) */ * from t3, t4 where t3.id =t4.t3_id and t3.n = 1100Plan hash value: 2039660043------------------------------------------------------------------------------------------------------------| Id  | Operation                    | Name     | Starts | E-Rows | A-Rows |   A-Time   | Buffers | Reads  |------------------------------------------------------------------------------------------------------------|   0 | SELECT STATEMENT             |          |      1 |        |     10 |00:00:00.01 |      29 |   1 ||   1 |  NESTED LOOPS                |          |      1 |        |     10 |00:00:00.01 |      29 |   1 ||   2 |   NESTED LOOPS               |          |      1 |     10 |     10 |00:00:00.01 |      19 |   1 ||*  3 |    TABLE ACCESS FULL         | T3       |      1 |      1 |      1 |00:00:00.01 |      16 |   0 ||*  4 |    INDEX RANGE SCAN          | T4_T3_ID |      1 |     10 |     10 |00:00:00.01 |       3 |   1 ||   5 |   TABLE ACCESS BY INDEX ROWID| T4       |     10 |     10 |     10 |00:00:00.01 |      10 |   0 |------------------------------------------------------------------------------------------------------------Predicate Information (identified by operation id):---------------------------------------------------   3 - filter("T3"."N"=1100)   4 - access("T3"."ID"="T4"."T3_ID")

在執行計畫中可以看到在被驅動表上的串連列上加上索引後, buffer 從 121 下降到了 29

在驅動表的謂詞條件列上 (T3 表的 n 列) 加上索引

SQL> create index t3_n on t3(n);Index created.SQL> select /*+ leading(t3) use_nl(t4) */ * from t3, t4  2  where t3.id = t4.t3_id and t3.n = 1100;10 rows selected.SQL> select * from table(dbms_xplan.display_cursor(null,null,'allstats last'));PLAN_TABLE_OUTPUT--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------SQL_ID  89hnfwqakjghg, child number 0-------------------------------------select /*+ leading(t3) use_nl(t4) */ * from t3, t4 where t3.id =t4.t3_id and t3.n = 1100Plan hash value: 2304842513-------------------------------------------------------------------------------------------------------------| Id  | Operation                     | Name     | Starts | E-Rows | A-Rows |   A-Time   | Buffers | Reads  |-------------------------------------------------------------------------------------------------------------|   0 | SELECT STATEMENT              |          |      1 |        |     10 |00:00:00.01 |      17 |   1 ||   1 |  NESTED LOOPS                 |          |      1 |        |     10 |00:00:00.01 |      17 |   1 ||   2 |   NESTED LOOPS                |          |      1 |     10 |     10 |00:00:00.01 |       7 |   1 ||   3 |    TABLE ACCESS BY INDEX ROWID| T3       |      1 |      1 |      1 |00:00:00.01 |       4 |   1 ||*  4 |     INDEX RANGE SCAN          | T3_N     |      1 |      1 |      1 |00:00:00.01 |       3 |   1 ||*  5 |    INDEX RANGE SCAN           | T4_T3_ID |      1 |     10 |     10 |00:00:00.01 |       3 |   0 ||   6 |   TABLE ACCESS BY INDEX ROWID | T4       |     10 |     10 |     10 |00:00:00.01 |      10 |   0 |-------------------------------------------------------------------------------------------------------------Predicate Information (identified by operation id):---------------------------------------------------   4 - access("T3"."N"=1100)   5 - access("T3"."ID"="T4"."T3_ID")

在執行計畫中可以看到在驅動表上的謂詞條件列上加上索引後, buffer 從 29 繼續下降到了 17

四. 小結

由此可見, 在 sql 調優時如果遇到表的串連方式是 nested loop：

首先，要確保結果集小的表為驅動表，結果集多的表為被驅動表。這不意味著記錄多的表不能作為驅動表, 只要通過謂詞條件過濾後得到的結果集比較小，也可以作為驅動表。

其次，在驅動表的謂詞條件列以及被驅動表的串連列上加上索引，能夠顯著的提高執行效能。

最後，如果要查詢的列都在索引中，避免回表查詢列資訊時，又將進一步提高執行效能。