How to use oracle temporary table? - Page 2
DBAsupport.com Forums - Powered by vBulletin
Page 2 of 2 FirstFirst 12
Results 11 to 14 of 14

Thread: How to use oracle temporary table?

  1. #11
    Join Date
    Nov 2002
    Location
    Geneva Switzerland
    Posts
    3,142
    Oracle does things a bit differently than you might expect - so you will run into trouble if you just copy methods from other databases:

    - You create the GTT only once, before you write any PL/SQL (You don't have CREATE or DROP in the routine).
    - Each session sees a view of the GTT which is independent of any other session. You just use it as if it had been created specially for you alone.

    As Gandolf say, this may not be the best way.
    "The power of instruction is seldom of much efficacy except in those happy dispositions where it is almost superfluous" - Gibbon, quoted by R.P.Feynman

  2. #12
    1. Create the structure of the global temporary table once, outside of pl/sql.
    2. Utilize that table in your procedures. The contents of the table will be local to your session and will automatically disappear when you either log out or commit, depending on how the table is configured.

    Multiple sessions can use the same GTT at the same time, but they will not be able to see or interact with each others data. They will also not block each other for any action against that table.

  3. #13
    Join Date
    Aug 2008
    Posts
    2
    Got it. Brilliant. Finally understood, implemented and working.
    Thanks a lot.

  4. #14
    Join Date
    May 2000
    Location
    ATLANTA, GA, USA
    Posts
    3,135
    Assaf,

    One more thing I would like to add here:

    The statistics on the GTT may not be optimal for all sessions when Oracle computes the execution plan.

    What I did in one of the projects is:
    1. Created a permanent table, loaded data and analyzed the table.
    2. Verified that the execution plan is optimal.
    3. Exported of the table and index stats from the permanent table
    4. Imported the stats to the GTT.

    After this exercise, I saw consistent execution plan.

    You may need to do it if you see slow performance.

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