Temporary Tables -
The generalized concept of temporary tables as described above as a role to store the sub results of 3 or 4 subqueries (which make the complex query) and then joining such temporary tables to get the final result of the original complex query is not believed to be an appropriate application of temporary tables in Oracle, unlike other database systems (Tom Kyte, Expert one-on-one Oracle, wrox publication). However for a simplified understanding of the concept, using this as an example is not wrong either.
Oracle Temporary Tables -
In Oracle, they are called "Global Temporary Tables" (GTT) rather than just "Temporary Tables". A GTT is just really a template for the table itself. They are "statically defined" to exist permanently in the database (data dictionary) as objects; and as the term "global" indicates in their type name they are created only once per database and not once per stored procedure. Temporary table will allocate storage from the currently logged user's temporary tablespace, or if they are accessed from a "definer's right procedure" then temporary tablespace of the owner of the procedure. Actually every user referring to "same" GTT actually creates a temporary segment in his/her temporary tablespace in the background when their sessions put the first data in the GTT. This arrangement avoids interference and visibility of data and transactions across concurrent sessions. Also there are no possibilities of one session blocking another session in concurrent transaction and extremely small amount of redo is generated as compared to the transactions made against the normal "Heap Organized Tables" The GTT may be either created to hold the data for the duration of a transaction or session as per the requirement and application design.
GTT can have many of the attributes of a normal "Heap Organized Table" like triggers, check constraints, indexes, use same SQL for all database query and manipulation; but may not have referential integrity constraint (Foreign key), neither can be a target of a foreign key, no varray or nested table columns, not be part of cluster or partitioned and analyzed for optimization.
GTT Example -
With that little background let us have an example with explanation to understand the use of GTT.
There are two tables "store" and "sales" and we have to answer "show all stores with above average sales". There are three aggregate operations needed -
- Total number of stores.
- Total sale for each store.
- Total sale for all the stores.