CODEWITHSUNDEEP
sqloracle-databasedatabase-design
kace91
kace91

Reputation: 853

Will having a unique key on several columns speed up queries over just one of the indexed colums?

Say I have a unique key over (1,2,3). That automatically creates a unique index over that combination of keys (that I know of). But if I search in column 2, will the index come into play? or do I need to also define an index just over 2?

Upvotes: 0

Views: 49

Answers (1)

Justin Cave
Justin Cave

Reputation: 231741

It is possible (depending on the version) that Oracle could use the index on 1,2,3 in a query on just column2. That would require, however, that Oracle does an index skip scan. That's generally not a particularly efficient access path. How inefficient it is, though, will depend on the number of distinct column1 values in the table-- the more such distinct values, the less efficient an index skip scan would be and the less likely Oracle would be to choose that path.

Generally, you'd probably only want Oracle to use the index if the query included predicates on one or more of the leading columns. If your index was defined on 2,1,3, for example, then your query would be much more likely to benefit from using the index. Of course, that would mean that a query that had a predicate on just column1 would have the same issue of either not using the index or doing an index skip scan depending on the number of distinct column2 values.

Upvotes: 3

Related Questions