Oracle表聯(lián)結(jié)之嵌套循環(huán)

1.單表訪問：分表，分區(qū)，建索引，全表掃描---開并行，永遠(yuǎn)把它放內(nèi)存，壓縮

創(chuàng)新互聯(lián)主要從事網(wǎng)站制作、網(wǎng)站設(shè)計(jì)、網(wǎng)頁設(shè)計(jì)、企業(yè)做網(wǎng)站、公司建網(wǎng)站等業(yè)務(wù)。立足成都服務(wù)重慶,十余年網(wǎng)站建設(shè)經(jīng)驗(yàn),價(jià)格優(yōu)惠、服務(wù)專業(yè),歡迎來電咨詢建站服務(wù):18982081108

2.多表關(guān)聯(lián)，任何時(shí)刻只能是2個(gè)表關(guān)聯(lián)，得到的結(jié)果集再和其他表關(guān)聯(lián)。

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

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.

離關(guān)鍵字近的是驅(qū)動(dòng)表，嵌套循環(huán)的rows是錯(cuò)誤的，嵌套循環(huán)的算法，比如a NL b，如a表有1000條，從a表中取1000條數(shù)據(jù)，掃描一次a，把這1000條數(shù)據(jù)傳給b，然后b表被掃描1000次，那么取出的a表的數(shù)據(jù)放在什么地方？匹配完一條然后立馬返回，NL不需要PGA，因?yàn)椴挥镁彺鏀?shù)據(jù)，如果多層NL，仍然是存PGA，多層NL容易引起CBC，

嵌套循環(huán)中，過濾后返回結(jié)果集的小的當(dāng)驅(qū)動(dòng)表，在外連接中，嵌套循環(huán)不能修改驅(qū)動(dòng)表，在嵌套循環(huán)中，被驅(qū)動(dòng)表的連接列一定要有索引，從上面執(zhí)行計(jì)劃可以看到E的deptno傳值給D表的deptno，驅(qū)動(dòng)表的連接列不用建索引，

nl 必須是驅(qū)動(dòng)表返回?cái)?shù)據(jù)量很少的時(shí)候才走，在sql語句中有count，group by，distinct，sum等關(guān)鍵字，不能走NL，如果OLTP系統(tǒng)，有大量的distinct，只能說明表設(shè)計(jì)有問題，用中間表把所有的關(guān)聯(lián)去重解決distinct，

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

怎樣判斷NL是否是對的？1，看驅(qū)動(dòng)表返回的數(shù)據(jù)量，2，看被驅(qū)動(dòng)表是否走索引，3.看最終返回多少結(jié)果集。那么第3條最重要。最終返回多少結(jié)果集決定是否走NL還是HASH.

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

被驅(qū)動(dòng)表的連接列要基數(shù)很高，如果基數(shù)很低，不能走NL，如1：1w，1：N，然后N太大

NL只需要SGA，不需要PGA，NL支持非等值jion，而HASH join只支持等值關(guān)聯(lián)。

判斷是否走NL和HASH，根據(jù)最終返回的結(jié)果集來判斷，其次驅(qū)動(dòng)表返回的行數(shù)，再是被驅(qū)動(dòng)表的jion列的基數(shù)。

錯(cuò)誤NL，1.單次返回大量數(shù)據(jù)，如100w

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

WITH t AS
 (SELECT a.object_name "表名",
         a.sql_id,
         c.sql_text,
         c.executions,
         (regexp_count(prjection, ',', 1) + 1) / 2 "訪問列數(shù)",
         COUNT(b.column_name) "總列數(shù)",
         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.訪問列數(shù) = t.總列數(shù) ORDER BY 表體積_MB,executions DESC;

文章名稱：Oracle表聯(lián)結(jié)之嵌套循環(huán)
轉(zhuǎn)載來源：http://muchs.cn/article14/jehcge.html

成都網(wǎng)站建設(shè)公司_創(chuàng)新互聯(lián)，為您提供移動(dòng)網(wǎng)站建設(shè)、標(biāo)簽優(yōu)化、品牌網(wǎng)站制作、網(wǎng)站改版、企業(yè)建站、做網(wǎng)站

聲明：本網(wǎng)站發(fā)布的內(nèi)容（圖片、視頻和文字）以用戶投稿、用戶轉(zhuǎn)載內(nèi)容為主，如果涉及侵權(quán)請盡快告知，我們將會在第一時(shí)間刪除。文章觀點(diǎn)不代表本網(wǎng)站立場，如需處理請聯(lián)系客服。電話：028-86922220；郵箱：631063699@qq.com。內(nèi)容未經(jīng)允許不得轉(zhuǎn)載，或轉(zhuǎn)載時(shí)需注明來源：創(chuàng)新互聯(lián)

猜你還喜歡下面的內(nèi)容