Re: help with multiple BETWEEN statements

"Yvonne G" <hlngus_at_hotmail.com> wrote in message news:d999b872.0301151554.4295efb2_at_posting.google.com...
> Here's the entire code, which just runs for over 1 hr without returning
> any data; it runs in just about 1 min with just 1 BETWEEN statement,
> so I suspect the culprit to be the addition of another BETWEEN statement:

I would imagine (but can't tell without an explain plan) that the addition of the second between statement means that it now looks more attractive for Oracle to do a FTS than some sort of index access since you will now be accessing more rows. have a look at the explain plans for the two different statements to see if this is the case. It seems to me as well that you probably don't need the two select distincts wrapping the whole lot in a select distinct would achieve the same effect I think. eg select distinct .... from (select .. minus select ...);

If my theory is correct it is likely that you will be able to hint the statement to achieve a better execution plan, but can we see the two different plans first.

--
Niall Litchfield
Oracle DBA
Audit Commission UK
*****************************************
Please include version and platform
and SQL where applicable
It makes life easier and increases the
likelihood of a good answer

******************************************