Cláusula CONSTRAINT para mejorar el rendimiento de las consultas PL/SQL

Resulta bastante interesante mencionar la importancia de que, al crear las tablas de nuestra base de datos Oracle, utilicemos, cuando sea posible, la cláusula CONSTRAINT para mejorar el rendimiento de las consultas PLSQL que utilicen dicha tabla. Muchos desarrolladores de PL/SQL piensan que la utilización de la cláusula CONSTRAINT sólo sirve para garantizar la integridad de los datos, lo cual es cierto, pero esta cláusula también la utiliza el optimizador Oracle para determinar el plan de ejecución óptimo.

De hecho, el optimizador PLSQL utiliza como entradas:

• La consulta SQL que se va a optimizar.
• Todas las estadísticas disponibles de los objetos de la base de datos.
• De estar disponibles, las estadísticas del sistema (velocidad de CPU, velocidad de los dispositivos de entrada/salida, etcétera).
• Los parámetros de inicialización.
• Las constraints (restricciones).

El optimizador utiliza todos estos datos para determinar el mejor plan de ejecución posible. Personalmente he detectado que mucha gente tiende a no emplear la funcionalidad CONSTRAINT en los data warehouse y sistemas de reporting. Su argumento es decir: "Ya hicimos una limpieza de datos y no necesitamos aplicar restricciones a los datos". Ciertamente es posible que no necesiten utilizar constraints para garantizar la integridad de los datos, pero lo que es seguro es que necesitarán dichas constraints para conseguir que el optimizador genere los planes de ejecución más adecuados. En un data warehouse un plan de ejecución incorrecto puede causar que la ejecución de una consulta lleve horas e incluso días. Por lo tanto, en un data warehouse la utilización de las cláusulas CONSTRAINT es necesaria por razones de rendimiento.

En el siguiente ejemplo analizaremos como conseguir eliminar el acceso a una tabla de una vista (view) gracias a la utilización de la cláusula CONSTRAINT:

CREATE TABLE e1 AS
SELECT * FROM empleados
WHERE nombre IN ('Pepe', 'Juan');

ALTER TABLE e1 MODIFY nombre NOT NULL;

ALTER TABLE e1 ADD CONSTRAINT e1_check
CHECK (nombre IN ('Pepe', 'Juan'));

CREATE TABLE e2 AS
SELECT * FROM empleados
WHERE nombre IN ('Paco', 'Luis');

ALTER TABLE e2 MODIFY nombre NOT NULL;

ALTER TABLE e2 ADD CONSTRAINT e1_check
CHECK (nombre IN ('Paco', 'Luis'));

CREATE OR REPLACE VIEW vemp AS
SELECT * FROM e1
UNION ALL
SELECT * FROM e2;

Según podéis ver, hemos creado dos tablas que contienen datos mutuamente excluyentes y una vista que utiliza la cláusula UNION ALL para mostrar todos los datos juntos. A continuación lo que haremos es realizar una consulta sobre dicha vista utilizando el campo nombre en la cláusula WHERE para ver como el optimizador decide no utilizar una de las tablas.

SELECT * FROM vemp WHERE name = 'Juan';

Execution Plan
————————————————————————————————
Plan hash value: 3412345678

————————————————————————————————
|Id | Operation         | Name | 
————————————————————————————————
| 0 | SELECT STATEMENT  |      |
| 1 | VIEW              | VEMP | 
| 2 | UNION-ALL         |      | 
| 3 | TABLE ACCESS FULL | E1   |
| 4 | FILTER            |      |
| 5 | TABLE ACCESS FULL | E2   |
————————————————————————————————

Predicate Information 
(identified by operation id):
————————————————————————————————
3 - filter(“NOMBRE”='Juan')
4 - filter(NULL IS NOT NULL)
5 - filter(“NOMBRE”='Juan')

Arriba podemos ver el plan de ejecución generado desde una base de datos Oracle versión 11g y en el que hemos quitado algunas columnas que no son de utilidad en este ejemplo. En él podemos observar que el optimizador realmente no ha prescindido de realizar un FULL SCAN de la tabla e2, lo cual, evidentemente, podría haber hecho ya que dicha tabla sólo contiene a los empleados cuyos nombres son Paco o Luis. Sin embargo, si realizamos una análisis más concienzudo, observaremos que, en el paso 4, el optimizador ha aplicado un filtro, y que dicho filtro reza NULL IS NOT NULL. Está claro que nuestra consulta SELECT no incluye esta condición, sino que el optimizador la ha añadido automáticamente. Puesto que NULL IS NOT NULL es una condición falsa, esa parte del árbol del plan de ejecución nunca se ejecutará, por lo que en ningún momento se accederá a la tabla e2.

Este ejemplo es muy simple, pero creo que bastante explicativo de como las cláusulas CONSTRAINT pueden ayudar a mejorar el rendimiento de nuestra base de datos Oracle.