Oracle两种临时表的创建与使用详解
什么时候使用临时表?用临时表和用中间表有啥区别呢?
我觉得是在需要的时候应用,下面是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,保存中间数据,使用完后删除)那个更适合用来存储中间数据,我个人更倾向于使用临时表,而不建议使用中间表
ORACLE数据库除了可以保存永久表外,还可以建立临时表temporary tables。这些临时表用来保存一个会话SESSION的数据,或者保存在一个事务中需要的数据。当会话退出或者用户提交commit和回滚rollback事务的时候,临时表的数据自动清空,但是临时表的结构以及元数据还存储在用户的数据字典中。
分类:1.会话级临时表
会话级临时表是指临时表中的数据只在会话生命周期之中存在,当用户退出会话结束的时候,Oracle自动清除临时表中数据。
格式:
1
2
3
4
5
6
7
|
Create
Global Temporary Table Table_Name
(
Col1
Type1,
Col2
Type2
...
)
On
Commit Preserve Rows;
|
2.事务级临时表
事务级临时表是指临时表中的数据只在事务生命周期中存在。
1
2
3
4
5
6
7
|
Create
Global Temporary Table Table_Name
(
Col1
Type1,
Col2
Type2
...
)
On
Commit Delete Rows;
|
当一个事务结束(commit or rollback),Oracle自动清除临时表中数据。
下面在Oracle 10g中演示了临时表的创建与使用:
1.创建事务级临时表,插入一条数据,并查询:
1
2
3
|
create global
temporary
table
transaction_temp_tb (col1 varchar (20)) on commit
delete
rows ;
insert into
transaction_temp_tb values ( 'test' );
select *
from transaction_temp_tb;
|
2.执行commit或者rollback操作,临时表内数据就被清空,但表结构依然存在:
3.创建一个会话级临时表,插入一条数据,并查询:
1
2
3
|
create global
temporary
table
session_temp_tb (col1 varchar (20)) on commit
preserve rows ;
insert into
session_temp_tb values ( 'test' );
select *
from session_temp_tb;
|
4..执行commit或者rollback操作,表内数据依然存在,新建一个命令窗口(相当于开启了一个新的会话),表内的数据就查询不到了:
5.如果创建会话临时表的会话没有结束,则无法删除此临时表,因为临时表,还在使用之中,但是结束会话(关闭创建会话级临时表的命令窗口)后就可以删除了: