Dear Experts,
I have a query which is fetching around 2,603,675 records. When it is running without group clause it is running fine but as soon as Group by claused comes into picture it's dying. I have seent the plan which is correct and hitting to right indexs with right order. We have oracle 11g. Could some one please help me how to get rid of this? Below is the expaling plan for the same. Cost Bytes and Carfinality looks ok to me.
Plan
SELECT STATEMENT HINT: ALL_ROWSCost: 1,363 Bytes: 304 Cardinality: 1
31 HASH GROUP BY Cost: 1,363 Bytes: 304 Cardinality: 1
30 NESTED LOOPS
28 NESTED LOOPS Cost: 1,362 Bytes: 304 Cardinality: 1
25 NESTED LOOPS OUTER Cost: 1,360 Bytes: 183 Cardinality: 1
22 NESTED LOOPS Cost: 1,359 Bytes: 168 Cardinality: 1
19 NESTED LOOPS Cost: 1,357 Bytes: 121 Cardinality: 1
16 NESTED LOOPS Cost: 1,356 Bytes: 107 Cardinality: 1
13 NESTED LOOPS Cost: 1,356 Bytes: 100 Cardinality: 1
6 NESTED LOOPS Cost: 54 Bytes: 2,209 Cardinality: 47
2 TABLE ACCESS BY INDEX ROWID TABLE FDM.WH_SOURCES_D Cost: 2 Bytes: 16 Cardinality: 1
1 INDEX UNIQUE SCAN INDEX (UNIQUE) FDM.WSRC_UK1 Cost: 1 Cardinality: 1
5 INLIST ITERATOR
4 TABLE ACCESS BY INDEX ROWID TABLE FDM.WH_ACCOUNTS_D Cost: 52 Bytes: 1,457 Cardinality: 47
3 INDEX UNIQUE SCAN INDEX (UNIQUE) FDM.WACC_UK1 Cost: 50 Cardinality: 47
12 PARTITION RANGE SINGLE Cost: 1,356 Bytes: 53 Cardinality: 1 Partition #: 15 Partitions accessed #KEY(AP)
11 TABLE ACCESS BY LOCAL INDEX ROWID TABLE FDM.WH_ATM_BALANCES_F Cost: 1,356 Bytes: 53 Cardinality: 1 Partition #: 15 Partitions accessed #KEY(AP)
10 BITMAP CONVERSION TO ROWIDS
9 BITMAP AND
7 BITMAP INDEX SINGLE VALUE INDEX (BITMAP) FDM.WABF_BM6 Partition #: 15 Partitions accessed #KEY(AP)
8 BITMAP INDEX SINGLE VALUE INDEX (BITMAP) FDM.WABF_BM10 Partition #: 15 Partitions accessed #KEY(AP)
15 TABLE ACCESS BY INDEX ROWID TABLE FDM.T_SDM_GLPRODUCT Cost: 0 Bytes: 7 Cardinality: 1
14 INDEX UNIQUE SCAN INDEX (UNIQUE) FDM.TSGP_PK Cost: 0 Cardinality: 1
18 TABLE ACCESS BY INDEX ROWID TABLE FDM.WH_PRODUCTS_D Cost: 1 Bytes: 14 Cardinality: 1
17 INDEX UNIQUE SCAN INDEX (UNIQUE) FDM.WPRD_PK Cost: 0 Cardinality: 1
21 TABLE ACCESS BY INDEX ROWID TABLE FDM.WH_COMMON_TRADES_D Cost: 2 Bytes: 47 Cardinality: 1
20 INDEX UNIQUE SCAN INDEX (UNIQUE) FDM.WCTD_PK Cost: 1 Cardinality: 1
24 TABLE ACCESS BY INDEX ROWID TABLE FDM.T_SDM_SECURITYINSTRUMENT Cost: 1 Bytes: 15 Cardinality: 1
23 INDEX UNIQUE SCAN INDEX (UNIQUE) FDM.TSSI_PK Cost: 0 Cardinality: 1
27 PARTITION RANGE SINGLE Cost: 1 Cardinality: 1 Partition #: 29 Partitions accessed #KEY(AP)
26 INDEX RANGE SCAN INDEX (UNIQUE) FDM.WBKS_UK2 Cost: 1 Cardinality: 1 Partition #: 29 Partitions accessed #KEY(AP)
29 TABLE ACCESS BY LOCAL INDEX ROWID TABLE FDM.WH_BOOKS_D Cost: 2 Bytes: 121 Cardinality: 1 Partition #: 29 Partitions accessed #1
Edited by: BluShadow on 21-Oct-2011 08:57
added {noformat}
{noformat} tags. Please read {message:id=9360002} and learn to do this yourself.