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.