| Index - used/not used! [message #372339] |
Sat, 03 February 2001 05:40  |
gvenkat
Messages: 8
Registered: February 2001
Location: DUBAI
|
Junior Member |
|
|
Tell me if the following query will use the index or not?
sql> select ename from emp where dno=10;
note: ename and dno are indexed!
|
|
|
|
| Re: Index - used/not used! [message #372367 is a reply to message #372339] |
Mon, 05 February 2001 12:16  |
Andrew again...
Messages: 270
Registered: July 2000
|
Senior Member |
|
|
Whether it is used or not depends on several things. If you have the RULE based optimizer enabled (default of using a hint) then it will use the index on dno (dno is numeric).
If you have the COST based optimizer enabled, but you have not analyzed the table, it will default to back RULE and will still use the index.
If you have COST based optimizer enabled, and you have analyzed the table, then it will only use the index if dno is selective and the table has a significant number of rows in it.
Using an index is not always best. If the table is small, then it is cheaper to just read the whole table. Oracle usually reads in 8k blocks on Unix, so 1 block could be a whole small table.
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
