DBMS_REDEFINITION und Massenupdates

Eine der von mir am häufigsten zitierten Antworten auf Oracle-Performance-Fragen ist Tom Kytes schöner Satz: "If I had to update millions of records I would probably opt to NOT update." Er bezieht sich darauf, dass Massenupdates eine sehr teure Operation darstellen, da sie Änderungen an jedem betroffenen Block und große Menge von redo und undo hervorrufen. Effizienter ist stattdessen der Neuaufbau der geänderten Datenmenge über CTAS in einer Hilfstabelle und die anschließende Umbenennung der Objekte.

Das Verfahren stößt allerdings an seine Grenzen, wenn es kein Wartungsfenster für die Durchführung des Austauschs gibt, da permanent DML-Operationen auf der fraglichen Tabelle durchgeführt werden. Für diesen Fall gibt es die Möglichkeit der Online-Reorganisation mit Hilfe von DBMS_REDEFINITION, die Tom Kyte ebenfalls gelegentlich erläutert hat. Dazu ein übersichtliches Beispiel:

-- Session 1:
drop table t;
drop table t_redefine;

create table t (id primary key, col_org, col2, padding)
as
select rownum id
     , 0 col_org
     , 0 col2
     , lpad('*', 50, '*') padding
  from dual
connect by level <= 1000000;


create table t_redefine (
    id number
  , col_upd number
  , col2 number
  , padding varchar2(50)
);  
    

declare
    l_colmap varchar(512);
  begin
    l_colmap := 'id, 1 col_upd, col2 + 2 col2, padding ';

    dbms_redefinition.start_redef_table
    (  uname           => user,
       orig_table      => 'T',
       int_table       => 'T_REDEFINE',
       orderby_cols    => 'ID',
       col_mapping     => l_colmap );
 end;
/

-- Session 2:   
update t set col2 = col2 + 2 where id <= 5;

select id, col_org, col2 from t where id <= 10;

        ID    COL_ORG       COL2
---------- ---------- ----------
         1          0          2
         2          0          2
         3          0          2
         4          0          2
         5          0          2
         6          0          0
         7          0          0
         8          0          0
         9          0          0
        10          0          0

-- Session 1:  
exec dbms_redefinition.finish_redef_table ( user, 'T', 'T_REDEFINE' );
--> wartet auf einen Abschluss der offenen Transaktion gegen T

-- Session 2:
commit;
-- Session 1:  
--> der Aufruf von dbms_redefinition.finish_redef_table meldet Vollzug

-- Session 2:
select id, col_upd, col2 from t where id <= 10;

        ID    COL_UPD       COL2
---------- ---------- ----------
         1          1          4
         2          1          4
         3          1          4
         4          1          4
         5          1          4
         6          1          2
         7          1          2
         8          1          2
         9          1          2
        10          1          2

Der Test definiert eine Quelltabelle T, deren Spalte col_org durch eine Spalte col_upd ersetzt werden soll, außerdem wird der Wert für col2 behutsam erhöht. Dazu wird eine Hilfstabelle T_REDEFINE mit den gewünschten Spalten angelegt und anschließend die Prozedur dbms_redefinition.start_redef_table aufgerufen, der neben den Angaben der Ziel- und der Hilfstabelle ein column_mapping übergeben wird, das die inhaltliche Füllung der Spalten nach der Reorganisation definiert. In diesem Mapping sind leider keine skalaren Subqueries erlaubt - wenn man versucht l_colmap mit der Angabe:

l_colmap := 'id, (select 2 from dual) col_upd, padding ';

zu füllen, erhält man den Fehler

ORA-22818: Unterabfrage-Ausdrücke sind hier nicht zulässig

Demnach können im Rahmen der Redefinition offenbar keine komplexeren Join-Operationen zur Füllung einer verändert definierten Spalte eingesetzt werden. Möglich ist allerdings die Ableitung von Spalten (col2 + 2). Änderungen der Quelltabelle, die nach dem Start der Redefinition in anderen Sessions durchgeführt wurden, werden korrekt propagiert. Die technische Erklärung des Verhaltens liefert ein SQL-Trace, dem zu entnehmen ist, dass zur Tabelle T eine Materialized View T_REDEFINE mit fast refresh erzeugt wird:

create snapshot "TEST"."T_REDEFINE"   on prebuilt table with reduced 
  precision  refresh fast with primary key  as select id, 1 col_upd, col2 + 2 
  col2, padding  from "TEST"."T"   "T"

...

INSERT /*+ BYPASS_RECURSIVE_CHECK APPEND  */ INTO "TEST"."T_REDEFINE"("ID",
  "COL_UPD","COL2","PADDING") SELECT "T"."ID",1,"T"."COL2"+2,"T"."PADDING" 
  FROM "TEST"."T" "T" ORDER BY ID

Die aus der Quelltabelle T übernommenen Spalten werden somit aktualisiert und parallel durchgeführte Änderungen sind im Zielobjekt weiterhin sichtbar. Nicht möglich scheint allerdings die Aktualisierung unter Zuhilfenahme einer Referenz zu sein, die beim Massenupdate über CTAS zu meinen präferierten Vorgehensweisen gehört. Ein großer Vorteil von dbms_redefinition ist allerdings, dass die Prozeduren des Packages sich um die Behandlung abhängiger Objekte (Indizes, Trigger etc.) kümmern können.

Nachtrag 16.11.2016: Connor McDonald zeigt, wie man die hier nicht verwendbare Join-Operation durch eine deterministische Funktion ersetzen kann: damit wird dbms_redefinition dann extrem wertvoll.