Sql 巢狀迴圈最佳化案例
Sql 巢狀迴圈最佳化案例
資料庫版本：
SQL> select * from v$version;
BANNER
-------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.1.0.7.0 - 64bit Production
4節點 HPUX RAC OLAP 環境
SQL> show parameter db_block_size
NAME TYPE VALUE
------------------------------------ --------------------------------- ------
db_block_size integer 16384
SQL> show parameter db_file
NAME TYPE VALUE
------------------------------------ --------------------------------- ------
db_file_multiblock_read_count integer 64
ETL 開發人員發來郵件說有個long running job，跑了2小時左右 還未完成 叫我check一下
SQL> select * from table(dbms_xplan.display_cursor('gh1hw18uz6dcm',0));
PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID gh1hw18uz6dcm, child number 0
-------------------------------------
create table OPT_REF_BASE_UOM_TEMP_SDIM parallel 2 TABLESPACE
OPTIMA01M nologging as SELECT PROD_SKID, RELTV_CURR_QTY,
STAT_CURR_VAL, BAR_CURR_CODE FROM OPT_REF_BASE_UOM_DIM_VW
Plan hash value: 2933813170
------------------------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | TQ |IN-OUT| PQ Distrib |
------------------------------------------------------------------------------------------------------------------------------------------
| 0 | CREATE TABLE STATEMENT | | | | 8883 (100)| | | | |
| 1 | PX COORDINATOR | | | | | | | | |
| 2 | PX SEND QC (RANDOM) | :TQ10001 | 54 | 2916 | 8882 (1)| 00:01:02 | Q1,01 | P->S | QC (RAND) |
| 3 | LOAD AS SELECT | | | | | | Q1,01 | PCWP | |
| 4 | HASH GROUP BY | | 54 | 2916 | 8882 (1)| 00:01:02 | Q1,01 | PCWP | |
| 5 | PX RECEIVE | | 54 | 2916 | 8882 (1)| 00:01:02 | Q1,01 | PCWP | |
| 6 | PX SEND HASH | :TQ10000 | 54 | 2916 | 8882 (1)| 00:01:02 | Q1,00 | P->P | HASH |
| 7 | HASH GROUP BY | | 54 | 2916 | 8882 (1)| 00:01:02 | Q1,00 | PCWP | |
| 8 | NESTED LOOPS | | | | | | Q1,00 | PCWP | |
| 9 | NESTED LOOPS | | 3134 | 165K| 8881 (1)| 00:01:02 | Q1,00 | PCWP | |
| 10 | PX BLOCK ITERATOR | | | | | | Q1,00 | PCWC | |
|* 11 | TABLE ACCESS FULL | OPT_REF_UOM_TEMP_SDIM | 3065 | 104K| 355 (13)| 00:00:03 | Q1,00 | PCWP | |
|* 12 | INDEX RANGE SCAN | PROD_DIM_PK | 3 | | 1 (0)| 00:00:01 | Q1,00 | PCWP | |
|* 13 | TABLE ACCESS BY INDEX ROWID| PROD_DIM | 1 | 19 | 3 (0)| 00:00:01 | Q1,00 | PCWP | |
------------------------------------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
11 - access(:Z>=:Z AND :Z<=:Z)
filter("UOM"."RELTV_CURR_QTY"=1)
12 - access("PROD"."PROD_SKID"="UOM"."PROD_SKID")
13 - filter(("PROD"."BUOM_CURR_SKID" IS NOT NULL AND "PROD"."PROD_END_DATE"=TO_DATE(' 9999-12-31 00:00:00', 'syyyy-mm-dd
hh24:mi:ss') AND "PROD"."CURR_IND"='Y' AND "PROD"."BUOM_CURR_SKID"="UOM"."UOM_SKID"))
36 rows selected.
那麼這個JOB 很簡單，就是一個 create table xxx as select
上面的執行計劃是有問題的，執行計劃裡面顯示有PX 操作，其實我沒發現有並行，不過上面的執行計劃訪問路徑是對的
關於執行計劃顯示錯誤，這裡不予討論，11G上面有很多BUG 我們已經遇到5個了，另外也不要問我為什麼升級到11gR1
那麼實際上要執行的SQL就是
create table OPT_REF_BASE_UOM_TEMP_SDIM parallel 2 TABLESPACE
OPTIMA01M nologging as SELECT PROD_SKID, RELTV_CURR_QTY,
STAT_CURR_VAL, BAR_CURR_CODE FROM OPT_REF_BASE_UOM_DIM_VW
OPT_REF_BASE_UOM_DIM_VW是一個檢視，該檢視定義如下
SELECT UOM.PROD_SKID,
MAX (UOM.RELTV_CURR_QTY) RELTV_CURR_QTY,
MAX (UOM.STAT_CURR_VAL) STAT_CURR_VAL,
MAX (UOM.BAR_CURR_CODE) BAR_CURR_CODE
--modified by Daniel on 10/26/2010 for CR 326285 of QMR10.1 hypercare retrofit begin
FROM OPT_REF_UOM_TEMP_SDIM UOM,
REF_PROD_DIM PROD
WHERE UOM.RELTV_CURR_QTY = 1
AND PROD.CURR_IND = 'Y'
AND PROD.PROD_END_DATE = TO_DATE ('31-12-9999', 'dd-mm-yyyy')
AND PROD.PROD_SKID = UOM.PROD_SKID
AND PROD.BUOM_CURR_SKID = UOM.UOM_SKID
--modified by Daniel on 10/26/2010 for CR 326285 of QMR10.1 hypercare retrofit end
GROUP BY UOM.PROD_SKID
那麼現在 這個檢視的查詢效率就直接決定了該JOB的效率，我們 現在來看這個檢視的執行計劃
SQL> explain plan for SELECT UOM.PROD_SKID,
2 MAX (UOM.RELTV_CURR_QTY) RELTV_CURR_QTY,
3 MAX (UOM.STAT_CURR_VAL) STAT_CURR_VAL,
4 MAX (UOM.BAR_CURR_CODE) BAR_CURR_CODE
5 --modified by Daniel on 10/26/2010 for CR 326285 of QMR10.1 hypercare retrofit begin
6 FROM OPT_REF_UOM_TEMP_SDIM UOM,
7 REF_PROD_DIM PROD
8 WHERE UOM.RELTV_CURR_QTY = 1
9 AND PROD.CURR_IND = 'Y'
10 AND PROD.PROD_END_DATE = TO_DATE ('31-12-9999', 'dd-mm-yyyy')
11 AND PROD.PROD_SKID = UOM.PROD_SKID
12 AND PROD.BUOM_CURR_SKID = UOM.UOM_SKID
13 --modified by Daniel on 10/26/2010 for CR 326285 of QMR10.1 hypercare retrofit end
14 GROUP BY UOM.PROD_SKID;
Explained.
Elapsed: 00:00:01.29
SQL> select * from table(dbms_xplan.display);
PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------
Plan hash value: 3215660883
-------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 78 | 4212 | 15507 (1)| 00:01:47 |
| 1 | HASH GROUP BY | | 78 | 4212 | 15507 (1)| 00:01:47 |
| 2 | NESTED LOOPS | | | | | |
| 3 | NESTED LOOPS | | 3034 | 159K| 15506 (1)| 00:01:47 |
|* 4 | TABLE ACCESS FULL | OPT_REF_UOM_TEMP_SDIM | 2967 | 101K| 650 (14)| 00:00:05 |
|* 5 | INDEX RANGE SCAN | PROD_DIM_PK | 3 | | 2 (0)| 00:00:01 |
|* 6 | TABLE ACCESS BY INDEX ROWID| PROD_DIM | 1 | 19 | 5 (0)| 00:00:01 |
-------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
4 - filter("UOM"."RELTV_CURR_QTY"=1)
5 - access("PROD"."PROD_SKID"="UOM"."PROD_SKID")
6 - filter("PROD"."BUOM_CURR_SKID" IS NOT NULL AND "PROD"."PROD_END_DATE"=TO_DATE('
9999-12-31 00:00:00', 'syyyy-mm-dd hh24:mi:ss') AND "PROD"."CURR_IND"='Y' AND
"PROD"."BUOM_CURR_SKID"="UOM"."UOM_SKID")
22 rows selected.
Elapsed: 00:00:02.06
大家請看執行計劃 中ID=3的步驟，cost從 650一下子飆升到15506,根據我的SQL調優經驗，這是非常不能理解的。
我懷疑CBO選出了執行計劃。
那麼這裡OPT_REF_UOM_TEMP_SDIM 作為驅動表，Oracle先對它做一次全表掃描，然後應用過濾條件
WHERE UOM.RELTV_CURR_QTY = 1 所得到的資料作為驅動行源(你可以看 filter("UOM"."RELTV_CURR_QTY"=1))
那麼這裡 CBO計算出 根據 filter("UOM"."RELTV_CURR_QTY"=1) 過濾後 會返回2967條資料
SQL> select count(*) from OPT_REF_UOM_TEMP_SDIM;
COUNT(*)
----------
2137706
Elapsed: 00:00:08.87
SQL> select count(*) from OPT_REF_UOM_TEMP_SDIM where "RELTV_CURR_QTY"=1;
COUNT(*)
----------
946432
Elapsed: 00:00:01.54
SQL> select 946432/2137706 from dual;
946432/2137706
--------------
.442732537
Elapsed: 00:00:02.04
根據我下面的查詢，那麼很明顯CBO計算錯誤，filter("UOM"."RELTV_CURR_QTY"=1) 過濾之後實際上會返回946432條資料
然而 CBO認為只返回 2967條資料，所以這裡不應該走巢狀迴圈，應該走 hash。
為什麼不應該走巢狀迴圈？原因在於
SQL> select 946432/2137706 from dual;
946432/2137706
--------------
.442732537
全表掃描然後過濾之後 會返回44%的資料， 什麼時候應該選擇巢狀迴圈 ，通常情況下驅動表(行源) 應該返回
少量資料，而且被驅動表上面有選擇性很高的索引才選擇 巢狀迴圈。也就是說通常情況下驅動表應該返回不超過
源表10%的資料，而這裡居然返回44%的資料，明顯打錯特錯了。
我自己測試了一下，如果走巢狀迴圈要花 2小時40分鐘
867176 rows selected.
Elapsed: 02:39:58.33
Execution Plan
----------------------------------------------------------
Plan hash value: 3215660883
-------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 2 | 108 | 15996 (1)| 00:01:50 |
| 1 | HASH GROUP BY | | 2 | 108 | 15996 (1)| 00:01:50 |
| 2 | NESTED LOOPS | | | | | |
| 3 | NESTED LOOPS | | 3134 | 165K| 15995 (1)| 00:01:50 |
|* 4 | TABLE ACCESS FULL | OPT_REF_UOM_TEMP_SDIM | 3065 | 104K| 649 (14)| 00:00:05 |
|* 5 | INDEX RANGE SCAN | PROD_DIM_PK | 3 | | 2 (0)| 00:00:01 |
|* 6 | TABLE ACCESS BY INDEX ROWID| PROD_DIM | 1 | 19 | 5 (0)| 00:00:01 |
-------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
4 - filter("UOM"."RELTV_CURR_QTY"=1)
5 - access("PROD"."PROD_SKID"="UOM"."PROD_SKID")
6 - filter("PROD"."BUOM_CURR_SKID" IS NOT NULL AND "PROD"."PROD_END_DATE"=TO_DATE('
9999-12-31 00:00:00', 'syyyy-mm-dd hh24:mi:ss') AND "PROD"."CURR_IND"='Y' AND
"PROD"."BUOM_CURR_SKID"="UOM"."UOM_SKID")
注意，這個巢狀迴圈的資料和上面的巢狀迴圈的資料有點出入，原因是資料有變動，統計資訊有變動，不過不影響案例
如果走 HASH 連線 ，只要 8 分鐘左右，怎麼讓它走hash? 這裡我給大家列舉3中方法
1 使用hint full 強制PROD_DIM 走全表掃描
2 使用hint use_hash 強制2表做hash連線
3 使用hint leading 強制 PROD_DIM 作為驅動表
867176 rows selected.
Elapsed: 00:07:52.33
Execution Plan
----------------------------------------------------------
Plan hash value: 612020119
-----------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes |TempSpc| Cost (%CPU)| Time |
-----------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 60 | 3240 | | 44136 (5)| 00:05:04 |
| 1 | HASH GROUP BY | | 60 | 3240 | | 44136 (5)| 00:05:04 |
|* 2 | HASH JOIN | | 3065 | 161K| 29M| 44135 (5)| 00:05:04 |
|* 3 | TABLE ACCESS FULL| PROD_DIM | 998K| 18M| | 43022 (5)| 00:04:56 |
|* 4 | TABLE ACCESS FULL| OPT_REF_UOM_TEMP_SDIM | 3065 | 104K| | 649 (14)| 00:00:05 |
-----------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("PROD"."PROD_SKID"="UOM"."PROD_SKID" AND
"PROD"."BUOM_CURR_SKID"="UOM"."UOM_SKID")
3 - filter("PROD"."BUOM_CURR_SKID" IS NOT NULL AND "PROD"."PROD_END_DATE"=TO_DATE('
9999-12-31 00:00:00', 'syyyy-mm-dd hh24:mi:ss') AND "PROD"."CURR_IND"='Y')
4 - filter("UOM"."RELTV_CURR_QTY"=1)
那麼到這裡 還沒完，我們不僅僅要對SQL做最佳化，還要搞清楚為什麼CBO選擇錯了執行計劃，根據上面的分析，
CBO選擇錯誤的執行計劃的原因在於 它認為 filter("UOM"."RELTV_CURR_QTY"=1) 只返回2967 條資料，所以
為題出現在統計資訊 上面，而且是列統計資訊上面 那麼我們來查詢一些 列的統計資訊
SQL> select a.owner ||'.'||a.table_name name ,a.column_name,b.num_rows,a.num_distinct Cardinality,a.num_distinct/b.num_rows selectivity,
2 num_nulls,density,a.histogram,a.num_buckets from dba_tab_col_statistics a,dba_tables b where a.owner=b.owner and a.table_name=b.table_name
3 and a.owner=upper('adwu_optima_we11') and a.table_name=upper('OPT_REF_UOM_TEMP_SDIM') and a.column_name=upper('RELTV_CURR_QTY');
NAME COLUMN_NAME NUM_ROWS CARDINALITY SELECTIVITY NUM_NULLS DENSITY HISTOGRAM
---------------------------------------- -------------------- ---------- ----------- ----------- ---------- ---------- --------------------------
ADWU_OPTIMA_WE11.OPT_REF_UOM_TEMP_SDIM RELTV_CURR_QTY 2160000 728 .000337037 0 .001373626 NONE
大家請看，列上面一共只有728個基數(唯一值)，然而表有200多萬的資料，並且沒有對列收集過直方圖統計
那麼我現在對該列收集直方圖
SQL> BEGIN
2 DBMS_STATS.GATHER_TABLE_STATS(ownname => 'adwu_optima_we11',
3 tabname => 'OPT_REF_UOM_TEMP_SDIM',
4 estimate_percent => 100,
5 method_opt => 'for columns RELTV_CURR_QTY size 200',
6 degree => DBMS_STATS.AUTO_DEGREE,
7 cascade=>TRUE
8 );
9 END;
10 /
PL/SQL procedure successfully completed.
我再來檢視一下該列的統計資訊，這裡基數上升到2110，並且直方圖也收集了
SQL> select a.owner ||'.'||a.table_name name ,a.column_name,b.num_rows,a.num_distinct Cardinality,a.num_distinct/b.num_rows selectivity,
2 num_nulls,density,a.histogram,a.num_buckets from dba_tab_col_statistics a,dba_tables b where a.owner=b.owner and a.table_name=b.table_name
3 and a.owner=upper('adwu_optima_we11') and a.table_name=upper('OPT_REF_UOM_TEMP_SDIM') and a.column_name=upper('RELTV_CURR_QTY');
NAME COLUMN_NAME NUM_ROWS CARDINALITY SELECTIVITY NUM_NULLS DENSITY HISTOGRAM
---------------------------------------- -------------------- ---------- ----------- ----------- ---------- ---------- ----------------------------
ADWU_OPTIMA_WE11.OPT_REF_UOM_TEMP_SDIM RELTV_CURR_QTY 2137706 2110 .000987039 0 .00217122 HEIGHT BALANCED
我們再來檢視一下執行計劃
SQL> explain plan for SELECT UOM.PROD_SKID,
2 MAX (UOM.RELTV_CURR_QTY) RELTV_CURR_QTY,
3 MAX (UOM.STAT_CURR_VAL) STAT_CURR_VAL,
4 MAX (UOM.BAR_CURR_CODE) BAR_CURR_CODE
5 --modified by Daniel on 10/26/2010 for CR 326285 of QMR10.1 hypercare retrofit begin
6 FROM OPT_REF_UOM_TEMP_SDIM UOM,
7 REF_PROD_DIM PROD
8 WHERE UOM.RELTV_CURR_QTY = 1
9 AND PROD.CURR_IND = 'Y'
10 AND PROD.PROD_END_DATE = TO_DATE ('31-12-9999', 'dd-mm-yyyy')
11 AND PROD.PROD_SKID = UOM.PROD_SKID
12 AND PROD.BUOM_CURR_SKID = UOM.UOM_SKID
13 --modified by Daniel on 10/26/2010 for CR 326285 of QMR10.1 hypercare retrofit end
14 GROUP BY UOM.PROD_SKID;
Explained.
Elapsed: 00:00:00.82
這回執行計劃就走對了
SQL> select * from table(dbms_xplan.display);
PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------
Plan hash value: 612020119
-----------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes |TempSpc| Cost (%CPU)| Time |
-----------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 12097 | 637K| | 44911 (5)| 00:05:09 |
| 1 | HASH GROUP BY | | 12097 | 637K| | 44911 (5)| 00:05:09 |
|* 2 | HASH JOIN | | 951K| 48M| 29M| 44799 (5)| 00:05:08 |
|* 3 | TABLE ACCESS FULL| PROD_DIM | 998K| 18M| | 43022 (5)| 00:04:56 |
|* 4 | TABLE ACCESS FULL| OPT_REF_UOM_TEMP_SDIM | 951K| 31M| | 654 (15)| 00:00:05 |
-----------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("PROD"."PROD_SKID"="UOM"."PROD_SKID" AND
"PROD"."BUOM_CURR_SKID"="UOM"."UOM_SKID")
3 - filter("PROD"."BUOM_CURR_SKID" IS NOT NULL AND "PROD"."PROD_END_DATE"=TO_DATE('
9999-12-31 00:00:00', 'syyyy-mm-dd hh24:mi:ss') AND "PROD"."CURR_IND"='Y')
4 - filter("UOM"."RELTV_CURR_QTY"=1)
20 rows selected.
也許你們說 是由於統計資訊過期導致的，那麼我現在把直方圖刪除
SQL> BEGIN
2 DBMS_STATS.GATHER_TABLE_STATS(ownname => 'adwu_optima_we11',
3 tabname => 'OPT_REF_UOM_TEMP_SDIM',
4 estimate_percent => 100,
5 method_opt => 'for columns RELTV_CURR_QTY size 1',
6 degree => DBMS_STATS.AUTO_DEGREE,
7 cascade=>TRUE
8 );
9 END;
10 /
PL/SQL procedure successfully completed.
Elapsed: 00:00:12.65
SQL> explain plan for SELECT UOM.PROD_SKID,
2 MAX (UOM.RELTV_CURR_QTY) RELTV_CURR_QTY,
3 MAX (UOM.STAT_CURR_VAL) STAT_CURR_VAL,
4 MAX (UOM.BAR_CURR_CODE) BAR_CURR_CODE
5 --modified by Daniel on 10/26/2010 for CR 326285 of QMR10.1 hypercare retrofit begin
6 FROM OPT_REF_UOM_TEMP_SDIM UOM,
7 REF_PROD_DIM PROD
8 WHERE UOM.RELTV_CURR_QTY = 1
9 AND PROD.CURR_IND = 'Y'
10 AND PROD.PROD_END_DATE = TO_DATE ('31-12-9999', 'dd-mm-yyyy')
11 AND PROD.PROD_SKID = UOM.PROD_SKID
12 AND PROD.BUOM_CURR_SKID = UOM.UOM_SKID
13 --modified by Daniel on 10/26/2010 for CR 326285 of QMR10.1 hypercare retrofit end
14 GROUP BY UOM.PROD_SKID;
Explained.
Elapsed: 00:00:00.82
SQL> select * from table(dbms_xplan.display);
PLAN_TABLE_OUTPUT
-----------------------------------------------------------------------------------------------------------
Plan hash value: 3215660883
-------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 9 | 486 | 5722 (2)| 00:00:40 |
| 1 | HASH GROUP BY | | 9 | 486 | 5722 (2)| 00:00:40 |
| 2 | NESTED LOOPS | | | | | |
| 3 | NESTED LOOPS | | 1036 | 55944 | 5721 (2)| 00:00:40 |
|* 4 | TABLE ACCESS FULL | OPT_REF_UOM_TEMP_SDIM | 1013 | 35455 | 650 (14)| 00:00:05 |
|* 5 | INDEX RANGE SCAN | PROD_DIM_PK | 3 | | 2 (0)| 00:00:01 |
|* 6 | TABLE ACCESS BY INDEX ROWID| PROD_DIM | 1 | 19 | 5 (0)| 00:00:01 |
-------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
4 - filter("UOM"."RELTV_CURR_QTY"=1)
5 - access("PROD"."PROD_SKID"="UOM"."PROD_SKID")
6 - filter("PROD"."BUOM_CURR_SKID" IS NOT NULL AND "PROD"."PROD_END_DATE"=TO_DATE('
9999-12-31 00:00:00', 'syyyy-mm-dd hh24:mi:ss') AND "PROD"."CURR_IND"='Y' AND
"PROD"."BUOM_CURR_SKID"="UOM"."UOM_SKID")
22 rows selected.
Elapsed: 00:00:01.86
所以，最終導致CBO選錯執行計劃的罪魁禍首就是列沒有收集直方圖統計
透過這一次的案例分析，相信大家對巢狀迴圈的最佳化應該不成問題了，大家一定要深刻理解巢狀迴圈的原理
另外一個就是，大家要深入研究統計資訊，CBO能不能選對執行計劃，很關鍵的就在於統計資訊的準確與否。
資料庫版本：
SQL> select * from v$version;
BANNER
-------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.1.0.7.0 - 64bit Production
4節點 HPUX RAC OLAP 環境
SQL> show parameter db_block_size
NAME TYPE VALUE
------------------------------------ --------------------------------- ------
db_block_size integer 16384
SQL> show parameter db_file
NAME TYPE VALUE
------------------------------------ --------------------------------- ------
db_file_multiblock_read_count integer 64
ETL 開發人員發來郵件說有個long running job，跑了2小時左右 還未完成 叫我check一下
SQL> select * from table(dbms_xplan.display_cursor('gh1hw18uz6dcm',0));
PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID gh1hw18uz6dcm, child number 0
-------------------------------------
create table OPT_REF_BASE_UOM_TEMP_SDIM parallel 2 TABLESPACE
OPTIMA01M nologging as SELECT PROD_SKID, RELTV_CURR_QTY,
STAT_CURR_VAL, BAR_CURR_CODE FROM OPT_REF_BASE_UOM_DIM_VW
Plan hash value: 2933813170
------------------------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | TQ |IN-OUT| PQ Distrib |
------------------------------------------------------------------------------------------------------------------------------------------
| 0 | CREATE TABLE STATEMENT | | | | 8883 (100)| | | | |
| 1 | PX COORDINATOR | | | | | | | | |
| 2 | PX SEND QC (RANDOM) | :TQ10001 | 54 | 2916 | 8882 (1)| 00:01:02 | Q1,01 | P->S | QC (RAND) |
| 3 | LOAD AS SELECT | | | | | | Q1,01 | PCWP | |
| 4 | HASH GROUP BY | | 54 | 2916 | 8882 (1)| 00:01:02 | Q1,01 | PCWP | |
| 5 | PX RECEIVE | | 54 | 2916 | 8882 (1)| 00:01:02 | Q1,01 | PCWP | |
| 6 | PX SEND HASH | :TQ10000 | 54 | 2916 | 8882 (1)| 00:01:02 | Q1,00 | P->P | HASH |
| 7 | HASH GROUP BY | | 54 | 2916 | 8882 (1)| 00:01:02 | Q1,00 | PCWP | |
| 8 | NESTED LOOPS | | | | | | Q1,00 | PCWP | |
| 9 | NESTED LOOPS | | 3134 | 165K| 8881 (1)| 00:01:02 | Q1,00 | PCWP | |
| 10 | PX BLOCK ITERATOR | | | | | | Q1,00 | PCWC | |
|* 11 | TABLE ACCESS FULL | OPT_REF_UOM_TEMP_SDIM | 3065 | 104K| 355 (13)| 00:00:03 | Q1,00 | PCWP | |
|* 12 | INDEX RANGE SCAN | PROD_DIM_PK | 3 | | 1 (0)| 00:00:01 | Q1,00 | PCWP | |
|* 13 | TABLE ACCESS BY INDEX ROWID| PROD_DIM | 1 | 19 | 3 (0)| 00:00:01 | Q1,00 | PCWP | |
------------------------------------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
11 - access(:Z>=:Z AND :Z<=:Z)
filter("UOM"."RELTV_CURR_QTY"=1)
12 - access("PROD"."PROD_SKID"="UOM"."PROD_SKID")
13 - filter(("PROD"."BUOM_CURR_SKID" IS NOT NULL AND "PROD"."PROD_END_DATE"=TO_DATE(' 9999-12-31 00:00:00', 'syyyy-mm-dd
hh24:mi:ss') AND "PROD"."CURR_IND"='Y' AND "PROD"."BUOM_CURR_SKID"="UOM"."UOM_SKID"))
36 rows selected.
那麼這個JOB 很簡單，就是一個 create table xxx as select
上面的執行計劃是有問題的，執行計劃裡面顯示有PX 操作，其實我沒發現有並行，不過上面的執行計劃訪問路徑是對的
關於執行計劃顯示錯誤，這裡不予討論，11G上面有很多BUG 我們已經遇到5個了，另外也不要問我為什麼升級到11gR1
那麼實際上要執行的SQL就是
create table OPT_REF_BASE_UOM_TEMP_SDIM parallel 2 TABLESPACE
OPTIMA01M nologging as SELECT PROD_SKID, RELTV_CURR_QTY,
STAT_CURR_VAL, BAR_CURR_CODE FROM OPT_REF_BASE_UOM_DIM_VW
OPT_REF_BASE_UOM_DIM_VW是一個檢視，該檢視定義如下
SELECT UOM.PROD_SKID,
MAX (UOM.RELTV_CURR_QTY) RELTV_CURR_QTY,
MAX (UOM.STAT_CURR_VAL) STAT_CURR_VAL,
MAX (UOM.BAR_CURR_CODE) BAR_CURR_CODE
--modified by Daniel on 10/26/2010 for CR 326285 of QMR10.1 hypercare retrofit begin
FROM OPT_REF_UOM_TEMP_SDIM UOM,
REF_PROD_DIM PROD
WHERE UOM.RELTV_CURR_QTY = 1
AND PROD.CURR_IND = 'Y'
AND PROD.PROD_END_DATE = TO_DATE ('31-12-9999', 'dd-mm-yyyy')
AND PROD.PROD_SKID = UOM.PROD_SKID
AND PROD.BUOM_CURR_SKID = UOM.UOM_SKID
--modified by Daniel on 10/26/2010 for CR 326285 of QMR10.1 hypercare retrofit end
GROUP BY UOM.PROD_SKID
那麼現在 這個檢視的查詢效率就直接決定了該JOB的效率，我們 現在來看這個檢視的執行計劃
SQL> explain plan for SELECT UOM.PROD_SKID,
2 MAX (UOM.RELTV_CURR_QTY) RELTV_CURR_QTY,
3 MAX (UOM.STAT_CURR_VAL) STAT_CURR_VAL,
4 MAX (UOM.BAR_CURR_CODE) BAR_CURR_CODE
5 --modified by Daniel on 10/26/2010 for CR 326285 of QMR10.1 hypercare retrofit begin
6 FROM OPT_REF_UOM_TEMP_SDIM UOM,
7 REF_PROD_DIM PROD
8 WHERE UOM.RELTV_CURR_QTY = 1
9 AND PROD.CURR_IND = 'Y'
10 AND PROD.PROD_END_DATE = TO_DATE ('31-12-9999', 'dd-mm-yyyy')
11 AND PROD.PROD_SKID = UOM.PROD_SKID
12 AND PROD.BUOM_CURR_SKID = UOM.UOM_SKID
13 --modified by Daniel on 10/26/2010 for CR 326285 of QMR10.1 hypercare retrofit end
14 GROUP BY UOM.PROD_SKID;
Explained.
Elapsed: 00:00:01.29
SQL> select * from table(dbms_xplan.display);
PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------
Plan hash value: 3215660883
-------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 78 | 4212 | 15507 (1)| 00:01:47 |
| 1 | HASH GROUP BY | | 78 | 4212 | 15507 (1)| 00:01:47 |
| 2 | NESTED LOOPS | | | | | |
| 3 | NESTED LOOPS | | 3034 | 159K| 15506 (1)| 00:01:47 |
|* 4 | TABLE ACCESS FULL | OPT_REF_UOM_TEMP_SDIM | 2967 | 101K| 650 (14)| 00:00:05 |
|* 5 | INDEX RANGE SCAN | PROD_DIM_PK | 3 | | 2 (0)| 00:00:01 |
|* 6 | TABLE ACCESS BY INDEX ROWID| PROD_DIM | 1 | 19 | 5 (0)| 00:00:01 |
-------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
4 - filter("UOM"."RELTV_CURR_QTY"=1)
5 - access("PROD"."PROD_SKID"="UOM"."PROD_SKID")
6 - filter("PROD"."BUOM_CURR_SKID" IS NOT NULL AND "PROD"."PROD_END_DATE"=TO_DATE('
9999-12-31 00:00:00', 'syyyy-mm-dd hh24:mi:ss') AND "PROD"."CURR_IND"='Y' AND
"PROD"."BUOM_CURR_SKID"="UOM"."UOM_SKID")
22 rows selected.
Elapsed: 00:00:02.06
大家請看執行計劃 中ID=3的步驟，cost從 650一下子飆升到15506,根據我的SQL調優經驗，這是非常不能理解的。
我懷疑CBO選出了執行計劃。
那麼這裡OPT_REF_UOM_TEMP_SDIM 作為驅動表，Oracle先對它做一次全表掃描，然後應用過濾條件
WHERE UOM.RELTV_CURR_QTY = 1 所得到的資料作為驅動行源(你可以看 filter("UOM"."RELTV_CURR_QTY"=1))
那麼這裡 CBO計算出 根據 filter("UOM"."RELTV_CURR_QTY"=1) 過濾後 會返回2967條資料
SQL> select count(*) from OPT_REF_UOM_TEMP_SDIM;
COUNT(*)
----------
2137706
Elapsed: 00:00:08.87
SQL> select count(*) from OPT_REF_UOM_TEMP_SDIM where "RELTV_CURR_QTY"=1;
COUNT(*)
----------
946432
Elapsed: 00:00:01.54
SQL> select 946432/2137706 from dual;
946432/2137706
--------------
.442732537
Elapsed: 00:00:02.04
根據我下面的查詢，那麼很明顯CBO計算錯誤，filter("UOM"."RELTV_CURR_QTY"=1) 過濾之後實際上會返回946432條資料
然而 CBO認為只返回 2967條資料，所以這裡不應該走巢狀迴圈，應該走 hash。
為什麼不應該走巢狀迴圈？原因在於
SQL> select 946432/2137706 from dual;
946432/2137706
--------------
.442732537
全表掃描然後過濾之後 會返回44%的資料， 什麼時候應該選擇巢狀迴圈 ，通常情況下驅動表(行源) 應該返回
少量資料，而且被驅動表上面有選擇性很高的索引才選擇 巢狀迴圈。也就是說通常情況下驅動表應該返回不超過
源表10%的資料，而這裡居然返回44%的資料，明顯打錯特錯了。
我自己測試了一下，如果走巢狀迴圈要花 2小時40分鐘
867176 rows selected.
Elapsed: 02:39:58.33
Execution Plan
----------------------------------------------------------
Plan hash value: 3215660883
-------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 2 | 108 | 15996 (1)| 00:01:50 |
| 1 | HASH GROUP BY | | 2 | 108 | 15996 (1)| 00:01:50 |
| 2 | NESTED LOOPS | | | | | |
| 3 | NESTED LOOPS | | 3134 | 165K| 15995 (1)| 00:01:50 |
|* 4 | TABLE ACCESS FULL | OPT_REF_UOM_TEMP_SDIM | 3065 | 104K| 649 (14)| 00:00:05 |
|* 5 | INDEX RANGE SCAN | PROD_DIM_PK | 3 | | 2 (0)| 00:00:01 |
|* 6 | TABLE ACCESS BY INDEX ROWID| PROD_DIM | 1 | 19 | 5 (0)| 00:00:01 |
-------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
4 - filter("UOM"."RELTV_CURR_QTY"=1)
5 - access("PROD"."PROD_SKID"="UOM"."PROD_SKID")
6 - filter("PROD"."BUOM_CURR_SKID" IS NOT NULL AND "PROD"."PROD_END_DATE"=TO_DATE('
9999-12-31 00:00:00', 'syyyy-mm-dd hh24:mi:ss') AND "PROD"."CURR_IND"='Y' AND
"PROD"."BUOM_CURR_SKID"="UOM"."UOM_SKID")
注意，這個巢狀迴圈的資料和上面的巢狀迴圈的資料有點出入，原因是資料有變動，統計資訊有變動，不過不影響案例
如果走 HASH 連線 ，只要 8 分鐘左右，怎麼讓它走hash? 這裡我給大家列舉3中方法
1 使用hint full 強制PROD_DIM 走全表掃描
2 使用hint use_hash 強制2表做hash連線
3 使用hint leading 強制 PROD_DIM 作為驅動表
867176 rows selected.
Elapsed: 00:07:52.33
Execution Plan
----------------------------------------------------------
Plan hash value: 612020119
-----------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes |TempSpc| Cost (%CPU)| Time |
-----------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 60 | 3240 | | 44136 (5)| 00:05:04 |
| 1 | HASH GROUP BY | | 60 | 3240 | | 44136 (5)| 00:05:04 |
|* 2 | HASH JOIN | | 3065 | 161K| 29M| 44135 (5)| 00:05:04 |
|* 3 | TABLE ACCESS FULL| PROD_DIM | 998K| 18M| | 43022 (5)| 00:04:56 |
|* 4 | TABLE ACCESS FULL| OPT_REF_UOM_TEMP_SDIM | 3065 | 104K| | 649 (14)| 00:00:05 |
-----------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("PROD"."PROD_SKID"="UOM"."PROD_SKID" AND
"PROD"."BUOM_CURR_SKID"="UOM"."UOM_SKID")
3 - filter("PROD"."BUOM_CURR_SKID" IS NOT NULL AND "PROD"."PROD_END_DATE"=TO_DATE('
9999-12-31 00:00:00', 'syyyy-mm-dd hh24:mi:ss') AND "PROD"."CURR_IND"='Y')
4 - filter("UOM"."RELTV_CURR_QTY"=1)
那麼到這裡 還沒完，我們不僅僅要對SQL做最佳化，還要搞清楚為什麼CBO選擇錯了執行計劃，根據上面的分析，
CBO選擇錯誤的執行計劃的原因在於 它認為 filter("UOM"."RELTV_CURR_QTY"=1) 只返回2967 條資料，所以
為題出現在統計資訊 上面，而且是列統計資訊上面 那麼我們來查詢一些 列的統計資訊
SQL> select a.owner ||'.'||a.table_name name ,a.column_name,b.num_rows,a.num_distinct Cardinality,a.num_distinct/b.num_rows selectivity,
2 num_nulls,density,a.histogram,a.num_buckets from dba_tab_col_statistics a,dba_tables b where a.owner=b.owner and a.table_name=b.table_name
3 and a.owner=upper('adwu_optima_we11') and a.table_name=upper('OPT_REF_UOM_TEMP_SDIM') and a.column_name=upper('RELTV_CURR_QTY');
NAME COLUMN_NAME NUM_ROWS CARDINALITY SELECTIVITY NUM_NULLS DENSITY HISTOGRAM
---------------------------------------- -------------------- ---------- ----------- ----------- ---------- ---------- --------------------------
ADWU_OPTIMA_WE11.OPT_REF_UOM_TEMP_SDIM RELTV_CURR_QTY 2160000 728 .000337037 0 .001373626 NONE
大家請看，列上面一共只有728個基數(唯一值)，然而表有200多萬的資料，並且沒有對列收集過直方圖統計
那麼我現在對該列收集直方圖
SQL> BEGIN
2 DBMS_STATS.GATHER_TABLE_STATS(ownname => 'adwu_optima_we11',
3 tabname => 'OPT_REF_UOM_TEMP_SDIM',
4 estimate_percent => 100,
5 method_opt => 'for columns RELTV_CURR_QTY size 200',
6 degree => DBMS_STATS.AUTO_DEGREE,
7 cascade=>TRUE
8 );
9 END;
10 /
PL/SQL procedure successfully completed.
我再來檢視一下該列的統計資訊，這裡基數上升到2110，並且直方圖也收集了
SQL> select a.owner ||'.'||a.table_name name ,a.column_name,b.num_rows,a.num_distinct Cardinality,a.num_distinct/b.num_rows selectivity,
2 num_nulls,density,a.histogram,a.num_buckets from dba_tab_col_statistics a,dba_tables b where a.owner=b.owner and a.table_name=b.table_name
3 and a.owner=upper('adwu_optima_we11') and a.table_name=upper('OPT_REF_UOM_TEMP_SDIM') and a.column_name=upper('RELTV_CURR_QTY');
NAME COLUMN_NAME NUM_ROWS CARDINALITY SELECTIVITY NUM_NULLS DENSITY HISTOGRAM
---------------------------------------- -------------------- ---------- ----------- ----------- ---------- ---------- ----------------------------
ADWU_OPTIMA_WE11.OPT_REF_UOM_TEMP_SDIM RELTV_CURR_QTY 2137706 2110 .000987039 0 .00217122 HEIGHT BALANCED
我們再來檢視一下執行計劃
SQL> explain plan for SELECT UOM.PROD_SKID,
2 MAX (UOM.RELTV_CURR_QTY) RELTV_CURR_QTY,
3 MAX (UOM.STAT_CURR_VAL) STAT_CURR_VAL,
4 MAX (UOM.BAR_CURR_CODE) BAR_CURR_CODE
5 --modified by Daniel on 10/26/2010 for CR 326285 of QMR10.1 hypercare retrofit begin
6 FROM OPT_REF_UOM_TEMP_SDIM UOM,
7 REF_PROD_DIM PROD
8 WHERE UOM.RELTV_CURR_QTY = 1
9 AND PROD.CURR_IND = 'Y'
10 AND PROD.PROD_END_DATE = TO_DATE ('31-12-9999', 'dd-mm-yyyy')
11 AND PROD.PROD_SKID = UOM.PROD_SKID
12 AND PROD.BUOM_CURR_SKID = UOM.UOM_SKID
13 --modified by Daniel on 10/26/2010 for CR 326285 of QMR10.1 hypercare retrofit end
14 GROUP BY UOM.PROD_SKID;
Explained.
Elapsed: 00:00:00.82
這回執行計劃就走對了
SQL> select * from table(dbms_xplan.display);
PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------
Plan hash value: 612020119
-----------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes |TempSpc| Cost (%CPU)| Time |
-----------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 12097 | 637K| | 44911 (5)| 00:05:09 |
| 1 | HASH GROUP BY | | 12097 | 637K| | 44911 (5)| 00:05:09 |
|* 2 | HASH JOIN | | 951K| 48M| 29M| 44799 (5)| 00:05:08 |
|* 3 | TABLE ACCESS FULL| PROD_DIM | 998K| 18M| | 43022 (5)| 00:04:56 |
|* 4 | TABLE ACCESS FULL| OPT_REF_UOM_TEMP_SDIM | 951K| 31M| | 654 (15)| 00:00:05 |
-----------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("PROD"."PROD_SKID"="UOM"."PROD_SKID" AND
"PROD"."BUOM_CURR_SKID"="UOM"."UOM_SKID")
3 - filter("PROD"."BUOM_CURR_SKID" IS NOT NULL AND "PROD"."PROD_END_DATE"=TO_DATE('
9999-12-31 00:00:00', 'syyyy-mm-dd hh24:mi:ss') AND "PROD"."CURR_IND"='Y')
4 - filter("UOM"."RELTV_CURR_QTY"=1)
20 rows selected.
也許你們說 是由於統計資訊過期導致的，那麼我現在把直方圖刪除
SQL> BEGIN
2 DBMS_STATS.GATHER_TABLE_STATS(ownname => 'adwu_optima_we11',
3 tabname => 'OPT_REF_UOM_TEMP_SDIM',
4 estimate_percent => 100,
5 method_opt => 'for columns RELTV_CURR_QTY size 1',
6 degree => DBMS_STATS.AUTO_DEGREE,
7 cascade=>TRUE
8 );
9 END;
10 /
PL/SQL procedure successfully completed.
Elapsed: 00:00:12.65
SQL> explain plan for SELECT UOM.PROD_SKID,
2 MAX (UOM.RELTV_CURR_QTY) RELTV_CURR_QTY,
3 MAX (UOM.STAT_CURR_VAL) STAT_CURR_VAL,
4 MAX (UOM.BAR_CURR_CODE) BAR_CURR_CODE
5 --modified by Daniel on 10/26/2010 for CR 326285 of QMR10.1 hypercare retrofit begin
6 FROM OPT_REF_UOM_TEMP_SDIM UOM,
7 REF_PROD_DIM PROD
8 WHERE UOM.RELTV_CURR_QTY = 1
9 AND PROD.CURR_IND = 'Y'
10 AND PROD.PROD_END_DATE = TO_DATE ('31-12-9999', 'dd-mm-yyyy')
11 AND PROD.PROD_SKID = UOM.PROD_SKID
12 AND PROD.BUOM_CURR_SKID = UOM.UOM_SKID
13 --modified by Daniel on 10/26/2010 for CR 326285 of QMR10.1 hypercare retrofit end
14 GROUP BY UOM.PROD_SKID;
Explained.
Elapsed: 00:00:00.82
SQL> select * from table(dbms_xplan.display);
PLAN_TABLE_OUTPUT
-----------------------------------------------------------------------------------------------------------
Plan hash value: 3215660883
-------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 9 | 486 | 5722 (2)| 00:00:40 |
| 1 | HASH GROUP BY | | 9 | 486 | 5722 (2)| 00:00:40 |
| 2 | NESTED LOOPS | | | | | |
| 3 | NESTED LOOPS | | 1036 | 55944 | 5721 (2)| 00:00:40 |
|* 4 | TABLE ACCESS FULL | OPT_REF_UOM_TEMP_SDIM | 1013 | 35455 | 650 (14)| 00:00:05 |
|* 5 | INDEX RANGE SCAN | PROD_DIM_PK | 3 | | 2 (0)| 00:00:01 |
|* 6 | TABLE ACCESS BY INDEX ROWID| PROD_DIM | 1 | 19 | 5 (0)| 00:00:01 |
-------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
4 - filter("UOM"."RELTV_CURR_QTY"=1)
5 - access("PROD"."PROD_SKID"="UOM"."PROD_SKID")
6 - filter("PROD"."BUOM_CURR_SKID" IS NOT NULL AND "PROD"."PROD_END_DATE"=TO_DATE('
9999-12-31 00:00:00', 'syyyy-mm-dd hh24:mi:ss') AND "PROD"."CURR_IND"='Y' AND
"PROD"."BUOM_CURR_SKID"="UOM"."UOM_SKID")
22 rows selected.
Elapsed: 00:00:01.86
所以，最終導致CBO選錯執行計劃的罪魁禍首就是列沒有收集直方圖統計
透過這一次的案例分析，相信大家對巢狀迴圈的最佳化應該不成問題了，大家一定要深刻理解巢狀迴圈的原理
另外一個就是，大家要深入研究統計資訊，CBO能不能選對執行計劃，很關鍵的就在於統計資訊的準確與否。
來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/29500582/viewspace-1403123/，如需轉載，請註明出處，否則將追究法律責任。
相關文章
- 迴圈_巢狀巢狀
- 記錄一次 postgresql 最佳化案例（ 巢狀迴圈改HASH JOIN ）SQL巢狀
- 最佳化兩個簡單的巢狀迴圈巢狀
- Python 迴圈巢狀Python巢狀
- 巢狀迴圈成本消耗巢狀
- python怎麼迴圈巢狀Python巢狀
- python 跳出巢狀迴圈方法Python巢狀
- MySQL Join原理分析（緩衝塊巢狀與索引巢狀迴圈）MySql巢狀索引
- 微課sql最佳化(14)、表的連線方法(3)-關於Nested Loops Join(巢狀迴圈)SQLOOP巢狀
- python 利用 for ... else 跳出雙層巢狀迴圈Python巢狀
- Python的if else 巢狀 和forin while 迴圈Python巢狀While
- 兩表連線一：巢狀迴圈連線巢狀
- Linux Shell程式設計（17）——巢狀迴圈Linux程式設計巢狀
- java中如何將巢狀迴圈效能提高500倍Java巢狀
- thinkphp中volist的多重迴圈，標籤巢狀PHP巢狀
- 瞭解巢狀迴圈聯接、合併聯接巢狀
- while + else 使用，while死迴圈與while的巢狀，for迴圈基本使用，range關鍵字，for的迴圈補充(break、continue、else) ，for迴圈的巢狀，基本資料型別及內建方法While巢狀資料型別
- 巢狀類遞迴巢狀遞迴
- 迴圈中巢狀非同步操作的流程控制巢狀非同步
- python基礎語法迴圈巢狀和列表（一）Python巢狀
- python基礎語法迴圈巢狀和列表（二）Python巢狀
- Oracle的表連線方法（二）巢狀迴圈連線Oracle巢狀
- 關於迴圈巢狀nested loops的一點分析巢狀OOP
- 【TUNE_ORACLE】列出返回行數較多的巢狀迴圈（NESTED LOOPS）SQL的SQL參考Oracle巢狀OOPSQL
- 高效遍歷匹配Json資料，避免巢狀迴圈[轉]JSON巢狀
- Flutter for迴圈案例Flutter
- 碎片化學習Java（二十）Java for迴圈巢狀輸出指定圖案Java巢狀
- kingbase SQL最佳化案例 ( union遞迴 改 cte遞迴 )SQL遞迴
- PL/SQL 迴圈SQL
- sql server中巢狀事務*SQLServer巢狀
- Sql Server系列：巢狀查詢SQLServer巢狀
- ThinkPHP學習（四）volist標籤高階應用之多重巢狀迴圈PHP巢狀
- 微信小程式入門教程--列表渲染多層巢狀迴圈及wx:key的使用微信小程式巢狀
- 與小卡特一起學python 第11章 巢狀與可變迴圈Python巢狀
- Oracle PL/SQL迴圈示例OracleSQL
- 對比SQL中簡單巢狀查詢與非巢狀查詢CFSQL巢狀
- Java簡單迴圈語句案例Java
- Spring迴圈依賴+案例解析Spring