归档模式下表为nologging状态,且使用/*+ append*/ insert时会提高效率。
为什么呢,因为会减少redo和undo的产生。
如何计算一个insert产生了多少undo呢,通过对比当前session执行insert前后
undo的数量可以知道。
Connected to Oracle Database 10g Enterprise Edition Release 10.2.0.4.0
SQL> select sid from v$mystat where rownum=1;
SID
----------
312
SQL> select value "undo change vector size"
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 = '312' ;
undo change vector size
-----------------------
0
SQL> create table t as select rownum from user_objects where rownum<100;
create table t as select rownum from user_objects where rownum<100
ORA-00998: must name this expression with a column alias
-- 显示给rownum 指定个别名就好了
SQL> create table t as select rownum a from user_objects where rownum<100;
Table created
SQL> select value "undo change vector size"
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 = '312' ;
undo change vector size
-----------------------
3128
SQL> insert /*+ append */ into t select rownum a from user_objects where rownum<100;
99 rows inserted
SQL>
SQL> select value "undo change vector size"
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 = '312' ;
undo change vector size
-----------------------
3204
--计算采用/*+ append */ 模式插入100条记录产生undo的数量
SQL> select 3204 - 3128 d from dual;
D
----------
76
SQL> insert into t select rownum a from user_objects where rownum<100;
99 rows inserted
SQL> commit;
Commit complete
SQL> select value "undo change vector size"
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 = '312' ;
undo change vector size
-----------------------
3920
--计算采用正常模式插入100条记录产生undo的数量
SQL> select 3920 - 3204 d from dual;
D
----------
716
结果表明插入同样的记录,采用/*+ append*/ 方式产生的undo会更少一些。