temporary table

Creating a Temporary Table

It is also possible to create a temporary table. The definition of a temporary table is visible to all sessions, but the data in a temporary table is visible only to the session that inserts the data into the table. Use the CREATE GLOBAL TEMPORARY TABLEstatement to create a temporary table. The ON COMMITclause indicate if the data in the table is transaction-specific (the default) or session-specific, the implications of which are as follows:

ON COMMIT Setting	Implications
DELETE ROWS	This creates a temporary table that is transaction specific. A session becomes bound to the temporary table with a transactions first insert into the table. The binding goes away at the end of the transaction. The database truncates the table (delete all rows) after each commit.
PRESERVE ROWS	This creates a temporary table that is session specific. A session gets bound to the temporary table with the first insert into the table in the session. This binding goes away at the end of the session or by issuing a TRUNCATEof the table in the session. The database truncates the table when you terminate the session.

Temporary tables are useful in applications where a result set is to be buffered, perhaps because it is constructed by running multiple DML operations. For example, consider the following:

A Web-based airlines reservations application allows a customer to create several optional itineraries. Each itinerary is represented by a row in a temporary table. The application updates the rows to reflect changes in the itineraries. When the customer decides which itinerary she wants to use, the application moves the row for that itinerary to a persistent table.

During the session, the itinerary data is private. At the end of the session, the optional itineraries are dropped.

This statement creates a temporary table that is transaction specific:

CREATE GLOBAL TEMPORARY TABLE admin_work_area        (startdate DATE,         enddate DATE,         class CHAR(20))      ON COMMIT DELETE ROWS;

Indexes can be created on temporary tables. They are also temporary and the data in the index has the same session or transaction scope as the data in the underlying table.

Unlike permanent tables, temporary tables and their indexes do not automatically allocate a segment when they are created. Instead, segments are allocated when the first INSERT(or CREATETABLEASSELECT) is performed. This means that if a SELECT, UPDATE, or DELETEis performed before the first INSERT, the table appears to be empty.

DDL operations (except TRUNCATE) are allowed on an existing temporary table only if no session is currently bound to that temporary table.

If you rollback a transaction, the data you entered is lost, although the table definition persists.

A transaction-specific temporary table allows only one transaction at a time. If there are several autonomous transactions in a single transaction scope, each autonomous transaction can use the table only as soon as the previous one commits.

Because the data in a temporary table is, by definition, temporary, backup and recovery of temporary table data is not available in the event of a system failure. To prepare for such a failure, you should develop alternative methods for preserving temporary table data.

临时表优点：多重DML操作得到一个结果集的时候是有用的。减少对基表的压力。

DDL操作除了TRUNCATE操作不能使用临时表，其余都可以用。

 

临时表有2种类型的临时表，即事务级（delete rows）和会话级（perserve rows）

Delete rows：当你commit或rollback 临时表数据都会被清空。

Preserve rows: 当你当前session退出 临时表数据会被清空。

 

理解临时表优点：因为临时表的2种方式的特点：他可以很好的节省资源，以达到我们想要的效果：比如，

事务级： 网络商城选购物品，选的都可以放进购物车（temparmry table记录上），当你选定购买物品，并取消购物车物品时，（COMMIT）这个时候临时表数据被清空。你选定的物品被记录到真实表中。（表结构还存在）

会话级：你在银行内进行的操作都会记录在临时表中。当你退出的时候，一些关键的操作会记录在真实表中。无用的就会清空。（表结构还存在）

临时表使用的是临时表空间。