Printable View
Hi,
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.
Google out, you get plenty that you'll never ask here just like this.
How abt reading them and then post your problems? :cool:
temporary tables hold temporary data.
Permanent table store permanent data.
The rest as they say is left as an excercise for the reader.
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.
Learned something new. Thanks, that makes sense as well.Quote:
Originally Posted by marist89
If we set optimizer_dynamic_sampling parameter correctly, then oracle calculates the stats during query parse time in 9i/10g.Quote:
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.
Tamil
Quote:
Originally Posted by marist89
Are the data deleted or truncated?
If truncated how does it work? Does each session got it's own extent/segment ?
Yes. Each session has its own extent(s) for the GTT in the temp tablespace. The extent(s) will be removed after the session ends.Quote:
Originally Posted by mike9
Tamil