[20200801]sql hint衝突.txt
[20200801]sql hint衝突.txt
--//昨天看崔華<基於oracle的sql最佳化>,發現自己以前對於這個問題沒理解清楚.
1.環境:
SCOTT@test01p> @ ver1
PORT_STRING VERSION BANNER CON_ID
------------------------------ -------------- -------------------------------------------------------------------------------- ----------
IBMPC/WIN_NT64-9.1.0 12.2.0.1.0 Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production 0
2.測試:
SCOTT@test01p> alter session set statistics_level = all;
Session altered.
SCOTT@test01p> select /*+ use_hash(dept) */ emp.empno,emp.ename,dept.dname from emp,dept where dept.deptno=emp.deptno;
EMPNO ENAME DNAME
----- ---------- ----------
7782 CLARK ACCOUNTING
7839 KING ACCOUNTING
7934 MILLER ACCOUNTING
7566 JONES RESEARCH
7902 FORD RESEARCH
7876 ADAMS RESEARCH
7369 SMITH RESEARCH
7788 SCOTT RESEARCH
7521 WARD SALES
7844 TURNER SALES
7499 ALLEN SALES
7900 JAMES SALES
7698 BLAKE SALES
7654 MARTIN SALES
14 rows selected.
SCOTT@test01p> @ dpc '' ''
PLAN_TABLE_OUTPUT
-------------------------------------
SQL_ID fcvzctv91801v, child number 0
-------------------------------------
select /*+ use_hash(dept) */ emp.empno,emp.ename,dept.dname from
emp,dept where dept.deptno=emp.deptno
Plan hash value: 844388907
-------------------------------------------------------------------------------------------------------------------------------------------------------------
| 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 | | | 6 (100)| | 14 |00:00:00.01 | 11 | | | |
| 1 | MERGE JOIN | | 1 | 14 | 364 | 6 (17)| 00:00:01 | 14 |00:00:00.01 | 11 | | | |
| 2 | TABLE ACCESS BY INDEX ROWID| DEPT | 1 | 4 | 52 | 2 (0)| 00:00:01 | 4 |00:00:00.01 | 4 | | | |
| 3 | INDEX FULL SCAN | PK_DEPT | 1 | 4 | | 1 (0)| 00:00:01 | 4 |00:00:00.01 | 2 | | | |
|* 4 | SORT JOIN | | 4 | 14 | 182 | 4 (25)| 00:00:01 | 14 |00:00:00.01 | 7 | 2048 | 2048 | 2048 (0)|
| 5 | TABLE ACCESS FULL | EMP | 1 | 14 | 182 | 3 (0)| 00:00:01 | 14 |00:00:00.01 | 7 | | | |
-------------------------------------------------------------------------------------------------------------------------------------------------------------
Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------
1 - SEL$1
2 - SEL$1 / DEPT@SEL$1
3 - SEL$1 / DEPT@SEL$1
5 - SEL$1 / EMP@SEL$1
Predicate Information (identified by operation id):
---------------------------------------------------
4 - access("DEPT"."DEPTNO"="EMP"."DEPTNO")
filter("DEPT"."DEPTNO"="EMP"."DEPTNO")
32 rows selected.
--//可以發現sql hint無效,選擇merge join.
--//做hash連線,表dept無法做被驅動表.因為一般做hash連線都是小結果集做驅動表,大結果集做被驅動表.
--//sql語句中emp的結果集大,而dept的結果集小.而只有加入leading提示固定.
3.繼續:
SCOTT@test01p> select /*+ leading(emp dept) use_hash(dept) */ emp.empno,emp.ename,dept.dname from emp,dept where dept.deptno=emp.deptno;
EMPNO ENAME DNAME
----- ---------- --------------------
7782 CLARK ACCOUNTING
...
14 rows selected.
SCOTT@test01p> @ dpc '' ''
PLAN_TABLE_OUTPUT
-------------------------------------
SQL_ID 2mgqptn3ym690, child number 0
-------------------------------------
select /*+ leading(emp dept) use_hash(dept) */
emp.empno,emp.ename,dept.dname from emp,dept where
dept.deptno=emp.deptno
Plan hash value: 1123238657
------------------------------------------------------------------------------------------------------------------------------------------------
| 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 | | | 6 (100)| | 14 |00:00:00.01 | 15 | | | |
|* 1 | HASH JOIN | | 1 | 14 | 364 | 6 (0)| 00:00:01 | 14 |00:00:00.01 | 15 | 1650K| 1650K| 1048K (0)|
| 2 | TABLE ACCESS FULL| EMP | 1 | 14 | 182 | 3 (0)| 00:00:01 | 14 |00:00:00.01 | 7 | | | |
| 3 | TABLE ACCESS FULL| DEPT | 1 | 4 | 52 | 3 (0)| 00:00:01 | 4 |00:00:00.01 | 8 | | | |
------------------------------------------------------------------------------------------------------------------------------------------------
Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------
1 - SEL$1
2 - SEL$1 / EMP@SEL$1
3 - SEL$1 / DEPT@SEL$1
Predicate Information (identified by operation id):
---------------------------------------------------
1 - access("DEPT"."DEPTNO"="EMP"."DEPTNO")
29 rows selected.
--//OK.
SCOTT@test01p> select /*+ use_hash(dept emp) */ emp.empno,emp.ename,dept.dname from emp,dept where dept.deptno=emp.deptno;
...
SCOTT@test01p> @ dpc '' ''
PLAN_TABLE_OUTPUT
-------------------------------------
SQL_ID 0vs9jmh173bu1, child number 0
-------------------------------------
select /*+ use_hash(dept emp) */ emp.empno,emp.ename,dept.dname from
emp,dept where dept.deptno=emp.deptno
Plan hash value: 615168685
------------------------------------------------------------------------------------------------------------------------------------------------
| 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 | | | 6 (100)| | 14 |00:00:00.01 | 15 | | | |
|* 1 | HASH JOIN | | 1 | 14 | 364 | 6 (0)| 00:00:01 | 14 |00:00:00.01 | 15 | 1695K| 1695K| 1070K (0)|
| 2 | TABLE ACCESS FULL| DEPT | 1 | 4 | 52 | 3 (0)| 00:00:01 | 4 |00:00:00.01 | 7 | | | |
| 3 | TABLE ACCESS FULL| EMP | 1 | 14 | 182 | 3 (0)| 00:00:01 | 14 |00:00:00.01 | 8 | | | |
------------------------------------------------------------------------------------------------------------------------------------------------
Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------
1 - SEL$1
2 - SEL$1 / DEPT@SEL$1
3 - SEL$1 / EMP@SEL$1
Predicate Information (identified by operation id):
---------------------------------------------------
1 - access("DEPT"."DEPTNO"="EMP"."DEPTNO")
SCOTT@test01p> select /*+ use_hash(emp dept) */ emp.empno,emp.ename,dept.dname from emp,dept where dept.deptno=emp.deptno;
SCOTT@test01p> @ dpc '' ''
PLAN_TABLE_OUTPUT
-------------------------------------
SQL_ID 58rkm699up0gv, child number 0
-------------------------------------
select /*+ use_hash(emp dept) */ emp.empno,emp.ename,dept.dname from
emp,dept where dept.deptno=emp.deptno
Plan hash value: 615168685
------------------------------------------------------------------------------------------------------------------------------------------------
| 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 | | | 6 (100)| | 14 |00:00:00.01 | 15 | | | |
|* 1 | HASH JOIN | | 1 | 14 | 364 | 6 (0)| 00:00:01 | 14 |00:00:00.01 | 15 | 1695K| 1695K| 1070K (0)|
| 2 | TABLE ACCESS FULL| DEPT | 1 | 4 | 52 | 3 (0)| 00:00:01 | 4 |00:00:00.01 | 7 | | | |
| 3 | TABLE ACCESS FULL| EMP | 1 | 14 | 182 | 3 (0)| 00:00:01 | 14 |00:00:00.01 | 8 | | | |
------------------------------------------------------------------------------------------------------------------------------------------------
Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------
1 - SEL$1
2 - SEL$1 / DEPT@SEL$1
3 - SEL$1 / EMP@SEL$1
Predicate Information (identified by operation id):
---------------------------------------------------
1 - access("DEPT"."DEPTNO"="EMP"."DEPTNO")
--//無論/*+ use_hash(dept emp) */ ,/*+ use_hash(emp dept) */ 提示,dept僅僅做驅動表.
--//如果看執行計劃的Outline Data.
Outline Data
-------------
/*+
BEGIN_OUTLINE_DATA
IGNORE_OPTIM_EMBEDDED_HINTS
OPTIMIZER_FEATURES_ENABLE('12.2.0.1')
DB_VERSION('12.2.0.1')
ALL_ROWS
OUTLINE_LEAF(@"SEL$1")
FULL(@"SEL$1" "DEPT"@"SEL$1")
FULL(@"SEL$1" "EMP"@"SEL$1")
LEADING(@"SEL$1" "DEPT"@"SEL$1" "EMP"@"SEL$1")
USE_HASH(@"SEL$1" "EMP"@"SEL$1")
END_OUTLINE_DATA
*/
--//提示use_NL,use_merge也存在類似的情況,裡面的引數是被驅動表.最佳化時最好配合leading提示.
--//避免一些歧義性.同時也要注意避擴音示衝突.比如:
SCOTT@test01p> select /*+ leading(emp dept) use_nl(emp) */ emp.empno,emp.ename,dept.dname from emp,dept where dept.deptno=emp.deptno;
Plan hash value: 1123238657
-------------------------------------------------------------------------------------------------------
| Id | Operation | Name | E-Rows |E-Bytes| Cost (%CPU)| E-Time | OMem | 1Mem | Used-Mem |
-------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 6 (100)| | | | |
|* 1 | HASH JOIN | | 14 | 364 | 6 (0)| 00:00:01 | 1572K| 1572K| 1033K (0)|
| 2 | TABLE ACCESS FULL| EMP | 14 | 182 | 3 (0)| 00:00:01 | | | |
| 3 | TABLE ACCESS FULL| DEPT | 4 | 52 | 3 (0)| 00:00:01 | | | |
-------------------------------------------------------------------------------------------------------
Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------
1 - SEL$1
2 - SEL$1 / EMP@SEL$1
3 - SEL$1 / DEPT@SEL$1
Predicate Information (identified by operation id):
---------------------------------------------------
1 - access("DEPT"."DEPTNO"="EMP"."DEPTNO")
--//提示leading emp在前,dept在後,而use_nl指定emp作為被驅動表,存在衝突.執行計劃並不走nested loop.
來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/267265/viewspace-2708590/，如需轉載，請註明出處，否則將追究法律責任。
相關文章
- [20140807]hash_value sql_id衝突.txtSQL
- sql server連線排序衝突處理SQLServer排序
- svn檔案衝突,樹衝突詳解
- [20190430]注意sql hint寫法.txtSQL
- [20150427]tmux與INPUTRC定義衝突.txtUX
- 【sql hint 提示】SQL
- sql hint articleSQL
- Oralce SQL hintSQL
- commit 衝突MIT
- 雜湊衝突
- Sql Server 聯合查詢的排序規則衝突SQLServer排序
- [20190515]熱備份模式與rman衝突.txt模式
- MySQL SQL hint 提示MySql
- Git 解決衝突Git
- jQuery的$命名衝突jQuery
- git 解決衝突Git
- 查詢maven衝突Maven
- jQuery多庫衝突jQuery
- [20181130]hash衝突導致查詢緩慢.txt
- 程式衝突及其解決
- Activemq和Rabbitmq埠衝突MQ
- git pull 衝突解決Git
- Java依賴版本衝突Java
- lvm 名稱衝突LVM
- Manjaro更新出現衝突JAR
- 雜湊衝突詳解
- windows解決埠衝突Windows
- IP衝突解決方案
- SVN 版本衝突解決
- shell檢測ip衝突
- 出現型別衝突型別
- RMAN並行度衝突並行
- Oracle Latch及latch衝突Oracle
- 處理併發衝突
- 用層級理解衝突
- 解衝突用到的命令
- hash衝突解決方法
- Git衝突解決技巧Git