Como realizar update/delete masivos en forma efectiva

En esta nota voy a mostrarles un método efectivo para modificar o eliminar una gran cantidad de filas sobre una tabla grande. En general las tablas voluminosas se encuentran particionadas para lograr escalar en forma natural. El particionamiento principalmente provee 3 tipos de beneficios: 1) mejora la performance, 2) facilita la administración y mantenimiento y 3) incrementa la disponibilidad de los datos. Resolver una consulta usando como tabla subyacente particionada puede verse de la misma forma que resolver un problema dividiendolo en partes. La conocida premisa: divide y conquistaras es el principal objetivo detrás de particionar.

Desde que se introdujo el feature de partitioning (Oracle 8) se ha ampliado notablemente el set de operaciones posibles sobre tablas e indices para dar soporte y manejar las tablas/indices particionados. Con cada nuevo release se fueron agregando distintas opciones, métodos de particionado y operaciones para manipulación de segmentos. Los distintos features introducidas en cada release son:

Oracle 8 (1997)

• Partition Pruning (*)
• Range Partitioning (incluye operaciones ADD, DROP, RENAME, TRUNCATE, MODIFY, MOVE, SPLIT y EXCHANGE)

Oracle 8i (1999)

• Particionamiento Hash
  
• Particionamiento compuesto: range/hash
  
• Se agregó la operación MERGE

Oracle 9i R2 (2002)

• List Partitioning
• Particionamiento compuesto: Range/List
  
• Cláusula UPDATE GLOBAL INDEXES
  

Oracle 10g R1 (2004)

• Indices globales particionados por Hash y List
  

Oracle 10g R2 (2005)

• Se incremento el limite de particiones/subparticiones de 65k a 4M

Oracle 11g R1 (2007)

• Particionamiento compuesto: range-range, list-range, list-list y list-hash.
• Se agregó particionamiento por intervalo, por referencia y de sistema.

Oracle 11g R2 (2009)

• Columnas virtuales como primary key para tablas particionadas referenciadas.
• Indices particionados por sistema para tablas particionadas por lista.

Como se puede ver, practicamente en cada nuevo release hubo algún agregado de nueva funcionalidad. Sin embargo, a mi entender, el principal feature existe desde el primer release con partitioning (1997). Me refiero al partition pruning o poda de partición, que posibilita que el optimizador (siempre hablando de CBO) elija en forma automática, precisa y transparente la partición o particiones donde se encuentra los datos requeridos. Esto permite segmentar los datos y solo procesar los que nos interesan, sin tener que agregar ninguna inteligencia adicional en el código de aplicación.

Con respecto a las operaciones, la gran mayoria existen desde Oracle 8, solo se agregó tiempo después el MERGE. Una operación muy interensante es EXCHANGE, con la cual se puede intercambiar una tabla sin particionar con una partición. Justamente es esta la operación que voy a usar para proponer una alternativa rapida para cambiar o borrar gran cantidad de filas sobre tablas particionadas. A continuación, somo suelo hacer, voy a mostrar los pasos en detalle y comparar los tiempos y uso de recursos:

Voy a crear una tabla T particionada por lista con 3 particiones

create table t(c1 int,c2 varchar2(10),
c3 date,
c4 char(1))
partition by list (c4)
(
partition t_a values ('A') ,
partition t_b values ('B') ,
partition t_c values ('C')
);

Ahora voy a insertar 10M de filas distribuidas en forma arbitraria sobre las particiones:

insert into t
select rownum,
dbms_random.string('a',10),
sysdate-dbms_random.value(-100,100),
chr(trunc(dbms_random.value(65,68)))
from dual
connect by rownum <= 10000000;                

Inserto 5M de filas sobre la partición en la que voy a trabajar para tener mas filas:

insert into t
select rownum+10000000,          
dbms_random.string('a',10),
sysdate-dbms_random.value(-100,100),
'A'  from dual
connect by rownum <= 5000000;         

Luego de cargados todos los valores se confirman (commit) y luego se recolectan estadisticas.
Veamos el plan para una consulta que cuenta filas sobre la partición 1 (t_a):

explain plan for
select count(1)
from t where c2 > 'R' and c4 = 'A';

select * from table(dbms_xplan.display);

PLAN_TABLE_OUTPUT
---------------------------------------------------------------------------------------------------
Plan hash value: 2901716037

