SQL*Plus: Release 9.2.0.4.0 - Production on 星期五 6月 10 15:05:46 2005

Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved.

连接到:
Oracle9i Enterprise Edition Release 9.2.0.4.0 - Production
With the Partitioning, OLAP and Oracle Data Mining options
JServer Release 9.2.0.4.0 - Production

SQL> set autotrace
用法: SET AUTOT[RACE] {OFF | ON | TRACE[ONLY]} [EXP[LAIN]] [STAT[ISTICS]]

SQL> create table test(a number);

表已创建。

SQL> set autotrace trace stat
SQL> insert into test select rownum from dba_objects;

已创建40720行。

Statistics
----------------------------------------------------------
564 recursive calls
590 db block gets
31117 consistent gets
711 physical reads
630920 redo size
613 bytes sent via SQL*Net to client
547 bytes received via SQL*Net from client
3 SQL*Net roundtrips to/from client
2 sorts (memory)
0 sorts (disk)
40720 rows processed

SQL> drop table test;

表已丢弃。

SQL> create global temporary table test(a number);

表已创建。

SQL> insert into test select rownum from dba_objects;

已创建40720行。

Statistics
----------------------------------------------------------
9 recursive calls
41623 db block gets
30801 consistent gets
0 physical reads
5069636 redo size
618 bytes sent via SQL*Net to client
547 bytes received via SQL*Net from client
3 SQL*Net roundtrips to/from client
2 sorts (memory)
0 sorts (disk)
40720 rows processed

SQL> drop table test;

表已丢弃。

SQL> create global temporary table test(a number) on commit preserve rows;

表已创建。

SQL> insert into test select rownum from dba_objects;

已创建40720行。

Statistics
----------------------------------------------------------
9 recursive calls
41625 db block gets
30803 consistent gets
0 physical reads
5070068 redo size
620 bytes sent via SQL*Net to client
547 bytes received via SQL*Net from client
3 SQL*Net roundtrips to/from client
2 sorts (memory)
0 sorts (disk)
40720 rows processed

SQL> truncate table test;

表已截掉。

SQL> insert/*+ append */ into test select rownum from dba_objects;

已创建40720行。

Statistics
----------------------------------------------------------
7 recursive calls
3 db block gets
30768 consistent gets
0 physical reads
224 redo size
604 bytes sent via SQL*Net to client
560 bytes received via SQL*Net from client
3 SQL*Net roundtrips to/from client
1 sorts (memory)
0 sorts (disk)
40720 rows processed

SQL> truncate table test;

表已截掉。

SQL> drop table test;

表已丢弃。

SQL> create table test(a number);

表已创建。

SQL> insert/*+ append */ into test select rownum from dba_objects;

已创建40720行。

Statistics
----------------------------------------------------------
191 recursive calls
94 db block gets
30894 consistent gets
0 physical reads
10304 redo size
606 bytes sent via SQL*Net to client
560 bytes received via SQL*Net from client
3 SQL*Net roundtrips to/from client
2 sorts (memory)
0 sorts (disk)
40720 rows processed

SQL>

结论：
通过上面的测试可以看出，
table_type insert_statement redo_size(Byte)
----------- --------------------- ---------------
table insert 630920
table insert/*+ append */ 10304
temporary insert 5070068
temporary insert/*+ append */ 224
对临时表用append提示可以大大的减小redo的大小。

使用条件：
1、表必须是通过truncate删除数据
2、表必须是会话级临时表，on commit preserve rows
3、insert/*+ append */后必须用commit进行提交，否则查询临时表数据会出错