Composite index, when index is used?

In one of my table, there is a composite index, I am not using all the columns inside my where clause,
but still I can see Index range scan in my plan,

how this could happen? In which way it is possible.?
sakthikumarAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

PortletPaulEE Topic AdvisorCommented:
if parts of that index are relevant to that query, then the optimizer can choose to utilize the index.

It is not mandatory that all parts of the composite index be present in a query before the index can be useful.

{+edit} 'is' to 'can be'
0
johnsoneSenior Oracle DBACommented:
Oracle can utilize the leading columns of a composite index in a range scan.

For example, if you have an index on col1, col2 and col3.

If your where clause restricts on col1, the index could be used.

If your where clause restricts on col1 and col2, the index could be used.

You should see about the same performance as if you have individual indexes on these columns.

There is also an index skip scan where the index can be used even if leading columns are not in the where clause.  Like if col2 and col3 were in the where clause in the above example.
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Oracle Database

From novice to tech pro — start learning today.