oracle效能優化之--hints

hints是oracle提供的一種機制，用來告訴優化器按照我們告訴它的方式生成執行計劃。可以用hints來實現：
　　1) 使用的優化器的型別
　　2) 基於代價的優化器的優化目標，是all_rows還是first_rows。
　　3) 表的訪問路徑，是全表掃描，還是索引掃描，還是直接利用rowid。
　　4) 表之間的連線型別
　　5) 表之間的連線順序
　　6) 語句的並行程度


1、寫HINT目的
人為的改變SQL語句的執行計劃
　　
2、HINT可以基於以下規則產生作用
表連線的順序、表連線的方法、訪問路徑、並行度

3、HINT應用範圍
dml語句
查詢語句

4、語法

{DELETE|INSERT|SELECT|UPDATE} /*+ hint [text] [hint[text]]... */
or
{DELETE|INSERT|SELECT|UPDATE} --+ hint [text] [hint[text]]...

如果語（句）法不對，則ORACLE會自動忽略所寫的HINT，不報錯
5、指定優化器模式的HINT
RULE：不管是否有統計資訊，都將採用基於規則進行優化；
CHOOSE：只要被訪問的資料中有一個表有統計資訊，就將採用基於代價的方式進行優化；
FIRST_ROWS：不管是否有統計資訊，都將採用基於代價的方式進行優化，其優化目標是最快響應時間；
ALL_ROWS：不管是否有統計資訊，都將採用基於代價的方式進行優化，其優化目標是最大吞吐量；
例子：
儘快地顯示前10行記錄

select /*+ first_rows(10) */ * from emp where deptno=10;

6、指定訪問路徑的HINT
FULL: 執行全表掃描

/*+ FULL ( table ) */

ROID: 根據ROWID進行掃描

/*+ ROWID ( table ) */

INDEX: 根據某個索引進行掃描

/*+ INDEX ( table [index [index]...] ) */
select /*+ index(emp ind_emp_sal)*/ * from emp where deptno=200 and sal>300;

如果寫了多個，則ORACLE自動選擇最優的哪個

select /*+ index(emp ind_emp_sal ind_emp_deptno)*/ * from emp where deptno=200 and sal>300;

INDEX_JOIN: 如果所選的欄位都是索引欄位(是幾個索引的)，那麼可以通過索引連線就可訪問到資料，而不需要訪問表的資料。

/*+ INDEX_JOIN ( table [index [index ...]] ) */
select /*+ index_join(emp ind_emp_sal ind_emp_deptno)*/ deptno,sal from emp where deptno=20;

INDEX_FFS: 執行快速全索引掃描

/*+ INDEX_FFS ( table [index [index]...] ) */
select /*+ index_ffs(emp pk_emp)*/ count(*) from emp;

NO_INDEX: 指定不使用哪些索引

/*+ NO_INDEX ( table [index [index]...] ) */
select /*+ no_index(emp ind_emp_sal ind_emp_deptno)*/ * from emp where deptno=200 and sal>300;

AND_EQUAL: 指定合併兩個或以上索引檢索的結果(交集)，最多不能超過5個

/*+ AND_EQUAL ( table index index [index] [index] [index] ) */

7、指定表的連線順序
ORDERED: 按表出現的順序進行連線

/*+ ORDERED */
select /*+ordered*/ emp.ename,dept.dname from dept,emp where emp.deptno=dept.deptno;
select /*+ordered*/ emp.ename,dept.dname from emp,dept where emp.deptno=dept.deptno;

8、指定表的連線操作
USE_NL: 按nested loops方式連線
--預設hash join，獲取所有資料的最快返回時間

select emp.ename,dept.dname from dept,emp where emp.deptno=dept.deptno;

--指定emp作為inner table ,以獲取最快的響應時間

select /*+ordered use_nl(emp) to get first row faster */ emp.ename,dept.dname from dept,emp where emp.deptno=dept.deptno;
select /*+ordered use_nl(emp dept)*/ emp.ename,dept.dname from dept,emp where emp.deptno=dept.deptno;

另：每個SELECT/INSERT/UPDATE/DELETE命令後只能有一個/*+ */，但提示內容可以有多個，可以用逗號分開，空格也可以。如：

/*+ ordered index() use_nl() */

未完待續。

來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/25618347/viewspace-714544/，如需轉載，請註明出處，否則將追究法律責任。