Re: wildcard search and full tablescans

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. 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.

Charles Hooper
PC Support Specialist
K&M Machine-Fabricating, Inc. Received on Fri Dec 08 2006 - 04:42:15 CST