Question

Oracle: How can you avoid indexes?

Answer 1

You can use NO_INDEX hint.

The NO_INDEX hint instructs the optimizer not to use one or more indexes for the specified table. For example:

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

Each parameter serves the same purpose as in "INDEX Hint" with the following modifications:

1. If this hint specifies a single available index, then the optimizer does not consider a scan on this index. Other indexes not specified are still considered.

2. If this hint specifies a list of available indexes, then the optimizer does not consider a scan on any of the specified indexes. Other indexes not specified in the list are still considered.

3. If this hint specifies no indexes, then the optimizer does not consider a scan on any index on the table. This behavior is the same as a NO_INDEX hint that specifies a list of all available indexes for the table.

The NO_INDEX hint applies to function-based, B-tree, bitmap, cluster, or domain indexes. If a NO_INDEX hint and an index hint (INDEX, INDEX_ASC, INDEX_DESC, INDEX_COMBINE, or INDEX_FFS) both specify the same indexes, then the database ignores both the NO_INDEX hint and the index hint for the specified indexes and considers those indexes for use during execution of the statement.

Answer 2

To make index access path unavailable
• Use FULL hint to optimizer for full table scan
• Use INDEX or AND-EQUAL hint to optimizer to use one index or set to
indexes instead of another.
• Use an expression in the Where Clause of the SQL.