• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 118
  • Last Modified:

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.?
1 Solution
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'
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.
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now