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?
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It only takes a minute to sign up.Sign up to join this community
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.