Re: wildcard search and full tablescans

DA Morgan wrote:
> 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. 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.
>
> Jonathan and other, oh how I hate to try paraphrasing others accurately,
> have been generally dismissive of throwing out that 20-25% number. I
> have run tests in my lab where I have been able to show wildly different
> numbers for the optimizer depending on many factors including version,
> caching related parameters, etc. But as a general rule it is certainly a
> good thing to keep in mind that >50% is probably not a winning
> percentage for index usage.

Anytime you have an application that is either doing full tablescans or chewing up all of an index to return the results of a select within a character column, you have a problem.

You might be able to get away with it in a low volume data warehouse environment. A high volume OLTP environment that's going to be a scalability killer.

The important point is to consider changes to your indexing choices IF you really require searching anywhere within a text string. Received on Fri Dec 08 2006 - 11:28:53 CST