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

If you CANNOT begin the search with some leading characters ( not the percent sign ) ... ie where some_col like 'abc%' ... then normally I would expect to the optimizer to pick either a full table scan or some kind of index scan.

You might want to do a 10053 trace and some analysis of where the optimizer is going with your query.

You can put in a hint like this select /*+ INDEX(table_or_view_name optional_index_name) */ column_list FROM etc ...

Eiher a full table scan or a full index scan ( of some type ) produces an application that has scalability limitations built into it. ( Your logical IO's are very high ).

For full searching within a column there are options including the TEXT indexes but they have some complications ( different SQL where syntax, maintenance and cpu implications etc ).

Oracle will work nicely with regular character indexes IF you put a where clause and a like that DOESN'T start with a percent sign. Received on Thu Dec 07 2006 - 07:52:53 CST