Re: EXPLAIN PLAN : better with RULE!

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 - 04:47:32 CDT