martes, 20 de octubre de 2009

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.

5 comentarios:

Anónimo dijo...

¿Como podríamos hacerlo en versiones anteriores a Oracle11g?

Muchas gracias y Enhorabuena por tu blog!!!

Andrés dijo...

Una consulta, necesito agregar un valor por defecto a una columna, como se hace?
Tengo una tabla así:

mi_tabla(
a NUMBER(10) null,
...
x VARCHAR2(1) null,
...,
CHECK ( x IN ( 'I' , 'O' , 'U');

Quiero que el campo x pase a tener valor por defecto 'I'.
He probado haciendo:

alter table mi_tabla
MODIFY (x VARCHAR2(1) DEFAULT 'I' );

El problema que tengo es que la hacer eso me quedan mal compilados todos los triggers que tiene la tabla.
Alguien sabe porque? como puedo evitar este problema?
Gracias

Leonardo Horikian dijo...

Hola Andrés,

Es correcto que al alterar una tabla, los objetos asociados a esta se descompilen (en tu caso, los triggers).
Esto es debido a que la tabla es el objeto base de los objetos que se relacionan con esta (triggers, procedimientos, funciones, etc) y al modificarse causa que los objetos asociados se descompilen.
Cuando realices el alter en la tabla para colocar un valor por defecto, lo que tienes que hacer luego es recompilar los triggers que te quedaron descompilados.

Ejemplo:

SQL> CREATE TABLE test AS
2 select level id
3 from dual
4 connect by level <= 10;

Table created.

SQL> CREATE TRIGGER trigger_test
2 BEFORE INSERT OR UPDATE OR DELETE OF id ON test
3 FOR EACH ROW
4 BEGIN
5 UPDATE test
6 SET id = id*100
7 WHERE id = :new.id;
8 END;
9 /

Trigger created.

SQL> SELECT status FROM user_objects WHERE object_name = 'TRIGGER_TEST';

STATUS
-------
VALID

SQL> ALTER TABLE test MODIFY id DEFAULT 0000;

Table altered.

SQL> SELECT status FROM user_objects WHERE object_name = 'TRIGGER_TEST';

STATUS
-------
INVALID

SQL> ALTER TRIGGER trigger_test COMPILE;

Trigger altered.

SQL> SELECT status FROM user_objects WHERE object_name = 'TRIGGER_TEST';

STATUS
-------
VALID

SQL> SELECT column_name, data_default FROM user_tab_columns WHERE table_name = 'TEST';

COLUMN_NAME DATA_DEFAULT
------------------------------ ---------------
ID 0000


Saludos

Manuel dijo...

Hola, muy buenos los articulos que presentas, son de mucha utilidad.
Te hago una consulta... probaste el Linux de Oracle (unbreakable)? si lo hiciste, que experiencia te dejo?

saludos,

Manuel

Anónimo dijo...

Muy útil, muchas gracias.



http://recursossassoftware.blogspot.com