[20160125]閉包傳遞問題.txt
[20160125]閉包傳遞問題.txt
--所謂閉包傳遞是指sql語句的謂詞條件A=B and B=C 可以推出 A=C. oracle 的 最佳化器能夠利用這個特性最佳化sql語句。
--但是這些依舊存在一些問題，在電子書<Apress.Expert.Oracle.SQL.Optimization.Deployment.and.Statistics.1430259779.pdf>
--提到一個例子，無法實現閉包傳遞。自己重複測試看看：
1.環境：
SCOTT@book> @ &r/ver1
PORT_STRING VERSION BANNER
------------------------------ -------------- --------------------------------------------------------------------------------
x86_64/Linux 2.4.xx 11.2.0.4.0 Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
CREATE TABLE t3 AS SELECT ROWNUM c1 FROM DUAL CONNECT BY LEVEL <= 10;
CREATE TABLE t4 AS SELECT MOD (ROWNUM, 10) + 100 c1 FROM DUAL CONNECT BY LEVEL <= 100;
CREATE TABLE t5 AS SELECT MOD (ROWNUM, 10) c1, RPAD ('X', 30) filler FROM DUAL CONNECT BY LEVEL <= 10000;
CREATE INDEX t5_i1 ON t5 (c1);
--分析表。
2.測試1：
SCOTT@book> alter session set statistics_level=all;
Session altered.
SCOTT@book> SELECT COUNT (*) FROM t3, t5 WHERE t3.c1 = t5.c1 AND t3.c1 = 1;
COUNT(*)
----------
1000
SCOTT@book> @&r/dpc '' ''
PLAN_TABLE_OUTPUT
-------------------------------------
SQL_ID dk73qsratggkh, child number 0
-------------------------------------
SELECT COUNT (*) FROM t3, t5 WHERE t3.c1 = t5.c1 AND t3.c1 = 1
Plan hash value: 1275968336
----------------------------------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows |E-Bytes| Cost (%CPU)| E-Time | A-Rows | A-Time | Buffers | OMem | 1Mem | Used-Mem |
----------------------------------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | | 5 (100)| | 1 |00:00:00.01 | 6 | | | |
| 1 | SORT AGGREGATE | | 1 | 1 | 6 | | | 1 |00:00:00.01 | 6 | | | |
| 2 | MERGE JOIN CARTESIAN| | 1 | 1000 | 6000 | 5 (0)| 00:00:01 | 1000 |00:00:00.01 | 6 | | | |
|* 3 | TABLE ACCESS FULL | T3 | 1 | 1 | 3 | 3 (0)| 00:00:01 | 1 |00:00:00.01 | 2 | | | |
| 4 | BUFFER SORT | | 1 | 1000 | 3000 | 2 (0)| 00:00:01 | 1000 |00:00:00.01 | 4 | 73728 | 73728 | |
|* 5 | INDEX RANGE SCAN | T5_I1 | 1 | 1000 | 3000 | 2 (0)| 00:00:01 | 1000 |00:00:00.01 | 4 | | | |
----------------------------------------------------------------------------------------------------------------------------------------------------
Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------
1 - SEL$1
3 - SEL$1 / T3@SEL$1
5 - SEL$1 / T5@SEL$1
Predicate Information (identified by operation id):
---------------------------------------------------
3 - filter("T3"."C1"=1)
5 - access("T5"."C1"=1)
--可以發現ID=3,5的access或者filter是C1=1.而且取消了連線條件，變成了CARTESIAN集。
3.測試2：
SCOTT@book> SELECT * FROM t3, t4, t5 WHERE t3.c1 = t4.c1 AND t4.c1 = t5.c1;
no rows selected
SCOTT@book> @&r/dpc '' ''
PLAN_TABLE_OUTPUT
-------------------------------------
SQL_ID bd3j0xbhyq4yx, child number 0
-------------------------------------
SELECT * FROM t3, t4, t5 WHERE t3.c1 = t4.c1 AND t4.c1 = t5.c1
Plan hash value: 3251999038
-----------------------------------------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows |E-Bytes| Cost (%CPU)| E-Time | A-Rows | A-Time | Buffers | OMem | 1Mem | Used-Mem |
-----------------------------------------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | | 7 (100)| | 0 |00:00:00.01 | 4 | | | |
| 1 | NESTED LOOPS | | 1 | 1 | 41 | 7 (0)| 00:00:01 | 0 |00:00:00.01 | 4 | | | |
| 2 | NESTED LOOPS | | 1 | 1000 | 41 | 7 (0)| 00:00:01 | 0 |00:00:00.01 | 4 | | | |
|* 3 | HASH JOIN | | 1 | 1 | 7 | 6 (0)| 00:00:01 | 0 |00:00:00.01 | 4 | 2440K| 2440K| 1313K (0)|
| 4 | TABLE ACCESS FULL | T3 | 1 | 10 | 30 | 3 (0)| 00:00:01 | 10 |00:00:00.01 | 2 | | | |
| 5 | TABLE ACCESS FULL | T4 | 1 | 100 | 400 | 3 (0)| 00:00:01 | 100 |00:00:00.01 | 2 | | | |
|* 6 | INDEX RANGE SCAN | T5_I1 | 0 | 1000 | | 1 (0)| 00:00:01 | 0 |00:00:00.01 | 0 | | | |
| 7 | TABLE ACCESS BY INDEX ROWID| T5 | 0 | 1 | 34 | 1 (0)| 00:00:01 | 0 |00:00:00.01 | 0 | | | |
-----------------------------------------------------------------------------------------------------------------------------------------------------------
Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------
1 - SEL$1
4 - SEL$1 / T3@SEL$1
5 - SEL$1 / T4@SEL$1
6 - SEL$1 / T5@SEL$1
7 - SEL$1 / T5@SEL$1
Predicate Information (identified by operation id):
---------------------------------------------------
3 - access("T3"."C1"="T4"."C1")
6 - access("T4"."C1"="T5"."C1")
--如果執行如下語句是等價的： SELECT * FROM t3, t4, t5 WHERE t3.c1 = t5.c1 AND t4.c1 = t5.c1;
SCOTT@book> SELECT * FROM t3, t4, t5 WHERE t3.c1 = t5.c1 AND t4.c1 = t5.c1;
no rows selected
SCOTT@book> @&r/dpc '' ''
PLAN_TABLE_OUTPUT
-------------------------------------
SQL_ID g5007pk488f76, child number 0
-------------------------------------
SELECT * FROM t3, t4, t5 WHERE t3.c1 = t5.c1 AND t4.c1 = t5.c1
Plan hash value: 1630033643
-------------------------------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows |E-Bytes| Cost (%CPU)| E-Time | A-Rows | A-Time | Buffers | OMem | 1Mem | Used-Mem |
-------------------------------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | | 24 (100)| | 0 |00:00:00.01 | 58 | | | |
|* 1 | HASH JOIN | | 1 | 1 | 41 | 24 (0)| 00:00:01 | 0 |00:00:00.01 | 58 | 1021K| 1021K| 159K (0)|
|* 2 | HASH JOIN | | 1 | 1 | 38 | 21 (0)| 00:00:01 | 0 |00:00:00.01 | 58 | 2440K| 2440K| 1230K (0)|
| 3 | TABLE ACCESS FULL| T4 | 1 | 100 | 400 | 3 (0)| 00:00:01 | 100 |00:00:00.01 | 2 | | | |
| 4 | TABLE ACCESS FULL| T5 | 1 | 10000 | 332K| 18 (0)| 00:00:01 | 10000 |00:00:00.01 | 56 | | | |
| 5 | TABLE ACCESS FULL | T3 | 0 | 10 | 30 | 3 (0)| 00:00:01 | 0 |00:00:00.01 | 0 | | | |
-------------------------------------------------------------------------------------------------------------------------------------------------
Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------
1 - SEL$1
3 - SEL$1 / T4@SEL$1
4 - SEL$1 / T5@SEL$1
5 - SEL$1 / T3@SEL$1
Predicate Information (identified by operation id):
---------------------------------------------------
1 - access("T3"."C1"="T5"."C1")
2 - access("T4"."C1"="T5"."C1")
--對比上下執行計劃發生了很大變化，連線順序發生了變化。
4.測試3：
--如果要達到測試1的效果，使用提示：
SELECT /*+ leading(t3 t4 t5) use_hash(t4) use_nl(t5) */ * FROM t3, t4, t5 WHERE t3.c1 = t5.c1 AND t4.c1 = t5.c1;
SCOTT@book> SELECT /*+ leading(t3 t4 t5) use_hash(t4) use_nl(t5) */ * FROM t3, t4, t5 WHERE t3.c1 = t5.c1 AND t4.c1 = t5.c1;
no rows selected
Plan hash value: 3284867853
-----------------------------------------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows |E-Bytes| Cost (%CPU)| E-Time | A-Rows | A-Time | Buffers | OMem | 1Mem | Used-Mem |
-----------------------------------------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | | 1015 (100)| | 0 |00:00:00.01 | 12 | | | |
| 1 | NESTED LOOPS | | 1 | 1 | 41 | 1015 (0)| 00:00:13 | 0 |00:00:00.01 | 12 | | | |
| 2 | NESTED LOOPS | | 1 | 100K| 41 | 1015 (0)| 00:00:13 | 0 |00:00:00.01 | 12 | | | |
| 3 | MERGE JOIN CARTESIAN | | 1 | 1000 | 7000 | 15 (0)| 00:00:01 | 1000 |00:00:00.01 | 4 | | | |
| 4 | TABLE ACCESS FULL | T3 | 1 | 10 | 30 | 3 (0)| 00:00:01 | 10 |00:00:00.01 | 2 | | | |
| 5 | BUFFER SORT | | 10 | 100 | 400 | 12 (0)| 00:00:01 | 1000 |00:00:00.01 | 2 | 4096 | 4096 | 4096 (0)|
| 6 | TABLE ACCESS FULL | T4 | 1 | 100 | 400 | 1 (0)| 00:00:01 | 100 |00:00:00.01 | 2 | | | |
|* 7 | INDEX RANGE SCAN | T5_I1 | 1000 | 100 | | 1 (0)| 00:00:01 | 0 |00:00:00.01 | 8 | | | |
| 8 | TABLE ACCESS BY INDEX ROWID| T5 | 0 | 1 | 34 | 1 (0)| 00:00:01 | 0 |00:00:00.01 | 0 | | | |
-----------------------------------------------------------------------------------------------------------------------------------------------------------
Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------
1 - SEL$1
4 - SEL$1 / T3@SEL$1
6 - SEL$1 / T4@SEL$1
7 - SEL$1 / T5@SEL$1
8 - SEL$1 / T5@SEL$1
Predicate Information (identified by operation id):
---------------------------------------------------
7 - access("T4"."C1"="T5"."C1")
filter("T3"."C1"="T5"."C1")
--實際上並沒有實現上面的執行計劃，而是產生了CARTESIAN集合。必須要加入t3.c1=t4.c1條件。
SCOTT@book> SELECT /*+ leading(t3 t4 t5) use_hash(t4) use_nl(t5) */ * FROM t3, t4, t5 WHERE t3.c1 = t5.c1 AND t4.c1 = t5.c1 and t3.c1=t4.c1;
no rows selected
SCOTT@book> @ &r/dpc '' ''
PLAN_TABLE_OUTPUT
-------------------------------------
SQL_ID 9nzh9uxm6b11z, child number 0
-------------------------------------
SELECT /*+ leading(t3 t4 t5) use_hash(t4) use_nl(t5) */ * FROM t3, t4,
t5 WHERE t3.c1 = t5.c1 AND t4.c1 = t5.c1 and t3.c1=t4.c1
Plan hash value: 3251999038
-----------------------------------------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows |E-Bytes| Cost (%CPU)| E-Time | A-Rows | A-Time | Buffers | OMem | 1Mem | Used-Mem |
-----------------------------------------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | | 7 (100)| | 0 |00:00:00.01 | 4 | | | |
| 1 | NESTED LOOPS | | 1 | 1 | 41 | 7 (0)| 00:00:01 | 0 |00:00:00.01 | 4 | | | |
| 2 | NESTED LOOPS | | 1 | 100 | 41 | 7 (0)| 00:00:01 | 0 |00:00:00.01 | 4 | | | |
|* 3 | HASH JOIN | | 1 | 1 | 7 | 6 (0)| 00:00:01 | 0 |00:00:00.01 | 4 | 2440K| 2440K| 1333K (0)|
| 4 | TABLE ACCESS FULL | T3 | 1 | 10 | 30 | 3 (0)| 00:00:01 | 10 |00:00:00.01 | 2 | | | |
| 5 | TABLE ACCESS FULL | T4 | 1 | 100 | 400 | 3 (0)| 00:00:01 | 100 |00:00:00.01 | 2 | | | |
|* 6 | INDEX RANGE SCAN | T5_I1 | 0 | 100 | | 1 (0)| 00:00:01 | 0 |00:00:00.01 | 0 | | | |
| 7 | TABLE ACCESS BY INDEX ROWID| T5 | 0 | 1 | 34 | 1 (0)| 00:00:01 | 0 |00:00:00.01 | 0 | | | |
-----------------------------------------------------------------------------------------------------------------------------------------------------------
Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------
1 - SEL$1
4 - SEL$1 / T3@SEL$1
5 - SEL$1 / T4@SEL$1
6 - SEL$1 / T5@SEL$1
7 - SEL$1 / T5@SEL$1
Predicate Information (identified by operation id):
---------------------------------------------------
3 - access("T3"."C1"="T4"."C1")
6 - access("T4"."C1"="T5"."C1")
filter("T3"."C1"="T5"."C1")
--看來以後在最佳化多表連線sql語句時適當加入多餘的條件看看執行計劃是否存在變化。也說明oracle的最佳化器還有許多需要改進的地方。
來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/267265/viewspace-1981803/，如需轉載，請註明出處，否則將追究法律責任。
相關文章
- [20160213]閉包傳遞5.txt
- [20160212]閉包傳遞4.txt
- [20160211]閉包傳遞3.txt
- [20160210]閉包傳遞2.txt
- 利用閉包傳遞引數
- 閉包 | 淺談JavaScript閉包問題JavaScript
- POJ 3360-Cow Contest(傳遞閉包)
- go 閉包捕獲問題Go
- Js 一道閉包問題JS
- POJ3660 Cow Contest【Floyd演算法 傳遞閉包】演算法
- swift 閉包傳值Swift
- poj2594Treasure Exploration【最小路徑覆蓋+floyd傳遞閉包】
- 揹包問題的遞迴與非遞迴演算法遞迴演算法
- php傳遞json給jquery的問題PHPJSONjQuery
- String型別函式傳遞問題型別函式
- Python閉包區域性變數問題Python變數
- JavaScript閉包與變數的經典問題JavaScript變數
- react要避免閉包問題，具體指的是哪些？React
- 二維陣列作為引數傳遞問題陣列
- rake 任務引數傳遞問題解決
- 一個關於值傳遞呼叫的問題
- go語言採坑：閉包共享變數問題Go變數
- vue中methods中的方法閉包快取問題Vue快取
- 面試官問：Go 中的引數傳遞是值傳遞還是引用傳遞？面試Go
- js閉包面試題目JS面試題
- 面試題：如何理解閉包面試題
- 解決CORS跨域不能傳遞cookies的問題CORS跨域Cookie
- Python-單繼承中值傳遞的問題Python繼承
- 分頁及查詢引數傳遞問題分享
- 一個STL物件的DLL邊界傳遞問題物件
- 不同資料庫間傳遞資料的問題資料庫
- JS函式表示式——函式遞迴、閉包JS函式遞迴
- 引數傳遞中編碼問題（Get/Post 方式）（一）
- 引數傳遞中編碼問題（Get/Post 方式）（二）
- 請教一個JSF引數傳遞的問題JS
- 經典 JS 閉包面試題JS面試題
- 還擔心面試官問閉包？面試
- （求教）關於js函式按值、引用傳遞的問題JS函式