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

往临时表里插入相同量的数据比普通heap表少产生很多redo。

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>