Oracle临时表学习小结

最近因为工作的原因，需要使用到Oracle的临时表。发现Oracle的临时表和Sql-Server的临时表之间还是有很多的不同之处。为了能够彻底弄清Oracle临时表的性能，也为了更好的在项目中决策是否使用临时表，我花了一些时间翻阅了一下资料。下面对这段时间临时表的学习做一个总结。

我们知道在数据库中除了可以创建永久表外，还可以建立临时表(temporary table)。那么为什么需要临时表呢？它给我们带来了什么优点？和永久表相比临时表有以下几个优点：

1.临时表在操作上比永久表要更快。因为临时表不需要往编目表中插入条目，临时表的使用也不需要访问编目表，因此也没有编目表的争用。

2.仅有创建临时表的app才可以存取临时表，在处理临时表时没有锁。因为临时表的数据只对当前Session有效，每个Session都有自己的临时数据，并且不能访问其他Session的临时表中的数据。因此，临时表不需要DML锁。

3.如果指定Not Logged选项，处理临时表时不记日志。所以如果有仅在数据库的一个会话中使用的大量的临时数据，这些数据存入临时表能大大提高性能。

4.临时表的数据不会永久存在(同永久表最大的区别)，当一个会话结束或者事务结束的时候，数据库自动清除数据。另外Oracle临时表建立后，不会删除(和Sql-server的不同之处)。

临时表详细介绍
Oracle的临时表有两种类型：

1.会话级临时表：(ON COMMIT PRESERVE ROWS)是指数据只在当前会话中有效的临时表。当关闭当前会话或者进行新的连接之后，数据表中的内容就会被清除。

view plain
CREATE GLOBAL TEMPOPARY TABLE TEMP1   
(ID NUMBER(12) Primary key,name varchar2(10))   
ON COMMIT PRESERVE ROWS;  
我们通过Insert into语句往TEMP1表中插入一条记录，Commit后，我们通过Select语句进行查询时，可以查到该条记录。

当关闭当前会话，重新连接数据库后，再利用Select语句查询时，我们发现TEMP1表已经没有此条记录啦。

2.事务级临时表：(ON COMMIT DELETE ROWS)是指数据只有在当前事务内有效。

view plain
Create global temporary table Temp2 (ID NUMBER(12) Primary key,name varchar2(10)) <pre name="code" class="sql" style="margin-top: 4px; margin-right: 0px; margin-bottom: 4px; margin-left: 0px; background-color: rgb(240, 240, 240); ">ON COMMIT DELETE ROWS 
一般情况下，如果建表时没有特殊指明，默认都是建立事务型临时表，所以可以省略 ON COMMIT DELETE ROWS。
我们通过Insert Into语句往Temp2中插入一条记录，不Commit，直接Select，可以查到该条记录。Commit后，再Select查询发现临时表数据为空。

Oracle临时表和Sql-Server临时表的比较

1.Sql-Server中临时表是一种"内存表“，表存储在TempDB中。Oracle临时表的定义会保留在数据字典中，要执行Drop Table来删除。

2.Sql-Server中临时表不存在事务级临时表。

3.Sql-Server中的全局临时表(##)是指多个连接共享同一片内存，当没有指针引用该内存区域时，Sql-Server自动释放全局临时表。

4.Sql-Server本地临时表(#)与Oracle的会话级临时表类似，但Oracle在会话结束后不会删除表。

5.Oracle不是内存数据库，所以如果Oracle类似Sql-server频繁的对临时表进行建立和删除必定会影响性能，所以Oracle要用户自己Drop Table。

6.Oracle中如果要多个用户共享一个表（类似于Sql-Server中的全局临时表），则可以利用永久表，并且在表中添加一些可以唯一标识用户的列。利用触发器和视图，当用户退出的时候，根据该登录用户的唯一信息删除相应表中的数据。

作者“gavinloo的专栏”