CODEWITHSUNDEEP
oracle-databaseindexing
Rnet
Rnet

Reputation: 5040

Oracle index use

I have an index on a few columns of a table. If I don't mention all of the columns in a where clause, then will that index be used?

Upvotes: 2

Views: 204

Answers (1)

Benoit
Benoit

Reputation: 79165

The answer is : it depends.

Actually this heavily depends on many statistical factors, that are usually gathered when creating an index, or by a scheduled job. Statistics include the cardinality of the data (how many rows you have), its variance, distribution, whether some values are overrepresented, whether there are many distinct values.

Usually an index can be used when the first column in the index order is filtered. However when that first column has sufficiently low cardinality, the Oracle optimizer can use the SKIP SCAN method to iterate over all values of the second column.

I recommend you read the section on indexes of the Oracle Concepts guide. Tom Kyte recommends the whole guide saying it is the most important reading when you want to know what Oracle RDBMS is about.

Upvotes: 7

Related Questions