Oracle - truncating a global temporary table

I am processing large am开发者_如何转开发ounts of data in iterations, each and iteration processes around 10-50 000 records. Because of such large number of records, I am inserting them into a global temporary table first, and then process it. Usually, each iteration takes 5-10 seconds.

Would it be wise to truncate the global temporary table after each iteration so that each iteration can start off with an empty table? There are around 5000 iterations.

---

No! The whole idea of a Global Temporary Table is that the data disappears automatically when you no longer need it.

For example, if you want the data to disappear when you COMMIT, you should use the ON COMMIT DELETE ROWS option when originally creating the table.

That way, you don't need to do a TRUNCATE - you just COMMIT, and the table is all fresh and empty and ready to be reused.

---

5000 iterations on 50000 records per run? If you need to be doing that much processing, surely you can optimise your processing logic to run more efficiently. That would give you more speed compared to truncating tables.

However, if you are finished with data in a temp table, you should truncate it, or just ensure that the next process to use the table isn't re-processing the same data over again.

E.g. have a 'processed' flag, so new processes don't use the existing data. OR remove data when no longer needed.