append the test increases nologging
rnUnder 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
Tags: implementation, sid, table test, rownum, test table, lt, table space, circumstances, rn, t statistic, tttttt, stopwatch, 5 million
Permalink: http://www.kods.netwww.kods.net/append-the-test-increases-nologging/
















