ITPub博客

首页 > 数据库 > 数据库开发技术 > 插入相同的数据量普通表和临时表产生的redo对比

插入相同的数据量普通表和临时表产生的redo对比

原创 数据库开发技术 作者:warehouse 时间:2010-12-23 18:07:34 0 删除 编辑
往临时表里插入相同量的数据比普通heap表少产生很多redo。[@more@]

SQL> create table t_heap tablespace users as select * from dba_objects where 1=2;

Table created.

SQL> create global temporary table t_temp on commit preserve rows as select * from dba_objects where 1=2;

Table created.

SQL> select a.name,b.value,b.sid from v$statname a ,v$sesstat b where a.STATISTIC# in (133,134)
2 and a.STATISTIC#=b.STATISTIC# and b.sid=(select distinct sid from v$mystat);

NAME VALUE SID
------------------------------ ---------- ----------
redo entries 140 138
redo size 35552 138

SQL> insert into t_heap select * from dba_objects;

11376 rows created.

SQL> insert into t_heap select * from dba_objects;

11376 rows created.

SQL> insert into t_heap select * from dba_objects;

11376 rows created.

SQL> insert into t_heap select * from dba_objects;

11376 rows created.

SQL> insert into t_heap select * from dba_objects;

11376 rows created.

SQL> insert into t_heap select * from dba_objects;

11376 rows created.

SQL> insert into t_heap select * from dba_objects;

11376 rows created.

SQL> insert into t_heap select * from dba_objects;

11376 rows created.

SQL> insert into t_heap select * from dba_objects;

11376 rows created.

SQL> insert into t_heap select * from dba_objects;

11376 rows created.

SQL> commit;

Commit complete.

SQL> select a.name,b.value,b.sid from v$statname a ,v$sesstat b where a.STATISTIC# in (133,134)
2 and a.STATISTIC#=b.STATISTIC# and b.sid=(select distinct sid from v$mystat);

NAME VALUE SID
------------------------------ ---------- ----------
redo entries 8493 138
redo size 11837876 138

SQL> select 11837876 - 35552 from dual;

11837876-35552
--------------
11802324
--在普通表里插入113760条数据产生的日志是11802324字节的日志
SQL> select log_mode from v$database;

LOG_MODE
------------
ARCHIVELOG

SQL> select a.name,b.value,b.sid from v$statname a ,v$sesstat b where a.STATISTIC# in (133,134)
2 and a.STATISTIC#=b.STATISTIC# and b.sid=(select distinct sid from v$mystat);

NAME VALUE SID
------------------------------ ---------- ----------
redo entries 8493 138
redo size 11837876 138

SQL> insert into t_temp select * from dba_objects;

11376 rows created.

SQL> insert into t_temp select * from dba_objects;

11376 rows created.

SQL> insert into t_temp select * from dba_objects;

11376 rows created.

SQL> insert into t_temp select * from dba_objects;

11376 rows created.

SQL> insert into t_temp select * from dba_objects;

11376 rows created.

SQL> insert into t_temp select * from dba_objects;

11376 rows created.

SQL> insert into t_temp select * from dba_objects;

11376 rows created.

SQL> insert into t_temp select * from dba_objects;

11376 rows created.

SQL> insert into t_temp select * from dba_objects;

11376 rows created.

SQL> insert into t_temp select * from dba_objects;

11376 rows created.

SQL> commit;

Commit complete.

SQL> select a.name,b.value,b.sid from v$statname a ,v$sesstat b where a.STATISTIC# in (133,134)
2 and a.STATISTIC#=b.STATISTIC# and b.sid=(select distinct sid from v$mystat);

NAME VALUE SID
------------------------------ ---------- ----------
redo entries 11157 138
redo size 12427444 138

SQL> select 12427444 - 11837876 from dual;

12427444-11837876
-----------------
589568
--在临时表里插入113760条数据产生的日志是589568字节的日志

SQL> select 11802324 - 589568 from dual;

11802324-589568
---------------
11212756

SQL> select (11802324 - 589568)/1024/1024 m from dual;

M
----------
10.6933174
结论:插入相同的数据量到普通表和临时表redo相差10m多...

SQL> select count(*) from t_heap;

COUNT(*)
----------
113760

SQL> select count(*) from t_temp;

COUNT(*)
----------
113760

SQL>

来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/19602/viewspace-1043541/,如需转载,请注明出处,否则将追究法律责任。

请登录后发表评论 登录
全部评论

注册时间:2007-12-07

  • 博文量
    717
  • 访问量
    5098847