Hints related to access paths

INDEX Hint

The INDEX hint instructs the optimizer to scan a specified table based on the index. You can use the INDEX hint for function-based, domain, B-tree, bitmap, and bitmap join indexes.

The INDEX hint uses the following syntax:

/*+ INDEX ( [ @ queryblock ] tablespec [ indexspec [ indexspec ]... ] ) */

The behavior of the hint depends on the indexspec specification:

• If the INDEX hint specifies a single available index, the database scans based on this index. The optimizer does not consider a full table scan or a scan on another index on the table.

• If the INDEX hint specifies multiple available indexes, the optimizer compares the costs of scans base on each index in the list. Then, the optimizer performs an index scan at the lowest cost. If an access path that is generated based on multiple indexes cause the lowest cost among all table scan plans, this access path is used. The database does not consider a full table scan or a scan on an index that is not specified in the hint.

• If the INDEX hint specifies no indexes, the optimizer compares the costs of scans on each available index on the table. Then, the optimizer performs an index scan at the lowest cost. If an access path that is generated based on multiple indexes cause the lowest cost among all table scan plans, this access path is used. The optimizer does not consider a full table scan.

The following statement provides an example:

SELECT /*+ INDEX (employees emp_department_ix)*/ employee_id, department_id
FROM employees 
WHERE department_id > 50;

FULL Hint

The FULL hint instructs the optimizer to perform a full table scan on the specified table.

The FULL hint uses the following syntax:

/*+ FULL ( [ @ queryblock ] tablespec ) */

The following statement provides an example:

SELECT /*+ FULL(e) */ employee_id, last_name
FROM hr.employees e 
WHERE last_name LIKE :b1;

The database performs a full table scan on the employees table to execute this statement, even if an index on the last_name column is listed in the condition in the WHERE clause.

The employees table has an alias e in the FROM clause. Therefore, the hint must reference the table by using its alias rather than its name. Do not reference schema names in the hint even if the schema names are specified in the FROM clause.