Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
Home -> Community -> Usenet -> c.d.o.server -> Re: EXPLAIN PLAN : better with RULE!
Oracle mentioned desupporting RBO 'some time and release in the future'
starting with 7.3 or 8.0. Now they finally made up their minds and released
official alert, which states that RBO will no longer be available in next
major Oracle release (speculatively named 10i so far). Among several
reasons for dumping RBO the most notable were #1: 'RBO was not enhanced
since 7.3' and #2: 'RBO does not support many of the new database features
introduced since version 7, because [see #1], and gets in the way of CBO
when these and other features are used.' So they advise all their customers
to certify and tune their applications for CBO-only environment and do not
rely on RBO anymore.
-- Vladimir Zakharychev (bob@dpsp-yes.com) http://www.dpsp-yes.com Dynamic PSP(tm) - the first true RAD toolkit for Oracle-based internet applications. All opinions are mine and do not necessarily go in line with those of my employer. "Tuning" <c_armanet_at_hotmail.com> wrote in message news:ah0r3s$akd$1_at_wanadoo.fr...Received on Tue Jul 16 2002 - 07:31:45 CDT
>
> When will RBO being desupported?????
> I saw articles with use of RBO in Oracle 9i (I use 8.1.7)!!
>
> "Telemachus" <telemachus_at_ulysseswillreturn.net> a écrit dans le message
> news: VYRY8.2359$zX3.2248_at_news.indigo.ie...
> >
> > WHAT VERSION OF THE PRODUCT ?
> >
> > What happens if you use FIRST_ROWS ?
> >
> > Are the tables analyzed ? What percentage ? is it a full compute on all
> cols
> > ?
> >
> > You should be aware that RBO is being desupported.
> >
> > And you have not said either if CBO is slower than RBO - only that it's
> more
> > expensive.
> >
> > However, if you just want sticking plaster you can store the plan outline
> so
> > that oracle will always use that plan for that SQL statement.
> >
> >
> > "Epicentre Team B Annecy" <carmanet_at_epicentre.fr> wrote in message
> > news:ah0k2t$4j0$1_at_wanadoo.fr...
> > > Hi!
> > >
> > > I've a query which join a quite big table with a (complex) view,
> > > and the explain plan with CBO (default) gives me plenty of Table Access
> > > Full,
> > > whereas RBO (forced with /*+RULE*/) uses all primary indexes!!
> > > And the problem is that CBO is much more expensive in this case!?
> > >
> > > Do someone have an idea?
> > >
> > > Here are the 2 explain plans:
> > >
> > > SELECT STATEMENT Optimizer=HINT: CHOOSE
> > >
> > > SORT (GROUP BY)
> > > NESTED LOOPS
> > > TABLE ACCESS (FULL) OF 'CATALOG'
> > >
> > > VIEW OF 'PRODUCTS'
> > > UNION-ALL
> > > INLIST ITERATOR
> > > TABLE ACCESS (BY INDEX ROWID) OF 'PROD_CAT1'
> > > INDEX (RANGE SCAN) OF 'PROD_CAT1_PK_PRIM' (UNIQUE)
> > >
> > > TABLE ACCESS (FULL) OF 'PROD_CAT2'
> > >
> > > INLIST ITERATOR
> > > TABLE ACCESS (BY INDEX ROWID) OF 'TRANS1'
> > > INDEX (RANGE SCAN) OF 'TRANS1_PK_PRIM' (UNIQUE)
> > >
> > > FILTER
> > > TABLE ACCESS (FULL) OF 'TRANS2'
> > >
> > > FILTER
> > > TABLE ACCESS (FULL) OF 'TRANS_F'
> > >
> > > TABLE ACCESS (FULL) OF 'PROD_CAT3'
> > >
> > > TABLE ACCESS (FULL) OF 'PROD_LIV'
> > >
> > > With RULE :
> > >
> > > SELECT STATEMENT Optimizer=HINT: RULE
> > > SORT (GROUP BY)
> > > NESTED LOOPS
> > > VIEW OF 'PRODUCTS'
> > > UNION-ALL
> > > CONCATENATION
> > > TABLE ACCESS (BY INDEX ROWID) OF 'PROD_CAT1'
> > > INDEX (RANGE SCAN) OF 'PROD_CAT1_PK_PRIM' (UNIQUE)
> > >
> > > TABLE ACCESS (BY INDEX ROWID) OF 'PROD_CAT1'
> > > INDEX (RANGE SCAN) OF 'PROD_CAT1_PK_PRIM' (UNIQUE)
> > >
> > > TABLE ACCESS (BY INDEX ROWID) OF 'PROD_CAT1'
> > > INDEX (RANGE SCAN) OF 'PROD_CAT1_PK_PRIM' (UNIQUE)
> > >
> > > CONCATENATION
> > > TABLE ACCESS (BY INDEX ROWID) OF 'PROD_CAT2'
> > > INDEX (RANGE SCAN) OF 'PROD_CAT2_PK_PRIM' (UNIQUE)
> > >
> > > TABLE ACCESS (BY INDEX ROWID) OF 'PROD_CAT2'
> > > INDEX (RANGE SCAN) OF 'PROD_CAT2_PK_PRIM' (UNIQUE)
> > >
> > > TABLE ACCESS (BY INDEX ROWID) OF 'PROD_CAT2'
> > > INDEX (RANGE SCAN) OF 'PROD_CAT2_PK_PRIM' (UNIQUE)
> > >
> > > CONCATENATION
> > > TABLE ACCESS (BY INDEX ROWID) OF 'TRANS1'
> > > INDEX (RANGE SCAN) OF 'TRANS1_PK_PRIM' (UNIQUE)
> > >
> > > TABLE ACCESS (BY INDEX ROWID) OF 'TRANS1'
> > > INDEX (RANGE SCAN) OF 'TRANS1_PK_PRIM' (UNIQUE)
> > >
> > > TABLE ACCESS (BY INDEX ROWID) OF 'TRANS1'
> > > INDEX (RANGE SCAN) OF 'TRANS1_PK_PRIM' (UNIQUE)
> > >
> > > CONCATENATION
> > > FILTER
> > > TABLE ACCESS (BY INDEX ROWID) OF 'TRANS2'
> > > INDEX (RANGE SCAN) OF 'TRANS2_PK_PRIM' (UNIQUE)
> > >
> > > FILTER
> > > TABLE ACCESS (BY INDEX ROWID) OF 'TRANS2'
> > > INDEX (RANGE SCAN) OF 'TRANS2_PK_PRIM' (UNIQUE)
> > >
> > > FILTER
> > > TABLE ACCESS (BY INDEX ROWID) OF 'TRANS2'
> > > INDEX (RANGE SCAN) OF 'TRANS2_PK_PRIM' (UNIQUE)
> > >
> > > FILTER
> > > TABLE ACCESS (FULL) OF 'TRANS_F'
> > > CONCATENATION
> > > TABLE ACCESS (BY INDEX ROWID) OF 'PROD_CAT3'
> > > INDEX (RANGE SCAN) OF 'PROD_CAT3_PK_PRIM' (UNIQUE)
> > >
> > > TABLE ACCESS (BY INDEX ROWID) OF 'PROD_CAT3'
> > > INDEX (RANGE SCAN) OF 'PROD_CAT3_PK_PRIM' (UNIQUE)
> > >
> > > TABLE ACCESS (BY INDEX ROWID) OF 'PROD_CAT3'
> > > INDEX (RANGE SCAN) OF 'PROD_CAT3_PK_PRIM' (UNIQUE)
> > >
> > > CONCATENATION
> > > TABLE ACCESS (BY INDEX ROWID) OF 'PROD_LIV'
> > > INDEX (RANGE SCAN) OF 'PROD_LIV_PK_PRIM' (UNIQUE)
> > >
> > > TABLE ACCESS (BY INDEX ROWID) OF 'PROD_LIV'
> > > INDEX (RANGE SCAN) OF 'PROD_LIV_PK_PRIM' (UNIQUE)
> > >
> > > TABLE ACCESS (BY INDEX ROWID) OF 'PROD_LIV'
> > > INDEX (RANGE SCAN) OF 'PROD_LIV_PK_PRIM' (UNIQUE)
> > >
> > > TABLE ACCESS (BY INDEX ROWID) OF 'CATALOG'
> > > INDEX (UNIQUE SCAN) OF 'CATALOG_PK_PRIM' (UNIQUE)
> > >
> > > Thanks!
> > >
> > > Colin.
> > >
> > >
> >
> >
>
>