Re: EXPLAIN PLAN : better with RULE!

From: Hieraklion <hieraklion_nospam_at_noos.duchemol>
Date: Tue, 16 Jul 2002 11:32:04 +0200
Message-ID: <3d353403$0$3838$79c14f64@nan-newsreader-01.noos.net>

Bonjour from Paris,

Verify you analyzed all the objects (including the indexes). If the result is the same, may be histograms can help u.

Hieraklion

---

Envoyé/Sent by Hieraklion

Pour répondre : Enlever "_nospam" à l'adresse mail et remplacer ".duchemol" par ".fr".
To answer : remove "_nospam" from the e-mail adress and replace ".duchemol" by ".fr".

"Epicentre Team B Annecy" <carmanet_at_epicentre.fr> a écrit dans le message de 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:32:04 CDT