Re: HELP: SQL query using condition LIKE

Liang Lin <llin_at_uv1.med.umich.edu> writes:

> When I use SELECT...LIKE on an indexed column, it's almost 10
> times slower than SELECT...=. Is there any way to improve
> the performance while still using SELECT...LIKE?

We've only noticed a performance problem in ORACLE V5 and V6 with the LIKE operator when the user chooses to use a leading wildcard in the LIKE string, thus disabling the use of an index which might exist on the column in question.

We got around this by storing the contents of the column in two versions: in normal and in reverse order. So if we have two columns called NAME and REVERSE_NAME, and if a particular row contains the string 'SMITH' in the NAME column, we would store the string 'HTIMS' in the REVERSE_NAME column. We have a separate index on each column.

Our query software (actually a SQL*Forms application) then checks the search string which the user has entered, and if it starts with a wildcard the search string is reversed (to produce a trailing wildcard) and a query is generated which compares it against the REVERSE_NAME column. If the search string doesn't contain a leading wildcard then it is compared "as is" against the NAME column.

Quite a lot of work to set up, but it allows us to process queries with leading wildcards just as quickly as those with trailing wildcards.

David Hazledine                                                EMBL Data Library
Database Administrator                                                PF 10.2209
EMBL Data Library                                      69012 Heidelberg, Germany

Internet: Hazledine_at_EMBL-Heidelberg.DE