Global Temporary Tables

Global Temporary Tables

Global Temporary tables are session dependant tables which could be used as temporary storage for calculations, sorting etc. What I mean by Session dependant is, the data being stored in the Global Temporary table is not written into the database or stored anywhere. Once the session ends (in which the Global Temporary table is used), the data also vanishes.

However the structure would still be available even after the Session is logged out. Also, the structure is available to other sessions even when one session is using it, but not the data. i.e multiple sessions could use the same Global Temporary table without interfering the data. Each session could insert/update/delete their own data into the same Global Temporary table as if the table is available to only that session. Any data inserted in one session is not available to another.

Now, why do we need Global Temporary tables? Well, imagine a requirement where you need to fetch some data from the database, do some kind of calculations, aggregations and provide the Result Set (many records) to a Front End. Again, in the Front End, you need to fetch the Result set may times, for some purpose. Then you could make use of the Global Temporary table. Until the user gets disconnected from that Database session, the data is available for him in the memory.

Well, that depends on what you have instructed the Global Temporary table to do while a commit is begin issued. And depending on that

there are 2 different methods of creating a Global Temporary table.

1. ON COMMIT DELETE ROWS

CREATE GLOBAL TEMPORARY TABLE GTT_EX (

Var_int1 NUMBER,

Var_char2 VARCHAR2

) ON COMMIT DELETE ROWS;

This is just opposite of what happens to regular tables. If a Global Temporary table is created with ON COMMIT DELETE ROWS option, it removes all data when a commit is issued.

Now you might think why somebody needs such a table which removes all data when a commit happens. Well, sometimes you need a temporary table to do some kind of calculation on 3-4 columns, some kind of summation, then aggregation etc, record by record.

All these results, could be stored in the Temporary table column. And you need to fetch only once to get the whole data and store in some other Reporting Table (Target). So, after the calculation, you don t need the data in the Temporary table to be preserved anywhere, but you need the data in the Reporting table. Then, we make use of ON COMMIT DELETE ROWS Global Temporary table, such that the commit writes the Data for Reporting table into the DB and the data in Global Temporary table is removed.

2. ON COMMIT PRESERVE ROWS

CREATE GLOBAL TEMPORARY TABLE GTT_EX (

Var_int1 NUMBER,

Var_char2 VARCHAR2

) ON COMMIT PRESERVE ROWS;

ON COMMIT PRESERVE ROWS Global Temporary tables do not remove the data once a commit is issued. It keeps the data until the end of the session. Remember, preserve rows doesn t mean, the data is stored anywhere. The data is removed once the Session ends.

Questions:

1. Can we do insert/update/delete on a Global Temporary table?

Yes we can do insert/update/delete on a Global Temporary table as to a normal Table.

2. What happens if we TRUNCATE a Global Temporary table?

The data is removed only from that specific session where the TRUNCATE is issued. It wont affect the data in the same Global Temporary table in other sessions.

3. Can we drop a Global Temporary table using the Drop command.

Yes, we can. Create and Drop commands work as usual as to a normal Table

4.What happens to the data in a Global Temporary table if the session ends abnormally?

Well, as I said earlier, no matter if the session ends normally or abnormally, the data will never be available once the session ends.

5.Can we have all other features of a normal table on a Global Temporary table.

Yes, we can have most of the features as that of a normal table on a Global Temporary table. We could create indexes, Triggers, Views etc.