[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
- Oracle中的sql hintOracleSQL
- openGauss 支援SQL-hintSQL
- GreatSQL 在SQL中使用 HINT 語法修改會話變數SQL會話變數
- [20200801]sql hint衝突.txtSQL
- SQL語句規範的寫法SQL
- Mysql 分組排序的sql寫法MySql排序
- 個人線段樹寫法 & 注意逝項
- SQL Server 2008連線字串寫法大全SQLServer字串
- [20191125]oracel SQL parsing function qcplgte 2.txtSQLFunction
- 第39期：MySQL 時間類分割槽寫 SQL 注意事項MySql
- 精讀《手寫 SQL 編譯器 - 詞法分析》SQL編譯詞法分析
- 精讀《手寫 SQL 編譯器 - 語法樹》SQL編譯
- 資料庫規範之SQL規範寫法資料庫SQL
- 精讀《手寫 SQL 編譯器 - 語法分析》SQL編譯語法分析
- 請求引數為物件，mybatis的sql寫法物件MyBatisSQL
- 精讀《手寫 SQL 編譯器 – 詞法分析》SQL編譯詞法分析
- django | 常見 SQL 及其對應的 ORM 寫法DjangoSQLORM
- MySQL與SQL的觸發器的不同寫法MySql觸發器
- [20181007]12cR2 Using SQL Patch 2.txtSQL
- [20200324]SQL語句優化的困惑2.txtSQL優化
- SQL WHERE IN引數化編譯寫法簡單示例SQL編譯
- 神奇的 SQL 之別樣的寫法 → 行行比較SQL
- SQL 抽象語法樹及改寫場景應用SQL抽象語法樹
- SQL抽象語法樹及改寫場景應用SQL抽象語法樹
- [20190523]修改引數後一些細節注意2.txt
- 【TUNE_ORACLE】Oracle Hint之常用Hint功能概述（五）Oracle
- 【TUNE_ORACLE】Oracle Hint之常用Hint功能概述（四）Oracle
- 【TUNE_ORACLE】Oracle Hint之常用Hint功能概述（三）Oracle
- 【TUNE_ORACLE】Oracle Hint之常用Hint功能概述（二）Oracle
- 【TUNE_ORACLE】Oracle Hint之常用Hint功能概述（一）Oracle
- [20190329]探究sql語句相關mutexes補充2.txtSQLMutex
- [20231017]使用dbms_workload_repository.add_colored_sql之2.txtSQL
- [20210113]ashtop查詢特定表的SQL語句2.txtSQL
- 修改python語法注意Python
- SQL 語句的注意事項SQL
- SQL語法SQL
- [20200211]使用DBMS_SHARED_POOL.MARKHOT與sql語句2.txtSQL