Re: SQL statement optimization

"Lakshmi Jagarlapudi" <jlnarayana_at_comcast.net> wrote in message news:<GkVsb.144275$ao4.460476_at_attbi_s51>...
> 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,
>

Dear Lakshmi,

the fact is that for any such WHERE clause as Evan ("music4") has,

        where sub_number like % || user_input || %; the optimizer cannot do an index range scan. Ranges are defined by the leading part of the search value, but '%' doesn't identify a leading value.

HINTs will do him no good. You are suggesting the Optimizer do something that is impossible.

Personally I see no easy solution. (I can imagine certain approaches, but they would involve significant table structure and code enhancements along with big assumptions about what's in the "user_input" parameter.)

Ed
if you ask for the world, sometimes you might get it. But it will take some time to download 8^) Received on Fri Nov 14 2003 - 16:32:38 CST