In production we have a failing batch with an error:
ORA-01652: unable to extend temp segment by 128 in tablespace MYTABLESPACE
The TEMP segment has already been increased to its maximum tolerable according to the DBA.
Indeed, the SQL request below performs so many outer joins that this behaviour is obvious…
There are two dates as inputs : from and to.
SELECT * /* In reality, not all columns are included but "only" the 122 required */ FROM TABLE1 t1 INNER JOIN TABLE2 t2 ON t2.ID = t1.ID_T2 INNER JOIN TABLE3 t3 ON t3.ID = t1.ID_T3 INNER JOIN TABLE4 t4 ON t4.ID_T1 = t1.ID LEFT JOIN TABLE5 t5 ON t5.ID_T1 = t1.ID LEFT JOIN TABLE6 t6 ON t6.ID_T1 = t1.ID LEFT JOIN TABLE7 t7 ON t7.ID_T3 = t3.ID AND t7.DATE BETWEEN TO_DATE('xxxx-xx-xx', 'YYYY-MM-DD') AND TO_DATE('yyyy-yy-yy', 'YYYY-MM-DD') LEFT JOIN TABLE8 t8 ON t8.ID_T1 = t1.ID LEFT JOIN TABLE9 t9a ON t9a.ID = t4.ID_T9 LEFT JOIN TABLE10 t10 ON t10.ID_T4 = t4.ID LEFT JOIN TABLE11 t11 ON t11.ID_T4 = t4.ID AND t11.DATE BETWEEN TO_DATE('xxxx-xx-xx', 'YYYY-MM-DD') AND TO_DATE('yyyy-yy-yy', 'YYYY-MM-DD') LEFT JOIN TABLE9 t9b ON t9b.ID = t11.ID_T9 LEFT JOIN TABLE12 t12 ON t12.ID_T1 = t1.ID LEFT JOIN TABLE13 t13 ON t13.ID_T4 = t4.ID LEFT JOIN TABLE14 t14 ON t14.ID_T13 = t13.ID LEFT JOIN TABLE15 t15 ON t15.ID_T4 = t4.ID LEFT JOIN TABLE16 t16 ON t16.ID_T4 = t4.ID LEFT JOIN TABLE17 t17 ON t17.ID_T1 = t1.ID LEFT JOIN TABLE18 t18 ON t18.ID_T17 = t17.ID LEFT JOIN TABLE19 t19 ON t19.ID_T17 = t17.ID LEFT JOIN TABLE20 t20 ON t20.ID_T17 = t17.ID WHERE t4.something IS NOT NULL OR t11.something IS NOT NULL ORDER BY t2.ID, t1.ID, t3.ID, t4.ID, t14.ID, t18.ID, t5.DATE DESC;
I’m very pessimist about this issue which raises a very big conception problem. We should have split it in several steps.
Do you see a way to reduce the TEMP segment usage without a change of the code that needs the returned data as-is? For example:
- Can a subquery performing the WHERE clause, help?
- Can a smaller interval between the date from and to, help?
In general, what affects the TEMP segment usage?