-----------------------------------------------------------------------------------------------
| Id  | Operation              | Name | Rows  | Bytes | Cost (%CPU)| Time     | Pstart| Pstop |
-----------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT       |      |     1 |    13 |  8455   (3)| 00:03:04 |       |       |
|   1 |  SORT AGGREGATE        |      |     1 |    13 |            |          |       |       |
|   2 |   PARTITION LIST SINGLE|      |  5588K|    69M|  8455   (3)| 00:03:04 |     1 |     1 |
|*  3 |    TABLE ACCESS FULL   | T    |  5588K|    69M|  8455   (3)| 00:03:04 |     1 |     1 |
-----------------------------------------------------------------------------------------------

Claramente se observa que el optimizador solo accedió la partición 1. Ejecutando la consulta vemos que la estimación del optimizador fue buena:

select count(1)
from t
where c4 = 'A' and c2 > 'R';



COUNT(1)
----------
5610297

El total de filas de la partición es:

select count(1)
from t
where c4 = 'A' ;

COUNT(1)
----------
8333946

En este punto, ya tenemos una partición con mas de 8.3M de filas de las cuales vamos a modificar 5.6M, lo cual es mas del 67%.
Primero voy a testear un update normal sobre la tabla T para luego realizar la comparativa con la misma modificación pero usando otro enfoque mas eficiente.

update t
set c3 = c3+1
where c4 = 'A'
and c2 > 'R'

5610297 filas actualizadas.

Transcurrido: 00:04:45.37

La modificación demoró 4' 45". Pensemos que la base de datos debe mantener la consistencia para garantizar la lectura consistente (mediante el UNDO) y persistir los cambios para poder recuperarse si un evento de falla ocurre durante la modificación (REDO). Estos mecanismos provocan que los tiempos se incrementen y se genere información adicional.

Revisemos cuanto espacio de UNDO y REDO se necesitó para realizar el update:

select 'REDO_SIZE',
round(ms.value/1024/1024) value
from v$mystat ms,
v$statname sn
where ms.STATISTIC# = sn.STATISTIC#
and sn.NAME = 'redo size'
union all
SELECT 'UNDO_SIZE',
t.used_ublk*8/1024 value
FROM v$transaction t, v$session s
WHERE t.addr = s.taddr
AND s.audsid = userenv('sessionid')

REDO_SIZE  2489 Mb
UNDO_SIZE   885 Mb

Para modificar 5.3M se necesitaron 2489Mb de redo y 885Mb de undo!!!. En el ejemplo, la tabla no tiene indices. Si tuviera indices y la columna modificada sea parte de las columnas de indexación se generaría mas redo y undo, y además la sentencia tendría que actualizar los indices por cada fila modificada lo cual provocaría que el update demore bastante mas. Si el procesamiento masivo fuera un delete en lugar de un update, se generará mas undo (el delete es la operación dml que mas undo genera) y se tendrá que mantener balanceados los indices, lo cual implica mas tiempo de procesamiento.

Existe una forma mas sencilla de realizar el update usando la operación estrella de partitioning: EXCHANGE. Antes de usar el exchange tenemos que crear una tabla auxiliar (T_A) y para acelerar la creación configuro la tabla como nologging e inserto en forma directa usando el hint APPEND.

create table t_a_aux nologging as
select /*+ APPEND */
c1,
c2,
case when (c2>'R') then c3+1
else c3 end c3,
c4
from t
where c4 = 'A'

Transcurrido: 00:00:22.04

Solo se necesitaron 22" para insertar la filas en la tabla auxiliar. Con la función DECODE o CASE realizo el cambio simulando el update. Ahora solo resta realizar el intercambio entre la tabla auxiliar y la partición t_a con la operación EXCHANGE:

ALTER TABLE t
EXCHANGE PARTITION t_a
WITH table t_a_aux ;

Transcurrido: 00:00:11.46

El exchange se realizó en casi 12". Sumando la creación de la tabla auxiliar y el exchange, todo demoró solo 44"!!!, es decir mas de 6 veces mas rapido que el update tradicional.
Ejecutando la consulta para obtener el espacio de redo y undo generado se obtiene:

REDO_SIZE     1 Mb
UNDO_SIZE     0 Mb

Practicamente no hubo alocación de undo/redo. Por lo cual, para ciertos casos resulta muy util usar este metodo para actualizar dado que los tiempos de procesamiento se reducen sensiblemente y ademas los requerimientos de undo y redo son minimizados casi por completo.

Para eliminar (delete) en forma masiva, la creación de la tabla auxiliar solo deberá llenarse con las filas que no se borran. Si se necesitara borrar muchas filas de una tabla no particionada se podrá utilizar el mismo enfoque, es decir reemplazar el delete por un insert en una tabla nueva, recrear los indices y renombrar.