Oracle与SQL Server临时表

写于2006年

在写非常复杂的业务逻辑时，不免用到临时表。临时表生成很少的日志，每种数据库又都会对其进行特定的处理，使它很适合保存事务或会话期间的中间结果集。
Oracle临时表保存的数据只对当前会话可见，所有会话都看不到其他会话的数据。即使当前会话已经提交了数据，别的会话也看不到。对于临时表，不存在多用户并发问题，因为一个会话不会因为使用一个临时表也阻塞另一个会话。Oracle的临时表是从当前登录用户的临时表空间分配存储空间，而在创建时不涉及存储空间的分配。Oracle中的临时表是全局临时表，是和其它表一样应该提前建好的，而不是在存储过程中创建、删除的。Oracle的临时表分为两种：基于会话的临时表和基于事务的临时表。基于会话的临时表，其中存储的数据可以跨越事务，但是断开连接后数据就没有了。而基于事务的临时表，提交之后数据就没有了。在临时表的自动清除过程中不存在开销。

以下是我在SQL*Plus中的试验：
在会话甲中
创建一个用于测试的表 t

    SQL> create table t          2  (x int         3  );      表已创建。

创建一个基于会话的临时表 tmp_sess

     SQL> create global temporary table tmp_sess         2   (x int         3   ) on commit preserve rows;       表已创建。

创建一个基于事务的临时表 tmp_tran

    SQL> create global temporary table tmp_tran         2   (x int          3    ) on commit delete rows;      表已创建。

然后向表t中插入三条数据

     SQL> insert into t values(1);       已创建 1 行。       SQL> insert into t values(2);       已创建 1 行。       SQL> insert into t values(3);       已创建 1 行。       SQL> commit;       提交完成。

再创建一个存储过程 tmp_table，先使用基于事务的临时表

     SQL> create or replace procedure tmp_table         2   is         3   begin         4        insert into tmp_tran         5        select sum(x) from t;         6        commit;         7   end;         8  /       过程已创建。       SQL> call tmp_table();       调用完成。       SQL> select * from tmp_tran;       未选定行

说明提交时基于事务的临时表中的数据已经被清除了。
然后打开另一会话乙：

     SQL> select * from tmp_tran;         未选定行

在会话甲中
将存储过程tmp_table中的临时表改为基于会话的临时表tmp_sess

      SQL> create or replace procedure tmp_table         2  is         3   begin         4    insert into tmp_sess         5    select sum(x) from t;         6    commit;         7   end;         8  /       过程已创建。       SQL> call tmp_table();       调用完成。       SQL> select * from tmp_sess;                X       ----------                6       SQL> call tmp_table();       调用完成。       SQL> select * from tmp_sess;                X       ----------                6                6

第一次调用存储过程tmp_table，然后查询得到一条记录；第二次调用，然后再查询得到两条记录。说明基于会话的临时表是跨越事务的。
在会话乙中

      SQL> select * from tmp_sess;       未选定行

说明基于会话的临时表里的数据是不能被另一个会话看到的。
在会话甲中

    SQL> disconnect;       从 Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production       With the Partitioning, OLAP and Data Mining options 断开       SQL> connect cuixz/cuixz@cuixz;       已连接。       SQL> select * from tmp_sess;       未选定行

说明基于会话的临时表断开时数据已经被Oracle自动清除了

SQL Server的临时表是存储在tempdb中，应该在存储过程中创建或者删掉的。它也分为两种：本地临时表和全局临时表。本地临时表以#开头，仅对当前连接有效，当与SQL Server连接断开时此表即被删除，如果是在存储过程中创建的，则存储过程执行完此表即被删除。当不同的用户创建本地临时表名相同时，SQL Server会自己在每个用户创建的临时表透明的加一下数据后辍（加此后辍是透明的，不影响各个用户程序对此临时表的使用）。因此本地临时表也不会发生并发问题。全局临时表以##开头，对所有会话都可见，只有所有引用该表的会话都断开连接时，才将此表删除。如果是在存储过程中创建的，则调整用此存储过程的会话断开后，此全局临时表即被删除。全局临时表中的数据会被其它会话看到，因此和普通表一样，存在多用户并发问题。
以下是我在SQL Server查询分析器里试验的结果
在查询分析器甲中
创建测试用的表t

     create table t       (x int       );       命令已成功完成。

