Mejorando el desempeño de consultas con QUERY REWRITE y Vistas Materializadas

Desde Oracle 8i existe una funcionalidad de la base de datos Oracle llamada QUERY REWRITE, en la cual en ciertas circunstancias de configuración de parámetros y objetos de base de datos, un SQL emitido por un usuario es re-escrito por otro equivalente que tiene mejor costo. En consecuencia esta funcionalidad tiene como requisito que el optimizador esté configurado como CBO.

Un caso de aplicación frecuente en el uso de QUERY REWRITE se da en ambientes de datawarehouse, donde consultas que usan funciones de grupo sobre una fact table de millones de registros, pueden ser resueltas sobre una tabla de resumen (de cardinalidad mucho menor que la fact table) que contenga la misma información pero con mucho menos lecturas en disco.

Las tablas de resúmenes indicadas se deben implementar con Vistas Materializadas para hacer uso de la característica de QUERY REWRITE.

En un sistema de gestión de base de datos que siga el modelo relacional, una vista es una tabla virtual, que representa el resultado de una consulta. Siempre que se consulta o se actualiza una vista normal, el RDBMS convierte estas operaciones en consultas o actualizaciones de las tablas usadas para definir la vista.

Una vista materializada utiliza una aproximación diferente: el resultado de la consulta se almacena en una tabla cache real, que será actualizada de forma periódica a partir de las tablas originales en las cuales se basa la vista materializada. Esto proporciona un acceso mucho más eficiente, a costa de un incremento en el tamaño de la base de datos y a una posible falta de sincronización, es decir, que los datos de la vista pueden estar potencialmente desfasados con respecto a los datos reales, lo cual en ambientes de gestión, que se usan para la toma de decisiones estratégicas y tácticas es un tema irrelevante.

Ejemplo:

A continuación se creará una tabla objetos que hace el papel de fact table. Se crea a partir de una vista de catalogo Oracle que contiene un volumen importante de registros.

REM Preparacion de tabla con volumen grande de registros

sqlplus scott/tiger
set echo on
set termout off

drop table objetos;

create table objetos
nologging
as
select * from all_objects
union all
select * from all_objects
union all
select * from all_objects
/

REM Se agregan mas datos a la tabla objetos para hacerla más voluminosa

REM Se usa el mecanismo de DIRECT PATH para acelerar el proceso de poblamiento

insert /*+ APPEND */ into objetos
select * from objetos;
commit;
insert /*+ APPEND */ into objetos
select * from objetos;
commit;
insert /*+ APPEND */ into objetos
select * from objetos;
commit;

analyze table objetos compute statistics;
select count(*) from objetos;

REM Se hace una consulta con funciones de grupo sobre la tabla objetos.

set autotrace on
set timing on
select owner, count(*) from objetos group by owner;

OWNER                            COUNT(*)
—————————— ———-
CTXSYS                               6264
ELAN                                 1272
HR                                    816
MDSYS                                5640
ODM                                  9768
…

28 rows selected.

Elapsed: 00:00:07.06

Execution Plan
———————————————————-
0      SELECT STATEMENT Optimizer=CHOOSE
(Cost=2719 Card=28 Bytes=140)
1    0   SORT (GROUP BY) (Cost=2719 Card=28 Bytes=140)
2    1     TABLE ACCESS (FULL) OF ‘OBJETOS’
(Cost=1226 Card=708456 Bytes=3542280)

Se observa que el costo de este primer plan de ejecución es alto debido a que la consulta requiere hacer un full table scan sobre una tabla de siete millones de registros, para solo recuperar finalmente 28 registros. ¿Qué se puede hacer? Crear una vista materializada basada en la consulta y habilitar query rewrite.

REM Se le entrega privilegio de query rewrite al usuario SCOTT con el cual se va a trabajar

sqlplus / as sysdba

grant query rewrite to scott;

REM Se activa para la sesión actual la capacidad de query rewrite

sqlplus scott/tiger
alter session set query_rewrite_enabled=true;
alter session set query_rewrite_integrity=enforced;

REM Notese que la vista materializada permite query rewrite

create materialized view vm_resumen_objetos
build immediate
refresh on commit
enable query rewrite
as
select owner, count(*)
from objetos
group by owner
/

REM Se generan estadísticas para la vista materializada (requisito de CBO)

analyze table vm_resumen_objetos compute statistics;

REM Ahora se ve en acción el efecto de la VM con la misma consulta en la que se basa la VM

set autotrace traceonly

select owner, count(*)
from objetos
group by owner;
set autotrace off
set timing off

28 rows selected.

Elapsed: 00:00:00.03

Execution Plan
———————————————————-
0      SELECT STATEMENT Optimizer=CHOOSE
(Cost=2 Card=28 Bytes=252)
1    0   TABLE ACCESS (FULL) OF ‘ VM_RESUMEN_OBJETOS ‘
(Cost=2 Card=28 Bytes=252)

Se observa que el costo de este segundo plan de ejecución es mucho menor que el costo que tenía el plan de ejecución original lo cual es resultado del gran impacto que tiene en el desempeño esta segunda consulta. Esta es la idea fundamental del QUERY REWRITE.