[20160210]閉包傳遞2.txt
[20160210]閉包傳遞2.txt
--所謂閉包傳遞是指sql語句的謂詞條件A=B and B=C 可以推出 A=C. oracle 的 最佳化器能夠利用這個特性最佳化sql語句。
--前一陣子看電子電子書<Apress.Expert.Oracle.SQL.Optimization.Deployment.and.Statistics.1430259779.pdf>測試連結:
http://blog.itpub.net/267265/viewspace-1981803/
--當時並沒有考慮許多細節,僅僅按照例子重複在12c下測試看看:
1.測試環境:
SCOTT@test01p> @ ver1
PORT_STRING VERSION BANNER CON_ID
------------------------------ -------------- -------------------------------------------------------------------------------- ----------
IBMPC/WIN_NT64-9.1.0 12.1.0.1.0 Oracle Database 12c Enterprise Edition Release 12.1.0.1.0 - 64bit Production 0
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);
-- 分析表.Method_Opt => 'FOR ALL COLUMNS SIZE 1 '
2.測試:
SCOTT@test01p> alter session set statistics_level = all;
Session altered.
SCOTT@test01p> SELECT * FROM t3, t4, t5 WHERE t3.c1 = t4.c1 AND t4.c1 = t5.c1;
no rows selected
SCOTT@test01p> @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 | 6 | | | |
| 1 | NESTED LOOPS | | 1 | | | | | 0 |00:00:00.01 | 6 | | | |
| 2 | NESTED LOOPS | | 1 | 1 | 41 | 7 (0)| 00:00:01 | 0 |00:00:00.01 | 6 | | | |
|* 3 | HASH JOIN | | 1 | 1 | 7 | 6 (0)| 00:00:01 | 0 |00:00:00.01 | 6 | 2440K| 2440K| 1496K (0)|
| 4 | TABLE ACCESS FULL | T3 | 1 | 10 | 30 | 3 (0)| 00:00:01 | 10 |00:00:00.01 | 3 | | | |
| 5 | TABLE ACCESS FULL | T4 | 1 | 100 | 400 | 3 (0)| 00:00:01 | 100 |00:00:00.01 | 3 | | | |
|* 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):
-------------------------------------------------------------
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")
Note
-----
- this is an adaptive plan
SCOTT@test01p> SELECT * FROM t3, t4, t5 WHERE t3.c1 = t4.c1 AND t3.c1 = t5.c1;
no rows selected
SCOTT@test01p> @ 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 | 60 | | | |
|* 1 | HASH JOIN | | 1 | 1 | 41 | 24 (0)| 00:00:01 | 0 |00:00:00.01 | 60 | 987K| 987K| 201K (0)|
|* 2 | HASH JOIN | | 1 | 1 | 38 | 21 (0)| 00:00:01 | 0 |00:00:00.01 | 60 | 1969K| 1969K| 1086K (0)|
| 3 | TABLE ACCESS FULL| T4 | 1 | 100 | 400 | 3 (0)| 00:00:01 | 100 |00:00:00.01 | 3 | | | |
| 4 | TABLE ACCESS FULL| T5 | 1 | 10000 | 332K| 18 (0)| 00:00:01 | 10000 |00:00:00.01 | 57 | | | |
| 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")
Note
-----
- this is an adaptive plan
--12C依舊,雖然出現執行計劃採用adaptive plan,但是連線順序發生無法出現t3,t4連線的情況.
3.繼續測試,加入提示看看:
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@test01p> @dpc '' ''
PLAN_TABLE_OUTPUT
-------------------------------------
SQL_ID 0b7tsj1ajpq0r, 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
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 | | | 1017 (100)| | 0 |00:00:00.01 | 14 | | | |
| 1 | NESTED LOOPS | | 1 | | | | | 0 |00:00:00.01 | 14 | | | |
| 2 | NESTED LOOPS | | 1 | 1 | 41 | 1017 (1)| 00:00:01 | 0 |00:00:00.01 | 14 | | | |
| 3 | MERGE JOIN CARTESIAN | | 1 | 1000 | 7000 | 15 (0)| 00:00:01 | 1000 |00:00:00.01 | 6 | | | |
| 4 | TABLE ACCESS FULL | T3 | 1 | 10 | 30 | 3 (0)| 00:00:01 | 10 |00:00:00.01 | 3 | | | |
| 5 | BUFFER SORT | | 10 | 100 | 400 | 12 (0)| 00:00:01 | 1000 |00:00:00.01 | 3 | 4096 | 4096 | 4096 (0)|
| 6 | TABLE ACCESS FULL | T4 | 1 | 100 | 400 | 1 (0)| 00:00:01 | 100 |00:00:00.01 | 3 | | | |
|* 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")
--剛使用提示時,可以發現問題依舊,T3,T4兩個表的連結走的是不是hash join,而是CARTESIAN.
--加入t3.c1=t4.c1條件。
SCOTT@test01p> 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@test01p> @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 | 6 | | | |
| 1 | NESTED LOOPS | | 1 | | | | | 0 |00:00:00.01 | 6 | | | |
| 2 | NESTED LOOPS | | 1 | 1 | 41 | 7 (0)| 00:00:01 | 0 |00:00:00.01 | 6 | | | |
|* 3 | HASH JOIN | | 1 | 1 | 7 | 6 (0)| 00:00:01 | 0 |00:00:00.01 | 6 | 2440K| 2440K| 1496K (0)|
| 4 | TABLE ACCESS FULL | T3 | 1 | 10 | 30 | 3 (0)| 00:00:01 | 10 |00:00:00.01 | 3 | | | |
| 5 | TABLE ACCESS FULL | T4 | 1 | 100 | 400 | 3 (0)| 00:00:01 | 100 |00:00:00.01 | 3 | | | |
|* 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")
--作者的例子非常特殊.T3,T4連線返回記錄為0.不過閉包傳遞確實存在一些問題.
4.自己做一些例子,測試看看:
create table t1 as select rownum id,rownum||'t1' name from dual connect by level<=5;
create table t2 as select rownum+1 id,rownum||'t2' name from dual connect by level<=5;
create table t3 as select rownum+1 id,rownum||'t3' name from dual connect by level<=5;
--分析表略.
select * from t1,t2,t3 where t1.id=t2.id and t2.id=t3.id;
Plan hash value: 261998084
-------------------------------------------------------------------------------------------------------------------------------------------------
| 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 | | | 9 (100)| | 3 |00:00:00.01 | 10 | | | |
|* 1 | HASH JOIN | | 1 | 5 | 105 | 9 (0)| 00:00:01 | 3 |00:00:00.01 | 10 | 1451K| 1451K| 919K (0)|
|* 2 | HASH JOIN | | 1 | 5 | 70 | 6 (0)| 00:00:01 | 4 |00:00:00.01 | 6 | 1888K| 1888K| 1091K (0)|
| 3 | TABLE ACCESS FULL| T1 | 1 | 5 | 35 | 3 (0)| 00:00:01 | 5 |00:00:00.01 | 3 | | | |
| 4 | TABLE ACCESS FULL| T2 | 1 | 5 | 35 | 3 (0)| 00:00:01 | 5 |00:00:00.01 | 3 | | | |
| 5 | TABLE ACCESS FULL | T3 | 1 | 5 | 35 | 3 (0)| 00:00:01 | 5 |00:00:00.01 | 4 | | | |
-------------------------------------------------------------------------------------------------------------------------------------------------
select * from t1,t2,t3 where t1.id=t3.id and t2.id=t3.id;
Plan hash value: 2308542799
-------------------------------------------------------------------------------------------------------------------------------------------------
| 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 | | | 9 (100)| | 3 |00:00:00.01 | 10 | | | |
|* 1 | HASH JOIN | | 1 | 5 | 105 | 9 (0)| 00:00:01 | 3 |00:00:00.01 | 10 | 1451K| 1451K| 804K (0)|
|* 2 | HASH JOIN | | 1 | 5 | 70 | 6 (0)| 00:00:01 | 3 |00:00:00.01 | 6 | 1888K| 1888K| 1090K (0)|
| 3 | TABLE ACCESS FULL| T1 | 1 | 5 | 35 | 3 (0)| 00:00:01 | 5 |00:00:00.01 | 3 | | | |
| 4 | TABLE ACCESS FULL| T3 | 1 | 5 | 35 | 3 (0)| 00:00:01 | 5 |00:00:00.01 | 3 | | | |
| 5 | TABLE ACCESS FULL | T2 | 1 | 5 | 35 | 3 (0)| 00:00:01 | 5 |00:00:00.01 | 4 | | | |
-------------------------------------------------------------------------------------------------------------------------------------------------
select /*+ leading (t1 t2 t3) */ * from t1,t2,t3 where t1.id=t3.id and t2.id=t3.id;
---------------------------------------------------------------------------------------------------------------------------------------------------
| 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 | | | 13 (100)| | 3 |00:00:00.01 | 10 | | | |
|* 1 | HASH JOIN | | 1 | 5 | 105 | 13 (0)| 00:00:01 | 3 |00:00:00.01 | 10 | 1451K| 1451K| 1514K (0)|
| 2 | MERGE JOIN CARTESIAN| | 1 | 25 | 350 | 10 (0)| 00:00:01 | 25 |00:00:00.01 | 6 | | | |
| 3 | TABLE ACCESS FULL | T1 | 1 | 5 | 35 | 3 (0)| 00:00:01 | 5 |00:00:00.01 | 3 | | | |
| 4 | BUFFER SORT | | 5 | 5 | 35 | 7 (0)| 00:00:01 | 25 |00:00:00.01 | 3 | 2048 | 2048 | 2048 (0)|
| 5 | TABLE ACCESS FULL | T2 | 1 | 5 | 35 | 1 (0)| 00:00:01 | 5 |00:00:00.01 | 3 | | | |
| 6 | TABLE ACCESS FULL | T3 | 1 | 5 | 35 | 3 (0)| 00:00:01 | 5 |00:00:00.01 | 4 | | | |
---------------------------------------------------------------------------------------------------------------------------------------------------
--可以發現T1,T2的連線走的是CARTESIAN.看來閉包傳遞確實存在其它問題.
--所謂閉包傳遞是指sql語句的謂詞條件A=B and B=C 可以推出 A=C. oracle 的 最佳化器能夠利用這個特性最佳化sql語句。
--前一陣子看電子電子書<Apress.Expert.Oracle.SQL.Optimization.Deployment.and.Statistics.1430259779.pdf>測試連結:
http://blog.itpub.net/267265/viewspace-1981803/
--當時並沒有考慮許多細節,僅僅按照例子重複在12c下測試看看:
1.測試環境:
SCOTT@test01p> @ ver1
PORT_STRING VERSION BANNER CON_ID
------------------------------ -------------- -------------------------------------------------------------------------------- ----------
IBMPC/WIN_NT64-9.1.0 12.1.0.1.0 Oracle Database 12c Enterprise Edition Release 12.1.0.1.0 - 64bit Production 0
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);
-- 分析表.Method_Opt => 'FOR ALL COLUMNS SIZE 1 '
2.測試:
SCOTT@test01p> alter session set statistics_level = all;
Session altered.
SCOTT@test01p> SELECT * FROM t3, t4, t5 WHERE t3.c1 = t4.c1 AND t4.c1 = t5.c1;
no rows selected
SCOTT@test01p> @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 | 6 | | | |
| 1 | NESTED LOOPS | | 1 | | | | | 0 |00:00:00.01 | 6 | | | |
| 2 | NESTED LOOPS | | 1 | 1 | 41 | 7 (0)| 00:00:01 | 0 |00:00:00.01 | 6 | | | |
|* 3 | HASH JOIN | | 1 | 1 | 7 | 6 (0)| 00:00:01 | 0 |00:00:00.01 | 6 | 2440K| 2440K| 1496K (0)|
| 4 | TABLE ACCESS FULL | T3 | 1 | 10 | 30 | 3 (0)| 00:00:01 | 10 |00:00:00.01 | 3 | | | |
| 5 | TABLE ACCESS FULL | T4 | 1 | 100 | 400 | 3 (0)| 00:00:01 | 100 |00:00:00.01 | 3 | | | |
|* 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):
-------------------------------------------------------------
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")
Note
-----
- this is an adaptive plan
SCOTT@test01p> SELECT * FROM t3, t4, t5 WHERE t3.c1 = t4.c1 AND t3.c1 = t5.c1;
no rows selected
SCOTT@test01p> @ 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 | 60 | | | |
|* 1 | HASH JOIN | | 1 | 1 | 41 | 24 (0)| 00:00:01 | 0 |00:00:00.01 | 60 | 987K| 987K| 201K (0)|
|* 2 | HASH JOIN | | 1 | 1 | 38 | 21 (0)| 00:00:01 | 0 |00:00:00.01 | 60 | 1969K| 1969K| 1086K (0)|
| 3 | TABLE ACCESS FULL| T4 | 1 | 100 | 400 | 3 (0)| 00:00:01 | 100 |00:00:00.01 | 3 | | | |
| 4 | TABLE ACCESS FULL| T5 | 1 | 10000 | 332K| 18 (0)| 00:00:01 | 10000 |00:00:00.01 | 57 | | | |
| 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")
Note
-----
- this is an adaptive plan
--12C依舊,雖然出現執行計劃採用adaptive plan,但是連線順序發生無法出現t3,t4連線的情況.
3.繼續測試,加入提示看看:
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@test01p> @dpc '' ''
PLAN_TABLE_OUTPUT
-------------------------------------
SQL_ID 0b7tsj1ajpq0r, 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
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 | | | 1017 (100)| | 0 |00:00:00.01 | 14 | | | |
| 1 | NESTED LOOPS | | 1 | | | | | 0 |00:00:00.01 | 14 | | | |
| 2 | NESTED LOOPS | | 1 | 1 | 41 | 1017 (1)| 00:00:01 | 0 |00:00:00.01 | 14 | | | |
| 3 | MERGE JOIN CARTESIAN | | 1 | 1000 | 7000 | 15 (0)| 00:00:01 | 1000 |00:00:00.01 | 6 | | | |
| 4 | TABLE ACCESS FULL | T3 | 1 | 10 | 30 | 3 (0)| 00:00:01 | 10 |00:00:00.01 | 3 | | | |
| 5 | BUFFER SORT | | 10 | 100 | 400 | 12 (0)| 00:00:01 | 1000 |00:00:00.01 | 3 | 4096 | 4096 | 4096 (0)|
| 6 | TABLE ACCESS FULL | T4 | 1 | 100 | 400 | 1 (0)| 00:00:01 | 100 |00:00:00.01 | 3 | | | |
|* 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")
--剛使用提示時,可以發現問題依舊,T3,T4兩個表的連結走的是不是hash join,而是CARTESIAN.
--加入t3.c1=t4.c1條件。
SCOTT@test01p> 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@test01p> @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 | 6 | | | |
| 1 | NESTED LOOPS | | 1 | | | | | 0 |00:00:00.01 | 6 | | | |
| 2 | NESTED LOOPS | | 1 | 1 | 41 | 7 (0)| 00:00:01 | 0 |00:00:00.01 | 6 | | | |
|* 3 | HASH JOIN | | 1 | 1 | 7 | 6 (0)| 00:00:01 | 0 |00:00:00.01 | 6 | 2440K| 2440K| 1496K (0)|
| 4 | TABLE ACCESS FULL | T3 | 1 | 10 | 30 | 3 (0)| 00:00:01 | 10 |00:00:00.01 | 3 | | | |
| 5 | TABLE ACCESS FULL | T4 | 1 | 100 | 400 | 3 (0)| 00:00:01 | 100 |00:00:00.01 | 3 | | | |
|* 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")
--作者的例子非常特殊.T3,T4連線返回記錄為0.不過閉包傳遞確實存在一些問題.
4.自己做一些例子,測試看看:
create table t1 as select rownum id,rownum||'t1' name from dual connect by level<=5;
create table t2 as select rownum+1 id,rownum||'t2' name from dual connect by level<=5;
create table t3 as select rownum+1 id,rownum||'t3' name from dual connect by level<=5;
--分析表略.
select * from t1,t2,t3 where t1.id=t2.id and t2.id=t3.id;
Plan hash value: 261998084
-------------------------------------------------------------------------------------------------------------------------------------------------
| 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 | | | 9 (100)| | 3 |00:00:00.01 | 10 | | | |
|* 1 | HASH JOIN | | 1 | 5 | 105 | 9 (0)| 00:00:01 | 3 |00:00:00.01 | 10 | 1451K| 1451K| 919K (0)|
|* 2 | HASH JOIN | | 1 | 5 | 70 | 6 (0)| 00:00:01 | 4 |00:00:00.01 | 6 | 1888K| 1888K| 1091K (0)|
| 3 | TABLE ACCESS FULL| T1 | 1 | 5 | 35 | 3 (0)| 00:00:01 | 5 |00:00:00.01 | 3 | | | |
| 4 | TABLE ACCESS FULL| T2 | 1 | 5 | 35 | 3 (0)| 00:00:01 | 5 |00:00:00.01 | 3 | | | |
| 5 | TABLE ACCESS FULL | T3 | 1 | 5 | 35 | 3 (0)| 00:00:01 | 5 |00:00:00.01 | 4 | | | |
-------------------------------------------------------------------------------------------------------------------------------------------------
select * from t1,t2,t3 where t1.id=t3.id and t2.id=t3.id;
Plan hash value: 2308542799
-------------------------------------------------------------------------------------------------------------------------------------------------
| 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 | | | 9 (100)| | 3 |00:00:00.01 | 10 | | | |
|* 1 | HASH JOIN | | 1 | 5 | 105 | 9 (0)| 00:00:01 | 3 |00:00:00.01 | 10 | 1451K| 1451K| 804K (0)|
|* 2 | HASH JOIN | | 1 | 5 | 70 | 6 (0)| 00:00:01 | 3 |00:00:00.01 | 6 | 1888K| 1888K| 1090K (0)|
| 3 | TABLE ACCESS FULL| T1 | 1 | 5 | 35 | 3 (0)| 00:00:01 | 5 |00:00:00.01 | 3 | | | |
| 4 | TABLE ACCESS FULL| T3 | 1 | 5 | 35 | 3 (0)| 00:00:01 | 5 |00:00:00.01 | 3 | | | |
| 5 | TABLE ACCESS FULL | T2 | 1 | 5 | 35 | 3 (0)| 00:00:01 | 5 |00:00:00.01 | 4 | | | |
-------------------------------------------------------------------------------------------------------------------------------------------------
select /*+ leading (t1 t2 t3) */ * from t1,t2,t3 where t1.id=t3.id and t2.id=t3.id;
---------------------------------------------------------------------------------------------------------------------------------------------------
| 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 | | | 13 (100)| | 3 |00:00:00.01 | 10 | | | |
|* 1 | HASH JOIN | | 1 | 5 | 105 | 13 (0)| 00:00:01 | 3 |00:00:00.01 | 10 | 1451K| 1451K| 1514K (0)|
| 2 | MERGE JOIN CARTESIAN| | 1 | 25 | 350 | 10 (0)| 00:00:01 | 25 |00:00:00.01 | 6 | | | |
| 3 | TABLE ACCESS FULL | T1 | 1 | 5 | 35 | 3 (0)| 00:00:01 | 5 |00:00:00.01 | 3 | | | |
| 4 | BUFFER SORT | | 5 | 5 | 35 | 7 (0)| 00:00:01 | 25 |00:00:00.01 | 3 | 2048 | 2048 | 2048 (0)|
| 5 | TABLE ACCESS FULL | T2 | 1 | 5 | 35 | 1 (0)| 00:00:01 | 5 |00:00:00.01 | 3 | | | |
| 6 | TABLE ACCESS FULL | T3 | 1 | 5 | 35 | 3 (0)| 00:00:01 | 5 |00:00:00.01 | 4 | | | |
---------------------------------------------------------------------------------------------------------------------------------------------------
--可以發現T1,T2的連線走的是CARTESIAN.看來閉包傳遞確實存在其它問題.
來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/267265/viewspace-1987668/，如需轉載，請註明出處，否則將追究法律責任。
相關文章
- 利用閉包傳遞引數
- POJ 3360-Cow Contest(傳遞閉包)
- [20160213]閉包傳遞5.txt
- [20160212]閉包傳遞4.txt
- [20160211]閉包傳遞3.txt
- POJ3660 Cow Contest【Floyd演算法 傳遞閉包】演算法
- [20160125]閉包傳遞問題.txt
- swift 閉包傳值Swift
- poj2594Treasure Exploration【最小路徑覆蓋+floyd傳遞閉包】
- JS函式表示式——函式遞迴、閉包JS函式遞迴
- 值傳遞與引用傳遞
- 值傳遞和引用傳遞
- 閉包
- BZOJ 1612 [Usaco2008 Jan]Cow Contest奶牛的比賽：floyd傳遞閉包
- 用閉包替換遞迴實現斐波拉契數列遞迴
- 閉包 | 淺談JavaScript閉包問題JavaScript
- Swift-逃逸閉包、自動閉包Swift
- Windows 10傳遞最佳化功能有什麼用 Windows 10傳遞最佳化功能怎麼關閉Windows
- JavaScript的值傳遞和引用傳遞JavaScript
- 快速搞懂值傳遞與引用傳遞
- Java的值傳遞和引用傳遞Java
- Python3之從遞迴到閉包再到裝飾器Python遞迴
- 【集合論】關係閉包 ( 關係閉包求法 | 關係圖求閉包 | 關係矩陣求閉包 | 閉包運算與關係性質 | 閉包複合運算 )矩陣
- PHP 閉包PHP
- JavaScript閉包JavaScript
- Golang閉包Golang
- golang 閉包Golang
- JavaScript 閉包JavaScript
- 理解“閉包”
- Swift 閉包Swift
- Swift，閉包Swift
- 理解閉包
- Swift - 閉包Swift
- lisp 閉包Lisp
- JavaScript - 閉包JavaScript
- go 值傳遞和地址傳遞的例子Go
- Java - 是值傳遞還是引用傳遞Java
- Java中的值傳遞和引用傳遞Java