Help - Search - Members - Calendar
Full Version: Index hint
Oracle DBA Forums > Oracle > Oracle Forum
soft techy
HI

when i modify a query
select a,b,c from test1,test2 where (condition)
to
select index(indexname) a,b,c from test1,test2 where (condition).

the query plan changes. before analyzing the query plan change. i need to unnderstand when the syntax for the hint is not correct, how does the query is influenced by the hint.
correct syntac should be index( tablename indexname).
i know i have not given much info, but i want to know index(indexname) hint, what does it do ?
burleson
>> i need to unnderstand when the syntax for the hint is not correct

Hints are embedded into comments, and when they are entered incorrectly, they are treated as comments, and ignored.
soft techy
Hints are embedded into comments, and when they are entered incorrectly, they are treated as comments, and ignored.---

well thanks for the followup, i didnt make my question clear. this question is about understanding hints and not for query tuning.

1. index(tablename) - is this an correct hint ?
2. if this is incorrect, it should be ingnored. I agree. but in my case it has changed my execution plan. it is using index with this hints.
HAL9000
"i want to know index(indexname) hint, what does it do ?"

It's an optimizer directive, telling the CBO to use that index.

You can see examples of index hints and the exact syntax in the docs:

http://www.acs.ilstu.edu/docs/Oracle/serve...52/hintsref.htm

Normally, the table name is used in an index hint.

But it may work without the table name, look it up. Here is the ayntax for the index hint:

CODE
/*+ INDEX ( [ @ qb_name ] tablespec [ indexspec [ indexspec ]... ] ) */


"in my case it has changed my execution plan"

OK, you are saying that the table name is optional, but that contradicts this page.

http://www.dba-oracle.com/t_hints.htm


QUOTE
The table name is mandatory in the hint. For example, the following hint will be ignored because the table name is not specified in the query:

select /*+ index(dept_idx) */ * from emp;


Does your query use the index with no hint?
This is a "lo-fi" version of our main content. To view the full version with more information, formatting and images, please click here.
Invision Power Board © 2001-2014 Invision Power Services, Inc.