Query runs slower under Oracle8i

Dear all,

I have a query in the format like this :

SELECT TAB1.COL1
,TAB1.COL2
,SUM(TMP.COL4)

FROM TAB1
,(SELECT TAB2.COL1
,TAB3.COL2
,TAB3.COL3
,SUM(TAB3.COL4) COL4

FROM TAB2
,TAB3

WHERE TAB2.COL2 = TAB3.COL1
AND TAB2.COL3 = TAB3.COL5
GROUP BY
TAB2.COL1
,TAB3.COL2
,TAB3.COL3

) TMP
WHERE TAB1.COL3 = TMP.COL1
AND TAB1.COL4 = TMP.COL2
GROUP BY
TAB1.COL1
,TAB1.COL2
which run fine under Oracle7 (7.3.4). However, under Oracle8i (8.1.6), I found that the query generated a lot of file i/o on the temporary tablespace (there is no file i/o on the temporary tablespace under Oracle7 during the execution of the same query) and caused the query run much slower than under Oracle7. Is there any default setting for Oracle8i would cause the optimizer to write the temporary result to disk ?

Thanks in advance,

O.K.Man Received on Mon Nov 06 2000 - 18:40:01 CST