Re: wildcard search and full tablescans

On Dec 7, 12:55 pm, wagen..._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

Index skip scan might work, but I doubt whether it will work when both the leading and the trailing part of a column are unknown. Actually Oracle Context/InterMedia, with the CONTAINS operator was invented for queries like this. I have never used it, so I can't help you further, but the very goal of CONTAINS is to address queries like this one.

Hth,

-- 
Sybrand Bakker
Senior Oracle DBA