Datenarchivierungs-Strategien in Oracle (Delete/Insert)

Jonathan Lewis hat vor kurzem zwei interessante Artikel zur Archivierung von Daten veröffentlicht, wobei die Anforderung so definiert ist, dass Daten via delete aus einer Tabelle gelöscht und die gleichen Datensätze per insert in eine zweite Tabelle eingefügt werden:

• Delete/Insert: erläutert das grundsätzliche Problem: wie kann sichergestellt werden, dass die gleichen Daten, die aus der ersten Tabelle (t1) gelöscht wurden auch in der zweiten Tabelle (t2) eingefügt werden. Relativ einfach ist diese Anforderung mit massiven Locks zu erreichen, mit denen man konkurrierende DML-Operationen verhindert, aber in der Regel ist das keine wünschenswerte Verhaltensweise in einem multi-user System. Im Artikel werden mehrere möglich Strategien - samt ihrer jeweiligen Vor- und Nachteile - erläutert, darunter: (1) die Sammlung der vom delete betroffenen Sätze in einer collection, für die man dann ein insert in einer forall Schleife durchführt, (2) Sammlung der betroffenen rowid-s über ein select for update gefolgt von einem delete und einem insert mit den ermittelten rowid-s. In den Kommentaren zum Artikel werden weitere Optionen diskutiert (Trigger; logische Löschung mit delete Flag - unter Umständen als invisible column; Verwendung eines table lock; Verwendung einer GTT in Kombination mit einer pipelined table function).
• Delete/Insert #2: analysiert eine in einem der Kommantare zum ersten Artikel aufgeführte Idee, die Jonathan Lewis zunächst verwarf und dann noch mal genauer untersuchte: die Verwendung einer "as of SCN" Klausel, die sich unter bestimmten Umständen ebenfalls verwenden lässt.

Sehr viel kompakter als alle Oracle-Varianten ist die postgres-Lösung zum Thema - mit Verwendung einer CTE und einer returning clause, die insert und delete zusammenfasst und die ich hier gelegentlich schon mal erwähnt hatte. Nachdem diese Syntax auch in den Kommentaren zum ersten Artikel erwähnt wurde und vom Herrn Lewis gelobt wurde ("That Postgres syntax is wonderfully compact – much more convenient than the PL/SQL of option 1 which is probably the closest you could get to it in Oracle.") habe ich eine entsprechende Idee im OTN Forum "database ideas" angelegt.