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.
How to Create Global Temporary Tables (GTTs)?
Usually,
when we does a commit after inserting/updating/deleting a table, the data gets
written into the Database.
But for GTTs, as I said, it never gets written to
the database. Then
what happens if we issue a commit after doing some
insert/update/delete on a
GTT?
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 ina 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.
Link to this page: If you find this page useful, we encourage you to link to this page. Simply copy and paste the code below to your website, blog, or profile.
' |
'
' |
'
')
