Re: Strange behaviour of CBO in 9ir2

If you run a full explain plan against the query, you will see that the addition of the 'OR' clause has changed the filtering in a way that suggests that Oracle has done a smart optimisation in unnesting the inner queries and pushing predicates in the code without the OR, but cannot do the same in the code with the OR.

You haven't given us a value for bind variable i_volume, and the test case went ballistic when I tried '1000' with the OR clause.

You don't have a 'start with' on your 'connect by' query, is this deliberate ?

If you want to avoid the problem can you do something like:

select count(*) from
(
select -- that works well
union
select -- to take only the part from the OR branch )

-- 
Regards

Jonathan Lewis

http://www.jlcomp.demon.co.uk/faq/ind_faq.html
The Co-operative Oracle Users' FAQ

http://www.jlcomp.demon.co.uk/seminar.html
Optimising Oracle Seminar - schedule updated Sept 19th





"Mike" <mike.jones_at_xenicom.com> wrote in message 
news:6cdd54ab.0411101002.77597e5_at_posting.google.com...


> The set up is below and the query at the end.  no matter how much I

> hint the query with the OR I can not make it perform as without the OR

> although I see no reason for any extra IO.

>

> I have also posted the explain plans it gives me for them. the or

> seems to generate 428 times as much IO for the IN test and then filter

> it out.  I don't understand why... This is giving some Java guys here

> a really good excuse to use Oracle as a bit bucket so could do with

> some bullets in my gun.