Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
Home -> Community -> Usenet -> c.d.o.server -> 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