Hi,
We are using a lot of GTTs in our ETL stored procedures in our new Exadata data warehouse environment. DBAs asked developers to create all GTTs with ON COMMIT PRESERVE setting and truncate them at the beginning of the procs using EXECUTE IMMEDIATE. This didn't seem like a good idea to me so I created my GTTs with ON COMMIT DELETE so that the tables get cleared after each rollback or commit.
I do think that my approach of using on commit delete and not needing to truncate tables explicitly is the right one. But there are hundreds of other GTTs that others have created using ON COMMIT PRESERVE that they truncate in their stored procs. Do you think it's worth convincing them to change the GTT definition of their tables to ON COMMIT DELETE and remove truncate statements from their procs? Are there any major benefits at this point that they would consider making this changes in hundreds of procs?