Í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.

Anuncios

15 respuestas a Índices y ‘FOREIGN KEYs’

  1. Al revisar el blog de CarlosAL, en específico la entrada indices-y-foreign-keys me entró la duda de que pasa con postgreSQL, claro que solo revise el caso de los bloqueos, me parece que en postgres también es bueno crear índices por cada llave foránea que se tenga.

  2. NOX dice:

    Saludos mi estimado, estoy en este ambiente sobre base de datos pero me ha sucedido una cuestión y es la siguientes:

    ¿Como se podrá crear un índice sobre una vista?, la verdad estoy buscando por toda la red y no he encontrado la solución, podrá parecer fácil pero en realidad no lo es, espero me pueda resolver esta duda y poder compartir conocimientos, manejo ORACLE 8i, y es que las versiones posteriores tienen un alto costo para migrar los datos que tengo en oracle 8i, que tengas un buen día, hasta pronto.

  3. carlosal dice:

    No se puede:

    ‘ORA-01702: a view is not appropriate here’

    Lo más que puedes hacer es utilizar vistas materializadas (materialized views / snapshots) que sí admiten índices.

    Saludos.

    Carlos.

  4. Daniel dice:

    Gracias por el artículo Carlos, ha resuelto mi duda sobre si indexar o no los FK 🙂

  5. Robert Diaz dice:

    Definitivamente es cierto lo indicado en dicho articulo. En este momento estoy trabajando en una entidad finaciera en Venezuela y esta viene presentado desde hace varios años ese problema, el cual se ha agravado luedo de realizar migración de 9i a 10g. Pero me he encontrado con tablas que tienen hasta 28 referencia o foreign key, creo todos los indices? cual seria la mejor practica o consejo al respecto?

    Gracias de verdad por ese articulo.

  6. carlosal dice:

    Por partes:

    Robert:

    Hay que tener en cuenta qué tipo de información y actividad tienen las tablas. Los bloqueos por falta de índices serán un asunto grave en sistemas con mucha actividad de adtualizaciones (OLTP).

    Por otra parte, no es necesario un índice para cada FK si hay FK’s que comparten columnas. P. ej.: FK1(co1, col2, col3) y FK2 (col1, col2, col4) se pueden beneficiar de un único índice para evitar los bloqueos de los que hablamos.

    Saludos.

    Carlos.

  7. Jhonatan dice:

    Bien, este artículo me ha resuelto la duda que tenía. Gracias.

  8. Laurita dice:

    Yo tengo una pregunta resulta que necesito crear un llave foranea que sea una relacion uno a uno, se supone que todas la fk tienes que decir references que es lo que conocemos como el padre pero en mi relacion uno a uno necesito que ninguno sea padre sino que solamente me colaboren con una informacion y ya si alguien me puede ayudar se lo agradezco

  9. carlosal dice:

    Laurita:

    Estás confundiendo conceptos. ‘Relación’ es un concepto de entidad-relación (o modelo ‘lógico’, o ‘conceptual’, según) mientras que una ‘foreign key’ es un concepto de diagrama de tablas (‘relacional’ o ‘físico’).

    Aunque en una relación se establecen ‘roles’ que pueden no ser padre-hijo, en una ‘foreign key’ sí: la tabla ‘padre’ es a la que se hace referencia en el ‘REFERENCES’ y es en la que deben existir filas para poder insertar en la tabla ‘hija’ (sobre la que se define la ‘foreign key’).

    Una relación uno a uno se puede conseguir añadiendo a la(s) colunmna(s) sobre la(s) que se define la ‘foreign key’ una ‘unique key’ o la misma ‘primary key’ de la tabla.

    No obstante, las relaciones uno a uno son infrecuentes (aunque a veces tienen su razón de ser). Revisa el modelo.

    Saludos.

    Carlos.

  10. Wladd dice:

    CarlosAl: Excelente tu explicación!, complemento tan acertada descripción con dos cosas mas:

    1. Este problema es el dia a dia de nosotros los DBA’s, de hecho si existiese un top de los problemas mas frecuentes que nos encontramos en instalaciones de clientes este seria el top 2 o el 3. (jeje escribo en medio de este problema presente en un cliente, ya lo conocia y buscaba en google una explicación para documentarselo detalladamente)

    2. Aunque mencionas por ahi un modelo ejemplo generador de este problema llamado Hibernate, (no se a quien se le ocurriría la genial idea incluir la actualizacion de los campos llave primaria de de una tabla (key=key)) si no lo hace automatizadamente alguna herramienta, para ser mas puntual Oracle Forms Developer es el mas frecuente generador de esta problematica unido al despistado programador de aplicaciones que utiliza los wizards para hacer los bloques basados en tabla y no excluye los campos de la llave primaria via properties y cuando ese ‘engendro’ sale a produccion, vienen las preguntas de porque funcionaba en desarrollo y no en produccion y el DBA con el problema encima. Nadie ve el otro lado (..)

    En Conclusión Oracle se niega a habilitar algun tipo de control sobre esta problematica en DOS de sus herramientas (Si… Pasa en Oracle también no todo es perfecto incluido Oracle y sus herramientas) , esto no es malo del todo ya que siempre tendremos los DBA’s consultores que resolverles estos problemas tan ‘tontos’ a los clientes.

    Suerte!

    • carlosal dice:

      Wladd:

      Creo recordar que Oracle Forms tenía una opción (con un ‘check’ o algo así) para NO actualizar todas las columnas de la fila modificada.

      Por mi parte, recuerdo un caso en el que una tabla (grande) tenía FK’s sobre sí misma -por supuesto, sin índices- y se montaban unos bloqueos que organizaban unos cuellos de botella de agárrate y no te menees.

      En cualquier caso, como digo siempre, lo importante es saber realmente qué se está haciendo y por qué.

      Saludos.

      Carlos.

  11. Este tema esta hablado en muchas partes, pero no bien tratado, una llave foranea nunca debe apuntar a cualquier cosa, que apunte a algo distinto de un PK, es un indicador de un problema en el diseño, lo que sí puede hacer el DBA Oracle es correr un script para revisar que llaves foraneas no tienen indices y si son sobre 2000 mil, cambiar al jefe de proyecto… ahora imagina lo que pasa con los inner join, join, delete, update, insert (full scan a la tabla padre). Si apunta a cualquier cosa… y como su nombre lo indica, una tabla hija tiene dos padres (personas) y las personas tienen un unico ID, RUT en el caso de Chile, en el caso de las relaciones, los PK son compuestos y las referencias a estas relaciones, deben apuntar a ese PK. no a cualquier cosa.

  12. Blanca dice:

    Hola,
    tengo una consulta a ver si alguien me puede ayudar:
    En oracle 10.2 tengo definida una FK sobre un campo y un índice único que incluye el campo de la FK y otros dos campos. En este caso ¿es suficiente con ese índice, o tengo que crear específicamente un índice sólo para el campo de la FK?

    Gracias y un saludo!

  13. JJ dice:

    Hola, muy bueno su blog, agradecería si me pueden ayudar con el siguiente inconveniente, estoy utilizando oracle developer 6i, y en una pantalla me veo forzadazo a utilizar el post (hacer insert/update sin commit – comprometer los datos), me veo forzado hacer esto ya que utilizo 3 bloques, A, B, C donde A es el padre del B y B es el padre del C, los bloques B y C son tabulares(detalle) y lo que se requiere es que la información se vaya almacenando sin necesidad de hacer commit cuando cambio de registro del bloque B y existen registros sin garbar en el bloque C (el famoso do you want save changes)

    Aparecen bloqueadas un sin numero de tablas que tiene relación con las tablas que yo estoy registrando o actualizando y esto provoca que se bloquee otro usuario al ingresar a la misma pantalla con otra transacción, ya cree el índice correspondiente al foreign de cada tabla, el bloque también tiene la propiedad Update Changed Columns Only a True

    En espera de sus comentarios y pronta ayuda

    Gracias

    • CarlosAL dice:

      JJ:
      Lo que dices más parece un problema de diseño de aplicación que de las estructuras de datos. Developer debe bloquear filas que están siendo editadas hasta commit.

      Por cierto, Developer 6i dejó de estar soportado desde enero de 2005.

      Saludos.

      Carlos.

Responder

Introduce tus datos o haz clic en un icono para iniciar sesión:

Logo de WordPress.com

Estás comentando usando tu cuenta de WordPress.com. Cerrar sesión / Cambiar )

Imagen de Twitter

Estás comentando usando tu cuenta de Twitter. Cerrar sesión / Cambiar )

Foto de Facebook

Estás comentando usando tu cuenta de Facebook. Cerrar sesión / Cambiar )

Google+ photo

Estás comentando usando tu cuenta de Google+. Cerrar sesión / Cambiar )

Conectando a %s

A %d blogueros les gusta esto: