【sql調優之執行計劃】使用hint(四)Hints for Join
Sql語句的關聯是sql調整的非常重要的一部分，選擇正確的join順序和方式是調優的基礎。
Ordered和leading用來選擇join的順序，ordered就是按照from後面的表名書寫的順序，而leading是將某個（或多個）表的join順序提前。
測試：
SQL> select /*+ */a.ename,c.dname from emp a ,dept c,emp b
2 where a.empno = b.mgr
3 and b.deptno = c.deptno;
Execution Plan
----------------------------------------------------------
Plan hash value: 453895177
----------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 12 | 360 | 7 (15)| 00:00:01 |
|* 1 | HASH JOIN | | 12 | 360 | 7 (15)| 00:00:01 |
|* 2 | HASH JOIN | | 12 | 240 | 5 (20)| 00:00:01 |
| 3 | TABLE ACCESS FULL| DEPT | 4 | 52 | 2 (0)| 00:00:01 |
|* 4 | TABLE ACCESS FULL| EMP | 12 | 84 | 2 (0)| 00:00:01 |
| 5 | TABLE ACCESS FULL | EMP | 14 | 140 | 2 (0)| 00:00:01 |
----------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - access("A"."EMPNO"="B"."MGR")
2 - access("B"."DEPTNO"="C"."DEPTNO")
4 - filter("B"."MGR" IS NOT NULL AND "B"."DEPTNO" IS NOT NULL)
SQL>
上面的查詢讓cbo自己選擇最佳的執行計劃，c表和b表先join，使用的是hash join，結果集再與a表hash join。
SQL> select /*+ ordered */a.ename,c.dname from emp a ,dept c,emp b
2 where a.empno = b.mgr
3 and b.deptno = c.deptno ;
Execution Plan
----------------------------------------------------------
Plan hash value: 1852559750
------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 12 | 360 | 13 (8)| 00:00:01 |
|* 1 | HASH JOIN | | 12 | 360 | 13 (8)| 00:00:01 |
| 2 | MERGE JOIN CARTESIAN| | 56 | 1288 | 11 (10)| 00:00:01 |
| 3 | TABLE ACCESS FULL | EMP | 14 | 140 | 2 (0)| 00:00:01 |
| 4 | BUFFER SORT | | 4 | 52 | 9 (12)| 00:00:01 |
| 5 | TABLE ACCESS FULL | DEPT | 4 | 52 | 1 (0)| 00:00:01 |
|* 6 | TABLE ACCESS FULL | EMP | 12 | 84 | 2 (0)| 00:00:01 |
------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - access("A"."EMPNO"="B"."MGR" AND "B"."DEPTNO"="C"."DEPTNO")
6 - filter("B"."MGR" IS NOT NULL AND "B"."DEPTNO" IS NOT NULL)
SQL>
上面的查詢增加了ordered的hint，使得優化器按照表名書寫順序來連線表（按照從from由近到遠的原則）。由於a和c表並沒有直接聯絡，所以a和c的關聯是merge join cartesian。然後得到的結果集再與b表做hash join。顯然這個做法並不太好。
SQL> select /*+ ordered */a.ename,c.dname from emp b, emp a ,dept c
2 where a.empno = b.mgr
3 and b.deptno = c.deptno;
Execution Plan
----------------------------------------------------------
Plan hash value: 665932159
----------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 12 | 360 | 7 (15)| 00:00:01 |
|* 1 | HASH JOIN | | 12 | 360 | 7 (15)| 00:00:01 |
|* 2 | HASH JOIN | | 12 | 204 | 5 (20)| 00:00:01 |
|* 3 | TABLE ACCESS FULL| EMP | 12 | 84 | 2 (0)| 00:00:01 |
| 4 | TABLE ACCESS FULL| EMP | 14 | 140 | 2 (0)| 00:00:01 |
| 5 | TABLE ACCESS FULL | DEPT | 4 | 52 | 2 (0)| 00:00:01 |
----------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - access("B"."DEPTNO"="C"."DEPTNO")
2 - access("A"."EMPNO"="B"."MGR")
3 - filter("B"."MGR" IS NOT NULL AND "B"."DEPTNO" IS NOT NULL)
修改一下b表的位置，放在最前面，則使用ordered以後，表b和表a先做join，然後再與c表join。
SQL>
SQL> select /*+ leading(b) */a.ename,c.dname from emp a ,dept c,emp b
2 where a.empno = b.mgr
3 and b.deptno = c.deptno;
Execution Plan
----------------------------------------------------------
Plan hash value: 141688920
----------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 12 | 360 | 7 (15)| 00:00:01 |
|* 1 | HASH JOIN | | 12 | 360 | 7 (15)| 00:00:01 |
|* 2 | HASH JOIN | | 12 | 240 | 5 (20)| 00:00:01 |
|* 3 | TABLE ACCESS FULL| EMP | 12 | 84 | 2 (0)| 00:00:01 |
| 4 | TABLE ACCESS FULL| DEPT | 4 | 52 | 2 (0)| 00:00:01 |
| 5 | TABLE ACCESS FULL | EMP | 14 | 140 | 2 (0)| 00:00:01 |
----------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - access("A"."EMPNO"="B"."MGR")
2 - access("B"."DEPTNO"="C"."DEPTNO")
3 - filter("B"."MGR" IS NOT NULL AND "B"."DEPTNO" IS NOT NULL)
SQL>
上述查詢使用leading將b表提前，或者也可以使用leading(b.a)將b和a提前join，然後再與c join，得到的結果很顯然應該是與ordered相同。
SQL> select /*+ leading(b,a) */a.ename,c.dname from emp a ,dept c,emp b
2 where a.empno = b.mgr
3 and b.deptno = c.deptno;
Execution Plan
----------------------------------------------------------
Plan hash value: 665932159
----------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 12 | 360 | 7 (15)| 00:00:01 |
|* 1 | HASH JOIN | | 12 | 360 | 7 (15)| 00:00:01 |
|* 2 | HASH JOIN | | 12 | 204 | 5 (20)| 00:00:01 |
|* 3 | TABLE ACCESS FULL| EMP | 12 | 84 | 2 (0)| 00:00:01 |
| 4 | TABLE ACCESS FULL| EMP | 14 | 140 | 2 (0)| 00:00:01 |
| 5 | TABLE ACCESS FULL | DEPT | 4 | 52 | 2 (0)| 00:00:01 |
----------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - access("B"."DEPTNO"="C"."DEPTNO")
2 - access("A"."EMPNO"="B"."MGR")
3 - filter("B"."MGR" IS NOT NULL AND "B"."DEPTNO" IS NOT NULL)
SQL>
Use_nl
Use_merge
Use_hash
SQL> select /*+ use_nl(b,c) */a.ename,c.dname from emp a ,dept c,emp b
2 where a.empno = b.mgr
3 and b.deptno = c.deptno;
Execution Plan
----------------------------------------------------------
Plan hash value: 2135812182
----------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 12 | 360 | 8 (13)| 00:00:01 |
|* 1 | HASH JOIN | | 12 | 360 | 8 (13)| 00:00:01 |
| 2 | NESTED LOOPS | | 12 | 240 | 5 (0)| 00:00:01 |
| 3 | TABLE ACCESS FULL| DEPT | 4 | 52 | 2 (0)| 00:00:01 |
|* 4 | TABLE ACCESS FULL| EMP | 3 | 21 | 1 (0)| 00:00:01 |
| 5 | TABLE ACCESS FULL | EMP | 14 | 140 | 2 (0)| 00:00:01 |
----------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - access("A"."EMPNO"="B"."MGR")
4 - filter("B"."MGR" IS NOT NULL AND "B"."DEPTNO" IS NOT NULL AND
"B"."DEPTNO"="C"."DEPTNO")
SQL> select /*+ use_merge(b,c) */a.ename,c.dname from emp a ,dept c,emp b
2 where a.empno = b.mgr
3 and b.deptno = c.deptno;
Execution Plan
----------------------------------------------------------
Plan hash value: 913045500
-----------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-----------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 12 | 360 | 8 (25)| 00:00:01 |
|* 1 | HASH JOIN | | 12 | 360 | 8 (25)| 00:00:01 |
| 2 | MERGE JOIN | | 12 | 240 | 5 (20)| 00:00:01 |
| 3 | TABLE ACCESS BY INDEX ROWID| DEPT | 4 | 52 | 2 (0)| 00:00:01 |
| 4 | INDEX FULL SCAN | PK_DEPT | 4 | | 1 (0)| 00:00:01 |
|* 5 | SORT JOIN | | 12 | 84 | 3 (34)| 00:00:01 |
|* 6 | TABLE ACCESS FULL | EMP | 12 | 84 | 2 (0)| 00:00:01 |
| 7 | TABLE ACCESS FULL | EMP | 14 | 140 | 2 (0)| 00:00:01 |
-----------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - access("A"."EMPNO"="B"."MGR")
5 - access("B"."DEPTNO"="C"."DEPTNO")
filter("B"."DEPTNO"="C"."DEPTNO")
6 - filter("B"."MGR" IS NOT NULL AND "B"."DEPTNO" IS NOT NULL)
SQL> select /*+ use_hash(b,c) */a.ename,c.dname from emp a ,dept c,emp b
2 where a.empno = b.mgr
3 and b.deptno = c.deptno;
Execution Plan
----------------------------------------------------------
Plan hash value: 453895177
----------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 12 | 360 | 7 (15)| 00:00:01 |
|* 1 | HASH JOIN | | 12 | 360 | 7 (15)| 00:00:01 |
|* 2 | HASH JOIN | | 12 | 240 | 5 (20)| 00:00:01 |
| 3 | TABLE ACCESS FULL| DEPT | 4 | 52 | 2 (0)| 00:00:01 |
|* 4 | TABLE ACCESS FULL| EMP | 12 | 84 | 2 (0)| 00:00:01 |
| 5 | TABLE ACCESS FULL | EMP | 14 | 140 | 2 (0)| 00:00:01 |
----------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - access("A"."EMPNO"="B"."MGR")
2 - access("B"."DEPTNO"="C"."DEPTNO")
4 - filter("B"."MGR" IS NOT NULL AND "B"."DEPTNO" IS NOT NULL)
SQL>
Driving_site
SQL> select * from t_policy a ,t_policy@core b
2 where a.policy_id = b.policy_id;
Execution Plan
----------------------------------------------------------
Plan hash value: 2448822570
-------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes |TempSpc| Cost (%CPU)| Time | Inst |IN-OUT|
-------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 5027K| 4320M| | 524K (25)| 00:15:39 | | |
|* 1 | HASH JOIN | | 5027K| 4320M| 2113M| 524K (25)| 00:15:39 | | |
| 2 | TABLE ACCESS FULL| T_POLICY | 5025K| 2056M| | 52697 (40)| 00:01:35 | | |
| 3 | REMOTE | T_POLICY | 22M| 9G| | 238K (40)| 00:07:07 | CORE | R->S |
SQL> select /*+ use_nl(a,b)*/* from t_policy a ,t_policy@core b
2 where a.policy_id = b.policy_id;
Execution Plan
----------------------------------------------------------
Plan hash value: 4230146719
--------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | Inst |IN-OUT|
--------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 5027K| 4320M| 28M (2)| 14:01:35 | | |
| 1 | NESTED LOOPS | | 5027K| 4320M| 28M (2)| 14:01:35 | | |
| 2 | REMOTE | T_POLICY | 22M| 9G| 238K (40)| 00:07:07 | CORE | R->S |
| 3 | TABLE ACCESS BY INDEX ROWID| T_POLICY | 1 | 429 | 2 (0)| 00:00:01 | | |
|* 4 | INDEX UNIQUE SCAN | IND_POLICY_ID | 1 | | 1 (0)| 00:00:01 | | |
--------------------------------------------------------------------------------------------------------------
SQL> set autot trace exp
SQL> select /*+ use_nl(a,b) driving_site(b) */* from t_policy a ,t_policy@core b
2 where a.policy_id = b.policy_id;
Execution Plan
----------------------------------------------------------
Error: cannot fetch plan for statement_id 'PLUS684867'
SQL>
SQL> set lines 130
SQL> set pages 1000
SQL> explain plan for
2 select /*+ use_nl(a,b) driving_site(b) */* from t_policy a ,t_policy@core b
3 where a.policy_id = b.policy_id;
Explained.
SQL> select a.operation,substr(a.options,1,30) options,a.object_name from plan_table a ;
select * from table(dbms_xplan.display) a ;
OPERATION OPTIONS OBJECT_NAME
------------------------------ ------------------------------ ------------------------------
SELECT STATEMENT REMOTE
NESTED LOOPS
REMOTE
TABLE ACCESS BY INDEX ROWID T_POLICY
INDEX UNIQUE SCAN PK_T_POLICY
使用driving_site，在分散式查詢的時候，非常有用。
Hash_aj,merge_aj,nl_aj和Hash_sj,merge_sj,nl_sj
Aj為anti-join，用在not in子查詢，sj為semi-join，用在exists 子查詢
來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/16179598/viewspace-674830/，如需轉載，請註明出處，否則將追究法律責任。
相關文章
- mysql調優之——執行計劃explainMySqlAI
- Oracle調優之看懂Oracle執行計劃Oracle
- TiDB 查詢優化及調優系列（四）查詢執行計劃的調整及優化原理TiDB優化
- Oracle sql執行計劃OracleSQL
- SQL優化案例-從執行計劃定位SQL問題（三）SQL優化
- MySQL調優篇 | EXPLAIN執行計劃解讀（4）MySqlAI
- 分析執行計劃優化SQLORACLE的執行計劃(轉)優化SQLOracle
- MySQL執行計劃解析（四）MySql
- .Oracle固定執行計劃之SQL PROFILE概要檔案OracleSQL
- Oracle優化案例-從執行計劃定位SQL問題（三）Oracle優化SQL
- 在MySQL中使用explain查詢SQL的執行計劃MySqlAI
- 如何檢視SQL的執行計劃SQL
- SQL執行內幕：從執行原理看調優的本質SQL
- Oracle SQL Profile固定執行計劃的方法OracleSQL
- 【TUNE_ORACLE】Oracle Hint之常用Hint功能概述（四）Oracle
- Sql 之 join 001SQL
- 十六、Mysql之Explain執行計劃MySqlAI
- 獲取執行計劃之Autotrace
- 執行計劃-1：獲取執行計劃
- Mysql SQL最佳化系列之——執行計劃連線方式淺釋MySql
- MySQL——通過EXPLAIN分析SQL的執行計劃MySqlAI
- Oracle SQL調優系列之SQL Monitor ReportOracleSQL
- 使用sql monitor獲取更加詳細的執行計劃 - dbms_sqltune.report_sql_monitorSQL
- MongoDb學習之Explain執行計劃MongoDBAI
- Oracle優化案例-view merge與coe_load_sql_profile固定執行計劃（十五）Oracle優化ViewSQL
- SQL優化案例-改變那些CBO無能為力的執行計劃（一）SQL優化
- 不會看 Explain執行計劃，勸你簡歷別寫熟悉 SQL優化AISQL優化
- 不會看 Explain 執行計劃，勸你簡歷別寫熟悉 SQL 優化AISQL優化
- 20200909]為什麼執行計劃不是出現hash join semi.txt
- 從Hash Join的執行計劃的細節中能看到點啥
- SQL最佳化案例-從執行計劃定位SQL問題（三）SQL
- SQL執行計劃異常引起的效能問題SQL
- SQL執行計劃異常 引起的效能問題SQL
- Java之 join 等待執行緒終止Java執行緒
- SQLServer統計監控SQL執行計劃突變的方法SQLServer
- MySQL查詢優化之優化器工作流程以及優化的執行計劃生成MySql優化
- 【MySQL】MySQL的執行計劃及索引優化MySql索引優化
- MySQL 5.7 優化不能只看執行計劃MySql優化
- Oracle執行計劃Explain Plan 如何使用OracleAI