A temporary table has a definition or structure like that of a regular table, but the data it contains exists for only the duration of a transaction or session. Oracle8i allows you to create temporary tables, which can be bound to a session or to a transaction.
You can use temporary tables to improve performance when running an otherwise complex and expensive query. To do this, you can cache the values from a more complex query in temporary tables, then run SQL statements, such as joins, against those temporary tables. Thus, the performance of your queries can improve drastically.
You can create indexes on temporary tables. Another benefit of using temporary tables is that they do not automatically allocate a segment when they are created. Instead, segments are allocated when the first INSERT (or CREATE TABLE AS SELECT) is performed. This means that if a SELECT, UPDATE, or DELETE is performed before the first INSERT, the table appears to be empty. The data in these tables get automatically deleted at the end of the transaction, or a session depending upon the type of the temporary table declared. The syntax for creating session-specific temporary tables is:
CREATE GLOBAL TEMPORARY TABLE . . . [ON COMMIT PRESERVE ROWS]The syntax for creating transaction-specific temporary tables is:
CREATE GLOBAL TEMPORARY TABLE . . . [ON COMMIT DELETE ROWS]
