Re: Doubt in rewriting SQL

Dwarak,

     For me the change you have applied keeps the two queries logically equivalent. Unless the optimizer goes berserk while rewriting, you should get the same result. My doubts are of another nature: I doubt it will make any difference, what you have written is something that the optimizer performs "naturally" these days. And as Toon underlined, it's impossible to tell how you should best rewrite your query without knowing anything about cardinality, indexing, and selectivity of the various criteria you are applying.

    I find the various definitions of time intervals pretty confusing; you get start_date and end_date values from two different tables, and all this seems pretty entangled. Intuitively, I think I'd go towards set operators such as INTERSECT; at least, I'd give it a try. I'm also quite uncomfortable, usually, seeing the same tables several times at different places in the query. One way to get out of it (if the result set isn't too big) might be to try a single pass, use CASE to sort out the various subset you want to consider separately, and possibly use an aggregate function over the resulting set. I hope I'm not too cryptic ...

HTH Stéphane Faroult

>

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