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进行提交,否则查询临时表数据会出错
来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/802415/viewspace-823037/,如需转载,请注明出处,否则将追究法律责任。
转载于:http://blog.itpub.net/802415/viewspace-823037/