临时表的APPEND方式插入

临时表的redo生成要比普通表少的多，但是undo的产生并不比普通表少。

通过一个简单的例子说明：

SQL> create global temporary table t_temp
2 (id number, name varchar2(30))
3 on commit preserve rows;

表已创建。

SQL> create table t_normal
2 (id number, name varchar2(30));

表已创建。

SQL> select sid
2 from v$mystat
3 where rownum = 1;

SID
----------
133

SQL> select value
2 from v$statname a, v$sesstat b
3 where a.statistic# = b.statistic#
4 and a.name = 'undo change vector size '
5 and b.sid = 133;

未选定行

SQL> select value
2 from v$statname a, v$sesstat b
3 where a.statistic# = b.statistic#
4 and a.name = 'undo change vector size'
5 and b.sid = 133;

VALUE
----------
3988

SQL> insert into t_normal
2 select rownum, object_name
3 from dba_objects;

已创建49081行。

SQL> commit;

提交完成。

SQL> select value
2 from v$statname a, v$sesstat b
3 where a.statistic# = b.statistic#
4 and a.name = 'undo change vector size'
5 and b.sid = 133;

VALUE
----------
135232

SQL> insert into t_temp
2 select rownum, object_name
3 from dba_objects;

已创建49081行。

SQL> commit;

提交完成。

SQL> select value
2 from v$statname a, v$sesstat b
3 where a.statistic# = b.statistic#
4 and a.name = 'undo change vector size'
5 and b.sid = 133;

VALUE
----------
254240

SQL> select 254240 - 135232 temp_table, 135232 - 3988 normal_table from dual;

TEMP_TABLE NORMAL_TABLE
---------- ------------
119008 131244

可以看到，临时表和普通表产生的undo数据没有太多的差别，而实际上临时表的插入产生的redo信息也是undo信息对应的redo。

SQL> insert into t_temp
2 select *
3 from t_temp;

已创建49081行。

SQL> commit;

提交完成。

SQL> select value
2 from v$statname a, v$sesstat b
3 where a.statistic# = b.statistic#
4 and a.name = 'undo change vector size'
5 and b.sid = 133;

VALUE
----------
254408

SQL> insert into t_normal
2 select *
3 from t_normal;

已创建49081行。

SQL> commit;

提交完成。

SQL> select value
2 from v$statname a, v$sesstat b
3 where a.statistic# = b.statistic#
4 and a.name = 'undo change vector size'
5 and b.sid = 133;

VALUE
----------
256468

SQL> select 254408 - 254240 temp_table, 256468 - 254408 normal_table from dual;

TEMP_TABLE NORMAL_TABLE
---------- ------------
168 2060

对于append方式插入，普通表和临时表都会产生少量的undo，而临时表相对会更少一些。

来源：http://blog.sina.com.cn/s/blog_679e928c0100z6fd.html

 

上一篇文章最后介绍了APPEND方式插入临时表，这种方式产生的UNDO和REDO都是最小的。

而且利用这种方式实现插入，还拥有普通表所不具备的优势。

由于普通表执行APPEND方式插入，锁表方式不再是行级锁：

SQL> insert into t_normal
2 select rownum, object_name
3 from dba_objects;

已创建49081行。

在其他的会话执行DML操作，则被锁定：

SQL> set sqlp 'SQL2> '
SQL2> insert into t_normal
2 values (1, 'a');

回到会话1：

SQL> select object_id
2 from dba_objects
3 where owner = user
4 and object_name = 'T_NORMAL';

OBJECT_ID
----------
65319

SQL> select sid, type, id1, id2, lmode, request, ctime, block
2 from v$lock
3 where id1 = 65319;

SID TY ID1 ID2 LMODE REQUEST CTIME BLOCK
---------- -- ---------- ---------- ---------- ---------- ---------- ----------
133 TM 65319 0 6 0 217 1
134 TM 65319 0 0 3 202 0

SQL> commit;

提交完成。

可以看到表锁信息，直到会话1提交，会话2的DML才能继续：

已创建 1 行。

SQL2> commit;

提交完成。

因此使用APPEND方式处理普通表需要谨慎，这会影响到其他会话对这个表的并非访问。但是对于临时表，这不是个问题：

SQL> insert into t_temp
2 select rownum, object_name
3 from dba_objects;

已创建49081行。

在另外的会话执行：

SQL2> insert into t_temp
2 values (1, 'a');

已创建 1 行。

SQL2> commit;

提交完成。

SQL2> update t_temp
2 set name = 'b';

已更新 1 行。

SQL2> delete t_temp;

已删除 1 行。

SQL2> commit;

提交完成。

SQL2> truncate table t_temp;

表被截断。

无论会话执行何种操作，都不会受到其他会话的影响。

对于临时表而言，Oracle实现方式是为每个会话都实体化了一个临时表，每个用户都只会访问自己对应的临时表，因此不会影响到其他会话，也不会受其他会话影响，这也是为什么临时表的数据修改只有当前会话可以看到。因此当用户执行truncate操作也只是影响自己会话的临时表，而不会影响其他会话的修改结果。

回到会话1：

SQL> commit;

提交完成。

SQL> select count(*) from t_temp;

COUNT(*)
----------
49081

临时表的APPEND方式插入还能避免另一个严重的问题，就是备份恢复的问题。如果数据库级没有设置FORCE LOGGING，那么APPEND方式插入的数据是不写入归档的，对于普通表空间而言，意味着操作结束后需要单独备份，否则可能面临数据丢失的危险，而对于临时表则根本不存在这个问题，因为临时表存储在临时表空间上，因此根本不会进行备份，而且如果发生数据库的崩溃，临时表的数据本来也不需要恢复。

临时表使用APPEND方式的唯一缺点来自APPEND的另外一个特性，在提交前无法对该表进行查询和修改：

SQL> insert into t_temp
2 select rownum, object_name
3 from dba_objects;

已创建49081行。

SQL> select count(*) from t_temp;
select count(*) from t_temp
*第 1 行出现错误:
ORA-12838: 无法在并行模式下修改之后读/修改对象

SQL> insert into t_temp
2 values (1, 'a');
insert into t_temp
*第 1 行出现错误:
ORA-12838: 无法在并行模式下修改之后读/修改对象

SQL> commit;

提交完成。

SQL> select count(*) from t_temp;

COUNT(*)
----------
98162

这个限制和普通表没有区别，但是对于COMMIT DETELE ROWS类型的临时表则是致命的，这意味着这种类型的临时表无法使用这个特性：

SQL> create global temporary table t_temp_del
2 (id number, name varchar2(30))
3 on commit delete rows;

表已创建。

SQL> insert into t_temp_del
2 select rownum, object_name
3 from dba_objects;

已创建49082行。

SQL> select count(*) from t_temp_del;
select count(*) from t_temp_del
*第 1 行出现错误:
ORA-12838: 无法在并行模式下修改之后读/修改对象

SQL> commit;

提交完成。

SQL> select count(*) from t_temp_del;

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

 

来源：http://blog.sina.com.cn/s/blog_679e928c0100z6fx.html

相关阅读：oracle append作用分析