Global Temporary Tables (GTT)

Global temporary tables (GTTs) are permanent objects storing temporary data. This is worth mentioning right at the beginning. They store transactional data just for the session itself. Each session can have its own set of data processing and therefore they are very handy and might be shared across multiple applications.

There are two ways temporary data expires in the table:

• after the commit is executed (ON COMMIT DELETE ROWS)
• after the transaction/session ends (ON COMMIT PRESERVE ROWS)

Let’s have a quick look at a few ways how to create a global temporary table:

-- data will remain until the end of the session; C(GT)TAS
CREATE GLOBAL TEMPORARY TABLE my_schema.gtt_my_data 
ON COMMIT PRESERVE ROWS
AS
SELECT *
FROM employee
WHERE 1=1
AND id_department = 1001;


-- data will remain only until the first commit is issued; C(GT)TAS
CREATE GLOBAL TEMPORARY TABLE my_schema.gtt_my_data 
ON COMMIT DELETE ROWS
AS
SELECT *
FROM employee
WHERE 1=1
AND id_department = 1001;


-- data will remain only until the first commit is issued; regular CREATE 
CREATE GLOBAL TEMPORARY TABLE my_schema.gtt_my_data (
    id INT,
    dtime_inserted DATE,
    id_user_inserted INT,
    data_value VARCHAR2(100)
)
ON COMMIT DELETE ROWS;

Global temporary tables are stored in temporary segments (you can read more about temporary and permanent segments and objects in the article Logical Storage Structures).

It is worth mentioning as well, that whenever you use direct loads into temporary tables (i.e. INSERT /+APPEND/ INTO … ) you have to commit all changes there – thus you should use the clause ON COMMIT PRESERVE ROWS.

You can easily use indexes with GTTs and as long as your statistics are not stale you can get great results with them as well. However, you cannot partition GTTs, which I, personally, find very sad.

I strongly recommend using GTTs when you need to do a huge load especially with complicated queries with multiple subqueries reading millions of records. Rather split your load into multiple steps, load your subqueries into GTTs first and then join them in your final query. One note to remember though; Because when you do this, you usually insert data and after the load, they are truncated (that’s the feature of GTT 🙂 ) – you have to update your statistics accordingly to use indexes effectively. Otherwise, your statistics will be stale and your indexes won’t work properly.

Last few things to remember when dealing with GTTs:

1. Because they are stored in the temporary tablespace, they cannot be backed up / restored in case of any failure. Please do not store any vital data for business there!
2. You cannot perform any DDL when the table is in “transactional” mode. You have to close all your transactions (either with a commit or close the session) to do so.
3. All data stored with one application is visible to that application only! You can never see processing data of any other application!
4. When using it as a temporary load table (which is a good idea) you follow the following sequences of commands:
   • TRUNCATE TABLE …. – just in case you are reloading the data withing a session (to avoid duplications)
   • INSERT /*+APPEND*/ INTO …. – direct insert will be way faster than a regular insert
   • COMMIT – just to make sure you can operate with that table further without any limitations
   • GATHER_TABLE_STATS – don’t forget to update the stats 😉
5. GTTs significantly decrease the REDO LOG FILE operations !! Which is good when it comes to performance but again wrong if you would want to recover your data in case of any disaster.
6. TRUNCATE only removes session-related data … not all of them
7. You can create VIEWs and TRIGGERs using GTTs

To summarize, I strongly recommend using GTTs to improve your performance and simplify your loads. On the other hand, never use GTTs to store data you need for your business operation!