Oracle 临时表

作者: Distance 出自:

Oracle Temporary Tables

1. 建立语法

A．ON COMMIT DELETE ROWS 定义了建立事务级临时表的方法

CREATE GLOBAL TEMPORARY TABLE TABLE_NAME

-----(COUMNS …)

-----AS SELECT … FROM TABLE…

ON COMMIT DELETE ROWS;

当前session发出commit/rollback命令，则该事务周期发生的所有数据自动被Oracle删除(Oracle truncate table)。但不影响任何其他session的数据。


B．ON COMMIT PRESERVE ROWS 定义了创建会话级临时表的方法

CREATE GLOBAL TEMPORARY TABLE TABLE_NAME

-----(COUMNS …)

-----AS SELECT … FROM TABLE…

ON COMMIT PRESERVE ROWS;

当前session结束(用户正常退出 / 用户不正常退出 / Oracle实例崩溃)，Oracle对这个会话的中发生的数据进行删除(Oracle truncate table)。但不影响任何其他session的数据。



2. 特点说明

A．临时表数据自动清空后，但是临时表的结构以及元数据还存储在用户的数据字典中。表的定义对所有的会话可见

B．临时表不需要DML锁

C．可以索引临时表和在临时表基础上建立视图

D．在临时表上的索引也是临时的，也是只对当前会话或者事务有效

E．临时表可以拥有触发器

F．可以用export和import工具导入导出临时表的定义，但是不能导出数据


3. 使用技巧

A．当某一个SQL语句关联的表在2张及以上，并且和一些小表关联。可以采用将大表进行分拆并且得到比较小的结果集合存放在临时表中

B．程序执行过程中可能需要存放一些临时的数据，可以将这类数据放在临时表里非常方便

C．存储过程中用到临时表:

1> 在建立临时表前，应先加上对表名的判断

Select count(*) into v_count from user_tables where table_name = ‘XXX’;

If v_count=0 then

Create global temporary table …

在存储过程结束处，应该记得删除表

execute immediate 'drop table t_temp';


2> 若在存储过程中建的临时表为on commit delete rows。且存储过程中无commit/rollback命令，则在当前会话执行存储过程后，在该存储过程中发生的数据仍然可以select到，因为没有发出commit/rollback命令。因此无论是以何种形式建立的临时表，或者PLSQL中没有建立任何临时表，但只要在PLSQL中有DML(insert/update/delete)的操作，则在PLSQL结束处应加上COMMIT;

否则对有临时表的情况造成表数据无法及时清除，对没有临时表的情况造成对DML操作的表的死锁

D．数据处理比较复杂的时候临时表快，反之视图快点

E．在仅仅查询数据的时候建议用游标: open cursor for 'sql clause'; 有时可使用临时表避免其他时候使用游标


4. 与SQL Server临时表的对比

A．SQL Server临时表的临时表与永久表相似，但临时表存储在tempdb中，当不再使用时会自动删除

B．SQL Server临时表的临时表有本地和全局两种类型的临时表，二者在名称、可见性和可用性上均不相同

本地临时表的名称以单个数字符号#打头，仅对当前的用户连接是可见的，当用户从SQL Server 2000实例断开连接时被删除。全局临时表的名称以数学符号##打头，创建后对任何用户都是可见的，当所有引用该表的用户从SQL Server断开连接时被删除

EG:

1> 如果创建名为employees的表，则任何人只要在数据库中有使用该表的安全权限就可以使用该表，除非它已删除。

2> 如果创建名为#employees的本地临时表，只有创建者能对该表执行操作且在断开连接时该表自动删除。

3> 如果创建名为##employees的全局临时表，数据表中的任何用户均可对该表执行操作。如果该表在创建者创建后没有其他用户使用，则当您断开连接时该表删除。如果该表在创建者创建后有其他用户使用，则SQL Server在所有用户断开连接后菜自动删除该表

C．小结

1> SQL Server临时表是一种”内存表”，表是存储在内存中的。Oracle临时表除非执行DROP TABLE，否则表定义会保留在数据字典中

2> SQL Server临时表不存在类似Oracle临时表事务级别上的功能

3> SQL Server本地临时表#与Oracle的会话级别临时表类似，但是在会话退出的时候，Oracle不会删除表

4> SQL Server的全局临时表##是指多个连接共享同一片内存。当没有指针引用该内存区域时，SQL SERVER自动释放全局临时表

5> 由于Oracle不是一种内存中的数据库。所以如果Oracle类似SQL Server频繁的对临时表进行建立和删除，必定会影响性能。所以Oracle会保留临时表的定义直到用户DROP TABLE

6> 在Oracle中，如果需要多个用户共享一个表(类似SQL Server的全局临时表##)，则可以利用永久表， 并且在表中添加一些可以唯一标识用户的列。利用触发器和视图。当用户退出的时候，根据该登陆用户的唯一信息删除相应的表中的数据。这种方法给Oracle带来了一定量的负载

7> SQL Server的全局临时表##的数据对于所有用户是可见的，没有针对指定用户可见的特性