Hints related to access path are INDEX and FULL.
INDEX hint
The INDEX hint instructs the optimizer to use an index scan for the specified table. You can use INDEX hints for indexes that are based on functions, domains, B-trees, bitmaps, or bitmap joins.
Syntax of the INDEX hint:
/*+ INDEX ( [ @queryblock ] tablespec [ indexspec [ indexspec ]... ] ) */
The behavior of a hint depends on the indexspec specification:
If an
INDEXhint specifies a single available index, the database performs a scan on this index. The optimizer does not consider a full table scan or a scan on another index on the table.If an
INDEXhint specifies a list of available indexes, the optimizer considers the cost of a scan on each index in the list and then performs an index scan with the lowest cost. The database chooses to scan multiple indexes from this list and merge the results, if such an access path has the lowest cost. The database does not perform a full table scan or a scan on an index that is not in the list.If an
INDEXhint does not specify a specific index, the optimizer considers the cost of a scan on each available index on the table and then performs an index scan with the lowest cost. The database chooses to scan multiple indexes and merge the results, if such an access path has the lowest cost. The optimizer does not consider a full table scan.
Sample code of the INDEX hint:
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 for the specified table.
Syntax of the FULL hint:
/*+ FULL ( [ @queryblock ] tablespec ) */
Sample code of the FULL hint:
SELECT /*+ FULL(e) */ employee_id, last_name
FROM hr.employees e
WHERE last_name LIKE :al;
In the preceding example, the database performs a full table scan on the employees table to execute this statement, even if an index is defined on the last_name column that is made available by the condition in the WHERE clause.
In the FROM clause, the EMPLOYEES table has an alias e, so the hint must refer to the table by its alias instead of its name. Do not refer to schema names in the hint even if they are specified in the FROM clause.