TECNOLOGÍA: Pregúntele a Tom


Sobre Redefinición, Naturaleza y Triggers


por Tom Kyte Oracle ACE

 

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;
                      
es que las probabilidades de un ORA-01555 son muy altas, porque usted está leyendo la tabla que está modificando, y SELECT * FROM T debe producirse a partir del momento en el que se inicia la consulta. A medida que modifica la tabla, aumenta la probabilidad de un ORA-01555—y usted es la causa de ello. Es probable que SELECT en la tabla T necesite el proceso de “cancelación” ( undo), generado con su UPDATE de la tabla T; no obstante, cuando ejecuta un COMMIT, hace posible que la base de datos reutilice el undo generado—y si es así (porque la retención undo está configurada demasiado baja o porque hay espacio undo insuficiente asignado para contener todas las “cancelaciones” que está generando), casi con seguridad recibirá el error ORA-01555.

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.

Para lograr esto, necesitaré una tabla provisional para copiar los datos existentes a:
 
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.

Ahora estoy listo para comenzar el UPDATE, usando el paquete DBMS_REDEFINITION—disponible con Oracle9i Database y superior—para hacer una redefinición de tabla online, como se muestra en la Lista 2. (la capacidad de clasificar los datos durante una redefinición fue incorporada en Oracle Database 10g versión 1)
Lista de Códigos 2: procedimiento DBMS_REDEFINITION.START_REDEF_TABLE

 

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.

El parámetro COL_MAPPING en el procedimiento START_REDEF_TABLE es lo que hace que la columna UPDATE y DROP sea mágica. Básicamente, ejecuta un SELECT sobre los datos, usando el parámetro COL_MAPPING, que puede incluir funciones (que hubiese utilizado en la cláusula SET del UPDATE). El parámetro ORDERBY_COLS logra el resecuenciamiento de los datos existentes de tabla en el disco. Si posibilita SQL_TRACE=TRUE al ejecutar la invocación a START_REDEF_TABLE, verá la ejecución de un INSERT como este:

 

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.

Ahora necesito copiar los "elementos" dependientes—como índices, restricciones y autorizaciones. Puedo utilizar la invocación COPY_TABLE_DEPENDENTS API (una característica de Oracle Database 10g y superior) para realizar esto, o puedo copiar los elementos dependientes de tabla yo mismo, usando DDL (junto con cualquier opción que yo quiera: NOLOGGING, PARALLEL, etc.). En este ejemplo, uso COPY_TABLE_DEPENDENTS API:
 
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

Si consulta el diccionario de datos ahora, verá dos tablas con restricciones, autorizaciones, triggers, índices, etc. equivalentes. Estoy listo para terminar la redefinición ahora—implicará sincronizar las tablas y luego bloquear T y T_INTERIM por un momento para intercambiar sus nombres de objeto en el diccionario de datos, de manera que T se convertirá en T_INTERIM, y T_INTERIM se convertirá en T:
 
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).

Es decir, lo configuraría así:
 
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.

La clave natural debería estar presente en mi tabla desde el principio, con restricciones NOT NULL y UNIQUE. El uso de una clave sustituta solo se incorporaría al trabajo de todas las operaciones INSERT—teniendo ahora que generar una clave sustituta, y restringir exclusivamente la clave sustituta y la clave natural. Por lo tanto, si la clave natural es inalterable y aceptable, úsela. (En este caso, aceptable implica, por ejemplo, que no utiliza 17 columnas para almacenar la clave natural—tener 2, 3, 4 o tal vez incluso 5 columnas es aceptable). Para leer más sobre este tema, visite asktom.oracle.com/pls/ask/search?p_string=%22natural+key%22.


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?

Bueno, para responder la pregunta N.1, pragma autonomous_transaction le permitiría usar DDL en un trigger, pero afortunadamente no tuvo éxito. Esté muy agradecido por eso. Para la respuesta N.2, en un caso extremadamente poco común—extremadamente poco común—donde esto sea realmente necesario, sugiero utilizar DBMS_JOB para programar la sentencia CREATE después de que se haya validado su transacción.

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:


Próximos Pasos


PREGUNTELE a Tom
Tom Kyte responde las preguntas de tecnología más difíciles. En esta columna, aparecen los puntos destacados de ese foro.
asktom.oracle.com

LEA más sobre
Oracle Database 11g
consistencia de escritura
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

LEA más sobre Tom
Expert Oracle Database Architecture: 9i and 10g Programming Techniques and Solutions
Effective Oracle By Design
The Tom Kyte Blog

DESCARGAS
Oracle Database 11g
Oracle Database 10g Express Edition



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.