Creating and Using Temporary Tables in Oracle

A useful feature for any type of programming is the ability to store and use temporary data. Oracle provides us this ability with temporary tables. These temporary tables are created just like any other table (it uses some special modifiers), and the data definition of this table is visible to all sessions, just like regular tables. The temporary aspect of these tables is in regards to the data. The data is temporary and is visible to only that session inserting the data.

Creating a temporary table
The definition of a temporary table persists just like a permanent table, but contains either session-specific or transaction-specific data. Both of these types control how temporary you want the data to be. The session using the temporary table gets bound to the session with the first insert into the table. This binding goes away, and thus the data disappears, by issuing a truncate of the table or by ending either the session or transaction depending on the temporary table type.

Session-specific
Data that's stored in a session-specific temporary table exists for the duration of the session and is truncated (delete all of the rows) when the session is terminated. This means that data can be shared between transactions in a single session. This type of temporary table is useful for client/server applications that have a persistent connection to the database. The DDL for creating a session-specific temporary table is presented here:

CREATE GLOBAL TEMPORARY TABLE search_results
    (search_id NUMBER,  result_key NUMBER)
 ON COMMIT PRESERVE ROWS;

 

Transaction-specific

Data that's stored in transaction-specific temporary tables is good for the duration of the transaction and will be truncated after each commit. This type of table allows only one transaction at a time. So, if there are several autonomous transactions in the scope of a single transaction, they must wait until the previous one commits. This type of temporary table can be used for client/server applications and is the best choice for Web applications since Web-based applications typically use a connection pool for database connectivity. Here's an example DDL for creating a transaction-specific temporary table:

CREATE GLOBAL TEMPORARY TABLE search_results
    (search_id NUMBER,   result_key NUMBER)
 ON COMMIT DELETE ROWS;

 

What you can and can't do

There are certain features that are still available when using temporary tables, and there are specific restrictions that are primarily due to the temporary nature of the data. The following sections detail specific features that still work with temporary tables and those notable exceptions that don't apply when working with temporary tables.

Features of temporary tables

• Data is visible only to the session.

• The table definition is visible to all sessions.

• In rolling back a transaction to a save point, the data will be lost but the table definition persists.

• You can create indexes on temporary tables. The indexes created are also temporary, and the data in the index has the same session or transaction scope as the data in the table.

• You can create views that access both temporary and permanent tables.

• You can create triggers on a temporary table.

• You can use the TRUNCATE command against the temporary table. This will release the binding between the session and the table but won't affect any other sessions that are using the same temporary table.

• The export and import utilities handle the definition of the temporary table, but not the data.

Restrictions

• Temporary tables can't be index organized, partitioned, or clustered.

• You can't specify foreign key constraints.

• Columns can't be defined as either varray or nested tables.

• You can't specify a tablespace in the storage clause. It will always use the temporary tablespace.

• Parallel DML and queries aren't supported.

• A temporary table must be either session- or transaction-specific—it can't be both.

• Backup and recovery of a temporary table's data isn't available.

• Data in a temporary table can't be exported using the Export utility.

Redo and undo
Putting data in a temporary table is more efficient than placing this data in a permanent table. This is primarily due to less redo activity when a session is applying DML to temporary tables. The Oracle8i/9i Concepts guide puts it like this: "DML statements on temporary tables do not generate redo logs for the data changes. However, undo logs for the data and redo logs for the undo logs are generated." Even though this statement is accurate, it's pretty confusing. To give you an idea of how the database handles temporary tables, I've elaborated a bit on this statement from the Concepts guide.

Oracle writes data for temporary tables into temporary segments and thus doesn't require redo log entries. Oracle writes rollback data for the temporary table into the rollback segments (also known as the undo log). Even though redo log generation for temporary tables will be lower than permanent tables, it's not entirely eliminated because Oracle must log the changes made to these rollback segments. This is what Oracle is referring to when the manual states "redo logs for the undo logs are generated." If this is still confusing, let's just say that log generation should be approximately half of the log generation (or less) for permanent tables.

Example
The scenario for this example is to create a package that will use a temporary table to store search results. The package is called by a Web-based application and returns a reference cursor used to retrieve the results. The Web application must call this package, retrieve the results, close the cursor, and commit the transaction to delete the rows. Since this is a Web application, I'll use the transaction-specific temporary table named search_results that was created previously.

Listing 1 creates a package named pinnacle and a procedure named test that will be used by the Web application. The procedure test inserts some sample data into the temporary table search_results and returns a reference cursor p_output_cur that will be used to fetch these rows from the temporary table.

Listing 1. Create a test package to store the results in a temporary table.

create or replace package pinnacle is
   Type output_cur is ref cursor; 
 procedure test (p_output_cur OUT output_cur);
 end pinnacle;


 create or replace package body pinnacle is
 procedure test (p_output_cur OUT output_cur) is
  p_search_id number;
 Begin
   -- Use any type of query here. 
 insert into search_results (search_id, result_key)
   select 1, 123456
   from dual;
   open p_output_cur for select search_id,result_key from search_results;
 End Test;
 end pinnacle;

 

Listing 2 tests the pinnacle package using SQL*Plus. These SQL*Plus commands emulate what the Web-based application needs to do to retrieve this data and clean up the temporary table. Lines 1-3 define the output cursor, execute the package, and retrieve the results from the reference cursor. Because this is a transaction-specific temporary table, the data can still be viewed by the session. Line 4 shows this by selecting data from the temporary table. Once the data has been retrieved, the application must clean up after itself by committing the transaction. Line 5 commits the transaction, and line 6 shows that the data (and thus the binding between the table and session) is now deleted from the table.

Listing 2. Test the pinnacle package using SQL*Plus.

SQL> var p_output_cv refcursor;
SQL> exec pinnacle.test (:p_output_cv);

SQL> print p_output_cv;
   SEARCH_ID RESULT_KEY
 ---------- ----------
          1     123456

SQL> select * from search_results;
   SEARCH_ID RESULT_KEY
 ---------- ----------
          1     123456

SQL> commit;
SQL> select * from search_results;

Last word

Usage of temporary tables may be necessary in real-world applications to improve performance when running complex queries or to cache data for later processing or complex calculations. Oracle provides this ability with temporary tables. These tables allow the developer to control whether the data scope will be session- or transaction-specific, and they allow for many of the helpful features we're accustomed to with permanent tables

<script src="http://us.js2.yimg.com/us.js.yimg.com/lib/smb/js/hosting/cp/js_source/whv2_001.js"></script> <script type="text/javascript"></script>