Global Temporary tables vs permanent
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 8 of 8

Thread: Global Temporary tables vs permanent

  1. #1
    Join Date
    Jun 2006
    Location
    Montreal
    Posts
    6

    Global Temporary tables vs permanent

    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.

  2. #2
    Join Date
    Mar 2004
    Location
    DC,USA
    Posts
    650
    Google out, you get plenty that you'll never ask here just like this.

    How abt reading them and then post your problems?
    "What is past is PROLOGUE"

  3. #3
    Join Date
    Jun 2006
    Posts
    259
    temporary tables hold temporary data.
    Permanent table store permanent data.

    The rest as they say is left as an excercise for the reader.

  4. #4
    Join Date
    Nov 2000
    Location
    greenwich.ct.us
    Posts
    9,092
    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.
    Jeff Hunter
    marist89@yahoo.com
    http://marist89.blogspot.com/
    Get Firefox!
    "I pledge to stop eating sharks fin soup and will not do so under any circumstances."

  5. #5
    Join Date
    Jun 2006
    Posts
    259
    Quote Originally Posted by marist89

    5. GTTs don't have stats calculated unless they are specifically set.
    Learned something new. Thanks, that makes sense as well.

  6. #6
    Join Date
    May 2000
    Location
    ATLANTA, GA, USA
    Posts
    3,135
    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.
    If we set optimizer_dynamic_sampling parameter correctly, then oracle calculates the stats during query parse time in 9i/10g.

    Tamil

  7. #7
    Join Date
    Mar 2002
    Posts
    534
    Quote Originally Posted by marist89
    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.

    Are the data deleted or truncated?

    If truncated how does it work? Does each session got it's own extent/segment ?

  8. #8
    Join Date
    May 2000
    Location
    ATLANTA, GA, USA
    Posts
    3,135
    Quote Originally Posted by mike9
    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.

    Tamil

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  


Click Here to Expand Forum to Full Width