The hash join in the following query is requiring more than 16GB of temp tablespace. All the tables/indexes are analyzed everyday and I am running Oracle 9.2.0.6 on Linux. Here is the query with explain plan.
Code:
SELECT COUNT(DISTINCT car.assessment_locator) AS assessments_mapped 
FROM      CLG_ASSESSMENT_RESULT        car,      CLG_ITEM_RESULT              cir,      CLG_ITEM                     ci,      CLG_CORRELATION              corr,      CB_STANDARD                  cbs,   CLG_ASSESSMENT_INSTANCE cai,   CLG_USER u  
WHERE        u.org_unit_id = :1        
AND u.user_id = cai.assignor_id        
AND car.ASSESSMENT_locator = :2       
AND car.completed_date >= :3        
AND car.completed_date <= :4        
AND cir.ASSESSMENT_RESULT_ID = car.ASSESSMENT_RESULT_ID        
AND (ci.LOCATOR = corr.CONTENT_ID OR ci.ancestor_locator = corr.content_id)        
AND corr.ASSET_TYPE_ID = 113        
AND corr.cbs_id = cbs.CBS_ID       
AND ci.LOCATOR = cir.item_locator


Execution Plan
----------------------------------------------------------
   0      SELECT STATEMENT Optimizer=CHOOSE (Cost=120 Card=1 Bytes=208 )
   1    0   SORT (GROUP BY)
   2    1     HASH JOIN (Cost=120 Card=6846 Bytes=1423968)
   3    2       MERGE JOIN (CARTESIAN) (Cost=107 Card=320 Bytes=64960)
   4    3         NESTED LOOPS (Cost=65 Card=1 Bytes=193)
   5    4           NESTED LOOPS (Cost=64 Card=1 Bytes=189)
   6    5             NESTED LOOPS (Cost=15 Card=1 Bytes=154)
   7    6               NESTED LOOPS (Cost=13 Card=1 Bytes=88)
   8    7                 TABLE ACCESS (BY INDEX ROWID) OF 'CLG_ASSESSMENT_RESULT' (Cost=10 Card=1 Bytes=45)
   9    8                   BITMAP CONVERSION (TO ROWIDS)
  10    9                     BITMAP AND
  11   10                       BITMAP CONVERSION (FROM ROWIDS)
  12   11                         SORT (ORDER BY)
  13   12                           INDEX (RANGE SCAN) OF 'IDX_ASMNTRES_COMPDATE' (NON-UNIQUE) (Cost=2 Card=184)
  14   10                       BITMAP CONVERSION (FROM ROWIDS)
  15   14                         INDEX (RANGE SCAN) OF 'IDX_ASMNTRES_ASMNTLOC' (NON-UNIQUE) (Cost=4 Card=184)

  16    7                 TABLE ACCESS (BY INDEX ROWID) OF 'CLG_ITEM_RESULT' (Cost=3 Card=12 Bytes=516)

  17   16                   INDEX (RANGE SCAN) OF 'PK_ITEM_RESULT' (UNIQUE) (Cost=2 Card=12)

  18    6               TABLE ACCESS (BY INDEX ROWID) OF 'CLG_ITEM' (Cost=2 Card=1 Bytes=66)

  19   18                 INDEX (UNIQUE SCAN) OF 'PK_ITEM' (UNIQUE) (Cost=1 Card=1)

  20    5             TABLE ACCESS (BY INDEX ROWID) OF 'CLG_CORRELATION' (Cost=64 Card=1 Bytes=35)

  21   20               BITMAP CONVERSION (TO ROWIDS)
  22   21                 BITMAP OR
  23   22                   BITMAP CONVERSION (FROM ROWIDS)
  24   23                     SORT (ORDER BY)
  25   24                       INDEX (RANGE SCAN) OF 'PK_CORRELATION' (UNIQUE) (Cost=2 Card=32064)

  26   22                   BITMAP CONVERSION (FROM ROWIDS)
  27   26                     SORT (ORDER BY)
  28   27                       INDEX (RANGE SCAN) OF 'PK_CORRELATION' (UNIQUE) (Cost=2 Card=32064)

  29    4           INDEX (UNIQUE SCAN) OF 'PK_CB_STANDARD' (UNIQUE)
  30    3         BUFFER (SORT) (Cost=107 Card=435 Bytes=4350)
  31   30           TABLE ACCESS (BY INDEX ROWID) OF 'CLG_USER' (Cost=42 Card=435 Bytes=4350)

  32   31             INDEX (RANGE SCAN) OF 'IDX_USER_ORGUNITID' (NON-UNIQUE) (Cost=2 Card=435)

  33    2       INDEX (FAST FULL SCAN) OF 'IDX_ASMNTINST_ASSIGNORID' (NON-UNIQUE) (Cost=9 Card=35353 Bytes=176765)
Any suggestions/ideas on what I can do to improve this ?

Thanks.