temporary table vs heap organized table for staging data
MoazzamMay 17 2013 — edited May 17 2013We are using Oracle 10g on Linux platform.
We have a staging table where data read from file (containing one million rows daily) is loaded, enriched(insert/update/delete) and then finally loaded into the another table. I want to know if this table be a global temporary table or normal heap organized to reduce the undo/redo generation.
I am not in favour of temporary table because:
1- Any additional pressure on temporary tablespace may cause ORA-01652: unable to extend temp segment temporary issues
2- They are primarily intended for handling session specific data.
3- The statistics are not gathered for these tables by Oracle job. For this, we'll have to do dynamic sampling in the query.
The problem with heap-organized table is that they generate more undo/redo than temporary tables.
Please guide me.