The New Home of Oracle-wiki.net

All things oracle...

A website for IT Professionals involved with Oracle database.

LinkLinkedIn

INDEX AND NO_INDEX

Index Hint Description

The INDEX hint forces Oracle to use an index scan for the table specified. An INDEX hint can be used for function-based, domain, B-tree, bitmap, and bitmap join indexes.

The behavior of the hint depends on the indexspec specification

* If the INDEX hint specifies a single available index, then the database performs a scan on this index. The optimizer does not consider a full table scan or a scan of another index on the table.

* For a hint on a combination of multiple indexes, Oracle recommends using INDEX_COMBINE rather than INDEX, because it is a more versatile hint. If the INDEX hint specifies a list of available indexes, then the optimizer considers the cost of a scan on each index in the list and then performs the index scan with the lowest cost. The database can also choose to scan multiple indexes from this list and merge the results, if such an access path has the lowest cost. The database does not consider a full table scan or a scan on an index not listed in the hint.

* If the INDEX hint specifies no indexes, then the optimizer considers the cost of a scan on each available index on the table and then performs the index scan with the lowest cost. The database can also choose 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.

Example

SELECT /*+ INDEX(myTab myTabInd1) */ col1 

  FROM myTab 

  WHERE col1 > sysdate -100

/



No_Index Hint Description

The NO_INDEX hint force Oracle not to use one or more indexes for the table specified.


Example

SELECT /*+ NO_INDEX(myTab myTabInd1) */ col1 

  FROM myTab 

  WHERE col1 > sysdate -100

/


Published on 

Published 24th May 2022