Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

Why we require global temporary tables, their practical use cases

I just got to know about the global temporary tables(I use oracle as db) and understood them but not able to know the exact use of these.

As the name suggests they are used to keep temporary data/session and on closing session the data vanishes for that session.

What can be the examples, business use cases where we need to use them.

like image 338
lowLatency Avatar asked Apr 15 '12 09:04

lowLatency


People also ask

Why do we need temporary tables?

As its name indicates, temporary tables are used to store data temporarily and they can perform CRUD (Create, Read, Update, and Delete), join, and some other operations like the persistent database tables.

Why do we need global temporary table in Oracle?

Enhancing security and privacy: In sensitive businesses, GTT is invaluable because it only saves data for the session and the same cannot be queried by other users. Quicker data retrieval: During a session, you can store your data in a tabular form in the temporary table and access it instantly when you need it.

What is the use of global temporary table?

Global Temporary Tables (GTTs) are the Oracle tables, having data type as private; such that data inserted by a session can be accessed by that session only. The session-specific rows in a GTT can be preserved for the entire session, as AI report tables are created using ON COMMIT PRESERVE ROWS clause.

What is the advantage of using a temporary table instead of a table?

Advantages of Temporary Tables You can create a temporary table and insert, delete and update its records without worrying about whether you have sufficient rights to change data in permanent tables, or whether you might be accidentally doing so.


1 Answers

A possible use case would be a user that has only rights to create reports. These reports use a lot of aggregated data and these information is reused in more than one of the reports. So it would be a good idea to cache the information for this session and this can be done by using temporary tables.

Another use case would be that you have a reporting framework and want to have an easy way to query information. So you would just call a PLSQL package that puts together the information in a temporary table and after the package has finished you can just do a SELECT * FROM temporary_table.

like image 112
Eggi Avatar answered Oct 01 '22 15:10

Eggi