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.
--
Daniel A. Morgan
University of Washington
damorgan_at_x.washington.edu
(replace x with u to respond)
Puget Sound Oracle Users Group
www.psoug.org
Received on Fri Dec 08 2006 - 10:39:24 CST
