Foreign Keys no indexadas

Suelo encontrarme en los diseños de las aplicaciones, foreign keys que no se encuentran indexadas. No indexar las foreign keys puede ser un gran problema de performance.

Podemos encontrar 2 tipos de problemas:
1.- Se produce un loqueo de la tabla si modificamos (muy inusual) o eliminamos algún registro de la primary key (tabla padre) y las foreign keys (tabla hija) no se encuentran indexadas.
2.- Si tenemos la cláusula ON DELETE CASCADE y no tenemos índices creados en la foreign keys, si eliminamos algún registro de la primary key, como no tenemos indexadas las foreign keys, se producirá un full scan de la tabla que contiene las foreign keys. Esto puede ser el factor de un grave problema de performance ya que si eliminamos varios registros de la tabla padre, se realizará un full scan de la tabla hija por cada registro eliminado de la tabla padre.

Veamos un ejemplo:

SQL_9iR2> CREATE TABLE tabla_padre AS
  2  SELECT level id
  3  FROM dual
  4  CONNECT BY level <= 9 ;

Table created.

SQL_9iR2> CREATE TABLE tabla_hija AS
  2  SELECT decode(mod(level,9),0,9,mod(level,9)) id , 'detalle_'||level detalle
  3  FROM dual
  4  CONNECT BY level <= 1000000 ;

Table created.

SQL_9iR2> ALTER TABLE tabla_padre
  2  ADD CONSTRAINT tabla_padre_pk
  3  PRIMARY KEY (id) ;

Table altered.

SQL_9iR2> ALTER TABLE tabla_hija
  2  ADD CONSTRAINT tabla_padre_hija_fk
  3  FOREIGN KEY (id) REFERENCES tabla_padre(id)
  4  ON DELETE CASCADE ;

Table altered.

SQL_9iR2> EXEC DBMS_STATS.GATHER_TABLE_STATS(USER, 'TABLA_PADRE', cascade => true) ;

PL/SQL procedure successfully completed.

SQL_9iR2> EXEC DBMS_STATS.GATHER_TABLE_STATS(USER, 'TABLA_HIJA', cascade => true) ;

PL/SQL procedure successfully completed.

Bien, ya tenemos nuestras tablas de ejemplo con sus respectivos datos, constraints y estadísticas.

Ejecutamos un insert en la tabla hija:

SQL_9iR2> INSERT INTO tabla_hija VALUES(5,'detalle'||5) ;

1 row created.

Ahora ejecutamos un delete en la tabla padre:

SQL_9iR2> DECLARE
  2  PRAGMA AUTONOMOUS_TRANSACTION ;
  3  BEGIN
  4      DELETE FROM tabla_padre
  5      WHERE id = 2 ;
  6
  7      COMMIT ;
  8  END ;
  9  /
DECLARE
*
ERROR at line 1:
ORA-00060: deadlock detected while waiting for resource
ORA-06512: at line 4

Como podemos ver, al no tener un índice en la foreign key se produce un Deadlock.
Veamos qué sucede si creamos un índice sobre la foreign key y repetimos el procedimiento.

SQL_9iR2> CREATE INDEX tabla_hija_id_idx ON tabla_hija(id) ;

Index created.

SQL_9iR2> EXEC DBMS_STATS.GATHER_TABLE_STATS(USER, 'TABLA_HIJA', cascade => true) ;

PL/SQL procedure successfully completed.

SQL_9iR2> INSERT INTO tabla_hija VALUES(5,'detalle'||5) ;

1 row created.

SQL_9iR2> DECLARE
  2  PRAGMA AUTONOMOUS_TRANSACTION ;
  3  BEGIN
  4      DELETE FROM tabla_padre
  5      WHERE id = 2 ;
  6
  7      COMMIT ;
  8  END ;
  9  /

PL/SQL procedure successfully completed.

Mi consejo es el siguiente: Si realizamos deletes en la tabla padre y/o updates en la primary key de la tabla padre, entonces debemos crear índices en las foreign keys de la tabla hija.