Re: SQL statement optimization

music4 wrote:

>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
>
>

And you have which version and edition of Oracle?

Go to http://tahiti.oracle.com and look up Function Based Indexes (FBI).

-- 
Daniel Morgan
http://www.outreach.washington.edu/ext/certificates/oad/oad_crs.asp
http://www.outreach.washington.edu/ext/certificates/aoa/aoa_crs.asp
damorgan_at_x.washington.edu
(replace 'x' with a 'u' to reply)