jueves, 13 de septiembre de 2007

Modificar millones de registros de una tabla

Muchas veces me hacen la siguiente pregunta: ¿ Cómo puedo modificar de manera eficiente los datos de una tabla que contiene millones de registros sin tener impacto en la performance del sistema ?

Suele ocurrir, que la solución que se utiliza es realizar un simple UPDATE. Si hablamos de performance, la ejecución de ésta sentencia puede tener un gran impacto en la base de datos. Porqué? porque principalmente, generamos muchísimos bytes de redo y undo.

Si tengo que modificar millones de datos de una tabla, optaría por NO modificarlos.
Implementaría la siguiente estrategia.

Veamos un ejemplo:

Supongamos que tenemos una tabla llamada TEST que contiene 5 millones de registros, una primary key y un índice único:

SQL_9iR2> CREATE TABLE test NOLOGGING PARALLEL 4 AS
2 SELECT level id, 'nom_'||level nom
3 FROM dual
4 CONNECT BY level <= 5000000 ;

Table created.

Elapsed: 00:00:16.06

SQL_9iR2> ALTER TABLE test ADD CONSTRAINT test_id_pk PRIMARY KEY (id) NOLOGGING PARALLEL 4 ;

Table altered.

Elapsed: 00:00:14.03

SQL_9iR2> CREATE UNIQUE INDEX test_id_nom_uq ON test(id,nom) NOLOGGING PARALLEL 4 ;

Index created.

SQL_9iR2> SELECT *
2 FROM test
3 WHERE rownum <= 10 ;

ID NOM
---------- --------------
37011 nom_37011
37012 nom_37012
37013 nom_37013
37014 nom_37014
37015 nom_37015
37016 nom_37016
37017 nom_37017
37018 nom_37018
37019 nom_37019
37020 nom_37020

10 rows selected.

Si nosotros quisiéramos modificar los datos de la columna NOM, no vamos a utilizar la cláusula UPDATE, sino que vamos a realizar los siguientes pasos:

1) Lo primero que vamos a hacer es crear una tabla con las modificaciones que queremos realizar. En nuestro caso dijimos que vamos a modificar la columna NOM. Fijense que cuando creé la tabla, esa columna está en minúsculas. El cambio que vamos a realizar es colocar el contenido en mayúsculas:

SQL_9iR2> CREATE TABLE test_nueva NOLOGGING PARALLEL 4 AS
2 SELECT id, UPPER(nom) nom
3 FROM test ;

Table created.

Elapsed: 00:00:03.05

Como podemos observar, lo que hice fue colocar en el SELECT los cambios que quería realizar. En sólo 3 segundos tenemos nuestra tabla nueva creada y con los cambios realizados.

2)Agregamos las constraints a la nueva tabla y sus respectivos índices:

SQL_9iR2> ALTER TABLE test_nueva ADD CONSTRAINT test_id_pk_2 PRIMARY KEY(id) NOLOGGING PARALLEL 4 ;

Table altered.

Elapsed: 00:00:22.05

SQL_9iR2> CREATE UNIQUE INDEX test_id_nom_uq_2 ON test_nueva(id,nom) NOLOGGING PARALLEL 4 ;

Index created.

Elapsed: 00:00:20.03

3) Dropeamos la tabla TEST:

SQL_9iR2> DROP TABLE test ;

Table dropped.

Elapsed: 00:00:00.02

4) Modificamos el nombre de la tabla TEST_NUEVA, las constraints e índices:

SQL_9iR2> ALTER TABLE test_nueva RENAME TO test ;

Table altered.

Elapsed: 00:00:00.03

SQL_9iR2> ALTER INDEX test_id_nom_uq_2 RENAME TO test_id_nom_uq ;

Index altered.

Elapsed: 00:00:00.02

SQL_9iR2> ALTER TABLE test RENAME CONSTRAINT test_id_pk_2 TO test_id_pk ;

Table altered.

Elapsed: 00:00:00.02

5) Por último, granteamos los permisos que teníamos en la tabla vieja a la nueva tabla.

