【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

----------------------------------------------------------------------------

----------------------------------------------------------------------------

| 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

------------------------------------------------------------------------------

------------------------------------------------------------------------------

| 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

----------------------------------------------------------------------------

----------------------------------------------------------------------------

| 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

----------------------------------------------------------------------------

----------------------------------------------------------------------------

| 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

----------------------------------------------------------------------------

----------------------------------------------------------------------------

| 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

----------------------------------------------------------------------------

----------------------------------------------------------------------------

| 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

-----------------------------------------------------------------------------------------

-----------------------------------------------------------------------------------------

| 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

----------------------------------------------------------------------------

----------------------------------------------------------------------------

| 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

-------------------------------------------------------------------------------------------------------

-------------------------------------------------------------------------------------------------------

| 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

--------------------------------------------------------------------------------------------------------------

--------------------------------------------------------------------------------------------------------------

| 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/，如需轉載，請註明出處，否則將追究法律責任。

【sql調優之執行計劃】使用hint(四)Hints for Join

相關文章