Oracle的临时表

110次阅读

共计 4541 个字符，预计需要花费 12 分钟才能阅读完成。

临时表概念
临时表用于保存事务或会话期间的中间结果集。临时表中保存的数据只对当前会话可见，所有会话都看不到其他会话的数据；即使当前会话已经提交 (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 说明有会话正在使用临时表，无法删除

参考：
Oracle Database 9i/10g/11g 编程艺术：深入数据库体系结构（第 2 版）PDF 下载 http://www.linuxidc.com/Linux/2016-02/128078.htm

本文永久更新链接地址：http://www.linuxidc.com/Linux/2017-01/139063.htm

正文完

星哥说事-微信公众号