oracle中的临时表

最新推荐文章于 2023-05-31 08:37:59 发布

cuiyun6070

最新推荐文章于 2023-05-31 08:37:59 发布

阅读量188

点赞数

oracle中的临时表

临时表通常用来保存一个事务或者会话期间的数据.
临时表中保存的数据是具有独立性的,只对各自会话可见,并且每个会话
都只能查询和修改属于此会话的数据,在对temporary table作dml操作时,
不需要申请锁资源,因此lock语句对于临时表来说是没有作用的.

在空间方面,在创建永久性表时通常是需要为表分配initial extent,但是对于
临时表是不需要的,临时表只是在使用的时候,根据数据来分配创建临时段.

对临时表的 DML 操作不会产生数据修改的重做日志,但是将产生被修改数据的撤销记录，

及撤销记录的重做日志.

我们来看一下临时表所产生的redo size情况.

SQL> select * from v$sesstat where sid=159 and statistic#=134;

SID STATISTIC# VALUE
---------- ---------- ----------
159 134 929956

SQL> create global temporary table temp_ses on
2 commit preserve rows
3 as
4 select * from dba_objects;

Table created.

SQL> select * from v$sesstat where sid=159 and statistic#=134;

SID STATISTIC# VALUE
---------- ---------- ----------
159 134 948420

再来看一下创建一个同样大小数据量的永久性表:
SQL> select * from v$sesstat where sid=142 and statistic#=134;

SID STATISTIC# VALUE
---------- ---------- ----------
142 134 1432

SQL> create table pert as select * from dba_objects;

Table created.

SQL> select * from v$sesstat where sid=142 and statistic#=134;

SID STATISTIC# VALUE
---------- ---------- ----------
142 134 5724360

SQL> select 5724360-1432 from dual;

5724360-1432
------------
5722928

SQL>

可以看到两者的差距已经不仅仅是一个数据级了.

临时表一共有两种:
会话级别和事务级别的.

先来看一个会话级别的.

SQL> create global temporary table tmp_ses on commit preserve rows
2 as select * from dba_objects;

Table created.

SQL>

on commit preserve rows表明这一个基于会话的临时表,在会话断开以后.
所有数据都将被抹去.

SQL> create global temporary table tmp_trans on commit delete rows
2 as select * from dba_objects;

Table created.

SQL>

on commit delete rows表示这是一个基于事务的临时表，在会话提交的时候，数据

就会被自动清除掉。

SQL> insert into tmp_trans select * from dba_objects;

49772 rows created.

SQL> select count(*) from tmp_trans;

COUNT(*)
----------
49772

SQL> commit;

Commit complete.

SQL> select count(*) from tmp_trans;

COUNT(*)
----------
0

这里可以看到在事务commit以后，临时表中的数据被全部清空。而这个清空的过程

是几乎不存在开销，oracle完成的仅仅是把临时段回收的一个动作。

临时段的分配

临时表使用临时段来分配数据，因此在创建临时表的时候，oracle并不会为其分配段，

而是在使用的时候才分配。我们可以使用v$sort_usage来观察某个临时表所占用的

空间大小。

SQL> select * from v$sort_usage;

no rows selected

SQL> insert into tmp_ses select * from dba_objects;

49772 rows created.

SQL> commit;

Commit complete.

SQL> select count(*) from tmp_ses;

COUNT(*)
----------
49772

SQL> select distinct sid from v$mystat;

SID
----------
142

SQL> select sid,serial# from v$session where sid=142;

SID SERIAL#
---------- ----------
142 168

SQL> select username,user,session_num,
2 tablespace segtype,segfile#,segblk# ,blocks from v$sort_usage;

USERNAME USER SESSION_NUM SEGTYPE SEGFILE# SEGBLK# BLOCKS
---------- ---------- ----------- ---------- ---------- ---------- ----------
TEST TEST 168 TEMP 201 2313 768

这里可以看到，通过与v$session视图中的serial#相关联，可以得出某个会话的

所拥有的临时表中的数据的大小，当然，这里与永久表一样，在delete的时候

是不会释放出空间的：

SQL> select username,user,session_num,tablespace segtype,segfile#,segblk# ,blocks from v$sort_usage;

USERNAME USER SESSION_NUM SEGTYPE SEGFILE# SEGBLK# BLOCKS
---------- ---------- ----------- ---------- ---------- ---------- ----------
TEST TEST 168 TEMP 201 2313 768

SQL> delete tmp_ses;

19772 rows deleted.

SQL> commit;

Commit complete.

SQL> select count(*) from tmp_ses;

COUNT(*)
----------
0

SQL> select username,user,session_num,tablespace segtype,segfile#,segblk# ,blocks from v$sort_usage;

USERNAME USER SESSION_NUM SEGTYPE SEGFILE# SEGBLK# BLOCKS
---------- ---------- ----------- ---------- ---------- ---------- ----------
TEST TEST 168 TEMP 201 2313 768

SQL>

SQL> truncate table tmp_ses;

Table truncated.

SQL> select username,user,session_num,tablespace segtype,segfile#,segblk# ,blocks from v$sort_usage;

no rows selected

可以看到，在删除数据时，oracle采用了节省成本的方式，减少了不必要的开销。

关于临时表的事务，与事务相关的临时表中的数据可以被用户的事务及子事务访问。

但是这些数据不能被同一会话里的两个事务同时访问。不同会话中的事务可以同时

使用同一个事务相关的临时表。如果用户事务对临时表执行了 INSERT 操作，

在此之后此事务的子事务将不能使用这个临时表。

如果在子事务中对临时表执行了 INSERT 操作，临时表中已有的数据将被清除。

子事务结束后，父事务及其他子事务对此临时表访问权利将被恢复。
　