Re: wildcard search and full tablescans

Charles Hooper wrote:
> wagen123_at_yahoo.com wrote:
> > Oracle10g 10.2.0.1
> >
> > select * from <table_name> where <column_name> like '%abc%';
> >
> > does not use the index and does a full tablescan (guess this is the
> > expected behaviour as per explain plan).
> >
> > Any suggestions as to how to use the index (index hint didn't help), IF
> > possible or other alternatives.
> >
> > thanks
>
> Oracle Database SQL Reference 10g Release 2 Page 7-16 (page 432 in PDF)
> http://download-east.oracle.com/docs/cd/B19306_01/server.102/b14200.pdf
> "Pattern Matching on Indexed Columns
> When you use LIKE to search an indexed column for a pattern, Oracle can
> use the
> index to improve performance of a query if the leading character in the
> pattern is not %
> or _. In this case, Oracle can scan the index by this leading
> character. If the first
> character in the pattern is % or _, then the index cannot improve
> performance because
> Oracle cannot scan the index."
>
> I recall reading various write-ups by Tom Kyte, Jonathan Lewis, or
> others that detail why the index cannot be used in such a such a case,
> but cannot locate a reference to one of the write-ups.

Because the regular indexes are built based on all the "characters" starting with the first character. If you search for LIKE 'X%' then you start with index entries ( range scan ) that have an X in the first character. If the SQL says LIKE '%X%' then you need to look at the index entries that have "AX%", 'BX%', 'CX%' etc.

In other words all of the index entries. In certain cases it still "might be" better to look at them then get rowids versus tablescans. Cases could be built either way probably.

> As a general
> rule, if more than 20% to 25% of a table's rows are expected to be
> read, it is usually less expensive to perform a full table scan, rather
> than a scan of the index and then an access by ROWID to read the data
> from the table. Note that using a % or _ wildcard for the initial
> character, Oracle would be required to access every block in the index
> and then potentially every row in the table by ROWID.

I think the big point to emphasize is that if you specify LIKE '%PATTERN%' the most straightforward execution path for oracle is either a tablescan or a complete index search. Both of those are not good candidates for producing scalable applications. The logical IO is a huge problem for systems that need to support concurrent access to more than a few queries per time interval.

Many applications don't really require searching within a string. Customer last name searches can usually begin with one or more characters of the last name.

If you really need to support searching within a string there are other things to consider such as text indexes. Received on Fri Dec 08 2006 - 06:09:15 CST