Bloqueo de tablas hijo por causa de ejecutar sentencias PL/SQL sobre tablas padre

En versiones de la base de datos Oracle anteriores a la 9i, cuando la clave primaria de una tabla padre (parent table) no se encuentra indexada en la tabla hijo (child table), es muy probable que tengamos problemas con los bloqueos de la tabla hijo que se producen, bien cuando se actualiza (con la sentencia PLSQL UPDATE) la clave primaria de la tabla padre (lo cual ocurre con relativa frecuencia ya que existen determinados trabajos que actualizan todas las columnas de una tabla incluso cuando el valor de la misma no ha cambiado), o bien cuando se realizaba el borrado (con la sentencia PL/SQL DELETE) de algún registro de la tabla padre.

El caso es que en las circunstancias anteriores y para evitar que se produzca un bloqueo completo de la tabla hijo (full table lock), lo más recomendable es indexar también en la tabla hijo la clave primaria de la tabla padre. No obstante, esta norma de bloqueo cambió con la versión 9i de la base de datos Oracle.

La norma en Oracle 9i, aunque básicamente el bloqueo completo no se puede evitar, lo que sí que hace es disminuir sensiblemente el tiempo que dura dicho bloqueo. Así, para versiones de la base de datos Oracle posteriores a la 9i, si se actualiza o se borra algún registro de una tabla padre cuya clave primaria no está indexada en la tabla hijo, dicha tabla hijo queda totalmente bloqueada sólo mientras dura la ejecución de las sentencias PLSQL UPDATE o DELETE. Es decir, el bloqueo se libera cuando las sentencias terminan y no es necesario esperar a que se ejecute la sentencia PL/SQL COMMIT. Por lo tanto, aunque la situación es mejor que para versiones anteriores a Oracle 9i, el bloqueo completo de la tabla hijo todavía ocurre.

También conviene señalar que este bloqueo completo de la tabla hijo también ocurre cuando se ejecuta la sentencia PLSQL MERGE (sentencia que apareció precisamente con la versión de la base de datos Oracle 9i). No obstante, después de la versión Oracle 11g Release 1, la ejecución de una sentencia PL/SQL MERGE no siempre produce el bloqueo de la tabla hijo, esto ocurre cuando el MERGE es simplemente un INSERT, o cuando el MERGE funciona también como un UPDATE pero dicho UPDATE no cambia la clave primaria de la tabla padre.

A continuación os dejo un script SQL con el que es posible detectar las claves primarias que no están indexadas en las tablas hijo correspondientes (por cierto, en inglés las claves primarias de una tabla padre se conocen, desde el punto de vista de las tablas hijos, como foreign keys o claves extranjeras). El script sólo funciona para claves primarias que incluyen un máximo de ocho columnas.

SELECT nombre_tabla,
nombre_constraint,
cnom1 || NVL2(cnom2,','||cnom2,NULL) ||
NVL2(cnom3,','||cnom3,NULL) ||
NVL2(cnom4,','||cnom4,NULL) ||
NVL2(cnom5,','||cnom5,NULL) || 
NVL2(cnom6,','||cnom6,NULL) ||
NVL2(cnom7,','||cnom7,NULL) || 
NVL2(cnom8,','||cnom8,NULL) columnas
FROM ( SELECT uc.table_name nombre_tabla,
uc.constraint_name nombre_constraint,
MAX(DECODE(pos,1,coln,NULL)) cnom1,
MAX(DECODE(pos,2,coln,NULL)) cnom2,
MAX(DECODE(pos,3,coln,NULL)) cnom3,
MAX(DECODE(pos,4,coln,NULL)) cnom4,
MAX(DECODE(pos,5,coln,NULL)) cnom5,
MAX(DECODE(pos,6,coln,NULL)) cnom6,
MAX(DECODE(pos,7,coln,NULL)) cnom7,
MAX(DECODE(pos,8,coln,NULL)) cnom8,
count(*) ncol
FROM ( SELECT SUBSTR(table_name,1,30) tn,
SUBSTR(constraint_name,1,30) cn,
SUBSTR(column_name,1,30) coln,
position pos
FROM user_cons_columns ) ucc,
user_constraints uc
WHERE  ucc.cn = uc.constraint_name
AND  uc.constraint_type = 'R'
GROUP BY uc.table_name, uc.constraint_name
) user_cons
WHERE ncol > ALL
( SELECT count(*)
FROM   user_ind_columns uic
WHERE  uic.table_name = user_cons.nombre_tabla
AND  uic.column_name in (cnom1, cnom2, 
cnom3, cnom4,
cnom5, cnom6,
cnom7, cnom8)
AND  uic.column_position <= user_cons.ncol
GROUP BY uic.index_name
)

En conclusión, la clara recomendación que personalmente doy es que, si sobre una tabla padre vamos a realizar operaciones de actualización de la clave primaria, borrado de registros o merge (inserción + actualización + borrado), lo mejor es proceder a indexar dicha clave primaria en la tabla hijo. Además, otra circunstancia que hace recomendable la indexación de la clave primaria de la tabla padre en la tabla hijo, es que no hacerlo puede derivar en graves problemas de rendimiento.