Undo statements that do not generate data under direct insert

Theoretically, there is no need for the Undo (rowid) of direct insert, because hwm cannot be used by other processes during the moving process, you only need to record the redo and undo of the space involved in this direct insert operation. When the rollback fails, you only need to change these spaces to the original state, instead of record them one by one. That is to say, direct insert does not generate data undo regardless of whether the table is under nologging.

For this reason, I will do several combination experiments here.

SQL> select * from V $ version;

Banner
----------------------------------------------------------------
Oracle Database 10g Enterprise Edition Release 10.1.0.2.0-Prod
PL/SQL release 10.1.0.2.0-Production
Core 10.1.0.2.0 Production
TNS for Linux: Version 10.1.0.2.0-Production
Nlsrtl version 10.1.0.2.0-Production

SQL>
SQL> drop table t;

Table dropped.

SQL> Create Table T as select * From dba_objects;

Table created.

SQL> select USN, rssize, writes from V $ rollstat;

USN rssize writes
------------------------------
0 385024 7620
1 253952 11168864
2 2220032 11335220
3 2220032 9668346
4 1171456 8887572
5 122880 84154
6 122880 0
7 1171456 234
8 122880 0
9 122880 0
10 122880 0

11 rows selected.

SQL> insert into T select * From dba_objects;

13197 rows created.

SQL> commit;

Commit complete.

SQL> select USN, rssize, writes from V $ rollstat;

USN rssize writes
------------------------------
0 385024 7620
1 253952 11168864
2 2220032 11336024
3 2220032 9668346
4 2220032 9810066
5 122880 85452
6 122880 0
7 1171456 234
8 122880 0
9 122880 0
10 122880 0

11 rows selected.

SQL> select 9810066-8887572 from dual;

9810066-8887572
---------------
922494 volume of rollback segments generated under normal conditions
SQL> select 85452-84152 from dual;

85452-84152
-----------
1300 this subtle change in Undo is currently unknown. It is estimated that it may be caused by changes in space.

SQL> ALTER TABLE t nologging; set the table to nologging

Table altered.

SQL> insert/* + append */into T select * From dba_objects; direct insert

13197 rows created.

SQL> commit;

Commit complete.

SQL> select USN, rssize, writes from V $ rollstat;

USN rssize writes
------------------------------
0 385024 7620
1 253952 11169498
2 2220032 11339548
3 2220032 9670376
4 2220032 9813076
5 122880 85452
6 122880 0
7 1171456 234
8 122880 0
9 122880 0
10 122880 0

11 rows selected.

SQL> select 9670376-9668346 from dual;

9670376-9668346
---------------
2030 few rollback segments found

SQL> select 9813076-9810066 from dual;

9813076-9810066
---------------
3010

SQL>

SQL> ALTER TABLE t logging; change the table back to the logging status

Table altered.

SQL> select USN, rssize, writes from V $ rollstat;

USN rssize writes
------------------------------
0 385024 7620
1 253952 11173844
2 2220032 11343052
3 2220032 9676700
4 2220032 9816500
5 122880 89668
6 122880 0
7 1171456 234
8 122880 0
9 122880 0
10 122880 0

11 rows selected.

SQL> insert into T select * From dba_objects; normal data insertion

13197 rows created.

SQL> commit;

Commit complete.

SQL> select USN, rssize, writes from V $ rollstat;

USN rssize writes
------------------------------
0 385024 7620
1 253952 11173844
2 2220032 12266342
3 2220032 9676918
4 2220032 9817356
5 122880 90478
6 122880 0
7 1171456 234
8 122880 0
9 122880 0
10 122880 0

11 rows selected.

SQL> select 12266342-11343052 from dual;

12266342-11343052
-----------------
923290 rollback segment information

SQL> insert/* + append */into T select * From dba_objects; direct insert

13197 rows created.

SQL> commit;

Commit complete.

