Oracle 临时表
一. 临时表说明
Oracle Database temporary tables hold data that exists only for the duration of a transaction or session. Data in a temporary table is private to the session, which means that each session can only see and modify its own data.
Temporary tables are useful in applications where a result set must be buffered. For example, a scheduling application enables college students to create optional semester course schedules. Each schedule is represented by a row in a temporary table. During the session, the schedule data is private. When the student decides on a schedule, the application moves the row for the chosen schedule to a permanent table. At the end of the session, the schedule data in the temporary data is automatically dropped.
The CREATE GLOBAL TEMPORARY TABLE statement creates a temporary table. The ON COMMIT clause specifies whether the table data is transaction-specific (default) or session-specific.
Unlike temporary tables in some other relational databases, when you create a temporary table in an Oracle database, you create a static table definition. The temporary table is a persistent object described in the data dictionary, but appears empty until your session inserts data into the table. You create a temporary table for the database itself, not for every PL/SQL stored procedure.
Because temporary tables are statically defined, you can create indexes for them with the CREATE INDEX statement. Indexes created on temporary tables are also temporary. The data in the index has the same session or transaction scope as the data in the temporary table. You can also create a view or trigger on a temporary table.
Segment Allocation in Temporary Tables
Like permanent tables, temporary tables are defined in the data dictionary. Temporary segments are allocated when data is first inserted. Until data is loaded in a session the table appears empty. Temporary segments are deallocated at the end of the transaction for transaction-specific temporary tables and at the end of the session for session-specific temporary tables.
临时表只在Oracle 8i 以及以上产品中支持。ORACLE数据库除了可以保存永久表外，还可以建立临时表temporary tables。这些临时表用来保存一个会话SESSION的数据，或者保存在一个事务中需要的数据。当会话退出或者用户提交commit和回滚rollback事务的时候，临时表的数据自动清空，但是临时表的结构以及元数据还存储在用户的数据字典中。
Oracle的临时表创建之后基本不占用表空间，临时表并非存放在用户的表空间中，而是存放在 Schema 所指定的临时表空间中。如果你没有指定临时表（包括临时表的索引）存放的表空的时候，你插入到临时表的数据是存放在ORACLE系统的临时表空间中（TEMP）。
可以对临时表创建索引，视图，触发器，可以用export和import工具导入导出表的定义，但是不能导出数据。表的定义对所有的会话可见。建立在临时表上的索引也是临时的,也是只对当前会话或者事务有效.
尽管对临时表的DML操作速度比较快，但同样也是要产生 Redo Log ，只是同样的DML语句，比对 PERMANENT 的DML 产生的Redo Log 少。
临时表的不足之处：
1.不支持lob对象，这也许是设计者基于运行效率的考虑，但实际应用中确实需要此功能时就无法使用临时表了。
2.不支持主外键关系
特性和性能(与普通表和视图的比较)
1.临时表只在当前连接内有效
2.临时表不建立索引,所以如果数据量比较大或进行多次查询时,不推荐使用
3.数据处理比较复杂的时候时表快,反之视图快点
4.在仅仅查询数据的时候建议用游标: open cursor for 'sql clause';
临时表的应用：
对于一个电子商务类网站，不同消费者在网站上购物，就是一个独立的 SESSION，选购商品放进购物车中，最后将购物车中的商品进行结算。也就是说，必须在整个SESSION期间保存购物车中的信息。同时，还存在有些消费者，往往最终结账时放弃购买商品。如果，直接将消费者选购信息存放在最终表（PERMANENT）中，必然对最终表造成非常大的压力。因此，对于这种案例，就可以采用创建临时表(ON COMMIT PRESERVE ROWS)的方法来解决。数据只在 SESSION 期间有效，对于结算成功的有效数据，转移到最终表中后，ORACLE自动TRUNCATE 临时数据；对于放弃结算的数据，ORACLE 同样自动进行 TRUNCATE ，而无须编码控制，并且最终表只处理有效订单，减轻了频繁的DML的压力。
Temp Table 的另一个应用，就是存放数据分析的中间数据。
二. 创建临时表
2.1 Creating a Temporary Table
Temporary tables are useful in applications where a result set is to be buffered (temporarily persisted), 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.
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 TABLE statement to create a temporary table. The ON COMMIT clause indicates 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 TRUNCATE of the table in the session. The database truncates the table when you terminate the session. |
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.
By default, rows in a temporary table are stored in the default temporary tablespace of the user who creates it. However, you can assign a temporary table to another tablespace upon creation of the temporary table by using the TABLESPACE clause of CREATE GLOBAL TEMPORARY TABLE. You can use this feature to conserve space used by temporary tables.
For example, if you must perform many small temporary table operations and the default temporary tablespace is configured for sort operations and thus uses a large extent size, these small operations will consume lots of unnecessary disk space. In this case it is better to allocate a second temporary tablespace with a smaller extent size.
The following two statements create a temporary tablespace with a 64 KB extent size, and then a new temporary table in that tablespace.
CREATE TEMPORARY TABLESPACE tbs_t1
TEMPFILE 'tbs_t1.f' SIZE 50m REUSE AUTOEXTEND ON
MAXSIZE UNLIMITED
EXTENT MANAGEMENT LOCAL UNIFORM SIZE 64K;
CREATE GLOBAL TEMPORARY TABLE admin_work_area
(startdate DATE,
enddate DATE,
class CHAR(20))
ON COMMIT DELETE ROWS
TABLESPACE tbs_t1;
2.2 创建临时表
Oracle临时表，有两种类型：
会话级的临时表
事务级的临时表。
2.2.1. 会话级的临时表
因为这这个临时表中的数据和你的当前会话有关系，当你当前SESSION不退出的情况下，临时表中的数据就还存在，而当你退出当前SESSION的时候，临时表中的数据就全部没有了，当然这个时候你如果以另外一个SESSION登陆的时候是看不到另外一个SESSION中插入到临时表中的数据的。即两个不同的SESSION所插入的数据是互不相干的。
当某一个SESSION退出之后临时表中的数据就被截断(truncate table，即数据清空)了。
注：这里要说明的是，ORACLE Truncate 掉的数据仅仅是分配给不同 Session 或 Transaction的 Temp Segment 上的数据，而不是将整张表数据 TRUNCATE 掉。当Commit的时候则数据还在，当Rollback的时候则数据也是一样被回滚.
会话级的临时表创建方法：
SQL>CREATE GLOBAL TEMPORARY TABLE TABLE_NAME (<column specification>)
ON COMMIT PRESERVE ROWS;
或者
SQL>CREATE GLOBAL TEMPORARY TABLE TABLE_NAME ON COMMIT PRESERVE ROWS AS SELECT * FROM TABLE_NAME;
示例：
SQL> CREATE GLOBAL TEMPORARY TABLE TT(ID NUMBER(2)) ON COMMIT PRESERVE ROWS ;
表已创建。
SQL> SELECT * FROM TT;
未选定行
SQL> INSERT INTO TT VALUES(1);
已创建 1 行。
SQL> COMMIT;
提交完成。
SQL> SELECT * FROM TT;
ID
----------
1
SQL> INSERT INTO TT VALUES(2);
已创建 1 行。
SQL> SELECT * FROM TT;
ID
----------
1
2
SQL> ROLLBACK;
回退已完成。
SQL> SELECT * FROM TT;
ID
----------
1
2.2.2 事务特有的临时表(默认类型)
该临时表与事务相关，当进行事务提交或者事务回滚的时候，临时表中的数据将自行被截断，其他的内容和会话级的临时表的一致（包括退出SESSION的时候，事务级的临时表也会被自动截断）。一旦COMMIT后，数据就被自动 TRUNCATE 掉了.
事务级临时表的创建方法：
SQL>CREATE GLOBAL TEMPORARY TABLE TABLE_NAME (<column specification>)
ON COMMIT DELETE ROWS;
或者
SQL>CREATE GLOBAL TEMPORARY TABLE TABLE_NAME ON COMMIT DELETE ROWS AS SELECT * FROM TABLE_NAME;
CREATE GLOBAL TEMPORARY TABLE TABLE_NAME; 在不指明类型的情况下，默认为事务临时表。
SQL> CREATE GLOBAL TEMPORARY TABLE TT2(ID NUMBER(2)) ON COMMIT DELETE ROWS ;
表已创建。
SQL> SELECT * FROM TT2;
未选定行
SQL> INSERT INTO TT2 VALUES(1);
已创建 1 行。
SQL> SELECT * FROM TT2;
ID
----------
1
SQL> COMMIT;
提交完成。
SQL> SELECT * FROM TT2;
未选定行
SQL>
-------------------------------------------------------------------------------------------------------
Blog： http://blog.csdn.net/tianlesoftware
Email: dvd.dba@gmail.com
DBA1 群：62697716(满); DBA2 群：62697977(满) DBA3 群：62697850(满)
DBA 超级群：63306533(满); DBA4 群： 83829929 DBA5群： 142216823
DBA6 群：158654907 聊天 群：40132017 聊天2群：69087192
--加群需要在备注说明Oracle表空间和数据文件的关系，否则拒绝申请