向表t中添加三条数据

      insert into t values(1);       insert into t values(2);       insert into t values(3);       （所影响的行数为 1 行）       （所影响的行数为 1 行）       （所影响的行数为 1 行）

再创建一个存储过程 tmp_table，使用本地临时表

    create procedure tmp_table       as              create table #tmp_local              (x int)              insert into #tmp_local              select sum(x) from t           命令已成功完成。       exec tmp_table       （所影响的行数为 3 行）       select * from #tmp_local       服务器: 消息 208，级别 16，状态 1，行 1       对象名 ‘#tmp_local‘ 无效。

说明执行完存储过程后本地临时表就被SQL Server自动清除了
在另一个查询分析器乙中

     select * from #tmp_local       服务器: 消息 208，级别 16，状态 1，行 1       对象名 ‘#tmp_local‘ 无效。

在查询分析器甲中

    drop procedure tmp_table       命令已成功完成。

将存储过程tmp_table中的临时表改为全局临时表##tmp_global

     create procedure tmp_table       as              create table ##tmp_global              (x int)              insert into ##tmp_global              select sum(x) from t 命令已成功完成。       exec tmp_table       （所影响的行数为 1 行）       select * from ##tmp_global       6

在另一个查询分析器乙中

      select * from ##tmp_global       6

在查询分析器甲中

     exec tmp_table       服务器: 消息 2714，级别 16，状态 6，过程 tmp_table，行 3       数据库中已存在名为 ‘##tmp_global‘ 的对象。

在查询分析器乙中

      exec tmp_table       服务器: 消息 2714，级别 16，状态 6，过程 tmp_table，行 3       数据库中已存在名为 ‘##tmp_global‘ 的对象。

在不同的会话中全局临时表表现的都一样。当把查询分析器甲关闭后，在查询分析器乙中执行：

    select * from ##tmp_global       服务器: 消息 208，级别 16，状态 1，行 1       对象名 ‘##tmp_global‘ 无效。       exec tmp_table       （所影响的行数为 1 行）       select * from ##tmp_global       6

说明当在存储过程中创建的全局临时表，在被调用的会话连接被断开后，其创建的全局临时表即被SQL Server自动删除

   Oracle与SQL Server的临时表都可以加索引、检查约束等。但是都不支持以下特性：   1、不能有引用完整性约束。临时表不能作为外键的目标，也不能在临时表中定义外键；   2、不能分区；  在Oracle临时表可以加触发器，而SQL Server不可以；另外在Oracle临时表中还不可以有以下特性：   1、不能是索引组织表；   2、不能在任何类型的聚簇中；   3、不能通过ANALYZE表命令生成统计信息;   关于Oracle与SQL Server临时表的几点考虑：   1、DDL操作无论对于Oracle还是SQL Server都是很大的开销；   2、写存储过程时大可以利用每种临时表的优点，避免使用缺点及重复做系统已经做了的工作；   3、慎用临时表和其它大数据量表进行连接查询和修改；   4、对于有大量数据的临时表，可以对此创建索引；   5、在SQL Server中对于数据量比较少的，用表变量可以有更好的速度；   6、对于SQL Server中的全局临时表，创建时要进行相应的策略，避免表名重复；   7、尽量避免在Oracle临时表中作update操作，那样开销特别大；   8、在Oracle中，不要把临时表作为一个分解大查询的办法，即拿到一个大查询，把它分解为几个较小的结果集，然后把这些结果集并在一起。这样速度会更慢。