Search 800 + Posts

Apr 7, 2014

Oracle Performance : How to use Index hints in sql Query

Some key pointers we need to consider while defining index hints in sql query to improve performance
Syntax for an index hint is:

select /*+ index(TABLE_NAME INDEX_NAME) */ col1...
 There are a number of rules that need to be applied to this hint:

1.The TABLE_NAME is mandatory in the hint

2.The table alias MUST be used if the table is aliased in the query

3.If TABLE_NAME or alias is spelled incorrectly then the hint will not be used.

4.The INDEX_NAME is optional.
If an INDEX_NAME is entered without a TABLE_NAME then the hint will not be applie
If a TABLE_NAME is supplied on its own then the optimizer will decide which index to use based on statistics.

5.If the INDEX_NAME is spelt incorrectly but the TABLE_NAME is spelled correctly then the     hint will not be applied even though the TABLE_NAME is correct.

6.If there are multiple index hints to be applied, then the simplest way of addressing this is to         repeat the index hint syntax for each index e.g.:

 select /*+ index(TABLE_NAME1 INDEX_NAME1) index(TABLE_NAME2 INDEX_NAME2) */ col1...

7.Remember that the parser/optimizer may have transformed/rewritten the query  or may have chosen an access path which make the use of the index invalid and   this may result in the index not being used.

Please note that as long as the index() hint structure is correct it will force the use of the Cost Based Optimizer (CBO). This will happen even if the alias or table name is incorrect.

No comments:

Post a Comment