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