Índices y ‘FOREIGN KEYs’

Mucha gente aun no es muy consciente de los problemas que pueden provocarse en una base de datos cuando existen tablas con ‘foreign keys’ que no tengan índices asociados.

Este es un asunto que se ha repetido una y mil veces en diferentes foros, pero que sigue apareciendo en multitud de bases de datos.

Para resumir: si tenemos tablas que tienen ‘foreign keys’ y éstas no están indexadas, aparte de los problemas de rendimiento (gran cantidad de las ‘joins’ entre las tablas se harán basándose en las columnas correspondientes a las ‘foreign keys’), aparecerán problemas de concurrencias y bloqueos que degradarán enormemente el rendimiento de la actividad de actualizaciones de la base de datos.

Los SGBDR más conocidos (al menos los que yo conozco: Oracle, SQL Server, Sybase…) no fuerzan la creación de índices sobre las ‘foreign keys’, pero recomiendan encarecidamente su uso. El propio Oracle Designer genera automáticamente los índices para las ‘foreign keys’ en sus herramientas de diseño.

El ‘quid’ de la cuestión (o más castizo: la madre del cordero) está en que cualquier actualización sobre la tabla ‘padre’ deberá bloquear TODA la tabla ‘hija’ si no hay un índice sobre las columnas que corresponden a la ‘foreign key’. Esto es necesario para asegurar la integridad.

Vamos con un ejemplo para ver el comportamiento descrito:

carlos@db01.xxxxxxxx> SELECT * FROM V$VERSION
  2  /

BANNER
----------------------------------------------------------------
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - 64bi
PL/SQL Release 10.2.0.1.0 - Production
CORE    10.2.0.1.0      Production
TNS for Solaris: Version 10.2.0.1.0 - Production
NLSRTL Version 10.2.0.1.0 - Production

carlos@db01.xxxxxxxx>
carlos@db01.xxxxxxxx> SELECT * FROM PRUEBA01 ORDER BY ID_N
  2  /

      ID_N C_TXT
---------- -------------------------
         1 UNO
         2 DOS
         3 TRES
         4 CUATRO
         5 CINCO
         6 SEIS
        11 ONCE
        21 VEINTIUNO
        22 VEINTIDOS
        23 VEINTITRES
        24 VEINTICUATRO
        25 VEINTICINCO
        30 TREINTA

13 filas seleccionadas.

carlos@db01.xxxxxxxx>
carlos@db01.xxxxxxxx> CREATE TABLE PRUEBA04 AS
  2                 SELECT ID_N,
  3                        LOWER(C_TXT) C_TXT
  4                   FROM PRUEBA01 WHERE ID_N < 10
  5  /

Tabla creada.

carlos@db01.xxxxxxxx>
carlos@db01.xxxxxxxx> ALTER TABLE PRUEBA04 ADD CONSTRAINT PRUEBA04_PRUEBA01_FK
  2               FOREIGN KEY(ID_N) REFERENCES PRUEBA01(ID_N)
  3  /

Tabla modificada.

carlos@db01.xxxxxxxx>
carlos@db01.xxxxxxxx> SELECT * FROM PRUEBA04 ORDER BY ID_N
  2  /

      ID_N C_TXT
---------- -------------------------
         1 uno
         2 dos
         3 tres
         4 cuatro
         5 cinco
         6 seis

6 filas seleccionadas.

carlos@db01.xxxxxxxx>

Partiendo de nuestra vieja conocida ‘PRUEBA01’, que tiene su ‘primary key’ sobre ID_N, creamos la tabla PRUEBA04 como un subconjunto de ella.

Lo siguiente es fácil: abrimos una nueva sesión e insertamos una fila en PRUEBA04:

carlos@db01.xxxxxxxx> INSERT INTO PRUEBA04 VALUES (11,'once')
  2  /

1 fila creada.

carlos@db01.xxxxxxxx>

Si ahora vamos a la primera sesión e intentamos, por ejemplo, borrar una fila, tendremos que la sesión se bloquea esperando el ‘COMMIT’ o ‘ROLLBACK’ en la segunda sesión:

carlos@db01.xxxxxxxx> DELETE FROM PRUEBA01 WHERE ID_N=30
  2  /
_

Nótese que estamos intentando borrar una fila en PRUEBA01 que no tiene filas ‘hijas’ en PRUEBA04. Esto es porque la primera sesión está intentando bloquear toda la tabla PRUEBA04, pero la sesión 2 tiene una transacción pendiente sobre ella.

Una vez que terminamos la transacción en la segunda sesión:

carlos@db01.xxxxxxxx> COMMIT
  2  /

Confirmación terminada.

carlos@db01.xxxxxxxx>

La primera sesión puede continuar con su transacción:

1 fila suprimida.

carlos@db01.xxxxxxxx>

Ahora vamos a reproducir el proceso, pero con un índice sobre la columna correspondiente a la FK en PRUEBA04:

carlos@db01.xxxxxxxx> CREATE INDEX PRUEBA04_IDX01 ON PRUEBA04(ID_N)
  2  /

Índice creado.

carlos@db01.xxxxxxxx>

En la sesión 2 volvemos a hacer:

carlos@db01.xxxxxxxx> INSERT INTO PRUEBA04 VALUES (21, 'veintiuno')
  2  /

1 fila creada.
carlos@db01.xxxxxxxx>

Pero ahora la primera sessión no esperará si borramos una fila:

carlos@db01.xxxxxxxx> DELETE FROM PRUEBA01 WHERE ID_N=30
  2  /

1 fila suprimida.

carlos@db01.xxxxxxxx>

Una cosa más: en ausencia de índice, intentamos un ‘UPDATE’ sobre una fila en PRUEBA01 que no tiene ‘hijas’ en PRUEBA04 en el que actualizamos el valor del PK con su mismo valor como hacen muchas aplicaciones (que actualizan TODAS LAS COLUMNAS DE UNA FILA en cada ‘UPDATE’) o como hacen ciertos ‘modelos de persistencia’ (Hibernate creo que también lo hace en sus clases ‘mapeadas’ a tablas de la base de datos)

Primero ‘tiramos’ el índice en la primera sesión:

carlos@db01.xxxxxxxx> drop index prueba04_idx01;

Índice borrado.

La segunda sesión hace:

carlos@db01.xxxxxxxx> INSERT INTO PRUEBA04 VALUES (21, 'veintiuno')
  2  /

1 fila creada.
carlos@db01.xxxxxxxx>

Y la primera intenta:

carlos@db01.xxxxxxxx> UPDATE PRUEBA01 SET ID_N = ID_N WHERE ID_N = 30
2 /
_

Como esperábamos, se queda esperando al fin de la transacción de la segunda sesión, aunque no tenga filas ‘hijas’ y el valor actualizado tenga el mismo valor que tenía.

Si imaginamos una base de datos con miles de transacciones actualizando datos sobre tablas con FK’s sin índices asociados podremos darnos cuenta de las consecuencias sobre el rendimiento que ello puede producir.

Después de esto, espero que si alguien decide seguir manteniendo FK’s sin índices asociados, tenga poderosas razones para hacerlo.

Saludos.

Carlos.