Oracle临时表用法的经验心得

Oracle临时表用法的经验心得

1. 案例

前段时间报表中心有一存储过程执行速度过慢，由于另外一个存储过程中用到了那个存储过程中的中间表，因此如果前面的一个过程没有执行完而执行后面的那个过程，后面的过程执行完之后还是没有数据。四月份那个过程执行大约需要两个小时的时间，本以为是过程里面的业务太复杂导致。可前段时间执行的时间超过了十小时。后来才发现是中间表中数据量越来越多导致，大约有五百多万数据，而且每天会增加两万左右的数据，慢的地方主要是在中间表中插入当天的数据之后需要更新某些字段，由于中间表中数据量过大，更新这些字段是速度才很慢（已经建了索引）。

解决方案：创建一个和中间表一摸一样的临时表，先把数据插入到临时表中，在临时表中更新需要更新的字段，更新完后再把临时表中所有的数据插入到中间表中，然后再使用中间表中的数据汇总出报表。 2.如何创建临时表

创建Oracle 临时表，可以有两种类型的临时表：会话级的临时表，事务级的临时表 。

2.1会话级临时表

因为这个临时表中的数据和你的当前会话有关系，当你当前SESSION 不退出的情况

下，临时表中的数据就还存在，而当你退出当前SESSION 的时候，临时表中的数据就全部没有了，当然这个时候你如果以另外一个SESSION 登陆的时候是看不到另外一个SESSION 中插入到临时表中的数据的。

即两个不同的SESSION 所插入的数据是互不相干的。当某一个SESSION 退出之后临时表中的数据就被截断（truncate table ，即数据清空）了。会话级的临时表创建方法：

Create Global Temporary Table Table_Name

(Col1 Type1,Col2 Type2...) On Commit Preserve Rows ；

举例：

create global temporary table Student

(Stu_id Number(5),

Class_id Number(5),

Stu_Name Varchar2(8),

Stu_Memo varchar2(200)) on Commit Preserve Rows ；

2.2 事务级临时表

事务级临时表是指该临时表与事务相关，当进行事务提交或者事务回滚的时候，Oracle

临时表中的数据将自行被截断，其他的内容和会话级的临时表的一致（包括退出SESSION 的时候，事务级的临时表也会被自动截断）。事务级临时表的创建方法：

Create Global Temporary Table Table_Name

(Col1 Type1,Col2 Type2...) On Commit Delete Rows ；

举例：

create global temporary table Classes

(Class_id Number(5),

Class_Name Varchar2(8),

Class_Memo varchar2(200)) on Commit delete Rows ;

2.3 两种类型临时表的区别

会话级临时表采用 on commit preserve rows ；而事务级则采用 on commit delete rows ；用法上，会话级别只有当会话结束临时表中的数据才会被截断，而且事务级临时表则不管是 commit 、 rollback 或者是会话结束，Oracle临时表中的数据都将被截断

2.4临时表的不足之处

1）不支持 lob 对象，这也许是设计者基于运行效率的考虑，但实际应用中确实需要此功能时就无法使用临时表了。

2 ）不支持主外键关系

3.示例

1. 会话级临时表

create global temporary table emp_temp_preserve

on commit preserve rows

as select * from emp where 1=2;

insert into emp_temp_preserve select * from emp;

commit;

select * from emp_temp_preserve;

在同一个会话中查询有数据，不在同一个会话中查询没数据

在不同会话中查询：

2. 事务级临时表

create global temporary table emp_temp_delete

on commit delete rows

as select * from emp where 1=2;

insert into emp_temp_delete select * from emp;

commit;

提交之后再同一个会话中查询，没有数据，说明提交之后临时表中的数据已清空，提交之前查询时是有数据的，

select * from emp_temp_delete;