Re: Strange cost difference
Date: Fri, 18 Feb 2011 04:14:44 -0800 (PST)
Message-ID: <1c648c6f-13a9-45f5-a59d-c80aee46ec4c_at_y3g2000vbh.googlegroups.com>
On Feb 17, 6:02 pm, Mladen Gogala <n..._at_email.here.invalid> wrote:
> On Thu, 17 Feb 2011 22:34:02 +0000, Mladen Gogala wrote:
> > The question is why does oracle think that this statement will return 15
> > rows?
>
> I figured it out. The recursive CTE has 2 parts: the initial and the
> recursive part. The part of the 1st statement which says
>
> "select empno,mgr,1 from emp where empno=7839"
>
> plays the same role as the "start with empnp=7839" part of the "connect
> by" version, but is apparently executed separately. It seems to me that
> the good, old "connect by" stuff is still preferred by Oracle.
>
> --http://mgogala.byethost5.com
You can get a very similar plan for the CONNECT BY variant if you use the "CONNECT_BY_FILTERING" hint - your simple query is using the "NO_CONNECT_BY_FILTERING" variant which basically corresponds to the old connect by implementation if I remember correctly.
The costs will be quite similar I believe since the plan will be almost identical (in this particular case).
Of course it might not work out as expected since you seem to have baselines activated for both statements - I wonder why?
Regards,
Randolf
Oracle related stuff blog:
http://oracle-randolf.blogspot.com/
Co-author of the "OakTable Expert Oracle Practices" book: http://www.apress.com/book/view/1430226684 http://www.amazon.com/Expert-Oracle-Practices-Database-Administration/dp/1430226684 Received on Fri Feb 18 2011 - 06:14:44 CST