Re: SQL statement optimization

Firstly,
Look at the explain plan that you are getting, if it is doing a index range scan, then it is too bad, because it has to do a table scan after that.

Since you have % leading and trailing , I think you are better off doing a full table scan. Also if this is the kind of query you issue in most of the case, then you can possibly provide a hint to this query.

try it,bye,

"music4" <music4_at_163.net> wrote in message news:bof0na$rlg_at_netnews.proxy.lucent.com...
> Greetings,
>
> I have a table for subscriber information, and sub_number is primary key
of
> VARCHAR(30) type. And I have query as following:
>
> select * from table where sub_number like % || user_input || %;
>
> For example, if there are sub_numbers: '12345678' and '23456789', by input
> '234', the query will return both sub_numbers.
>
> But this query is extremely slow, and occupy too much CPU usage since it
> doesn't apply to index.
>
> Is there a way to optimize such query?
>
> Any idea will be greatly appreciated! Thanks in advance!
>
> Evan
>
>
Received on Thu Nov 13 2003 - 18:30:03 CST