Sobre Redefinición, Naturaleza y Triggers

por Tom Kyte

Nuestro experto en tecnología redefine tablas, compara claves y advierte sobre DDL en triggers.

Tengo una tabla que contiene millones de registros, y necesito actualizarla regularmente. Quiero realizar una ACTUALIZACIÓN (UPDATE) y una VALIDACIÓN (COMMIT) para cada, digamos, 10.000 registros. No quiero hacer esto en un solo paso, porque puedo encontrarme con problemas en el segmento rollback. ¿Alguna sugerencia?

Bueno, esto es mucho más complejo de lo que parece. Supongamos que desglosa esta transacción en muchas pequeñas, y a mitad de camino, algo, como un error ORA-01555 ( snapshot demasiado antigua) causado por su constante validación o una falla del sistema, sale mal. Entonces ahora su actualización batch queda parcialmente finalizada—está "a mitad de camino"—y necesita reiniciarse. A menos que haya escrito muchos códigos para hacer que se reinicie, puede encontrarse ante un gran problema. ¿Cómo reanuda la tarea donde la dejó?

Por ejemplo, el problema con códigos como este: 

declare
        cursor c is select * from t;
begin
        open c;
        loop
                fetch c bulk collect 
                into l_data limit 500;
                 
                         ... some process ...
                forall I in 1 .. l_data.count
                        update t set ... 
                /* using l_data */
                commit;
                exit when c%notfound;
        end loop;
        close c;
end;
                      

Además, cuando se encuentra con el error ORA-01555 y el bloque de código falla, ¿cómo lo reinicia? Puede necesitar una columna en esa tabla que informe si se ha realizado alguna actualización grande u otra tabla de seguimiento en la cual haya insertado las claves primarias de filas ya modificadas, utilizando "SELECT * FROM T WHERE PK NOT IN (seleccionar pk desde tracking_table)" o un enfoque similar para hacer esto.

Por lo tanto, debe escribir muchos códigos para lograr esto. Mi preferencia sería hacer una de las siguientes opciones:

• Simplemente actualice la tabla en una sola sentencia. Este enfoque será el más eficiente en términos de uso de recursos. La preocupación sobre posibles problemas en segmentos rollback se ve compensada por el hecho de que necesita ese undo para que su consulta tenga éxito.
• Use DBMS_REDEFINITION para procesar la actualización. Esto hará surgir la posible ventaja de una operación online (sin conflictos de bloqueo—lo cual puede ser bueno o malo, según las circunstancias). También evitará cualquier migración de filas que pueda ocurrir con un UPDATE y que “agrande” las filas más que antes. Dará como resultado las estructuras de datos más compactas. Puede incluso agregar ORDER BY a la redefinición de tabla en Oracle Database 10g y superior para resecuenciar las filas en disco.
• Use CREATE TABLE AS SELECT para seleccionar los "datos actualizados" en una nueva tabla. Esto es similar a la opción DBMS_REDEFINITION pero es una operación offline (de manera que no se permiten modificaciones en la tabla de origen mientras realiza esto) y es más manual. Mientras que la opción DBMS_REDEFINITION automatiza la creación de índices, autorizaciones y restricciones—todo lo que usted necesita para crear una copia de un objeto—el método CREATE TABLE AS SELECT requiere hacer todo esto manualmente.

Ahora veamos cómo utilizar DBMS_REDEFINITION para crear una "copia actualizada" de una tabla. El objetivo es crear una nueva columna que sea una concadenación de tres columnas existentes, librar a la tabla de las tres columnas existentes y secuenciar las filas en disco en otra columna (para que los datos existentes se clasifiquen en esa columna en el disco). Empezaré con una copia de ALL_OBJECTS para probar, como se muestra en la Lista 1.

Lista de Códigos 1: tabla inicial T

