Row Migration nach ALTER TABLE

Heute wurde ich von einem Kollegen nach meiner Einschätzung gefragt, welchen Effekt das Anhängen einer neuen Spalte an eine bereits existierende Tabelle mit Massendaten hat. Ich hatte dazu zwar eine Meinung und eine relativ starke Vermutung (nämlich dass die Effekte weitgehend denen eines entsprechenden Updates entsprechen sollten), die ich aber trotzdem durch einen Test absichern wollte:

-- Anlage einer Tabelle ohne vorgesehenen Platz für Satzerweiterungen
create table t1 pctfree 0
as
select rownum id
     , dbms_random.string('a', 1000) col1
  from dual
connect by level <= 10000;

-- Ergänzung einer neuen Spalte 

alter table t1 add col2 varchar2(100) default 
('aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa');

-- Analyze zur Bestimmung der Row Migration
analyze table t1 compute statistics;

-- Prüfung in user_tables
TABLE_NAME                       PCT_FREE   NUM_ROWS     BLOCKS  CHAIN_CNT
------------------------------ ---------- ---------- ---------- ----------
T1                                      0      10000        766       1250

Ich habe dann auch noch einen Block-Dump erstellt, der ebenfalls zeigt, dass die neue Spalte in die bestehenden Sätze integriert wird, was dann abhängig von den gegebenen Umständen (PCTFREE, COMPRESS etc.) zu Row Migration oder Row Chaning führen kann (wobei Chaining im gegebenen Fall nicht vorkommen dürfte, da die Einzelsätze sicher nicht zu groß für einen Block werden).

Das Ergänzen einer neuen Spalte ohne Daten ist übrigens offenbar eine reine Metadaten-Operation, die nahezu keine Arbeit verursacht, wie ein 10046er Trace zeigt:

alter table t1 add col3 varchar2(100)

call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        1      0.00       0.00          0          0          0           0
Execute      1      0.01       0.00          0          0          2           0
Fetch        0      0.00       0.00          0          0          0           0
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total        2      0.01       0.01          0          0          2           0

Misses in library cache during parse: 1
Optimizer mode: ALL_ROWS
Parsing user id: 48  

Elapsed times include waiting on following events:
  Event waited on                             Times   Max. Wait  Total Waited
  ----------------------------------------   Waited  ----------  ------------
  log file sync                                   2        0.00          0.00
  SQL*Net message to client                       1        0.00          0.00
  SQL*Net message from client                     1        0.00          0.00

-- auch die zugehörigen rekursiven Queries sind billig

In diesem Fall würde ein folgendes Update der neuen Spalte dann die gleichen Migrations-Effekte hervorrufen.

Wenn es wahrscheinlich ist, dass die ergänzte Spalte Row Migration hervorruft (also die Sätze um mehr als Prozent vergrößert), würde ich in einem solchen Fall eher einen kompletten Neuaufbau der Tabelle über CTAS vorschlagen.