Global Temporary Tables-Database Tables-1

Global temporary tables are used to hold intermediate resultsets for the duration of either a transaction or a session. The data held in a temporary table is only ever visible to the current session—no other session will see any other session’s data, even if the current session COMMITs the data.

Multiuser concurrency is not an issue with regard to temporary tables either, as one session can never block another session by using a temporary table. Even if we “lock” the temporary table, it will not prevent other sessions from using their temporary table.

Note As we observed in Chapter 9, temporary tables generate significantly less redo than regular tables. However, since temporary tables generate undo information for the data they contain, they will generate some amount of redo.

UPDATEs and DELETEs will generate the largest amount; INSERTs and SELECTs the least amount. We also saw in Chapter 9, starting with Oracle 12c, that temporary tables can be configured to generate next to zero redo; this is done by setting the TEMP_UNDO_ENABLED parameter to TRUE.

Temporary tables will allocate storage from the currently logged-in user’s temporary tablespace, or if they are accessed from a definer rights procedure, the temporary tablespace of the owner of that procedure will be used.

A global temporary table is really just a template for the table itself. The act of creating a temporary table involves no storage allocation; no INITIAL extent is allocated, as it would be for a regular heap-organized table (unless the deferred segment feature is in effect).

Rather, at runtime when a session first puts data into the temporary table, a temporary segment for that session will be created. Since each session gets its own temporary segment (not just an extent of an existing segment), every user might be allocating space for their temporary table in different tablespaces.

USER1 might have their temporary tablespace set to TEMP1, so their temporary tables will be allocated from this space. USER2 might have TEMP2 as their temporary tablespace, and their temporary tables will be allocated there.

Oracle’s global temporary tables are similar in some ways to temporary tables in other relational databases, with the main exception being that they are statically defined. You create them once per database, not once per stored procedure in the database.

They always exist—they will be in the data dictionary as objects, but they will always appear empty until your session puts data into them. The fact that they are statically defined allows you to create views that reference temporary tables, to create stored procedures that use static SQL to reference them, and so on.

Temporary tables may be session based (data survives in the table across COMMITs but not a disconnect/reconnect). They may also be transaction based (data disappears after a COMMIT). Here is an example showing the behavior of both. I used the SCOTT.EMP table as a template:

The ON COMMIT PRESERVE ROWS clause makes this a session-based temporary table. Rows will stay in this table until my session disconnects or I physically remove them via a DELETE or TRUNCATE. Only my session can see these rows; no other session will ever see my rows, even after I COMMIT.

The ON COMMIT DELETE ROWS makes this a transaction-based temporary table. When my session commits, the rows disappear. The rows will disappear by simply giving back the temporary extents allocated to my table—there is no overhead involved in the automatic clearing of temporary tables.

Now, let’s look at the differences between the two types:

You can check whether a table has been created as temporary and the duration of the data (per session or transaction) by querying the TEMPORARY and DURATION columns of the USER_TABLES view. The default DURATION is SYS$TRANSACTION (meaning ON COMMIT DELETE ROWS). Here’s what these values look like for this example:

If you have experience of temporary tables in SQL Server and/or Sybase, the major consideration for you is that instead of executing SELECT X, Y, Z INTO #TEMP FROM SOME_TABLE to dynamically create and populate a temporary table, you will

•\ Create all your global temporary tables once, as part of the application installation, just as you create permanent tables.

•\ In your procedures, simply INSERT INTO TEMP (X,Y,Z) SELECTX,Y,Z FROM SOME_TABLE.

Just to drive home the point, the goal here is not to create tables in your stored procedures at runtime. That is not the proper way to use temporary tables in Oracle. DDL is an expensive operation; you want to avoid doing that at runtime. The temporary tables for an application should be created during the application installation—never at runtime. The pitfalls you will encounter if you attempt to dynamically create the global temporary tables (or just tables in general) at runtime in PL/SQL will be as follows:

•\ You will be doing DDL at runtime. DDL is extremely expensive; it involves hundreds of recursive SQL statements. DDL involves a lot of serialization (one at a time, get in line).

•\ You will have to use dynamic SQL in your PL/SQL to use these tables. You lose all of the benefits of static, compile-time SQL. This is a huge loss.

•\ You will not be able to run two copies of your stored procedure at the same time, ever. Since both stored procedure instances would attempt to drop and create the same temporary table, they would conflict with each other (in this scenario, you could dynamically generate a unique name for the temporary table each time the procedure is created, but this would introduce complexity and potential headaches when troubleshooting).

•\ You will end up having your tables stick around some day—that is, your code will not drop them correctly. Due to an unforeseen error (a power failure is all it would take), your procedure might not complete. Your table will still be there when power is restored. You will have to manually clean up objects from time to time.

In short, there are no good reasons to create tables in PL/SQL at runtime, only reasons to not ever create tables in PL/SQL at runtime.

Temporary tables can have many of the attributes of a permanent table. They may have triggers, check constraints, indexes, and so on. Features of permanent tables that they do not support include the following:

•\ They cannot have referential integrity constraints. Neither can theybe the target of a foreign key, nor can they have a foreign key definedon them.
•\ They cannot have NESTED TABLE type columns.
•\ They cannot be IOTs.
•\ They cannot be in a cluster of any type.
•\ They cannot be partitioned.
•\ They cannot have statistics generated via the ANALYZE tablecommand.