Redo und Undo für INSERT APPEND

Mein Gedächtnis ist zwar manchmal etwas unzuverlässig, aber dass ich schon mehrfach über INSERT /*+ APPEND */ geschrieben habe, ist mir nicht entfallen. Trotzdem an dieser Stelle noch mal ein Versuch, die Effekte von Redo und Undo klar auseinander zu halten. Dazu folgender Test (in 10.2.0.4 auf zwei Datenbanken):

-- grundsätzlich ist das die gleiche Datengrundlage
-- wie in den oben verlinkten Tests vom Januar 2011
create table test_insert_source
as
select rownum rn
     , mod(rownum , 2) col1
     , mod(rownum , 4) col2
     , mod(rownum , 8) col3
     , mod(rownum , 16) col4
     , lpad('*', 50, '*') col_pad
  from dual
connect by level <= 1000000;

exec dbms_stats.gather_table_stats (ownname=>user, tabname=>'TEST_INSERT_SOURCE')

drop table test_insert;
create table test_insert
as
select *
  from test_insert_source
 where 1 = 0;

-- im zweiten Testfall relevant:
-- alter table test_insert nologging;

-- Statistikerfassung

insert into test_insert
select *
  from test_insert_source;

commit;  

-- Statistikerfassung

truncate table test_insert;

-- Statistikerfassung

insert /*+ append */ into test_insert
select *
  from test_insert_source;

-- Statistikerfassung

Zur Statistikerfassung dient jeweils folgende harmlose Query:

select name.name
     , ses.value
  from v$sesstat ses
     , v$statname name
 where ses.statistic# = name.statistic#
   and ses.sid in (select sid from v$mystat)
   and name in ('redo size', 'undo change vector size');

Getestet werden folgende Varianten:

• Insert / Insert Append
• Logging / Nologging
• Archivelog / Noarchivelog

DB-Logging     Table-Logging  Append       Redo      Undo 
------------   -------------  ------   --------   -------
NoArchiveLog             yes      no   75,89 MB   2,53 MB
NoArchiveLog             yes     yes    0,25 MB   0,06 MB
NoArchiveLog              mo      no   76,04 MB   2,55 MB
NoArchiveLog              no     yes    0,25 MB   0,06 MB
ArchiveLog               yes      no   79,18 MB   3,14 MB
ArchiveLog               yes     yes   78,78 MB   0,06 MB
ArchiveLog                no      no   79,18 MB   3,14 MB
ArchiveLog                no     yes    0,28 MB   0,06 MB

Demnach gilt für Inserts in Tabellen ohne Indizes:

• mit dem APPEND-Hint wird in allen Fällen nur ein minimales Undo erzeugt (was einleuchtet, da die Operation ja am Buffer Cache vorbei abläuft)
• in einer NoArchiveLog-Datenbank wird bei Verwendung des APPEND-Hints nur minimales Redo erzeugt
• in einer ArchiveLog-Datenbank wird bei Verwendung des APPEND-Hints nur dann minimales Redo erzeugt, wenn die Tabelle als nologging definiert ist
• in allen anderen Fällen wird redo in normalem Umfang erzeugt

Die Ergebnisse entsprechen (was wenig überrascht) auch der Matrix, die Tom Kyte vor fast zehn Jahren zur redo Erzeugung erstellt hat. Mit Indizes wäre der Fall noch mal etwas unübersichtlicher. Ein paar weitere Anmerkungen zum Thema liefert auch noch ein - ebenfalls uralter - Thread aus Oracle-L (darin vor allem Jonathan Lewis' Erläuterung). Ich vermute, dass das Verhalten in späteren Oracle-Versionen nicht verändert ist.