Para crear los objetos de éste ejemplo, utilicé las cláusulas NOLOGGING y PARALLEL. La cláusula NOLOGGING la utilicé para generar muy poco redo y nada de undo.
La cláusula PARALLEL la utilicé para paralelizar (a través de 4 CPU's en nuestro ejemplo) las operaciones que ejecutamos sobre los objetos.
En caso de que queramos volver a colocar la tabla en modo LOGGING y NOPARALLEL, ejecutaríamos...

SQL_9iR2> ALTER TABLE test LOGGING NOPARALLEL ;

Table altered.

Elapsed: 00:00:00.01

Cómo quedaron los datos de nuestra tabla? Veamos algunos registros...

SQL_9iR2> SELECT *
2 FROM test
3 WHERE rownum <= 10 ;

ID NOM
---------- --------------
37011 NOM_37011
37012 NOM_37012
37013 NOM_37013
37014 NOM_37014
37015 NOM_37015
37016 NOM_37016
37017 NOM_37017
37018 NOM_37018
37019 NOM_37019
37020 NOM_37020

10 rows selected.

Como podemos observar, realizamos las modificaciones que necesitábamos sin tener un impacto en la performance del sistema.

9 comentarios:

Diego Arenas C. dijo...

Super buen dato, se agradece. Estoy empezando en estos temas de bases de datos y me gusta mucho el diseño y modelamiento de éstas.

Saludos,

Humberto dijo...

Vamos a utilizar este procedimiento en nuestra empresa. Muchas Gracias Leonardo, tus informaciones son excelentes, Sigue así. Exito!!!.

Maria dijo...

Excelente tu idea... pero existe otra solución para el mismo planteamiento? pero con las siguientes variantes: no hay espacio en Disco Duro para crear una copia de la tabla, la cual en mi caso tiene 8Gb y mas de 13 millones de registros, no se puede aumentar el Table Space y tampoco tengo permisos de administrador de BD, pero existe la necesidad de cambiar la data de la mayoria de esos registros.
Muchisimas gracias

Leonardo Horikian dijo...

Hola Maria,

Podrías realizar los siguientes pasos para éste caso:

1.- Realizar un Backup.
2.- Colocar la tabla en modo NOLOGGING (para que genere la menor cantidad de Redo posible).
3.- Deshabilitar los índices de la tabla.
4.- Realizar el update de los registros.
5.- Habilitar los índices y realizar un rebuild.
6.- Colocar la tabla nuevamente en modo LOGGING.
7.- Realizar un Backup.

Saludos!

Patricio Gonzalez dijo...

Hola... me identifico... Patricio Gonzalez desde Chile. Te felicito, me parecen geniales los tips que entregas. Puntualmente este caso (UPDATE de millones de registros) me servia muchisimo, hasta que vi que era valido para cuando uno necesita modificar la tabla completa. Tienes algun otro tip similar a este, es decir, donde se modifiquen millones de registros, pero no la totalidad de los registros de una tabla?
Reitero mis felicitaciones por que efectivamente tus sugerencias son bastente buenas.
Atentamente, Patricio Gonzalez
patogonz@hotmail.com

Anónimo dijo...

Hola q tal Leonardo... aver si me puedes ayudar...

quiero insertar 2 millones de registros a una nueva tabla a partir de un query.... probe con el insert obviamente se demora horas de horas...utilice el bulk collect con forall y ahi mejoro el asunto pero igual se demora mucho..

me podrias dar alguna otra sugerencia de antemano agradecido

Miguel López
Guayaquil - Ecuador

Leonardo Horikian dijo...

Hola Miguel,

1) Primero debes verificar que el query sea performante. Ya que a lo mejor, el insert no es el problema, sino el query. Si el query demora, obviamente, el insert también va a demorar.

2) Una vez que hayas verificado que el query se ejecuta de manera performante, puedes hacer lo siguiente. Debido a que estás realizando un insert masivo, puedes realizar esa operación en modo NOLOGGING ya que un insert masivo genera mucho espacio de Redo y ésto puede ser el factor por el cual la operación está demorando mucho. Para generar la menor cantidad de Redo posible, lee el post http://lhorikian.blogspot.com/2007/08/append-hint.html

Realiza la operación en una sola sentencia!!! Es decir, realiza un INSERT ... SELECT ...
No utillices Bulk Collect ni Forall ya que demorarás más.

Una vez que hayas realizado éstos pasos, prueba nuevamente y cuéntame cómo te fue!

Saludos

Anónimo dijo...

Leonardo solo pasaba para aplaudir:

Segui tus consejos el query estaba bien ... pero segui el link q pusiste y solucione mi problema

muchas gracias

Antonino dijo...

Excelente Leonardo, Ojala hubiera muchos mas sites como este. Como decimos en Panamá. Estoy Creyendo.