Oracle表聯結之嵌套循環

1.單表訪問： 分表，分區，建索引，全表掃描---開并行， 永遠把它 放內存，壓縮

2.多表關聯，任何時刻只能是2個表關聯，得到的結果集再和其他表關聯。

3.嵌套循環：Oracle從較小結果集(驅動表/外部表)中讀取一行，然后和較大結果集(被探查表/內部表)中的所有數據逐條進行比較(嵌套循環可以用于非等值連接)，如果符合規則，就放入結果集中，然后取較小結果集的下一條數據繼續進行循環，直到結束。嵌套循環只適合輸出少量結果集或者是用于快速輸出結果集。其實相當于雙層FOR循環。

SQL> select * from table(dbms_xplan.display_cursor(null,null,'ALLSTATS LAST'));
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID bv300dy9b7gyn, child number 0
-------------------------------------
select /*+ first_rows */ e.ename,e.job,d.dname from emp e,dept d  where
e.deptno=d.deptno and e.sal<2000
Plan hash value: 3625962092
-----------------------------------------------------------------------------------------------------------
| Id  | Operation       | Name    | Starts | E-Rows | A-Rows |   A-Time   | Buffers | Reads  |
-----------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT      |        |      1 |  | 7 |00:00:00.14 |      18 | 8 |
|   1 |  NESTED LOOPS       |        |      1 |      4 | 7 |00:00:00.14 |      18 | 8 |
|   2 |   NESTED LOOPS       |        |      1 |      4 | 7 |00:00:00.14 |      11 | 7 |
|*  3 |    TABLE ACCESS FULL      | EMP     |      1 |      4 | 7 |00:00:00.12 |       7 | 6 |
|*  4 |    INDEX UNIQUE SCAN      | PK_DEPT |      7 |      1 | 7 |00:00:00.01 |       4 | 1 |
|   5 |   TABLE ACCESS BY INDEX ROWID| DEPT    |      7 |      1 | 7 |00:00:00.01 |       7 | 1 |
-----------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
   3 - filter("E"."SAL"<2000)
   4 - access("E"."DEPTNO"="D"."DEPTNO")

24 rows selected.

離關鍵字近的是驅動表，嵌套循環的rows是錯誤的，嵌套循環的算法，比如a NL b，如a表有1000條，從a表中取1000條數據，掃描一次a，把這1000條數據傳給b，然后b表被掃描1000次，那么取出的a表的數據放在什么地方？匹配完一條然后立馬返回，NL不需要PGA，因為不用緩存數據，如果多層NL，仍然是存PGA，多層NL容易引起CBC，

嵌套循環中，過濾后返回結果集的小的當驅動表，在外連接中，嵌套循環不能修改驅動表，在嵌套循環中，被驅動表的連接列一定要有索引，從上面執行計劃可以看到E的deptno傳值給D表的deptno，驅動表的連接列不用建索引，

nl 必須是驅動表返回數據量很少的時候才走，在sql語句中有count，group by，distinct，sum等關鍵字，不能走NL，如果OLTP系統，有大量的distinct，只能說明表設計有問題，用中間表把所有的關聯去重解決distinct，

如果在執行計劃里面有很多NL，從最里面開始搞，如果最里面錯誤了，那么外面的NL全部錯誤，由里向外不斷看NL.

怎樣判斷NL是否是對的？1，看驅動表返回的數據量，2，看被驅動表是否走索引，3.看最終返回多少結果集。那么第3條最重要。最終返回多少結果集決定是否走NL還是HASH.

如果A NL B，返回10w條數據，如果a:b=1:1,那么a至少返回10w條數據，然后b表被掃描10w次，如果a：b=1:10，那么a至少返回1w數據，那么b表被掃描1w次，b表走索引，b表走一次索引，回表10條數據，那么b表總回表次數是10w次，所以在NL中，被驅動表不管被掃描多少次，那么回表次數是最終返回數據條數，所以嵌套循環不適合大量數據，根本原因在于回表或者回表再過濾，如果不用回表或回表再過濾，那么NL非常有效。

被驅動表的連接列要基數很高，如果基數很低，不能走NL，如1：1w，1：N，然后N太大

NL只需要SGA，不需要PGA，NL支持非等值jion，而HASH join只支持等值關聯。

判斷是否走NL和HASH，根據最終返回的結果集來判斷，其次驅動表返回的行數，再是被驅動表的jion列的基數。

錯誤NL，1.單次返回大量數據，如100w

 附：查詢訪問表所有字段的sql信息：

WITH t AS
 (SELECT a.object_name "表名",
         a.sql_id,
         c.sql_text,
         c.executions,
         (regexp_count(prjection, ',', 1) + 1) / 2 "訪問列數",
         COUNT(b.column_name) "總列數",
         d.bytes / 1024 / 1024 "表體積_MB"
    FROM v$sql_plan a, dba_tab_columns b, v$sql c, dba_segments d
   WHERE a.object_owner = b.owner
     AND a.object_name = b.table_name
     AND a.object_owner = 'EOL'
     AND a.sql_id = c.sql_id
     AND a.object_name = d.segment_name
     AND a.object_owner = d.owner
   GROUP BY (regexp_count(prjection, ',', 1) + 1) / 2，a.object_name,
            a.sql_id,
            c.sql_text,
            c.executions,
            d.bytes / 1024 / 1024)
SELECT * FROM t WHERE t.訪問列數 = t.總列數 ORDER BY 表體積_MB,executions DESC;