We help IT Professionals succeed at work.

Global temporary tables

d27m11y
d27m11y asked
on

Can someeone provide me an example how global temporary tables are advantageous and why they are used over regular tables.

All I know is they are specific to session. Is there anything else I am missing, please advise me with
an example
Comment
Watch Question

Most Valuable Expert 2012
Distinguished Expert 2019
Commented:
You already answered the question.

The advantage is that only the session that entered the data can see/access it.  The data is also automatically deleted/truncated when the session ends.

No need to 'track' what needs cleaned up and who can access what like in a 'regular' table.

>>please advise me with an example

Now sure what sort of example you are looking for.  The syntax for creating them is online.
Most Valuable Expert 2011
Top Expert 2012
Commented:
temp tables don't have redo associated with them.  so they have less io overhead and hence, oftne perform faster

Note, indexes on temp tables will still generate redo as part of normal index maintenance, but this is still somewhat less than you'd find on a normal table
Top Expert 2008
Commented:
They could be also transaction tables (they will exists until COMMIT or ROLLBACK).

the advantage is that only the session/transaction will see the data and as mentioned above you have no maintenance issues.

Example:

CREATE GLOBAL TEMPORARY TABLE employees_temp
ON COMMIT PRESERVE ROWS
AS SELECT * FROM employees;

Use the TRUNCATE TABLE command to delete the contents of the table.
You can create the following on temporary tables:
Indexes
Views
Triggers
Top Expert 2011
Commented:

Author

Commented:
Thanks!