关于临时表的一点点学习

这两天在论坛中看到老狐狸还有sailor关于临时表和动态语句的贴子，看后我觉得自身在其应用中也经常犯有类似的错误，而且与sailor的想法最初也是一样的，究其根本原因，是对于临时表等没有理解，不知道应该如何去使用它，对于数据库的使用没有一定的规范(试想大家都以自己的想法去做，那这个数据库岂不是要遍体鳞伤，以后出了问题那么....不敢想像),利用两个小时把oracle的临时表又看了一遍,希望对于自己的学习经有和我有同样感受的人有一丁点帮助吧

Oracle临时表

临时表(temporary table)用于保存事务或会话期间的中间结果集.

在Oracle中临时表分为两种，一种是基于事务的，一种是基于会话的。

1.       它保存的数据只在当前会话可见，其它的会话均不可见，即便其它会话已经提交了数据.

2.       不存在多用户并发的问题，一个会话不会因为使用一个临时表而阻塞其它的会话

3.       临时表比常规表产生的redo少的多，我觉得这是我们应用中应该考虑的，因为Redo产生的多对系统各方面影响是蛮大的。

4.       临时表从当前登录用户的临时表空间分配存储空间。运行时当一个会话第一次在临时表中插入数据时才会为该会话创建一个临时段。（每个会话都有自己的临时段）

5.       Oracle临时表与其它关系数据库临时表的区别：Oracle中的临时表是静态定义的，每个数据库只创建一次临时表，而不是为数据库中每个存储过程都创建一次。

创建基于会话的临时表：

SQL> create table t_user(userid int,username varchar2(20));

表已创建。

SQL> insert into t_user values(1,'zxf');

已创建 1 行。

SQL> insert into t_user values(2,'feng');

已创建 1 行。

SQL> insert into t_user values(3,'hhh');

已创建 1 行。

SQL> insert into t_user values(4,'mmmm');

已创建 1 行。

SQL> commit;

提交完成。

创建一个基于会话的临时表: on commit preserve rows

SQL> create global temporary table tmp_session

  2  on commit preserve rows

  3  as select * from t_user where 1=0;

表已创建。

创建一个基于事务的临时表:on commit delete rows;

SQL> create global temporary table tmp_transaction

  2  on commit delete rows

  3  as select * from t_user where 1=0;

表已创建。

 

这时向临时表中插入数据（但却出错了，错误信息如下）

SQL> insert into tmp_session

  2  select * from fttest.t_user;

insert into tmp_session

            *

第 1 行出现错误:

ORA-25153: 临时表空间为空

根据错误提示，查了一下

1．SQL> select name from v$tempfile;

未选定行

发现没有文件

后来突然想到，由于最近一直学习备份与恢复（折腾），临时表空间需要手动添加文件（网上也有相关的贴子）

http://www.itpub.net/viewthread.php?tid=703279&extra=&highlight=ORA-25153%3A%20%C1%D9%CA%B1%B1%ED%BF%D5%BC%E4%CE%AA%BF%D5&page=1

2.处理过程

SQL> alter tablespace temp add tempfile

'E:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\temp01.dbf' size 100M;

表空间已更改。

SQL> select name from v$tempfile;

NAME

-------------------------------------------------

E:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\TEMP01.DBF

3.然再试正常喽

SQL>  insert into tmp_session

  2   select * from fttest.t_user;

已创建4行。

SQL> commit;

提交完成。

SQL> select * from tmp_session;

    USERID USERNAME

---------- --------------------

         1 zxf

         2 feng

         3 hhh

         4 mmmm

SQL> insert into tmp_transaction

  2  select * from fttest.t_user;

已创建4行。

SQL> commit;

提交完成。

SQL> select * from tmp_transaction;

未选定行

 

从上面的例子可以看出，基于事务的临时表tmp_transaction 当提交后，数据就不存在，而基于会话的表tmp_session中的数据仍然存在。

 

但打开另一个对话：

 

在TOM的书中特意提到：如果曾用到SQLSERVER或Sybase中临时表，现在要考虑的主要问题就是：不应该执 行select * into #TEMP from table1 来动态创建和填充一个临时表，而是应该：

1.       将所有的全局临时表只创建一次，就像永久表一样

2.       只需执行 insert into tmp select * from some_table

 

(不要在执行过程中创建临时表，这是Oracle最值得注意的)

下面做了个小例子来验证当两个事务同事操作一个表时，是否会锁表：

当在同一个会话中如果开启事务后不提交，再次增加，则会出现提示先提交事务

但在另一个程序中，可以增加，没有问题。而且两个界面中的数据是互相独立的

 

临时表的属性：可以有触发器，检查约束，索引，但以下则不可以

1.       不能有引用完整性约束（不能做为外键的目标，也不能定义外键）

我的理解是，因为临时表中的数据当会话结束或事务提交了就不存在了，所以不行。

2.       不能有Nested table类型的列（10g以前varray也不允许）

3.       不能是IOT表

4.       不能在任何类型的聚簇中

5.       不能分区

6.       不能通过analyze表命令统计信息

 

这里还有一个由于习惯的问题而养成的一个错误：

在以往用sqlserver时，由于一些大的查询过程时，为了提高运行效率，有时总是生成一些临时表，而把一个大的查询分成几个子查询来完成，但在oracle中则不需要这样，因为oracle的优化器会自动进行优化，所以在一个查询中引用多个表是可以的，在oracle中不需要临时表.

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/67668/viewspace-364775/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/67668/viewspace-364775/