Tuning y constraints (o restricciones en la base de datos Oracle)

En este artículo continuaré con el caso de tuning PL/SQL planteado en el artículo Tuning de consultas SELECT COUNT(*) y determinaré cómo es posible mejorar aún más el rendimiento de la consulta SELECT objeto del mencionado artículo. Eso sí, para poder profundizar en el estudio del rendimiento, tuve que solicitar al lector que me hizo la pregunta inicial que me enviase los datos del esquema de la base de datos Oracle para las tablas involucradas en la consulta PLSQL SELECT. Después de un par de correos pude disponer de toda la información que necesitaba, los campos de las tablas, los índices asociados, las claves primarias (primary keys), las claves extranjeras (foreign keys), y las diferentes restricciones (constraints) aplicadas sobre las mencionadas tablas.

En suma, disponía de la siguiente información incluida en las siguientes sentencias SQL:

SELECT COUNT(*)
INTO   v_count
FROM   tabla1 t1, tabla2 t2
WHERE  t1.id = t2.id
  AND  t2.otro_id = v_otro_id;

CREATE TABLE tabla1 (
  id       NUMBER(10) NOT NULL,
  ...
  datos    VARCHAR2(250)
);

ALTER TABLE tabla1 ADD CONSTRAINT t1_pk
PRIMARY KEY (id);

CREATE TABLE tabla2 (
  otro_id  NUMBER(10) NOT NULL,
  id       NUMBER(10) NOT NULL,
  ...
  datos  VARCHAR2(250)
);

ALTER TABLE tabla2 ADD CONSTRAINT t2_pk
PRIMARY KEY (otro_id);

ALTER TABLE tabla2 ADD CONSTRAINT t2_fk1
FOREIGN KEY (id) REFERENCES t1 (id);

Lo primero que me llamó la atención a ver esta información fue que la columna ID era la clave primaria de la TABLA1 y que, a la vez, era la clave extranjera (foreign key) de la TABLA2 a la TABLA1; además, TABLA2.id estaba definido como un campo no nulo (NOT NULL). Esto me daba una clara idea para poder reescribir la consulta SELECT de manera que mejorase su rendimiento, todo ello gracias a que además sabía lo siguiente:

• La salida del SELECT no proporcionaba ningún campo de la TABLA1.
• Al relacionar las tablas TABLA1 y TABLA2 por el campo ID, los registros en la TABLA2 son de clave preservada, ya que TABLA1.id es un valor único y al unir dicha tabla con la TABLA2, como mucho la consulta SELECT devolverá los registros contenidos en esta última tabla una única vez.
• Pero además, como TABLA2.id es una clave extranjera de TABLA2 a TABLA1, todos los registros de la TABLA2 que tienen un valor no nulo del campo ID, aparecerán al menos una vez en la salida de la consulta SELECT.
• Por último, como TABLA2.id es un campo no nulo (NOT NULL), todos los registros de la TABLA2 aparecerán a menos una vez y como mucho una vez en dicha salida.

Por los tanto, la consulta SELECT antes mencionada es equivalente a la siguiente consulta mucho más sencilla:

SELECT COUNT(*)
INTO   v_count
FROM   tabla2 t2
WHERE  t2.otro_id = v_otro_id;

Hemos podido por tanto eliminar una de las tablas de la consulta SELECT, con lo cual accederemos a un solo objeto de la base de datos Oracle, la TABLA2. El plan de ejecución de la sentencia se ha simplificado notablemente ya que, entre otras cosas, no será necesario utilizar en índice de la clave primaria T1_PK.

Lo que seguro que muchos os estaréis preguntando ahora es: "Si nosotros, tras realizar un sencillo análisis, hemos podido eliminar la TABLA1 de la consulta SELECT, ¿no podría hacer lo mismo el optimizador de la base de datos Oracle?" La respuesta es que sí, pero esto sólo ocurre para las versiones de la base de datos Oracle 11g Release 1 y posteriores, que además tengan habilitados los constraints a nivel de la base de datos. Si la base de datos Oracle no utiliza constraints, entonces los constraints que aparecen en las definiciones de la tablas sólo se aplican a nivel de la aplicación, pero no de la base de datos. Es decir, es la aplicación la que fuerza a que se cumplan esos constraints y no la base de datos misma.

En conclusión, para realizar el tuning o puesta a punto de una sentencia PL/SQL es necesario conocer el modelo de datos y la estructura de la base de datos (al menos de las tablas y objetos involucrados). Si en el anterior ejemplo yo no hubiera conocido las claves primarias, las claves extranjeras, ni los campos NOT NULL, no hubiera sido capaz de poner a punto la consulta SELECT de una manera eficaz. Por otro lado, es imprescindible que el optimizador conozca los constraints, si decidimos que la base de datos no use constraints, estaremos limitando enormemente la capacidad del optimizador que será incapaz de optimizar eficazmente cualquier consulta, en nuestro caso habría sido incapaz de eliminar el uso de la TABLA1, pero incluso puede ser incapaz de utilizar algún que otro índice, ya que los constraints NOT NULL pueden tener un gran impacto sobre el optimizador a la hora de saber si puede o no utilizar un índice determinado.

En mi opinión es mucho mejor que sea el optimizador el que elabore el plan de ejecución correcto, y no ser nosotros los que tengamos que pensar si un plan es equivalente a otro. Por lo tanto, los metadatos o constraints no sólo son imprescindibles para asegurar la integridad de los datos, sino también para que el optimizador pueda elegir el mejor plan de ejecución.

Artículos relacionados: Bloqueo de tablas hijo al ejecutar sentencia PL/SQL sobre tablas padre (sobre el uso de claves extranjeras o foreign keys).