临时表

临时表

 

一 临时表定义

临时表就是用来暂时保存临时数据（亦或叫中间数据）的一个数据库对象。

 

二 临时表特点

ORACLE临时表是会话或事务级别的，只对当前会话或事务可见。

 

三 临时表特点

1 用于保存事务或会话期间的中间结果集。

2 保存的数据只对当前会话可见，不存在并发问题。

3 必须为其中包含的数据生成undo，也会生成少量的redo。

4 临时表从当前登陆用户的临时表空间分配存储空间。

5只能存储在临时表空间，而非用户的表空间。

6每个会话只能查看和修改自己的数据。

7 永久表以下特性临时表不支持

：不能用参照完整性约束，也不能被参照完整性约束所引用；

：不能有VARRAY或者NESTED TABLE类型的列；

：不能是IOT；

：不能是索引或者散列聚簇；

：不能分区；

：通过ANALYZE命令不能产生统计信息，也即是说优化器在临时表上没有真正的统计功能。

 

四 临时表适用范围

1：当处理某一批临时数据，需要多次DML操作时（插入、更新等），建议使用临时表。

2：当某些表在查询里面，需要多次用来做连接时。（为了获取目标数据需要关联A、B、C， 同时为了获取另外一个目标数据，需要关联D、B、C....）

 

五 注意事项

1 ) 不支持 lob 对象，这也许是设计者基于运行效率的考虑，但实际应用中确实需要此功能时就无法使用临时表了。这点网上很多资料都这么说，我没有追查到底是那个版本不支持lob对象，至少在ORACLE 10g这个版本中，临时表是支持lob对象的.

2 ) 不支持主外键关系

3 ）临时表不能永久的保存数据。

4 ）临时表的数据不会备份，恢复，对其的修改也不会有任何日志信息

5 ）临时表不会有DML 锁

6 ）尽管对临时表的DML操作速度比较快，但同样也是要产生 Redo Log ，只是同样的DML语句，比对 PERMANENT 的DML 产生的Redo Log 少

7 ） 临时表可以创建临时的索引、视图、触发器。

8 ) 如果要DROP会话级别临时表，并且其中包含数据时，必须先截断其中的数据。否则会报错。

 

六 临时表分类

ORACLE临时表有两种类型：会话级的临时表和事务级的临时表。

 

1 ON COMMIT DELETE ROWS

它是临时表的默认参数，表示临时表中的数据仅在事物过程（Transaction）中有效，当事物提交（COMMIT）后，临时表的暂时段将被自动截断（TRUNCATE），但是临时表的结构 以及元数据还存储在用户的数据字典中。如果临时表完成它的使命后，最好删除临时表，否则数据库会残留很多临时表的表结构和元数据。

 

2 ON COMMIT PRESERVE ROWS

它表示临时表的内容可以跨事物而存在，不过，当该会话结束时，临时表的暂时段将随着会话的结束而被丢弃，临时表中的数据自然也就随之丢弃。但是临时表的结构以及元数据还存储在用户的数据字典中。如果临时表完成它的使命后，最好删除临时表，否则数据库会残留很多临时表的表结构和元数据。

 

七 创建临时表

1:会话级的临时表的数据和你当前会话有关系，当前SESSION不退出的情况下，临时表中的数据就还存在，临时表的数据只有当你退出当前SESSION的时候才被截断（TRUNCATE TABLE），如下所示：

会话级别的临时表创建：

SQL> create global temporary table tmp_t1(id number,name varchar2(32))

  2  on commit preserve rows;

 

Table created.

 

SQL> desc tmp_t1

 Name                                      Null?    Type

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

 ID                                                 NUMBER

 NAME                                               VARCHAR2(32)

 

SQL> insert into tmp_t1 values(1,'chen');

 

1 row created.

 

SQL> commit;

 

Commit complete.

 

SQL> select * from tmp_t1;

 

        ID NAME

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

         1 chen

另一个session

select * from tmp_t1;

空

 

退出session，表自动truncate

SQL> exit

 

[oracle@ogg1 ~]$ sqlplus

 

Enter user-name: /as sysdba

 

SQL> select * from tmp_t1;

 

no rows selected

 

 

2:事务级的临时表（默认），这种类型的临时表与事务有关，当进行事务提交或者事务回滚的时候，临时表的数据将自行截断，即当COMMIT或ROLLBACK时，数据就会被TRUNCATE掉，其它的特性和会话级的临时表一致。

 

事务级临时表的创建方法：

SQL> create global temporary table tmp_t2(id number,name varchar2(32))

  2  on commit delete rows;

 

Table created.

 

SQL> desc tmp_t2

 Name                                      Null?    Type

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

 ID                                                 NUMBER

 NAME                                               VARCHAR2(32)

 

SQL> insert into tmp_t2 values(1,'chen');

 

1 row created.

 

SQL> select * from tmp_t2;

 

        ID NAME

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

         1 chen

 

SQL> commit;

 

Commit complete.

 

SQL> select * from tmp_t2;

 

no rows selected

 

八 查看临时表与永久表区别

create table t3 (id number) tablespace users;

create table t2 as select level as id from dual connect by level<=3;

select owner,table_name,tablespace_name,logging,temporary,duration,monitoring

from dba_tables

where table_name in('T1','T2','T3','TMP_T1','TMP_T2') and owner='SYS';

     OWNER    TABLE_NAME  TABLESPACE_NAME         LOGGING          TEMPORARY    DURATION       MONITORING

1       SYS   TMP_T2                                   NO      Y       SYS$TRANSACTION NO

2       SYS   TMP_T1                                   NO      Y       SYS$SESSION    NO

3       SYS   T3                 USERS                  YES     N                YES

4       SYS   T2                 SYSTEM               YES     N                YES

5       SYS   T1                                                   N                YES

如上所示，临时表是存储在临时表空间里面的，但是上面脚本可以看出，临时表在数据字典中没有指定其表空间，临时表是NOLOGGING，DURATION为SYS$SESSION

临时表的DML操作速度比较快，但同样也是要产生 Redo Log ，只是同样的DML语句，比对 PERMANENT 的DML 产生的Redo Log 少，在应用中，往往会创建一个NOLOGGING的永久表（中间表）来保存中间数据，从而代替临时表。

 

 

 

 

 

来自：http://www.cnblogs.com/kerrycode/p/3285936.html

 

 

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

转载于:http://blog.itpub.net/29785807/viewspace-1733116/