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 -> Re: how to avoid fast full scan of index?

Re: how to avoid fast full scan of index?

From: oofoof <oofoofoof_at_ureach.com>
Date: 8 Aug 2003 14:01:04 -0700
Message-ID: <894b11eb.0308081301.403a135d@posting.google.com>


I'm using Oracle 8.1.7. The plans are huge, but here they are. My problem is with the access of index SYS_C007577. In the original plan, there is a range scan accessing 114782 rows. In the new plan, there is a fast full scan, accessing 2391272 rows. The response time for this query increases from 11 seconds to 15 even thogh the query itself has been simplified.

Original plan:


      0  SELECT STATEMENT   GOAL: CHOOSE
     59   SORT (GROUP BY)
  11670    FILTER
  12933     NESTED LOOPS
  38797      NESTED LOOPS
  38797       NESTED LOOPS
  12933        NESTED LOOPS
  12933         NESTED LOOPS
  15826          NESTED LOOPS
  57392           NESTED LOOPS
  57392            HASH JOIN
      1             TABLE ACCESS   GOAL: ANALYZED (FULL) OF
                        'U_SECURITYCUSTOMER'
 114782             HASH JOIN
      2              TABLE ACCESS   GOAL: ANALYZED (FULL) OF
                         'HIERARCHY_DESCRIPT'
 114782              NESTED LOOPS
      8               NESTED LOOPS
      3                NESTED LOOPS
      2                 TABLE ACCESS   GOAL: ANALYZED (FULL)
                            OF 'U_SECURITYCUSTOMER'
      3                 TABLE ACCESS   GOAL: ANALYZED (BY
                          GLOBAL INDEX ROWID) OF 'CUSTOMER_PARENT'
                            PARTITION:ROW LOCATION
      3                  INDEX   GOAL: ANALYZED (RANGE SCAN)
                              OF 'U_CUSTOMER' (NON-UNIQUE)
      9                TABLE ACCESS   GOAL: ANALYZED (BY
                         GLOBAL INDEX ROWID) OF 'CUSTOMER_PARENT'
                           PARTITION:ROW LOCATION
      9                 INDEX   GOAL: ANALYZED (RANGE SCAN)
                            OF 'U_CUSTOMER_PARENT_PARENT' (NON-UNIQUE)
 114782               INDEX   GOAL: ANALYZED (RANGE SCAN) OF
                          'SYS_C007577' (UNIQUE)
 114782            INDEX   GOAL: ANALYZED (UNIQUE SCAN) OF
                       'SYS_C007574' (UNIQUE)
  73216           TABLE ACCESS   GOAL: ANALYZED (BY INDEX ROWID)
                      OF 'FACT_EPISODE'
 100786            INDEX   GOAL: ANALYZED (RANGE SCAN) OF
                       'U_FACTEPISODECUSTOMER' (NON-UNIQUE)
  28757          TABLE ACCESS   GOAL: ANALYZED (BY INDEX ROWID) OF

'COMM_CHANNEL'
31650 INDEX GOAL: ANALYZED (UNIQUE SCAN) OF 'SYS_C007541' (UNIQUE) 25864 TABLE ACCESS GOAL: ANALYZED (BY INDEX ROWID) OF 'PRODUCT' 25864 INDEX GOAL: ANALYZED (UNIQUE SCAN) OF
'SYS_C008033' (UNIQUE)
51728 TABLE ACCESS GOAL: ANALYZED (FULL) OF 'PRODUCT_LEVEL' 77592 TABLE ACCESS GOAL: ANALYZED (BY INDEX ROWID) OF 'PRODUCT_PARENT' 77592 INDEX GOAL: ANALYZED (UNIQUE SCAN) OF 'SYS_C008044' (UNIQUE) 51728 TABLE ACCESS GOAL: ANALYZED (BY INDEX ROWID) OF 'HIERARCHY_DESCRIPT' 77592 INDEX GOAL: ANALYZED (UNIQUE SCAN) OF 'SYS_C007917' (UNIQUE) 2 TABLE ACCESS GOAL: ANALYZED (BY INDEX ROWID) OF 'CUSTOM_RANGE' 2 INDEX GOAL: ANALYZED (UNIQUE SCAN) OF 'SYS_C007591' (UNIQUE) 2 TABLE ACCESS GOAL: ANALYZED (BY INDEX ROWID) OF 'CUSTOM_RANGE' 2 INDEX GOAL: ANALYZED (UNIQUE SCAN) OF 'SYS_C007591' (UNIQUE) ===========================================================================
New plan:
      0  SELECT STATEMENT   GOAL: CHOOSE
     59   SORT (GROUP BY)
  11670    FILTER
  12933     NESTED LOOPS
  38797      NESTED LOOPS
  38797       NESTED LOOPS
  12933        NESTED LOOPS
  12933         NESTED LOOPS
  15826          NESTED LOOPS
  57392           NESTED LOOPS
  57392            HASH JOIN
      1             TABLE ACCESS   GOAL: ANALYZED (FULL) OF
                        'U_SECURITYCUSTOMER'
2391272             HASH JOIN
   1228              PARTITION RANGE (ALL) PARTITION: START=1
                         STOP=10
   1228               TABLE ACCESS   GOAL: ANALYZED (FULL) OF
                          'CUSTOMER_PARENT' PARTITION: START=1 STOP=10
2391272              INDEX   GOAL: ANALYZED (FAST FULL SCAN) OF
                         'SYS_C007577' (UNIQUE)
 114782            INDEX   GOAL: ANALYZED (UNIQUE SCAN) OF
                       'SYS_C007574' (UNIQUE)
  73216           TABLE ACCESS   GOAL: ANALYZED (BY INDEX ROWID)
                      OF 'FACT_EPISODE'
 100786            INDEX   GOAL: ANALYZED (RANGE SCAN) OF
                       'U_FACTEPISODECUSTOMER' (NON-UNIQUE)
  28757          TABLE ACCESS   GOAL: ANALYZED (BY INDEX ROWID) OF

'COMM_CHANNEL'
31650 INDEX GOAL: ANALYZED (UNIQUE SCAN) OF 'SYS_C007541' (UNIQUE) 25864 TABLE ACCESS GOAL: ANALYZED (BY INDEX ROWID) OF 'PRODUCT' 25864 INDEX GOAL: ANALYZED (UNIQUE SCAN) OF
'SYS_C008033' (UNIQUE)
51728 TABLE ACCESS GOAL: ANALYZED (FULL) OF 'PRODUCT_LEVEL' 77592 TABLE ACCESS GOAL: ANALYZED (BY INDEX ROWID) OF 'PRODUCT_PARENT' 77592 INDEX GOAL: ANALYZED (UNIQUE SCAN) OF 'SYS_C008044' (UNIQUE) 51728 TABLE ACCESS GOAL: ANALYZED (BY INDEX ROWID) OF 'HIERARCHY_DESCRIPT' 77592 INDEX GOAL: ANALYZED (UNIQUE SCAN) OF 'SYS_C007917' (UNIQUE) 2 TABLE ACCESS GOAL: ANALYZED (BY INDEX ROWID) OF 'CUSTOM_RANGE' 2 INDEX GOAL: ANALYZED (UNIQUE SCAN) OF 'SYS_C007591' (UNIQUE) 2 TABLE ACCESS GOAL: ANALYZED (BY INDEX ROWID) OF 'CUSTOM_RANGE' 2 INDEX GOAL: ANALYZED (UNIQUE SCAN) OF 'SYS_C007591' (UNIQUE)
Received on Fri Aug 08 2003 - 16:01:04 CDT

Original text of this message

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