SQL> create table t
  2    as
  3    select OWNER, OBJECT_NAME, SUBOBJECT_NAME,
  4         OBJECT_ID, DATA_OBJECT_ID, OBJECT_TYPE,
  5         CREATED, LAST_DDL_TIME, TIMESTAMP, STATUS,
  6         TEMPORARY, GENERATED, SECONDARY
  7      from all_objects
  8    order by dbms_random.random;
Table created.

SQL> alter table t
  2    add constraint t_pk
  3    primary key(object_id);
Table altered.

SQL> select object_name
  2    from t
  3    where rownum <= 5
  4    /

OBJECT_NAME
--------------------
java/rmi/MarshalException
SYS_C008650
ALL_IDENTIFIERS
/5ed18cf1_SimpleAuthPopupBasic
/1cca9769_MonitoredObjectImpl

Entonces, tengo una tabla T con una restricción (y posiblemente más, como autorizaciones, índices, triggers, etc.). Como se puede ver en la Lista 1, los datos se almacenan al azar en el disco—definitivamente no se clasifican por OBJECT_NAME, como muestra el escaneo completo de tabla que empecé con SELECT * FROM T. Me gustaría tomar tres columnas—TEMPORARY, GENERATED y SECONDARY—y concadenarlas en una nueva columna FLAGS. Asimismo, quisiera "eliminar" las columnas TEMPORARY, GENERATED y SECONDARY, así como las columnas SUBOBJECT_NAME, DATA_OBJECT_ID y TIMESTAMP de la nueva tabla, y por último, organizar los datos existentes por OBJECT_NAME. Tenga en cuenta que cualquier dato recientemente incorporado no será almacenado en orden en la tabla.

SQL> create table t_interim
  2   (
  3     object_id          number,
  4     object_type       varchar2(18),
  5     owner               varchar2(30),
  6     object_name      varchar2(30),
  7     created             date,
  8     last_ddl_time     date,
  9     status               varchar2(7),
 10     flags                 varchar2(5)
 11   )
 12   /
Table created.

SQL> declare
  2      l_colmap varchar(512);
  3    begin
  4      l_colmap :=
  5           'object_id,
  6            object_type,
  7            owner,
  8            object_name ,
  9            created,
 10            last_ddl_time,
 11            status,
 12            temporary || ''/'' ||
 13            generated || ''/'' ||
 14            secondary flags ';
 15
 16      dbms_redefinition.start_redef_table
 17      (  uname           => user,
 18         orig_table      => 'T',
 19         int_table       => 'T_INTERIM',
 20         orderby_cols   => 'OBJECT_NAME',
 21         col_mapping   => l_colmap );
 22   end;
 23   /
PL/SQL procedure successfully completed.

INSERT 
/*+ BYPASS_RECURSIVE_CHECK APPEND  */ 
INTO "OPS$TKYTE"."T_INTERIM"
("OBJECT_ID","OBJECT_TYPE","OWNER",
"OBJECT_NAME", "CREATED",
"LAST_DDL_TIME","STATUS","FLAGS")
SELECT "T"."OBJECT_ID",
"T"."OBJECT_TYPE",
"T"."OWNER","T"."OBJECT_NAME",
"T"."CREATED","T"."LAST_DDL_TIME",
"T"."STATUS",
"T"."TEMPORARY"||'/'||
"T"."GENERATED"||'/'||
"T"."SECONDARY" 
FROM "OPS$TKYTE"."T" "T" 
ORDER BY OBJECT_NAME

Tenga en cuenta que las columnas TEMPORARY, GENERATED, SECONDARY, SUBOBJECT_NAME, DATA_OBJECT_ID y TIMESTAMP no se copian en la tabla T_INTERIM, pero las columnas TEMPORARY, GENERATED y SECONDARY se concadenan en la nueva columna FLAGS.

Debido a que ese INSERT incluye APPEND, usted no solo puede eludir UNDO (que se produce por defecto con APPEND—ningún undo es generado para la tabla T_INTERIM durante esta carga inicial) sino que también, si quiere, puede eludir la generación de REDO para esta tabla alterándola para que sea NOLOGGING antes de realizar el procedimiento START_REDEF_TABLE. (si elude la generación de REDO, asegúrese de coordinarlo con el grupo responsable de los backups antes de hacerlo. Necesitarán programar un backup del archivo con datos afectados poco después de esta operación para que los nuevos datos sean recuperables).

