I have a query on a Varchar2 field in an Oracle table:

select column from tbl where column like 'value%'

That query uses a trailing wildcard.


Is Oracle capable of using an index on that column, when a trailing wildcard is used?


Yes it can. This is just a range predicate really:

Where column >= 'value'
And column < 'valuf'

A wildcard somewhere else in the string will change how much of the string can be used as the access predicate on the index. This is then just a cost based decision by the optimizer - how much of the index will it have to read and how many rows of the table will it need to lookup up from that.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.