I am trying to optimize a SQL statement like this:
<select statement> UNION ALL <select statement> UNION ALL ...... UNION ALL <select statement>
I changed it to:
WITH temp AS (select statement)
(<some select statements using temp "UNION ALL together">
UNION ALL <some select statement not using temp "UNION ALL" together>)
and then the Oracle database returns ORA-4030: out of process memory when trying to allocate XXX bytes (kxs-heap-c, temporary memory).
Anybody know why? and what can I do for that if I still want to simplify the SQL statement?