Re: Using LIKE with indexed column
From: Les Kopari <kopari_at_garlic.com>
Date: 1995/04/23
Message-ID: <3ndso1$qk4_at_garlic.com>#1/1
> I believe that indexes are NEVER used in a LIKE, because
> the full text of each index entry is stored in the index
> and oracle isn't prepared to deal with a partial index search within
> a column.
>
> The same rationale applies when trying to select on
> a substringed column - the index won't be used.
Date: 1995/04/23
Message-ID: <3ndso1$qk4_at_garlic.com>#1/1
tschqisten_at_aol.com (TSChqisten) wrote:
>
> I believe that indexes are NEVER used in a LIKE, because
> the full text of each index entry is stored in the index
> and oracle isn't prepared to deal with a partial index search within
> a column.
>
> The same rationale applies when trying to select on
> a substringed column - the index won't be used.
To quote Oracle:
When LIKE is used to search an indexed column for a pattern, the performance benefit associated with the index is lost if the first character in the pattern is "%" or "_". If the leading character in the pattern is not "%" or "_", there is some performance benefit to the index because ORACLE can restrict the comparison to rows known to begin with the specified first character.
from p. 3-8 Oracle 7 Server SQL Language Reference Manual. Received on Sun Apr 23 1995 - 00:00:00 CEST