Oracle Index Hint Syntax

Mehmet Salih Deveci

3 years ago

I will explain Oracle Index Hint Syntax in this post.

INDEX Hint: use the specified index for the related table. If your query is not using the Index, you can use this hint to force using it.

You can use the Index hint as follows.

select /*+ index(index_name) */ * from table_name;

SELECT company_name FROM companies c WHERE Company_ID = 1;

You can add /*+ index(c COMP_NDX) */ clause the SQL Statements as follows to force using COMP_NDX Index.

SELECT /*+ index(c COMP_NDX) */ company_name FROM companies c WHERE Company_ID = 1;

If you want to learn more details about why Index hint should be used, read the following post.

Optimizer Access Paths (Table and Index Scan) in Oracle | Oracle Database Performance Tuning Tutorial -7

What is Index and Why Should We Use Index?

If you want to learn more details about Oracle Hint, read the following post.

What is the Optimizer Hints and How to Use Hints in Oracle | Oracle Database Performance Tuning Tutorial -16

All Index hints in Oracle are as follows.

INDEX_ASC: it is used for Scanning an index in ascending order

INDEX_COMBINE: it is used for choosing a bitmap access path.

INDEX_JOIN: it is used for the optimizer to use an index join as an access path.

INDEX_DESC: it is used for an index scan in descending order.

INDEX_FFS: it is used for a fast-full index scan.

INDEX_SS: it is used for an index skip scan.

NO_INDEX: it is used for not allowing indexes usage.