RE: Sincere Advice on Sql Plan - Thanks

>As regards the In List ... (I am answering to Marks Comments also)

>This is Part of a Java Program and the Where clause is Built on the
>fly ... That is the Reason u see so many Tables there but none being
>used in the Where or Select List.

>The IN List needs to be there since it also Dynamic in the sense the
>User May select more than one.

<snip>

All of Lex's stuff, as usual, made sense.

I want to be clear that I expected that this was a generated statement and my point was that if you have the high and low bounds of those in lists in hand you can also generate the range restricting predicates. The ins are required filters, but the ranges may trigger the use of better plans and if correctly generated do not override the filtering provided by the in lists. Add the ranges where usable indexes exist and it *MIGHT* give you a better plan. You should be able to test this by hand before you alter your generator.

The usual advice about figuring out a way to generate this with bind variables should also be injected, though I don't know the rules of possibility of doing that in the "NEW" syntax format, and I don't think that is the problem you're trying to solve at this moment.

As for deciphering your two queries in detail for (lack of) equivalence as Lex suggested, I wish you good luck. While I find it easy to make changes to a query that I can guarantee are isofunctional, I find it difficult, mind numbing, and boring to analyze whether two similar complex queries someone else has generated are equivalent.

Regards,

mwf

--
http://www.freelists.org/webpage/oracle-l