[20200718]注意sql hint寫法2.txt
[20200718]注意sql hint寫法2.txt
--//更正連結http://blog.itpub.net/267265/viewspace-2642961/的錯誤.
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> select /*+ use_nl(emp ) */ * from dept ,emp where dept.deptno=emp.deptno;
SCOTT@test01p> @ dpc '' ''
PLAN_TABLE_OUTPUT
-------------------------------------
SQL_ID f6v18db4wf38v, child number 0
-------------------------------------
select /*+ use_nl(emp ) */ * from dept ,emp 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 | 812 | 6 (0)| 00:00:01 | 1055K| 1055K| 719K (0)|
| 2 | TABLE ACCESS FULL| EMP | 14 | 532 | 3 (0)| 00:00:01 | | | |
| 3 | TABLE ACCESS FULL| DEPT | 4 | 80 | 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")
--//emp作為驅動表,但是連線不是nested loop,而是hash join.應該寫成如下:
SCOTT@test01p> select /*+ use_nl(emp dept) */ * from dept ,emp where dept.deptno=emp.deptno;
SCOTT@test01p> @ dpc '' ''
PLAN_TABLE_OUTPUT
-------------------------------------
SQL_ID gk5d852xxj4b5, child number 0
-------------------------------------
select /*+ use_nl(emp dept) */ * from dept ,emp where
dept.deptno=emp.deptno
Plan hash value: 4192419542
----------------------------------------------------------------------------
| Id | Operation | Name | E-Rows |E-Bytes| Cost (%CPU)| E-Time |
----------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 10 (100)| |
| 1 | NESTED LOOPS | | 14 | 812 | 10 (0)| 00:00:01 |
| 2 | TABLE ACCESS FULL| DEPT | 4 | 80 | 3 (0)| 00:00:01 |
|* 3 | TABLE ACCESS FULL| EMP | 4 | 152 | 2 (0)| 00:00:01 |
----------------------------------------------------------------------------
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):
---------------------------------------------------
3 - filter("DEPT"."DEPTNO"="EMP"."DEPTNO")
--//這樣寫才是走nested loop.但是連線順序是dept在前,emp在後.
select /*+ use_nl(dept emp ) */ * from dept ,emp where dept.deptno=emp.deptno;
SCOTT@test01p> @ dpc '' ''
PLAN_TABLE_OUTPUT
-------------------------------------
SQL_ID 3t7v5jv2dwbpj, child number 0
-------------------------------------
select /*+ use_nl(dept emp ) */ * from dept ,emp where
dept.deptno=emp.deptno
Plan hash value: 4192419542
----------------------------------------------------------------------------
| Id | Operation | Name | E-Rows |E-Bytes| Cost (%CPU)| E-Time |
----------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 10 (100)| |
| 1 | NESTED LOOPS | | 14 | 812 | 10 (0)| 00:00:01 |
| 2 | TABLE ACCESS FULL| DEPT | 4 | 80 | 3 (0)| 00:00:01 |
|* 3 | TABLE ACCESS FULL| EMP | 4 | 152 | 2 (0)| 00:00:01 |
----------------------------------------------------------------------------
--//連線順序是dept在前,emp在後.也就是要控制連線順序必須使用leadiing或者order提示.
select /*+ leading(emp dept ) use_nl(dept emp ) */ * from dept ,emp where dept.deptno=emp.deptno;
SCOTT@test01p> @ dpc '' advanced
PLAN_TABLE_OUTPUT
-------------------------------------
SQL_ID 0nvmx1zbbdvbt, child number 0
-------------------------------------
select /*+ leading(emp dept ) use_nl(dept emp ) */ * from dept ,emp
where dept.deptno=emp.deptno
Plan hash value: 3625962092
-----------------------------------------------------------------------------------------
| Id | Operation | Name | E-Rows |E-Bytes| Cost (%CPU)| E-Time |
-----------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 17 (100)| |
| 1 | NESTED LOOPS | | 14 | 812 | 17 (0)| 00:00:01 |
| 2 | NESTED LOOPS | | 14 | 812 | 17 (0)| 00:00:01 |
| 3 | TABLE ACCESS FULL | EMP | 14 | 532 | 3 (0)| 00:00:01 |
|* 4 | INDEX UNIQUE SCAN | PK_DEPT | 1 | | 0 (0)| |
| 5 | TABLE ACCESS BY INDEX ROWID| DEPT | 1 | 20 | 1 (0)| 00:00:01 |
-----------------------------------------------------------------------------------------
select /*+ leading(dept emp ) use_nl(dept emp ) */ * from dept ,emp where dept.deptno=emp.deptno;
SCOTT@test01p> @ dpc '' advanced
PLAN_TABLE_OUTPUT
-------------------------------------
SQL_ID cfc054wzj13c1, child number 0
-------------------------------------
select /*+ leading(dept emp ) use_nl(dept emp ) */ * from dept ,emp
where dept.deptno=emp.deptno
Plan hash value: 4192419542
----------------------------------------------------------------------------
| Id | Operation | Name | E-Rows |E-Bytes| Cost (%CPU)| E-Time |
----------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 10 (100)| |
| 1 | NESTED LOOPS | | 14 | 812 | 10 (0)| 00:00:01 |
| 2 | TABLE ACCESS FULL| DEPT | 4 | 80 | 3 (0)| 00:00:01 |
|* 3 | TABLE ACCESS FULL| EMP | 4 | 152 | 2 (0)| 00:00:01 |
----------------------------------------------------------------------------
3.走hash join以及merge join也存在類似的情況?
SCOTT@test01p> select /*+ use_hash( emp dept) */ * from dept ,emp where dept.deptno=emp.deptno;
SCOTT@test01p> @ dpc '' ''
PLAN_TABLE_OUTPUT
-------------------------------------
SQL_ID 82kkvfn2wfm9u, child number 0
-------------------------------------
select /*+ use_hash( emp dept) */ * from dept ,emp where
dept.deptno=emp.deptno
Plan hash value: 615168685
-------------------------------------------------------------------------------------------------------
| Id | Operation | Name | E-Rows |E-Bytes| Cost (%CPU)| E-Time | OMem | 1Mem | Used-Mem |
-------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 6 (100)| | | | |
|* 1 | HASH JOIN | | 14 | 812 | 6 (0)| 00:00:01 | 1398K| 1398K| 1056K (0)|
| 2 | TABLE ACCESS FULL| DEPT | 4 | 80 | 3 (0)| 00:00:01 | | | |
| 3 | TABLE ACCESS FULL| EMP | 14 | 532 | 3 (0)| 00:00:01 | | | |
-------------------------------------------------------------------------------------------------------
SCOTT@test01p> select /*+ leading(emp dept) use_hash( emp dept) */ * from dept ,emp where dept.deptno=emp.deptno;
SCOTT@test01p> @ dpc '' ''
PLAN_TABLE_OUTPUT
-------------------------------------
SQL_ID 4uwf6gq3vqwt6, child number 0
-------------------------------------
select /*+ leading(emp dept) use_hash( emp dept) */ * from dept ,emp
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 | 812 | 6 (0)| 00:00:01 | 1098K| 1098K| 699K (0)|
| 2 | TABLE ACCESS FULL| EMP | 14 | 532 | 3 (0)| 00:00:01 | | | |
| 3 | TABLE ACCESS FULL| DEPT | 4 | 80 | 3 (0)| 00:00:01 | | | |
-------------------------------------------------------------------------------------------------------
SCOTT@test01p> select /*+ leading(emp dept) use_merge( emp dept) */ * from dept ,emp where dept.deptno=emp.deptno;
SCOTT@test01p> @ dpc '' ''
PLAN_TABLE_OUTPUT
-------------------------------------
SQL_ID a1933csmkkyd3, child number 0
-------------------------------------
select /*+ leading(emp dept) use_merge( emp dept) */ * from dept ,emp
where dept.deptno=emp.deptno
Plan hash value: 3406566467
--------------------------------------------------------------------------------------------------------
| Id | Operation | Name | E-Rows |E-Bytes| Cost (%CPU)| E-Time | OMem | 1Mem | Used-Mem |
--------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 8 (100)| | | | |
| 1 | MERGE JOIN | | 14 | 812 | 8 (25)| 00:00:01 | | | |
| 2 | SORT JOIN | | 14 | 532 | 4 (25)| 00:00:01 | 2048 | 2048 | 2048 (0)|
| 3 | TABLE ACCESS FULL| EMP | 14 | 532 | 3 (0)| 00:00:01 | | | |
|* 4 | SORT JOIN | | 4 | 80 | 4 (25)| 00:00:01 | 2048 | 2048 | 2048 (0)|
| 5 | TABLE ACCESS FULL| DEPT | 4 | 80 | 3 (0)| 00:00:01 | | | |
--------------------------------------------------------------------------------------------------------
SCOTT@test01p> select /*+ leading( dept emp) use_merge( emp dept) */ * from dept ,emp where dept.deptno=emp.deptno;
SCOTT@test01p> @ dpc '' ''
PLAN_TABLE_OUTPUT
-------------------------------------
SQL_ID 3jz1v9y326xvp, child number 0
-------------------------------------
select /*+ leading( dept emp) use_merge( emp dept) */ * from dept ,emp
where dept.deptno=emp.deptno
Plan hash value: 844388907
--------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | E-Rows |E-Bytes| Cost (%CPU)| E-Time | OMem | 1Mem | Used-Mem |
--------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 6 (100)| | | | |
| 1 | MERGE JOIN | | 14 | 812 | 6 (17)| 00:00:01 | | | |
| 2 | TABLE ACCESS BY INDEX ROWID| DEPT | 4 | 80 | 2 (0)| 00:00:01 | | | |
| 3 | INDEX FULL SCAN | PK_DEPT | 4 | | 1 (0)| 00:00:01 | | | |
|* 4 | SORT JOIN | | 14 | 532 | 4 (25)| 00:00:01 | 2048 | 2048 | 2048 (0)|
| 5 | TABLE ACCESS FULL | EMP | 14 | 532 | 3 (0)| 00:00:01 | | | |
--------------------------------------------------------------------------------------------------------------------
4.總結:
--//實際上在連結還做了一些例子:
--//http://blog.itpub.net/267265/viewspace-2122782/=>[20160730]hint 衝突.txt
--//這種細節問題總是不小心就會犯.
--//總之要控制執行計劃,最好使用leading,use_nl()裡面的表如果僅僅1個引數作為被驅動表才有效.
--//不行加入2個引數.避免執行計劃與提示衝突.
來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/267265/viewspace-2705445/，如需轉載，請註明出處，否則將追究法律責任。
相關文章
- [20190430]注意sql hint寫法.txtSQL
- 【sql hint 提示】SQL
- sql hint articleSQL
- Oralce SQL hintSQL
- Oracle SQL寫法OracleSQL
- MySQL SQL hint 提示MySql
- sql寫法小記SQL
- Oracle中的sql hintOracleSQL
- 【HINT】使用“NO_INDEX ”Hint提示避免SQL使用特定索引IndexSQL索引
- SQL常用的特殊寫法SQL
- openGauss 支援SQL-hintSQL
- Oracle PL/SQL編寫PL/SQL程式碼的注意事項OracleSQL
- 【sql調優之執行計劃】使用hint(五)Hint for parallelSQLParallel
- 檢視SQL PROFILE使用的HINTSQL
- 11G new SQL hint大全SQL
- [20150803]無法通過sql_id找到sql語句2.txtSQL
- oracle 通過sql profile為sql語句加hintOracleSQL
- SQL語句規範的寫法SQL
- Mysql 分組排序的sql寫法MySql排序
- OCI插入SQL語句的寫法SQL
- Oracle 12c 新SQL提示（hint）OracleSQL
- 使用hint來調優sql語句SQL
- 資料分佈決定SQL寫法SQL
- 許可權控制下的SQL寫法SQL
- 【丁原】分頁sql中普通寫法和rowid寫法的效能比較SQL
- Sql最佳化(五) hint（提示）介紹SQL
- 通過hint push_subq優化sql優化SQL
- sql調優一例---索引排序hintSQL索引排序
- 關於append sql hint的實驗APPSQL
- SQL hint中正確使用use_nl提示SQL
- ORACLE常用SQL最佳化hint語句OracleSQL
- 關於號段選取的sql寫法SQL
- Update 多個關聯表SQL的寫法SQL
- 第39期：MySQL 時間類分割槽寫 SQL 注意事項MySql
- ORACLE 通過SPM為SQL語句加HINTOracleSQL
- 透過使用hint unnest調優sql語句SQL
- 通過使用hint unnest調優sql語句SQL
- oracle sql hint提示_BITMAP CONVERSION FROM ROWIDSOracleSQL