top button
Flag Notify
    Connect to us
      Site Registration

Site Registration

Oracle: How can you avoid indexes?

+1 vote
353 views
Oracle: How can you avoid indexes?
posted Jan 9, 2015 by Archana

Share this question
Facebook Share Button Twitter Share Button LinkedIn Share Button

2 Answers

0 votes

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 Jan 9, 2015 by Amit Kumar Pandey
0 votes

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.

answer Jan 12, 2015 by Arun Gowda
...