Re: EXPLAIN PLAN : better with RULE!

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.
> >
> >
>
>
Received on Tue Jul 16 2002 - 05:05:31 CDT