Ese procedimiento START_REDEF_TABLE que invoqué recién realizó varias cosas. Copió los datos de T a T_INTERIM, copió solo los datos de interés, clasificó los datos durante la carga y lo hizo todo eficientemente (eludiendo UNDO y opcionalmente REDO). También configuró la replicación suficiente entre T y T_INTERIM para permitirme mantenerlas en sincronización, para que al final de la redefinición, las dos tablas sean lógicamente equivalentes—tienen la misma cantidad de filas.

SQL> variable nerrors number

SQL> begin
  2    dbms_redefinition.copy_
table_dependents
  3    ( user, 'T', 'T_INTERIM',
  4      copy_indexes => dbms_
redefinition.cons_orig_params,
  5      num_errors => :nerrors );
  6  end;
  7  /
PL/SQL procedure successfully completed.

SQL> print nerrors

   NERRORS
------
             0

SQL> begin
  2        dbms_redefinition.finish_
redef_table
  3       ( user, 'T', 'T_INTERIM' );
  4    end;
  5    /
PL/SQL procedure successfully completed.

SQL> select object_name, flags
  2    from t
  3   where rownum <= 5;

OBJECT_NAME                    FLAGS
-----------------              ---
/1000323d_DelegateInvocationHa N/N/N
/1000323d_DelegateInvocationHa N/N/N
/1000e8d1_LinkedHashMapValueIt N/N/N
/1000e8d1_LinkedHashMapValueIt N/N/N
/1005bd30_LnkdConstant         N/N/N

Como puede ver, la tabla T ahora es la tabla actualizada, con la nueva columna FLAGS y con las filas existentes en la tabla, clasificadas en el disco por OBJECT_NAME.

Como se mencionó anteriormente, usar CREATE TABLE AS SELECT para seleccionar los datos de una nueva tabla podría producir un resultado similar a utilizar DBMS_REDEFINITION, pero el proceso, incluida la creación de índices, autorizaciones y restricciones, sería más manual. No obstante, en la edición estándar de Oracle Database, donde la redefinición online no está disponible, usar CREATE TABLE AS SELECT podría ser la forma más eficiente de lograr esto.

Claves Naturales o Sustitutas

El proyecto en el que actualmente trabajo tiene la regla, una clave sustituta. Esto es requerido incluso si existe una clave natural perfectamente útil.

La motivación primaria parece ser mejorar la eficiencia conjunta al eliminar la posibilidad de tener que unir dos tablas en más de una columna.

Personalmente, no soy partidario de claves sustitutas en general ni del tipo de política global en particular. Creo que se pueden obtener muchos beneficios al hacer que la tabla secundaria herede la clave primaria de la tabla principal como parte de la clave primaria de la tabla secundaria.

¿Tiene algún comentario sobre claves naturales vs. claves sustitutas en general? ¿Prefiere las claves sustitutas en lugar de las claves naturales? ¿Cuán estrictas deberían ser las consideraciones de desempeño para justificar dicho esquema?

Ah, el debate ancestral—uno que ninguna de las dos partes “ganará” nunca. Tener una regla como esta es el ejemplo perfecto de por qué no me agrada el término mejores prácticas, ni ROTs ( rules of thumb). La mejor práctica de una persona es la pesadilla de otra.

Si tiene una clave natural, úsela. La clave natural debería ser inalterable y sensible; algunas veces son poco comunes en la vida real, pero existen.

Por ejemplo, si tuviese una tabla DOCUMENT y una tabla DOCUMENT_VERSION, definitivamente usaría document_id (que podría ser sustituta) como clave primaria en una tabla y document_id,version# combinado como la clave primaria de la otra tabla (y DOCUMENT_VERSION podría estar asociado a los autores, entonces su clave primaria es una clave foránea en cualquier parte del sistema).

