Activar la reescritura de consultas

Las vista materializada pueden ser útiles para obtener datos precalculados, obtenidos a partir de funciones de agrupamiento como Max, Count, Avg... como en el siguiente ejemplo:

CREATE MATERIALIZED VIEW LOG ON t2
WITH PRIMARY KEY, ROWID, SEQUENCE ( t_key, amt );

CREATE MATERIALIZED VIEW mv
REFRESH FAST ON COMMIT
AS SELECT t_key, MAX( amt ) AS amt_max
FROM t2
GROUP BY t_key;

Con esta vista, podemos realizar consultas como:

SELECT t_key, amt_max
FROM mv
ORDER BY t_key ;

De forma mucho más rápida que con la consulta equivalente:

SELECT t_key, Max( amt ) AS amt_max
FROM t2
GROUP BY t_key
ORDER BY t_key;

Lo bueno del caso es que si tenemos activada en la base de datos una funcionalidad llamada reescritura de consultas (Query Rewrite), Oracle puede usar la información almacenada en la vista materializada para responder la segunda consulta de forma más rápida. Podemos activar esta reescritura realizando la siguiente acción sobre la vista materializada:

ALTER MATERIALIZED VIEW mv ENABLE QUERY REWRITE ;

Nota: El gestor de base de datos Oracle tiene que permitir esta posibilidad. Si no está activa la sentencia anterior producirá un error.

Por defecto, las vistas materializadas tienen la opción de reescritura desactivada.

Para que sea útil, necesitamos ahora recopilar estadísticas de uso que permitan a Oracle optimizar las consultas. Para ello, hacemos uso de las funciones definidas en el paquete DBMS_STATS. En nuestro ejemplo, realizaríamos la siguiente llamada:

EXECUTE DBMS_STATS.GATHER_TABLE_STATS( USER, 'mv' );

Vista materializada de joins

En el caso de crear una vista materializada de un join, se deben cumplir ciertas condiciones adicionales si queremos que sea de refresco rápido:

Deben existir logs o registros para cada tabla implicada en el join

La sentencia SELECT no debe contener objetos

No se pueden usar sentencias GROUP BY, ni funciones de agrupación.

Los campos ROWID de todas las tablas implicadas deben aparecer en la cláusula SELECT

Además de estas obligaciones, también hay unas cuantas recomendaciones:

Como este tipo de vistas suele ser bastante grande, ya
que no tiene agrupaciones, se puede aumentar mucho la
eficacia definiendo un índice para cada campo ROWID de
la vista.

Después de haber creado la vista, se deben realizar un
análisis estadístico usando el paquete DBMS_STATS. Oracle
necesitas estas estadísticas para optimizar la reescritura
de consultas, como ya se explicó.