Agregado de columnas con valor default (revisión de nuevo mecanismo interno en 11g)

Hace un tiempo me pidieron estimar el tiempo que llevaría agregar una columna a una tabla de 800 millones de registros. Cuando me hicieron esa pregunta inmediatamente les consulté si la columna a agregar iba a ser null ó not null con un valor default. Eso marcaba una gran diferencia ya que agregar una columna con un valor default implica que internamente ser ejecute un update con el consiguiente consumo de redo y undo además del lockeo DML propio del update. Desafortunadamente la versión de la base era 10g así que tuve que realizar una estimación en un ambiente de test similar. La operación demoró unas horas.
Ahora en 11g esa misma operación es casi instantánea ya que en lugar de actualizarse el valor default de la columna por cada fila se agrega una entrada en el diccionario de datos para almacenar el valor default. Cuando se realiza una consulta de la tabla y la columna en una fila determinada no tiene valor Oracle sabe que debe obtener ese valor del catalogo.

Voy a comparar este cambio de comportamiento interno comparando la misma operación entre bases 10g y 11g


Primero voy a crear una tabla T y luego la voy a ir copiando a si misma para si generar un par de millones de filas

rop@TEST10G> create table t as select * from dba_objects;

Tabla creada.

rop@TEST10G> insert into t select * from t;

92631 filas creadas.

rop@TEST10G> /

185262 filas creadas.

rop@TEST10G> /

370524 filas creadas.

rop@TEST10G> /

741048 filas creadas.

rop@TEST10G> /

1482096 filas creadas.

rop@TEST10G> select count(1) from t;

 COUNT(1)
----------
  2964192

rop@TEST10G> commit;

Confirmación terminada.


rop@TEST10G> set timing on
rop@TEST10G> alter table t add x varchar2(2) null;

Tabla modificada.

Transcurrido: 00:00:00.17

Vemos que agregar un columna x null demoró 17 decimas de segundo.

rop@TEST10G> alter table t add y varchar2(2) default 'E' not null;

Tabla modificada.

Transcurrido: 00:04:30.61

En cambio al agregar otra columna con valor default se demoró 4 minutos y medio.

Ahora voy a realizar lo mismo en 11g:

rop@TEST10G> conn rop@rop111
Introduzca la contraseña: ********
Conectado.
rop@ROP111> set timing on

Transcurrido: 00:01:29.95
rop@ROP111> alter table t add x varchar2(2) null;

Tabla modificada.

Transcurrido: 00:00:00.93
rop@ROP111> alter table t add y varchar2(2) default 'E' not null;

Tabla modificada.

Transcurrido: 00:00:00.20
rop@ROP111>

Como vemos el tiempo de agregado de la columna X fue similar al tiempo en 10g. Por el contrario el tiempo insumido en agregar la columna Y fue instantaneo comparado con los 4 minutos y medio que demoró en 10g.

Ahora comprobamos que efectivamente se haya agregado la columna con el valor 'E'

rop@ROP111> select count(1) from t where Y = 'E';

 COUNT(1)
----------
  2964192

Transcurrido: 00:00:08.65

Para mayor detalle se puede consultar la documentación oficial online en: http://download.oracle.com/docs/cd/B28359_01/server.111/b28310/tables006.htm#i1006666