Could someone please advise what are the advantages/disadvantages of global temporary tables vs permanent tables in assigned tablespaces? Any performance advantages?
I can't seem to find a good write up of the differences and pros and cons. Thanks for your support.
I'm assuming (which may be a mistake on my part, but anyway...) that you mean global temporary tables vs. permanent tables that you use in a temporary fashion for reporting.
1. GTTs use space in your temporary tablespace. Regular tables use space in their assigned tablespace.
2. GTTs never have to be truncated, the data automatically goes away when your session ends (or you commit). Regular tables persist until you delete/truncate.
3. GTTs allow you to see only the data your session has put in. Regular tables allow you to see other people's data that they put in (Assuming you're not using FGAC).
4. Regular tables get fragmented/oversized over time because your report is 100 rows and the next guy's report is 10000000 rows which may lead to lots of empty blocks being scanned. GTTs are effectively truncated when you logout/commit.
5. Regular tables have stats calculated in order to help the optimizer (which may be good or bad). GTTs don't have stats calculated unless they are specifically set.
6. GTTs don't persist very well. This makes it harder for stateless or pooled connections to "build" with one connection and then "Query" with another connection.