Oracle的临时表

lshboo

　　临时表概念
　　临时表用于保存事务或会话期间的中间结果集。临时表中保存的数据只对当前会话可见，所有会话都看不到其他会话的数据；即使当前会话已经提交(commit)了数据，别的会话也看不到它的数据。对于临时表，不存在多用户并发问题，因为一个会话不会因为使用一个临时表而阻塞另一个会话，即使我们“锁住”临时表，也不妨碍其他会话使用它们自己的临时表。
　　临时表默认存储在用户的默认临时空间中，用户也可以使用TABLESPACE命令指定临时表所在的临时表空间。
　　临时表可以创建临时的索引、视图、触发器。临时表的数据不会备份，恢复，对其的修改也不会有任何日志信息。尽管对临时表的DML操作速度比较快，但同样也是要产生Redo Log ，只是同样的DML语句，比对PERMANENT表的DML 产生的Redo Log少。
　　基本语法
CREATE GLOBAL TEMPORARY TABLE [ schema. ] table　　
[ (relational_properties) ]
　　
[ ON COMMIT { DELETE | PRESERVE } ROWS ]
　　
[ physical_properties ]
　　
[ table_properties ]
　　详细的语法说明参见官方文档：http://docs.oracle.com/cd/E11882_01/server.112/e41084/statements_7002.htm#i2149006
　　临时表分类
　　ORACLE临时表有两种类型：会话级的临时表和事务级的临时表。
　　1.ON COMMIT DELETE ROWS
　　它是临时表的默认参数，表示临时表中的数据仅在事物过程（Transaction）中有效，当事物提交（COMMIT）后，临时表的暂时段将被自动截断（TRUNCATE）。
　　2.ON COMMIT PRESERVE ROWS
　　它表示临时表的内容可以在当前会话跨事物而存在，不过，当该会话结束时，临时表的临时段将随着会话的结束而被丢弃，临时表中的数据自然也就随之丢弃。
　　临时表使用演示
　　1.基于事务的临时表
--创建临时表　　
zx@ORCL>create global temporary table temp_transaction
　　
  2  (id number,
　　
  3  name varchar2(10) )
　　
  4  on commit delete rows;
　　

　　
Table created.
　　
--session1插入测试数据
　　
zx@ORCL>insert into temp_transaction values (1,'zx');
　　

　　
1 row created.
　　

　　
zx@ORCL>select * from temp_transaction;
　　

　　
        ID NAME
　　
---------- ------------------------------
　　
         1 zx
　　
--同时session2查询临时表并插入测试数据
　　
SQL> select * from temp_transaction;
　　

　　
未选定行
　　

　　
SQL> insert into temp_transaction values (2,'lx');
　　

　　
已创建 1 行。
　　

　　
SQL> select * from temp_transaction;
　　

　　ID NAME
　　
---------- --------------------
　　2 lx
　　
--session1 commit操作
　　
zx@ORCL>commit;
　　

　　
Commit complete.
　　

　　
zx@ORCL>select * from temp_transaction;
　　

　　
no rows selected
　　
--session2 查询数据，commit操作
　　
SQL> select * from temp_transaction;
　　

　　ID NAME
　　
---------- --------------------
　　2 lx
　　

　　
SQL> commit;
　　

　　
提交完成。
　　

　　
SQL> select * from temp_transaction;
　　

　　
未选定行
　　由上述操作过程可以看到不同的会话对于临时表的操作是完全隔离的，commit后，基于事务的临时表数据就会“消失”。
　　2.基于会话的临时表
zx@ORCL>create global temporary table temp_session　　
  2  (id number,
　　
  3  name varchar2(10) )
　　
  4  on commit preserve rows;
　　

　　
Table created.
　　

　　
zx@ORCL>insert into temp_session values (1,'zx');
　　

　　
1 row created.
　　

　　
zx@ORCL>select * from temp_session;
　　

　　
        ID NAME
　　
---------- ------------------------------
　　
         1 zx
　　

　　
zx@ORCL>commit;
　　

　　
Commit complete.
　　

　　
zx@ORCL>select * from temp_session;
　　

　　
        ID NAME
　　
---------- ------------------------------
　　
         1 zx
　　

　　
