SELECT UPPER('fem_savings') , to_date('31-Jan-2007','DD-Mon-YYYY') , 
FEM_SAVINGS.ISO_CURRENCY_CD, 
MIN(REF_ETL_PRODUCT_ASSUMPTIONS.RM_COA_ID), NULL, NULL,
ADD_MONTHS (to_date('31-Jan-2007','DD-Mon-YYYY') , 10 *12), 
SUM(NVL(FEM_SAVINGS.ACCRUED_INTEREST,0)),1151 
FROM FEM_SAVINGS,REF_ETL_PRODUCT_ASSUMPTIONS 
WHERE FEM_SAVINGS.AS_OF_DATE = to_date('31-Jan-2007','DD-Mon-YYYY')
AND REF_ETL_PRODUCT_ASSUMPTIONS.PRODUCT_TYPE_CD = 182 
GROUP BY FEM_SAVINGS.ISO_CURRENCY_CD

0		SELECT STATEMENT 	
1	0	 SORT GROUP BY	
2	1	  MERGE JOIN CARTESIAN	
3	2	   TABLE ACCESS BY INDEX ROWID	REF_ETL_PRODUCT_ASSUMPTIONS
4	3	    INDEX RANGE SCAN	REF_ETL_PRODUCT_ASSUMPTIONS_1
5	2	   BUFFER SORT	
6	5	    TABLE ACCESS BY INDEX ROWID	FEM_SAVINGS
7	6	     INDEX RANGE SCAN	FEM_SAVINGS_RM

SELECT UPPER('fem_savings') ,
   to_date('31-Jan-2007','DD-Mon-YYYY') , 
   T.ISO_CURRENCY_CD,
   R.RM_COA_ID,
   ADD_MONTHS (to_date('31-Jan-2007','DD-Mon-YYYY') , 10 *12), 
   T.TOTAL_SUM,
   1151
FROM
( SELECT ISO_CURRENCY_CD,
     SUM(NVL(FEM_SAVINGS.ACCRUED_INTEREST,0)) TOTAL_SUM
  FROM FEM_SAVINGS
  WHERE
    AS_OF_DATE = to_date('31-Jan-2007','DD-Mon-YYYY')
  GROUP BY ISO_CURRENCY_CD ) T,
  REF_ETL_PRODUCT_ASSUMPTIONS R
WHERE R.PRODUCT_TYPE_CD = 182 

SELECT UPPER('fem_savings') , to_date('31-Jan-2007','DD-Mon-YYYY') , 
FEM_SAVINGS.ISO_CURRENCY_CD, 
MIN(REF_ETL_PRODUCT_ASSUMPTIONS.RM_COA_ID), NULL, NULL,
ADD_MONTHS (to_date('31-Jan-2007','DD-Mon-YYYY') , 10 *12), 
SUM(NVL(FEM_SAVINGS.ACCRUED_INTEREST,0)),1151 
FROM FEM_SAVINGS,REF_ETL_PRODUCT_ASSUMPTIONS 
WHERE FEM_SAVINGS.AS_OF_DATE = to_date('31-Jan-2007','DD-Mon-YYYY')
AND REF_ETL_PRODUCT_ASSUMPTIONS.PRODUCT_TYPE_CD = 182 
GROUP BY FEM_SAVINGS.ISO_CURRENCY_CD

call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        1      0.03       0.02          0          0          0           0
Execute      1      0.00       0.00          0          0          0           0
Fetch        1     96.81     369.95     643759     626585          2           3
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total        3     96.84     369.98     643759     626585          2           3

Misses in library cache during parse: 1
Optimizer goal: CHOOSE
Parsing user id: 108  (BOIDW)

Rows     Execution Plan
-------  ---------------------------------------------------
      0  SELECT STATEMENT   GOAL: CHOOSE
      0   SORT (GROUP BY)
      0    MERGE JOIN (CARTESIAN)
      0     TABLE ACCESS   GOAL: ANALYZED (BY INDEX ROWID) OF 
                'REF_ETL_PRODUCT_ASSUMPTIONS'
      0      INDEX   GOAL: ANALYZED (RANGE SCAN) OF 
                 'REF_ETL_PRODUCT_ASSUMPTIONS_1' (NON-UNIQUE)
      0     BUFFER (SORT)
      0      TABLE ACCESS   GOAL: ANALYZED (BY INDEX ROWID) OF 
                 'FEM_SAVINGS'
      0       INDEX   GOAL: ANALYZED (RANGE SCAN) OF 'FEM_SAVINGS_RM' 
                  (NON-UNIQUE)


Elapsed times include waiting on following events:
  Event waited on                             Times   Max. Wait  Total Waited
  ----------------------------------------   Waited  ----------  ------------
  SQL*Net message to client                       2        0.00          0.00
  SQL*Net message from client                     2       68.63         68.64
  db file sequential read                    616163        0.05        318.20
  direct path write                               2        0.00          0.00
  direct path read                             5519        0.00          0.00

SELECT count(*)
FROM FEM_SAVINGS
WHERE FEM_SAVINGS.AS_OF_DATE = to_date('31-Jan-2007','DD-Mon-YYYY');

SELECT count(*)
FROM REF_ETL_PRODUCT_ASSUMPTIONS 
WHERE PRODUCT_TYPE_CD = 182;

SQL> SELECT count(*)
  2  FROM FEM_SAVINGS
  3  WHERE FEM_SAVINGS.AS_OF_DATE = to_date('31-Jan-2007','DD-Mon-YYYY');

 COUNT(*)
---------
  6226364

SQL> SELECT count(*)
  2  FROM REF_ETL_PRODUCT_ASSUMPTIONS 
  3  WHERE PRODUCT_TYPE_CD = 182;

 COUNT(*)
---------
        1

SQL> 

SQL> l
  1  SELECT  i.table_name
  2  ,       i.index_name
  3  ,       NULL as partition_name
  4  ,       i.num_rows / i.leaf_blocks AS rows_per_block
  5  FROM    user_indexes i
  6  LEFT OUTER JOIN user_part_indexes p
  7  ON      p.index_name = i.index_name
  8  WHERE   p.index_name IS NULL
  9  AND     i.num_rows >= 5000
 10  AND     i.leaf_blocks > 0
 11  AND     i.table_name LIKE '&pTab'
 12  UNION ALL
 13  SELECT  i.table_name
 14  ,       p.index_name
 15  ,       p.partition_name
 16  ,       p.num_rows / p.leaf_blocks AS rows_per_block
 17  FROM    user_ind_partitions p
 18  JOIN    user_indexes i
 19  ON      i.index_name = p.index_name
 20  WHERE   p.num_rows >= 5000
 21  AND     p.leaf_blocks > 0
 22* AND     i.table_name LIKE '&pTab'
SQL> /
old  11: AND     i.table_name LIKE '&pTab'
new  11: AND     i.table_name LIKE 'FEM_SAVINGS'
old  22: AND     i.table_name LIKE '&pTab'
new  22: AND     i.table_name LIKE 'FEM_SAVINGS'

TABLE_NAME      INDEX_NAME           PARTITION_ ROWS_PER_BLOCK
--------------- -------------------- ---------- --------------
FEM_SAVINGS     FEM_SAVINGS                          207.14123
FEM_SAVINGS     FEM_SAVINGS_RM                       170.54415

SQL>