create table document 
( document_id number primary key, 
  -- populated by a sequence perhaps
  other_data...
);

create table document_version
( document_id references document,
  version# number,
  other_data ...
  constraint doc_ver_pk primary key
  (document_id,version#)
);

por los motivos mencionados. Sin embargo, algo de lo que sí tiene que estar muy seguro es de que la clave primaria es inalterable. No cambia nunca. En este caso, la clave sustituta de la tabla DOCUMENT es inalterable, y la clave natural (que incluye una clave sustituta de otro elemento) de la tabla DOCUMENT_VERSION, también.

DDL en Triggers

Todo lo que he leído sobre triggers expone explícitamente que DDL, debido a un COMMIT implícito, no puede utilizarse dentro de un trigger. No obstante, he visto en blogs y otros lugares que la gente afirma haberlo hecho funcionar mediante varios programas pirata, pero nunca he tenido éxito haciéndolo. Tengo dos preguntas respecto de esto:

1. ¿Por qué el uso de pragma autonomous_transaction no resuelve esto en un procedimiento invocado desde un trigger?

2. ¿Conoce alguna solución para ejecutar DDL en un procedimiento invocado desde un trigger?

Primero, piense por un momento en las ramificaciones de hacer un trabajo no transaccional en un trigger. ¿Qué sucede cuando necesita hacer rollback? Bueno, por supuesto, DDL no haría un roll back—quedaría "a mitad de camino". DDL se hubiese producido, pero la transacción que causó DDL, no (habiéndose producido el rollback).

Estaría frente a un problema.

Cuando tenga la tentación de hacer algo no transaccional en un trigger, piense 500 veces en ello, y luego siempre decida en contra. Solo puede conducir a malas consecuencias.

Si utiliza DBMS_JOB, se verá algo como esto: 

SQL> create table do_ddl
   2    ( job number primary key,
   3      stmt varchar2(4000)
   4    );
Table created.

SQL> create or replace
   2    procedure do_ddl_safely 
       (  p_job in number )
   3    is
   4        l_rec do_ddl%rowtype;
   5    begin
   6       select *
   7         into l_rec
   8         from do_ddl
   9        where job = p_job;
 10      execute immediate l_rec.stmt;
 11   end;
 12   /
Procedure created.

Y luego utilizará un bloque de código similar al siguiente para invocar el procedimiento DDL poco después de haber validado su transacción: 

SQL> declare
  2        l_job number;
  3    begin
  4        dbms_job.submit
  5        ( l_job, 
              'do_ddl_safely(JOB);' );
  6         insert into do_ddl
  7         ( job, stmt ) values
  8         ( l_job, '...The statement 
              to be executed...' );
  9    end;
 10     /

DO_DDL_SAFELY es un procedimiento almacenado que se escribe, y que realiza DDL, encuentra errores en caso de ser necesario, notifica a las personas y hace correctamente lo que haga falta. Se ejecutará poco después de su validación.

Y lo mejor de todo es que si hace un roll back, INSERT en la cola de trabajo también lo hace. Está a salvo—DDL no ocurrirá. Use este enfoque en cualquier momento que quiera hacer algo no transaccional en un trigger.

V ea la serie sobre un tema relacionado (consistencia de escritura), que incluye cómo los triggers pueden lanzarse más de una vez para una sentencia determinada—otra razón para evitar las operaciones no transaccionales en un trigger:

• tkyte.blogspot.com/2005/08/something-different-part-i-of-iii.html
• tkyte.blogspot.com/2005/08/part-ii-seeing-restart.html
• tkyte.blogspot.com/2005/09/part-iii-why-is-restart-important-to.html

Tom Kyte es un evangelista de base de datos en la división de Tecnología para Servidores Oracle y ha trabajado para Oracle desde 1993. Es el autor de Expert Oracle Database Architecture: 9i and 10g Programming Techniques and Solutions (Apress, 2005) y Effective Oracle by Design (Oracle Press, 2003), entre otros.