Añadiendo columnas con valores por defecto y constraint NOT NULL en Oracle 11g

Antes de la versión 11g, cuando agregabamos una columna a una tabla con valores por defecto y una constraint del tipo NOT NULL, Oracle realizaba un loqueo exclusivo en la tabla para insertar los valores en cada uno de los registros existentes en la misma.

A partir de la versión 11g release 1, ésta operación se optimizó para mejorar en gran medida la utilización de los recursos del sistema y el espacio de almacenamiento de los nuevos valores. Oracle logra ésta optimización, guardando el valor por defecto en el diccionario de datos en vez de modificar todos los registros de la tabla, haciendo la ejecución de ésta operacion de manera instantánea. Gracias a ésto, obtenemos un beneficio enorme a la hora de modificar tablas con millones de registros.

Por otro lado, las siguientes operaciones ADD COLUMN ahora pueden ejecutarse de manera concurrente junto con las operaciones DML:
- Agregar una columna NOT NULL con un valor por defecto.
- Agregar una columna NULL sin un valor por defecto.
- Agregar una columna virtual.

Veamos un ejemplo:

- versión 9i release 2

SQL_9iR2> CREATE TABLE test
  2  NOLOGGING
  3  AS
  4  SELECT level id
  5  FROM dual
  6  CONNECT BY level <= 1000000;

Table created.

SQL_9iR2> ALTER TABLE test ADD (nombre VARCHAR2(100) DEFAULT 'LEONARDO_HORIKIAN' NOT NULL);

Table altered.

Elapsed: 00:01:17.62

SQL_9iR2> DESC test

 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 ID                                                 NUMBER
 NOMBRE                                    NOT NULL VARCHAR2(100)

SQL_9iR2> exec dbms_stats.gather_table_stats(USER, 'TEST');

PL/SQL procedure successfully completed.

SQL_9iR2> SELECT count(*)
  2  FROM test
  3  WHERE nombre IS NULL;

  COUNT(*)
----------
         0

- versión 11g release 1

SQL_11gR1> CREATE TABLE test
  2  NOLOGGING
  3  AS
  4  SELECT level id
  5  FROM dual
  6  CONNECT BY level <= 1000000;

Table created.

SQL_11gR1> ALTER TABLE test ADD (nombre VARCHAR2(100) DEFAULT 'LEONARDO_HORIKIAN' NOT NULL);

Table altered.

Elapsed: 00:00:00.17

SQL_11gR1> DESC test

 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 ID                                                 NUMBER
 NOMBRE                                    NOT NULL VARCHAR2(100)

SQL_11gR1> exec dbms_stats.gather_table_stats(USER, 'TEST');

PL/SQL procedure successfully completed.

SQL_11gR1> SELECT count(*)
  2  FROM test
  3  WHERE nombre IS NULL;

  COUNT(*)
----------
         0

Como podemos observar, creamos una tabla con 1 millón de registros y luego agregamos una columna con valores por defecto. En 9iR2, esa operación se ejecutó en 1 minuto 17 segundos; en cambio en 11gR1, se ejecutó de manera instantánea en tan solo 17 milisegundos!!!

IMPORTANTE: Tengan en cuenta que existen bugs en 11g (leer nota Metalink 602327.1) relacionados con éste tipo de operación.