插入相同的資料量普通表和臨時表產生的redo對比

往臨時表裡插入相同量的資料比普通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>