15. Oracle Hint (1)

當 CBO 選擇的執行計畫無法符合我們的期望時，可使用 Hint 來強制指定SQL 的執行計畫， Hint 的使用方式為在 select 、 insert 、 update 語句之後加上 /*+ <Hint_name> */ ，需要注意的是， <Hint_name> 與 /*+  */ 之間最好有空白，避免 Hint 在程式語法裡失效卻無法知道原因為何， Hint 依照種類可以區分幾項 : Optimizer 相關 Hint 、 SQL Access Path 相關 Hint 、 Join 相關 Hint 、 sub-query 相關 Hint ，以及其它種類。

• Optimizer相關Hint :

/*+ RULE */

使用 /*+ RULE */ 表示對當前 SQL 語句使用基於規則的優化器進行解析， RULE 提示在 Tuning 過程中十分有用，當我們無法確定 CBO 所選擇的執行計畫是否正確時，會以 RULE 提示所產生的執行計畫為基礎，然後再對 SQL 的執行計畫進行調整， RULE 提示便是彌補以 CBO 為主的優化模式下的不足。例如:

SQL> select /*+ RULE */ * from employees where department_id=50;

/*+ ALL_ROWS */

使用 /*+ ALL_ROWS */ 表示當前 SQL 使用 CBO 優化器進行解析，並且選擇能夠快速返回所有 rows 的最佳執行計畫，例如:

SQL> SELECT /*+ ALL_ROWS */ employee_id, last_name, salary, job_id        FROM employees WHERE employee_id = 7566;

/*+ FIRST_ROWS(n) */

使用 /*+ FIRST_ROWS(n) */ 表示當前 SQL 使用 CBO 優化器進行解析，並且選擇能夠最快返回前面 n 筆資料的最佳執行計畫，例如:

SQL> SELECT /*+ FIRST_ROWS(10) */ employee_id, last_name, salary, job_id FROM employees WHERE department_id = 20;

• SQL Access Path相關Hint :

/*+ FULL(TABLE) */

FULL 提示表示強制對 Table 進行全表掃描，先前我們曾提到過對於某些小表來說，使用全表掃描有時會比使用 Index 來得有效率。例如:

SQL> select /*+ FULL(EMP) */ * from employees emp  where department_id=50;

/*+ CACHE(TABLE) */

CACHE 提示確保 Table 能存放於 data buffer ，通常針對小 Table 並且常與 FULL 提示一起使用，例如:

SQL> select /*+ FULL(REG) CACHE(REG) */ count(*)  from regions REG;

/*+ NOCACHE(TABLE) */

NOCACHE 提示相對於 CACHE ，代表 select 過的 table 不要 cache 在buffer 裡，例如:

SQL> SELECT /*+ FULL(hr_emp) NOCACHE(hr_emp) */ last_name FROM employees hr_emp;

/*+ INDEX(TABLE INDEX_NAME) */

INDEX 提示可以強制使用 Table 中的某一個 Index ，使用方式為 Index 提示後面加上 Table_Name 與 Table 所附屬的 Index_Name ，在此需注意的是，若 SQL 語法有定義 Table 的別名，則 Index 提示後面也必須使用別名，否則 Hint 將無效。例如:

SQL> select /*+ INDEX(EMP EMP_EMP_ID_PK) */ * from employees emp;

/*+ NO_INDEX(TABLE INDEX_NAME) */

NO_INDEX 代表指定不使用 Table 中的某個 Index 。例如:

SQL> SELECT /*+ NO_INDEX(EMP emp_empid) */ employee_id FROM employees EMP WHERE employee_id > 200;

/*+ INDEX_ASC(TABLE INDEX_NAME) */

任何 Index 在建立的同時，我們可以指定 Index 上存放的資料是以遞增(ASC) 排序或是遞減 (DESC) 排序，若 Index 建立是以遞增 (ASC) 排序，則使用 Index 掃描的順序預設為 ASC ，反之若 Index 是以 DESC 建立，則預設以 DESC 進行掃描， INDEX_ASC 提示代表指定對 Index 進行遞增 (ASC) 方式的掃描，例如:

SQL> SELECT /*+ INDEX_ASC(e emp_name_ix) */ * FROM employees e;

/*+ INDEX_DESC(TABLE INDEX_NAME) */

INDEX_DESC 相對於 INDEX_ASC ，代表指定對 Index 進行遞減 (DESC) 方式掃描，例如:

SQL> SELECT /*+ INDEX_DESC(e emp_name_ix) */ * FROM employees e;

/*+ INDEX_FFS(TABLE INDEX_NAME) */