SQL> select USN, rssize, writes from V $ rollstat;

USN rssize writes
------------------------------
0 385024 7620
1 253952 11174528
2 2220032 12266768
3 2220032 9678420
4 2220032 9817356
5 122880 92562
6 122880 0
7 1171456 234
8 122880 0
9 122880 0
10 122880 0

11 rows selected.

SQL>

SQL> select 11174528-11173844 from dual;

11174528-11173844
-----------------
684

SQL> select 92562-90478 from dual;

92562-90478
-----------
2084

SQL>
Here, we checked the generation of any rollback segment and found that there were very few

Therefore, we can conclude that, no matter whether the table is under nologging or not, as long as it is direct insert, no undo will be generated for the data content, that is, the rowid will not be recorded for the insert

Next, we will conduct further experiments to test the table in the logging status.

SQL> select * from V $ sysstat where name = 'redo size ';

Statistic # name class value stat_id
------------------------------------------------------------
133 redo size 2 133173584 1236385760

SQL> insert into T select * From dba_objects;

13197 rows created.

SQL> select * from V $ sysstat where name = 'redo size ';

Statistic # name class value stat_id
------------------------------------------------------------
133 redo size 2 137974492 1236385760

SQL> select 137974492-133173584 from dual;

137974492-133173584
-------------------
4800908 logs generated by normal insertion

SQL> roll;
Rollback complete.
SQL> select * from V $ sysstat where name = 'redo size ';

Statistic # name class value stat_id
------------------------------------------------------------
133 redo size 2 140087680 1236385760

SQL> select 140087680-137974492 from dual;

140087680-137974492
-------------------
2113188 logs generated by rollback after normal insertion

SQL> insert/* + append */into T select * From dba_objects;

13197 rows created.

SQL> select * from V $ sysstat where name = 'redo size ';

Statistic # name class value stat_id
------------------------------------------------------------
133 redo size 2 141531644 1236385760

SQL> select 141531644-140087680 from dual;

141531644-140087680
-------------------
1443964 logs generated by direct insertion

SQL> roll;
Rollback complete.
SQL> select * from V $ sysstat where name = 'redo size ';

Statistic # name class value stat_id
------------------------------------------------------------
133 redo size 2 141534344 1236385760

SQL> select 141534344-141531644 from dual;

141534344-141531644
-------------------
2700 logs generated by rollback after direct insertion

SQL>

The experiment here shows that data rollback after direct insert does not actually delete data, but only recycles space. If it is deleted, it is impossible to generate only so few redo records. Here we prove from the other side that even if the direct insert under logging is used for rollback information, it is also an undo that does not generate undo for data and only produces space changes.

 

 

The table with an index and the index are both in the logging status. The test result and process are as follows:

---------------------------- General insert ------- direct insert

Log insertion volume ---------------- 8350864--------2364484

Insert rollback segment generation -------------- 2343894--------426838

Rollback log generation ---------------- 4018204--------76032

Rollback does not exist -------------------------------------

The conclusion is obvious. Some people may ask, since direct has so many advantages, why is it still common?
Due to sqlldr direct versions earlier than 920, trigger is useless and function is useless.
Direct moves directly on hwm without using Delete to delete the released space, which may result in a waste of space.
During Direct, it is said that the same extent can only be used by one process (not tested, currently there is no LMT tablespace environment)

Direct + nologging recovery may be caused by no data logs

SQL> truncate table t;

Table truncated.

SQL> Create index t_index on T (object_id );

Index created.

SQL> Col name format A20
SQL> select USN, rssize, writes from V $ rollstat;

USN rssize writes
------------------------------
0 385024 7620
1 516096 14166140
2 1171456 17003930
3 2220032 13918700
4 1171456 13550540
5 122880 756246
6 122880 0
7 1171456 312
8 122880 0
9 122880 0
10 122880 0

11 rows selected.

