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