INDEX_FFS 代表使用 Index fast full scan 進行掃描，若查詢的 rows 占總資料量的多數，則使用 full table scan 將比使用 Index 來的有效率，在此若 select 的欄位上已具有 Index ，則使用 Index fast full scan 將比 full table scan 來得有效率，例如:

SQL> SELECT /*+ INDEX_FFS(e emp_name_ix) */ first_name FROM employees e;

/*+ NO_INDEX_FFS(TABLE INDEX_NAME) */

NO_INDEX_FFS 代表使用 Index 進行掃描時，不考慮使用 Index fast full scan 這個方式進行掃描，例如:

SQL> SELECT /*+ NO_INDEX_FFS(e emp_name_ix) */ first_name FROM employees e;

/*+ INDEX_COMBINE(TABLE INDEX_NAME…INDEX_NAME) */

若 where 條件上的每個欄位均具有 Index ，在 Rule Based 下會以 And-Equal 的方式同時使用所有 Index ，使用的順序會依照 where 條件的順序來決定，但在 Cost Based 下只會使用選擇性較高的 Index 。而 INDEX_COMBINE 這個 HINT 則是實現了即使是 Cost Based 也能同時使用包含在 where 條件中的所有 Index 。 INDEX_COMBINE 的原理是使用 BITMAP 方式掃描數據，通過 BITMAP CONVERSION FROM ROWIDS 這個步驟將所有 B-Tree 索引中獲得的 rowid 信息組合 成BITMAP ，藉此縮小資料查詢的範圍，然後再經由 BITMAP CONVERSION TO ROWIDS 這個步驟轉換出 rowid 來獲得所需的數據。例如一段SQL如下:

SQL> select employee_id,department_id,first_name,salary from employees where department_id=30 and manager_id=114;

此段 SQL 包含兩個 where 條件，一個為 department_id=30 ，另一個為manager_id=114 ，而 employees 這個 table 於這兩個欄位上分別都建有 Index， EMP_DEPARTMENT_IX 與 EMP_MANAGER_IX ，在未使用 INDEX_COMBINE 提示之前 CBO 將使用選擇性較高的索引:

其挑選資料的順序是先以 rowid 找出所有 manager_id=114 的資料之後，再由這些資料過濾出 department_id=30 的資料。

接下來使用 INDEX_COMBINE 提示之後，兩個索引便會同時納入 CBO 的執行計畫當中 :

透過 INDEX_COMBINE ， CBO 先從兩個 B-Tree Index 中找出各自條件的rowid，經由比對之後留下同時符合 manager_id=114 與 department_id=30 兩個條件的 rowid ，最終再利用這組 rowid 來獲取所需的數據。

/*+ INDEX_SS(TABLE INDEX_NAME) */

INDEX_SS 代表使用 Index skip scan ，當複合索引時，若 where條件的欄位不包含複合索引的第一個欄位，則優化器不會使用索引，從 Oracle 9i 開始，可以使用 Index skip scan 來跳過複合索引的前綴欄位 (prefix column) 來使用索引，例如 EMP_NAME_IX 這個複合索引的欄位順序為 LAST_NAME、FIRST_NAME :

若一段 SQL 如下， where 條件中只包含 first_name :

SQL> select employee_id,first_name,salary  from employees where first_name='Daivid';

由於 where 條件不包含複合索引的第一個欄位，所以優化器不會使用索引 :

透過 INDEX_SS 提示使用 Index skip scan ，此時優化器便可使用索引 :

/*+ USE_CONCAT */

若於索引的欄位上使用 or 條件，優化器不會選擇使用索引，建議使用 UNION 替代 or ，如此一來優化器便可使用索引。若遇到無法修改程式語法的情況，便可使用 USE_CONCAT 來提示優化器以 UNION 方式來執行 or 語法，如此一來便可使用索引，例如一段使用 or 語法的 SQL 如下:

SQL> select * from employees  where department_id = 20 or  manager_id = 140;

由於使用 or ，因此優化器不會使用索引 :

使用了 USE_CONCAT 之後，優化器以 UNION 方式執行，此時便可使用到索引 :

/*+ DRIVING_SITE(Table) */

當 SQL 的查詢透過 DBLINK 與遠端的 Table 進行 JOIN 時，先對 Remote的 Table 進行 Full Table Scan 將所有的 row set 回傳到本地端再進行 JOIN 與篩選的動作，若 Remote 的 Table 資料量非常大時，此時 SQL 的效能將越差，而 DRIVING_SITE 提示可將資料於 Remote 端先篩選過後再傳回本地端進行 JOIN ，使用 DRIVING_SITE 可以減少傳輸的資料量，進而提升效能。例如:

SQL> select /*+ DRIVING_SITE(EMP) */ count(*)   from departments dep,employees@USA emp where dep.department_id=emp.department_id and emp.salary > 35000;