Re: Indexing for LIKE selection

John,

    The matter with LIKE is exactly like the case of composite indexes - that is if you refer to the start of the string (as to the first columns of the key) everything is fine, Oracle can descend the index tree, if not ... it can decide either to scan the index or the table, but in both cases it's probably not what you would expect from an index search.

   In other words

           like 'blah%' is fine,
           like '%blah%' is not.

Same problem, by the way, with regular expressions. Needless to say, if the queries you expect are mostly of the '%blah%' kind, what you are performing is a text search, which points to Intermedia (or whatever it is called nowadays, it has been renamed half a score times at least since introduction).

Perhaps it is also worth pointing out that if searching on parts of a column is slowly becoming a standard way of querying the database, it may mean that the model no longer satisfies the needs and that you are slowly drifting from the first normal form ...

Stéphane Faroult

John Dunn wrote:
> We currently allow users to search a large table for a specific
> indexed value. Performance is fine.
>
> However they have now requested to be able to enter a partial string
> or wildcards.
>
> I am asssuming we will use the LIKE clause in the select statement.
>
> Do we need to review how we index the table? If so, what is most
> appropriate?
>
> Oracle version is 10.2
>
> John
>

--
http://www.freelists.org/webpage/oracle-l