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/,如需转载,请注明出处,否则将追究法律责任。
转载于:http://blog.itpub.net/16179598/viewspace-674830/