append the test increases nologging

Posted on 2009-07-02 filed under: Oracle (RSS) 24 views

rn
Under normal circumstances
================================================== ==========================
Table space for the remaining 0.71 G

rn
Normal to create the test table
create table test as select * from tttttt where 0 = 1;

rn
Record of the current session's redo size
select s.SID, s.VALUE
from v $ sesstat s, v $ statname t
where s.sid = 16
and s.STATISTIC # = t.STATISTIC #
and t.name = 'redo size'
6 *
SQL> /

rn
SID VALUE
---------- ----------
10 80712300

Insert 5 million to start data

rn
insert into test select * from tttttt where rownum <= 50000

rn
Elapsed: 00:00:17.27

rn
Record of the current redo size

rn
1 select s.SID, s.VALUE
2 from v $ sesstat s, v $ statname t
3 where s.sid = 10
4 and s.STATISTIC # = t.STATISTIC #
5 and t.name = 'redo size'
6 *
SQL> /

rn
SID VALUE
---------- ----------
10 160754480

rn

Table space remaining 0.622802734375

rn
Summary of time: 17 stopwatch space: 0.71-0.62 = 0.09G
redo generated :160754480-80712300 = 80042180

rn
================================================== =======================
create table nologging

rn
Table space remaining 0.716552734375

rn
create table test nologging as select * from tttttt where 0 = 1;

rn
1 select s.SID, s.VALUE
2 from v $ sesstat s, v $ statname t
3 where s.sid = 10
4 and s.STATISTIC # = t.STATISTIC #
5 and t.name = 'redo size'
6 *
SQL> /

rn
SID VALUE
---------- ----------
10 161388232

rn
Insert 5 million to start data

rn
SQL> insert into test select * from tttttt where rownum <= 50000;

rn
50000 rows created.

rn
Elapsed: 00:00:17.34

rn
After the implementation of the redo

rn
SQL> l
1 select s.SID, s.VALUE
2 from v $ sesstat s, v $ statname t
3 where s.sid = 10
4 and s.STATISTIC # = t.STATISTIC #
5 and t.name = 'redo size'
6 *
SQL> /

rn
SID VALUE
---------- ----------
10 241385848

rn
Table space remaining 0.622802734375

rn
Summary of time: 17 stopwatch space: 0.71-0.62 = 0.09G
redo generated :241385848-161388232 = 79997616

================================================== ============
nologging way to build tables, and then used to insert append mode

rn
Table space remaining 0.71649169921875

rn
create table test nologging as select * from tttttt where 0 = 1;

rn
Record of the current redo size
1 select s.SID, s.VALUE
2 from v $ sesstat s, v $ statname t
3 where s.sid = 10
4 and s.STATISTIC # = t.STATISTIC #
5 and t.name = 'redo size'
6 *
SQL> /

rn
SID VALUE
---------- ----------
10 242019668

rn

Insert the beginning of the way to append data 50000
SQL> insert / * + append * / into test select * from tttttt where rownum <= 50000;

rn
50000 rows created.

rn
Elapsed: 00:00:08.31

rn
After the implementation of the redo
1 select s.SID, s.VALUE
2 from v $ sesstat s, v $ statname t
3 where s.sid = 10
4 and s.STATISTIC # = t.STATISTIC #
5 and t.name = 'redo size'
6 *
SQL> /

rn
SID VALUE
---------- ----------
10 242257132

Table space remaining 0.614990234375

rn
Summary of time: 8 stopwatch space: 0.71-0.61 = 0.1G
redo generated :242257132-242019668 = 237464

rn
================================================== ================

rn
Summed up under the test, or table nologging normal mode, the speed and redo of the insert does not have a fundamental impact, basically the same space occupied by

rn
Only used together with the insert table nologging prompted the use of append, the speed can increase, and will redo the emergence of much lower than the first two cases, however, take up some storage space and more!

rn