Hint

1. For optimization approaches and goals

ALL_ROWS, FIRST_ROWS(n)

Select /*+ FIRST_ROWS(100) */ FROM instrument;

2. For access paths

FULL:  全表扫描

CLUSTER：只适用于indexed cluster table

HASH：只适用于hash cluster table

INDEX: 如果指定一个索引，那么数据库使用这个索引；如果指定多个索引，那么数据库在这多个索引之间选择最优的；如果指定INDEX关键字，没有具体索引，那么数据库在所有索引之间选择，不考虑全表扫描。

SELECT /*+ INDEX (employees emp_department_ix)*/ employee_id, department_id

  FROM employees 

  WHERE department_id > 50;

NO_INDEX：类似于上面一个，不使用某个，或某多个，或所有索引。

INDEX_ASC：如果是index range scan，用升序扫描索引。

INDEX_DESC: 类似

SELECT /*+ INDEX_DESC(e emp_name_ix) */ *

  FROM employees e;

INDEX_COMBINE ：使用bitmap 索引

INDEX_JOIN: index join。下例中的两个条件都有索引，所以都用上。

SELECT /*+ INDEX_JOIN(e emp_manager_ix emp_department_ix) */ department_id

  FROM employees e

  WHERE manager_id < 110

    AND department_id < 50;

INDEX_FFS: fast full index scan快速全索引扫描

INDEX_SS: index skip scan

INDEX_SS_ASC

INDEX_SS_DESC

NO_INDEX_FFS: no fast full index scan

NO_INDEX_SS: no skip scan

3. For query transformations

FACT

  ？？

4. For join orders

ORDERED: 以from中表出现的顺序，做连接

SELECT  /*+ ORDERED */ o.order_id, c.customer_id, l.unit_price * l.quantity

  FROM customers c, order_items l, orders o

  WHERE c.cust_last_name = 'Taylor'

    AND o.customer_id = c.customer_id

    AND o.order_id = l.order_id;

LEADING：将指定的表作为连接次序的首表。

5. For join operations

USE_HASH: 使用hash join

NO_USE_HASH

USE_MERGE:使用sort-merge

SELECT /*+ USE_MERGE(employees departments) */ * 

  FROM employees, departments 

  WHERE employees.department_id = departments.department_id;

NO_USE_MERGE

USE_NL

USL_NL_WITH_INDEX:使用索引，然后nested loop

NO_USE_NL

6. For parallel execution

PARALLEL

SELECT /*+ PARALLEL */ last_name

  FROM employees;

PARALLEL_INDEX

SELECT /*+ PARALLEL_INDEX(table1, index1, 3) */

7. Additional hints