zx@ORCL>truncate table temp_session;
　　

　　
Table truncated.
　　

　　
zx@ORCL>select * from temp_session;
　　

　　
no rows selected
　　
--truncate表后数据“消失”
　　
zx@ORCL>insert into temp_session values (1,'zx');
　　

　　
1 row created.
　　

　　
zx@ORCL>commit;
　　

　　
Commit complete.
　　

　　
zx@ORCL>select * from temp_session;
　　

　　
        ID NAME
　　
---------- ------------------------------
　　
         1 zx
　　
--commit后数据还在
　　
--退出会话重新进入
　　
zx@ORCL>exit
　　
Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
　　
With the Partitioning, OLAP, Data Mining and Real Application Testing options
　　
[oracle@rhel6 ~]$ zx
　　

　　
SQL*Plus: Release 11.2.0.4.0 Production on Sun Jan 1 22:46:13 2017
　　

　　
Copyright (c) 1982, 2013, Oracle.  All rights reserved.
　　

　　

　　
Connected to:
　　
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
　　
With the Partitioning, OLAP, Data Mining and Real Application Testing options
　　

　　
zx@ORCL>select * from temp_session;
　　

　　
no rows selected
　　通过上述操作可知，基于会话的临时表，commit后数据也不会“消失”，只有当truncate或退出会话后数据才会“消失”。
　　查看临时表的存在
zx@ORCL>col table_name for a30　　
zx@ORCL>col tablespace_name for a30
　　
zx@ORCL>col DURATION for a30
　　
zx@ORCL>select table_name,TABLESPACE_NAME,LOGGING,TEMPORARY,DURATION,MONITORING from user_tables;
　　

　　
TABLE_NAME                     TABLESPACE_NAME                LOGGING   TEM DURATION                       MONITORIN
　　
------------------------------ ------------------------------ --------- --- ------------------------------ ---------
　　
T                              TT                             YES       N                                  YES
　　
SESS_STATS                     TT                             YES       N                                  YES
　　
TEMP_SESSION                                                  NO        Y   SYS$SESSION                    NO
　　
TEMP_TRANSACTION                                              NO        Y   SYS$TRANSACTION                NO
　　临时表用途
　　什么时候使用临时表？用临时表和用中间表有啥区别呢？
　　我觉得是在需要的时候应用，下面是David Dai关于临时表的一个应用说明，我觉得非常形象的说明了临时表的应用场景：对于一个电子商务类网站，不同消费者在网站上购物，就是一个独立的 SESSION，选购商品放进购物车中，最后将购物车中的商品进行结算。也就是说，必须在整个SESSION期间保存购物车中的信息。同时，还存在有些消费者，往往最终结账时放弃购买商品。如果，直接将消费者选购信息存放在最终表（PERMANENT）中，必然对最终表造成非常大的压力。因此，对于这种案例，就可以采用创建临时表(ON COMMIT PRESERVE ROWS)的方法来解决。数据只在 SESSION 期间有效，对于结算成功的有效数据，转移到最终表中后，ORACLE自动TRUNCATE 临时数据；对于放弃结算的数据，ORACLE 同样自动进行 TRUNCATE ，而无须编码控制，并且最终表只处理有效订单，减轻了频繁的DML操作的压力。
　　1：当处理某一批临时数据，需要多次DML操作时（插入、更新等），建议使用临时表。
　　2：当某些表在查询里面，需要多次用来做连接时。（为了获取目标数据需要关联A、B、C， 同时为了获取另外一个目标数据，需要关联D、B、C....）
　　关于临时表和中间表（NOLOGGING，保存中间数据，使用完后删除）那个更适合用来存储中间数据，我个人更倾向于使用临时表，而不建议使用中间表。
　　临时表的限制
　　临时表不能是分区表、聚簇表或索引组织表；
　　临时表不可以有外键；
　　临时表不能包含嵌套表；
　　临时表不支持并行UPDATE、DELETE和MERGE；
　　临时表不支持分布式事务；
　　删除临时表
zx@ORCL>drop table temp_transaction;　　

　　
Table dropped.
　　
--如果删除时报错ORA-14452: attempt to create, alter or drop an index on temporary table already in use说明有会话正在使用临时表，无法删除
　　参考：
　　http://www.cnblogs.com/kerrycode/p/3285936.html
　　《9I10G11G编程艺术  深入数据库体系结构》