odered hint 可以指示oracle 使用from 關(guān)鍵字后面的表的順序進行join連接！cbo會優(yōu)先按照from 后面的表的順序來進行join，當(dāng)統(tǒng)計

leading hint 可以指示Oracle使用leading 中指定的表作為驅(qū)動表,

比如 正常的訪問計劃如下

SCOTT@> select e.ename, hiredate, b.comm

2 from emp e, bonus b

3 where e.ename = b.ename;

Execution Plan

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

Plan hash value: 1125985041

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

| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |

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

| 0 | SELECT STATEMENT | | 1 | 34 | 6 (17)| 00:00:01 |

|* 1 | HASH JOIN | | 1 | 34 | 6 (17)| 00:00:01 |

| 2 | TABLE ACCESS FULL| BONUS | 1 | 20 | 2 (0)| 00:00:01 |

| 3 | TABLE ACCESS FULL| EMP | 14 | 196 | 3 (0)| 00:00:01 |

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

Predicate Information (identified by operation id):

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

1 - access("E"."ENAME"="B"."ENAME")

我們在leading 提示中指定 emp 表為驅(qū)動表

SCOTT@> select /*+ leading(e b) */ e.ename,hiredate,b.comm

2 from emp e, bonus b

3 where e.ename = b.ename;

Execution Plan

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

Plan hash value: 1842254584

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

| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |

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

| 0 | SELECT STATEMENT | | 1 | 34 | 6 (17)| 00:00:01 |

|* 1 | HASH JOIN | | 1 | 34 | 6 (17)| 00:00:01 |

| 2 | TABLE ACCESS FULL| EMP | 14 | 196 | 3 (0)| 00:00:01 |

| 3 | TABLE ACCESS FULL| BONUS | 1 | 20 | 2 (0)| 00:00:01 |

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

Predicate Information (identified by operation id):

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

1 - access("E"."ENAME"="B"."ENAME")

如結(jié)果執(zhí)行計劃中將emp 作為驅(qū)動表！

1 在leading 提示同時使用ordered hint，則leading hint無效

SCOTT@> select /*+ leading(b e) ordered */ e.ename,hiredate, b.comm

2 from emp e, bonus b

3 where e.ename = b.ename;

Execution Plan

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

Plan hash value: 1842254584

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

| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |

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

| 0 | SELECT STATEMENT | | 1 | 34 | 6 (17)| 00:00:01 |

|* 1 | HASH JOIN | | 1 | 34 | 6 (17)| 00:00:01 |

| 2 | TABLE ACCESS FULL| EMP | 14 | 196 | 3 (0)| 00:00:01 |

| 3 | TABLE ACCESS FULL| BONUS | 1 | 20 | 2 (0)| 00:00:01 |

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

Predicate Information (identified by operation id):

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

1 - access("E"."ENAME"="B"."ENAME")

2 使用兩個沖突的leading hint ，則oracle cbo會忽略所有的leading 提示！

SCOTT@> select /*+ leading(b e) leading(e b) */e.ename,hiredate, b.comm

2 from emp e, bonus b

3 where e.ename = b.ename;

Execution Plan

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

Plan hash value: 1125985041

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

| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |

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

| 0 | SELECT STATEMENT | | 1 | 34 | 6 (17)| 00:00:01 |

|* 1 | HASH JOIN | | 1 | 34 | 6 (17)| 00:00:01 |

| 2 | TABLE ACCESS FULL| BONUS | 1 | 20 | 2 (0)| 00:00:01 |

| 3 | TABLE ACCESS FULL| EMP | 14 | 196 | 3 (0)| 00:00:01 |

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

Predicate Information (identified by operation id):

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

1 - access("E"."ENAME"="B"."ENAME")

，

本文原創(chuàng)發(fā)布php中文網(wǎng)，轉(zhuǎn)載請注明出處，感謝您的尊重！

總結(jié)

以上是生活随笔為你收集整理的orderd mysql_Oracle入门教程：leading vs ordered hint的全部內(nèi)容，希望文章能夠幫你解決所遇到的問題。

如果覺得生活随笔網(wǎng)站內(nèi)容還不錯，歡迎將生活随笔推薦給好友。