Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
Home -> Community -> Usenet -> c.d.o.server -> EXPLAIN PLAN : better with RULE!
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 - 03:05:31 CDT