Oracle 临时表

 

Oracle 临时表

临时表（Temporary table）用于保存事务或会话期间的中间结果集。临时表中保存的数据只对当前会话可见，所有会话都看不到其他会话的数据；临时表比常规表生成的redo少得多。不过，由于临时表必须为其中包含 的数据生成undo信息， 所以也会生成一定的redo。 UPDATE和DELETE会生成最多的undo； INSERT和SELECT生成的undo最少。

对于临时表，运行时当一个会话第一次在临时表中放入数据时，才会为该会话创建一个临时段。由于每个会话会得到其自己的临时段（而不是一个现有段的一个区段），每个用户可能在不同的表空间为其临时表分配空间。

Oracle的 临时表与其他关系数据库中的临时表类似，这样区别只是：Oracle的临时表是“静态”定义的。每个数据库只创建一次临时表，而不是为数据库中的每个存储过程都创建一次。在Oracle中，临时表一定存在，它们作为对象放在数据字典中，但是在会话向临时表中放入数据之前，临时表看上去总是空。由于临时表是 静态定义的，所以你能创建引用临时表的视图，还可以创建存储过程使用静态SQL来引用临时表，等等。临时表可以是基于会话的（临时表中的数据可以跨提交存 在，即提交之前仍然存在，但是断开连接后再连接后再连接时数据就没有了），也可以是基于事务的（提交之后数据就消失）。下面这个例子显示了这两种不同的临 时表。我使用SCOTT.EMP表作为一个模板：

ops$tkyte@ORA10G> create global temporary table temp_table_session

2      on commit preserve rows

3      as

4      select * from scott.emp where 1=0

5      /

Table created.

ON COMMIT PRESERVE ROWS 子句使得这是一个基于会话的临时表。在我的会话断开连接之前，或者我通过一个DELETE或TRUNCATE物理地删除行之前，这些行会一直存在于这个临时表中。

 

ops$tkyte@ORA10G> create global temporary table temp_table_transaction

2      on commit delete rows

3      as

4      select * from scott.emp where 1=0

5      /

Table created.

ON COMMIT DELETE ROWS 子句使得这是一个基于事务的临时表。我的会话提交时，临时表中的行就不见了。只需把分配给这个表的临时区段交回，这些行就会消失，在临时表的自动清除过程中不存在开销。

 

如果你曾在SQL Server和/或 Sybase中用过临时表，现在所要考虑的主要问题是：不应该执行SELECT X, Y, Z INTO #TEMP FROM SOME_TABLE来动态创建和填充一个临时表，而应该：

l         将所有全局临时表只创建一次，作为应用安装的一部分，就像是创建永久表一样。

l         在你的过程中，只需执行INSERT INTO TEMP(X, Y, Z) SELECT X, Y, Z FROM SOME_TABLE。

归根结底，这里的目标是：不要在运行时在你的存储过程中创建表。这不是Oracle 中使用临时表的正确做法。DDL是一种代价昂贵的操作：你要全力避免在运行时执行这种操作。一个应用的临时表应该在应用安装期间创建，绝对不要在运行时创建。

临时表可以有永久表的许多属性。它们可以有触发器、检查约束、索引等。但永久表的某些特性在临时表中并不支持，这包括：

l         不能有引用完整性约束。临时表不能作为外键的目标，也不能在临时表中定义外键。

l         不能有 NESTED TABLE类型的列。 在 Oracle 9i及以前版本中， VARRAY类型的列也不允许；不过Oracle 10g中去掉了这个限制。

l         不能是 IOT。

l         不能在任何类型的聚簇中。

l         不能分区。

l         不能通过ANALYZE表命令生成统计信息。

 

在所有数据库中，临时表的缺点之一是优化器不能正常地得到临时表的真实统计。（但是有人为的方法改进，这里的具体的方法参考书本的内容，在此略！）

 

临时表小结

如果应用中需要临时存储一个行集由其他表处理（可能对应一个会话，也可能对应一个事务），临时表就很有用。不要把临时表作为一个分解大查询的方法，即拿到一 个大查询，把它“分解”为几个较小的结果集，然后再把这些结果集合并在一起（这看来是其他数据库中最常见的临时表用法）。实际上，你会发现，在几乎所有的 情况下。Oracle中如果将一个查询分解为较小的临时表查询，与原来的一个查询相比，只会执行得更慢。我就经常看到人们这样做，如果有可能把对临时表的 一系列INSERT重写为一个大查询（SELECT），所得到的单个查询会比原来的多步过程快得多。

临时表会生成少量的redo，但是确实还是会生成redo，而且没有办法避免。这些redo是为回滚数据生成的，而且在最典型的情况下，可以忽略不计。如果 只是对临时表执行INSERT和SELETE，生成的redo量几乎注意不到。只有对临时表执行大量DELETE和UPDATE 时，才会看到生成大量的redo。

如果精心设计，可以在临时表上生成CBO 使用的统计信息；不过，可以使用DBMS_STATS包对临时表上的统计给出更好的猜测，或者由优化器使用动态采样在硬解析时动态收集。