| does a query can use more than one index on table at a time [message #600658] |
Mon, 11 November 2013 03:03  |
 |
neerumishra
Messages: 6
Registered: November 2012
Location: delhi
|
Junior Member |
|
|
Hello everyone ,
My problem is that i want to know ,
if i have a table as t1 and have column
col1
col2
col3
col4
col5
col6
col7
and also have indexes on table
inx1 as col1,col2,col3
and index
inx2 as col3,col4
then if i have query like this
select * from t1 where col1='somevalue'
and col2='somevalue'
and col3='somevalue'
and col4='somevalue'
and col5='somevalue'
....
now my problem is that
if i am doing Explain Plan for the query i am getting only one index is working suppose inx1 what about another index.
My questions are:
1) does oracle uses only one index at a time on single table.
2) does all the columns must be in same index .
3) does sequence of columns in index and query matters.
please reply me as soon as possible
|
|
|
|
| Re: does a query can use more than one index on table at a time [message #600665 is a reply to message #600658] |
Mon, 11 November 2013 03:26  |
Lalit Kumar B
Messages: 3174
Registered: May 2013
Location: World Wide on the Web
|
Senior Member |
|
|
neerumishra wrote on Mon, 11 November 2013 14:33
1) does oracle uses only one index at a time on single table.
No. The optimizer decides whether or not to use an index.
Quote:
2) does all the columns must be in same index .
Not necessarily. Depends on the predicates, but again, it might differ due to sorting.
Quote:3) does sequence of columns in index and query matters.
Well it depends on lot of things like the uniqueness of values, cardinality etc.
Quote:please reply me as soon as possible
I hope it's a quick reply, and for sure it won't make you understand the concepts clearly.
|
|
|
|
| Re: does a query can use more than one index on table at a time [message #600666 is a reply to message #600658] |
Mon, 11 November 2013 03:27 |
John Watson
Messages: 8931
Registered: January 2010
Location: Global Village
|
Senior Member |
|
|
First, forum etiquette. ThisQuote:please reply me as soon as possible is more likely to annoy people than to generate any response at all. Related to this, I see that you have not said "thank you" to the answers for the other questions you have posted.
With regard to your question, It is possible but unlikely for Oracle to use two indexes in your example. It would require a range scan of both indexes followed by dynamic conversion to bitmaps and a bitmap merge. This is probably less efficient that a range scan on inx2 or a skip scan on inx1, with a filter.
|
|
|
|
|
|
|
|
|
|
|
|
| Re: does a query can use more than one index on table at a time [message #600733 is a reply to message #600684] |
Mon, 11 November 2013 15:04 |
rleishman
Messages: 3728
Registered: October 2005
Location: Melbourne, Australia
|
Senior Member |
|
|
If it is possible for Oracle to use Index 1 or Index 2, then the optimizer will decide which one it thinks is faster and use that one. As John pointed out, it is theoretically possible to use both, but will almost certainly be slower.
You don't need to query on every column in the index, but you do need a leading subset. For example, with your Index#1 you could query one (col1, col2, col3), or (col1, col2), or even just (col1).
If you use ALL of the columns in your query (using equals clauses), then column sequence does not matter. But as you can see above, the sequence of columns will affect which subsets of columns you can query on. eg. An index on (col2, col1, col3) could query on just col2 - something that Index#1 cannot do.
Ross Leishman
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