SQL> select * from V $ sysstat where name = 'redo size ';

Statistic # name class value stat_id
------------------------------------------------------------
133 redo size 2 181757168 1236385760

SQL> insert into T select * From dba_objects;

13198 rows created.

SQL> select USN, rssize, writes from V $ rollstat;

USN rssize writes
------------------------------
0 385024 7620
1 516096 14166140
2 4317184 19347824
3 2220032 13918700
4 1171456 13551396
5 122880 756246
6 122880 0
7 1171456 312
8 122880 0
9 122880 0
10 122880 0

11 rows selected.

SQL> select * from V $ sysstat where name = 'redo size ';

Statistic # name class value stat_id
------------------------------------------------------------
133 redo size 2 190108032 1236385760

SQL> select 19347824-17003930 from dual;

19347824-17003930
-----------------
2343894 there is an index, and the rollback volume is generated in normal insert mode.

SQL> select 190108032-181757168 from dual;

190108032-181757168
-------------------
8350864 there is an index, and the log volume generated in the regular insert mode

SQL> roll;
Rollback complete.
SQL> select USN, rssize, writes from V $ rollstat;

USN rssize writes
------------------------------
0 385024 7620
1 516096 14166140
2 4317184 19347824
3 2220032 13918700
4 1171456 13551396
5 122880 757102
6 122880 0
7 1171456 312
8 122880 0
9 122880 0
10 122880 0

11 rows selected.

SQL> select * from V $ sysstat where name = 'redo size ';

Statistic # name class value stat_id
------------------------------------------------------------
133 redo size 2 194126236 1236385760

SQL> select 194126236-190108032 from dual;

194126236-190108032
-------------------
4018204 there is an index, and the log volume generated by General insertion and rollback

SQL> truncate table t;

Table truncated.

SQL> select USN, rssize, writes from V $ rollstat;

USN rssize writes
------------------------------
0 385024 7620
1 581632 14185742
2 4317184 19356862
3 2220032 13936438
4 1171456 13566936
5 122880 757102
6 122880 0
7 1171456 312
8 122880 0
9 122880 0
10 122880 0

11 rows selected.

SQL> select * from V $ sysstat where name = 'redo size ';

Statistic # name class value stat_id
------------------------------------------------------------
133 redo size 2 194287336 1236385760

SQL> insert/* + append */into T select * From dba_objects;

13198 rows created.

SQL> select USN, rssize, writes from V $ rollstat;

USN rssize writes
------------------------------
0 385024 7620
1 2088960 14612580
2 4317184 19356862
3 2220032 13936438
4 1171456 13569090
5 122880 757102
6 122880 0
7 1171456 312
8 122880 0
9 122880 0
10 122880 0

11 rows selected.

SQL> select * from V $ sysstat where name = 'redo size ';

Statistic # name class value stat_id
------------------------------------------------------------
133 redo size 2 196651820 1236385760

SQL> select 14612580-14185742 from dual;

14612580-14185742
-----------------
426838 there are indexes and the rollback volume generated in direct insertion Mode

SQL> select 196651820-194287336 from dual;

196651820-194287336
-------------------
2364484 there are indexes and the amount of logs generated in direct insertion Mode

SQL> roll;
Rollback complete.
SQL> select USN, rssize, writes from V $ rollstat;

USN rssize writes
------------------------------
0 385024 7620
1 2088960 14612580
2 4317184 19360722
3 2220032 13936438
4 1171456 13569946
5 122880 757908
6 122880 0
7 1171456 312
8 122880 0
9 122880 0
10 122880 0

11 rows selected.

SQL> select * from V $ sysstat where name = 'redo size ';

Statistic # name class value stat_id
------------------------------------------------------------
133 redo size 2 196727852 1236385760

SQL> select 196727852-196651820 from dual;

196727852-196651820
-------------------
76032 there is an index, and the amount of logs generated by direct insertion rollback

SQL>