In theory, the Undo (RowID) for Direct Insert does not exist, because HWM is in the process of mobile, these Block can be used by other processs, then means that as long as the Direct Insert is recorded REDO and UNDO involved in the space, when failure, you only need to modify these spaces to the original state, but do not have to record DELETE one by one. That is to say, Direct INSERT does not generate the UNDO of data regardless of whether the table is in nologging.
For this reason, I will do a few combination experiments SQL> SELECT * FROM V $ VERSION; banner ----------------------------- ---------------------------------- Oracle Database 10g Enterprise Edition Release 10.1.2.0 - Prodpl / SQL Release 10.1.0.2.0 - ProductionCORE 10.1.0.2.0 ProductionTNS for Linux: Version 10.1.0.2.0 - ProductionNLSRTL Version 10.1.0.2.0 - ProductionSQL> 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 76201 253952 111688642 2220032 113352203 2220032 96683464 1171456 88875725 122880 841546 122880 07 1171456 2348 122880 09 122880 010 122880 011 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 76201 253952 111688642 2220032 113360243 2220032 96683464 2220032 98100662 122880 854526 122880 07 1171456 2348 12 2880 09 122880 010 122880 011 Rows SELECTED.SQL> SELECT 9810066 - 8887572 from Dual; 9810066-8887572 ---------------- 922494 Entularly generated returns SQL> SELECT 85452 - 84152 ------------- 1300 undo This fine change is unknown, and it is estimated that the SQL> ALTER TABLE T NOLOGGING caused by the change in space; the set table is NOLOGGINGTABLE ALTERED. SQL> insert / * append * / into t select * from dba_objects; direct insert13197 rows created.SQL> commit; Commit complete.SQL> select usn, RSSIZE, WRITES from v $ rollstat; USN RSSIZE WRITES ------ ---- ---------- ---------- 0 385024 76201 253952 111694982 2220032 113395483 2220032 96703764 2220032 98130765 122880 854526 122880 07 1171456 2348 122880 09 122880 010 122880 011 rows selected. SQL>
Select 9670376 - 9668346 from dual; 9670376-9668346 --------------------------------------------------------------- ----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- . SQL> INSERT INTO T SELECT * DBA_OBJECT *; Normal Insert Data 13197 Rows Created.SQL> Commit; Commit Complete.SQL> SELECT USN, RSSIZE, WRITES from V $ ROLLSTAT; USN RSSIZE WRITES ------------------------ ---------- ---------- 0 385024 76201 253952 111738442 2220032 122663423 2220032 96769184 2220032 98173565 122880 904786 122880 07 1171456 2348 122880 09 122880 010 122880 011 rows selected.SQL> select 12266342 - 11343052 from Dual; 12266342-11343052 ----------------- 923290 generated back to rollover segment information SQL> INSERT / * APPEND * / INTO T SELECT * FROM DBA_OBJECTS; DIRECT INSERT 13197 rows created.sql> commit; commit completion.sql> SELECT USN, RSSIZE, WRITES from V $ ROLLSTAT; USN RSSIZE WRITES --------------------- 11174528-11173844 ---; 11173844 from dual - ------- 0 385024 76201 253952 111745282 2220032 122667683 2220032 96784204 2220032 98173565 122880 925626 122880 07 1171456 2348 122880 09 122880 010 122880 011 rows selected.SQL> SQL> select 11174528 -------------- 684SQL> SELECT 92562 - 90478 from Dual; 92562-90478 ------------ 2084SQL> Here we check the generation of any returns Quantity, it is found that there is almost very few such that we can almost conclusions, regardless of whether the table is under NOLGGIN, will not generate UNDO for data content, that is, not for INSERT, ROWID will follow us further experiment Test SQL> SELECT * FROM V $ sysstat where name = 'in the table
Redo size '; statistic # name class value stat_id ------------------------------------ -------------------- 133 Redo Size 2 133173584 1236385760SQL> 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 1236385760SQL> SELECT 137974492 - 133173584 from Dual; 13797492-133173584 ------------ ------- 4800908 Normal insertion generated log SQL> roll; rollback completion.sql> select * from v $ sysstat where name = 'redo size'; statistic # name class value stat_id -------- - - ------------------------------------ --133 Redo Size 2 140087680 1236385760SQL> SELECT 140087680 - 137974492 from Dual; 140087680-137974492 -------------------- 2113188 Normal Insert Roll Rolling Generated Log 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 1236385760SQL> SELECT 141531644 - 140087680 from dual; 141531644-140087680 ------------------- 1443964 Direct insertion generated log SQL> Roll; Rollback Complete.SQL> SELECT * FROM V $ sysstat where name = 'redo size'; statistic # name class value stat_id ------------------------------- -------------------------- 133 Redo Size 2 141534344 1236385760SQL> SELECT 141534344 - 141531644 from Dual; 141534344-141531644 ------ ---------------------------------------------- Instead, only the space is recovered.
If it is deleted, it is impossible to generate only such a few REDO, which is proved from another side, even if Direct INSERT under Logging is not rollove information, it is not to generate UNDO to generate UNDO with a UNDO band cablebook, table and The index is the Logging status, the test results and the process are as follows -------------------------- - General Insert ------ Direct Insert Insert the log generation amount ---------------- 8350864 -------- 2364484 Insert the return segment generation amount ---------------- 2343894 -------- 426838 Rollback log generation amount ---------------- 4018204 -------- 76032 Rollback itself does not exist production rollback - ------------------------------------ conclusion is obvious, maybe someone here is asking, since Direct Have so good, why is it used in convention? Because 920, the SQLDR Direct causes Trigger useless, the function is useless to move directly on the HWM without using the delete deleted space, it is said that the same extent can only be used by one process (not tested, current LMT table space environment) Direct NOLogging Since the recovery will occur because the recovery does not generate a problem SQL> Truncate Table T; table truncate Table T; table truncated.sql> create index t_index on t (Object_id); index created.sql> col Name Format A20SQL> SELECT USN, RSSIZE, WRITES from V $ ROLLSTAT; USN RSSIZE WRITES ---------------------------- 0 385024 76201 516096 141661402 1171456 170039303 2220032 139187004 1171456 135505405 122880 7562466 122880 07 1171456 3128 122880 09 122880 010 122880 011 rows selected.SQL> select * from v $ sysstat where name = 'redo size'; STATISTIC # nAME CLASS VALUE STAT_ID --------- - ----------------------------------- -133 Redo Size 2 181757168 1236385760SQL> INSERT INTO T Select * from dba_objects; 13198 rows created.sql> SELECT USN, RSSIZE, WRITES from V $ Rollstat; USN RSSIZE WRITES ------------------------------ ------ ---------- 0 385024 76201 516096 141661402 4317184 193478243 2220032 139187004 1171456 135513965 122880 7562466 1228 80 07 1171456 3128 122880 09 122880 010 122880 011 ROWS SELECTED.SQL> SELECT * from V $ sysstat where name = 'redo size';
Statistic # name class value stat_id ------------------------------------ ---- ------ ---------- 133 Redo Size 2 190108032 1236385760SQL> SELECT 19347824 - 17003930 from Dual; 19347824-17003930 ----------------- 2,343,894 index exists, the rollback amount of the generated SQL conventional insert mode> select 190108032 - 181757168 from dual; 190108032-181757168 ------------------- 8350864 index exists, conventional insert SQL> Roll; Rollback Complete.sql> SELLBACK Complete.SQL> SELECT USN, RSSIZE, WRITES from V $ ROLLSTAT; USN RSSIZE WRITES ---------------------------------------------------------------------------------------------------- --------- 0 385024 76201 516096 141661402 4317184 193478243 2220032 139187004 1171456 135513965 122880 7571026 122880 07 1171456 3128 122880 09 122880 010 122880 011 rows selected.SQL> select * from v $ sysstat where name = 'redo size' ; Statistic # name class value stat_id ------------------------------------ --- ---------------- 133 Redo Size 2 194126236 1236385760SQL> SELECT 194126236 - 190108032 from Dual; 194126236-190108032 -------------------------------------------------------------------------------------------------------- --- 4018204 There is an index, the regular insert method then rolls back to the generated log amount SQL> Truncate Table T; table truncated.sql> SELECT USN, RSSIZ E, WRITES from V $ ROLLSTAT; USN RSSIZE WRITES ---------------------------- 0 385024 76201 581632 141857422 4317184 193568623 2220032 139364384 1171456 135669365 122880 7571026 122880 07 1171456 3128 122880 09 122880 010 122880 011 rows selected.SQL> select * from v $ sysstat where name = 'redo size'; STATISTIC # nAME CLASS VALUE STAT_ID ---------- - ---------------------------------------------- 133 Redo Size 2 194287336 1236385760SQL> INSERT / * APPEND * / INTO T SELECT * FROM DBA_OBJECTS; 13198 ROWS CREATED.SQL> SELECT USN, RSSIZE, WRITES from V $ ROLLSTAT;
