Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.server -> EXPLAIN PLAN : better with RULE!

EXPLAIN PLAN : better with RULE!

From: Epicentre Team B Annecy <carmanet_at_epicentre.fr>
Date: Tue, 16 Jul 2002 10:05:31 +0200
Message-ID: <ah0k2t$4j0$1@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 - 03:05:31 CDT

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US