Oracle与SQL Server临时表

在写非常复杂的业务逻辑时，不免用到临时表。临时表生成很少的日志，每种数据库又都会对其进行特定的处理，使它很适合保存事务或会话期间的中间结果集。
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> connectcuixz/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中，不要把临时表作为一个分解大查询的办法，即拿到一个大查询，把它分解为几个较小的结果集，然后把这些结果集并在一起。这样速度会更慢。