Ejemplo de reescritura de compilador: movimiento de operación

Durante la reescritura de consultas, el optimizador puede mover el punto en el que se realiza la operación DISTINCT, para reducir el coste de esta operación. En algunos casos, la operación DISTINCT se puede eliminar completamente.

Por ejemplo, si la columna EMPNO de la tabla EMPLOYEE se ha definido como clave primaria, la consulta siguiente:

   select distinct empno, firstnme, lastname
     from employee

podría reescribirse eliminando la cláusula DISTINCT:

   select empno, firstnme, lastname
     from employee

En este ejemplo, debido a que se está seleccionando la clave primaria, el compilador sabe que cada fila devuelta es exclusiva. En este caso, la palabra clave DISTINCT es redundante. Si la consulta no se reescribe, el optimizador debe crear un plan con el proceso necesario (por ejemplo, una clasificación) para asegurarse de que los valores de columna son exclusivos.

Ejemplo: desplazamiento descendente de predicado general

La modificación del nivel en el que se aplica normalmente un predicado puede mejorar el rendimiento. Por ejemplo, la vista siguiente proporciona una lista de todos los empleados del departamento D11:

   create view d11_employee
     (empno, firstnme, lastname, phoneno, salary, bonus, comm) as
       select empno, firstnme, lastname, phoneno, salary, bonus, comm
         from employee
         where workdept = 'D11'

La consulta siguiente para esta vista no es tan eficiente como podría ser:

   select firstnme, phoneno
     from d11_employee
     where lastname = 'BROWN'

Durante la reescritura de la consulta, el compilador envía el predicado lastname = 'BROWN' a la vista D11_EMPLOYEE . Esto permite que el predicado se aplique antes y potencialmente de forma más eficiente. La consulta real que se podría ejecutar en este ejemplo es la siguiente:

   select firstnme, phoneno
     from employee
     where
       lastname = 'BROWN' and
       workdept = 'D11'

El desplazamiento descendente de predicado no se limita a las vistas. Otras situaciones en las que los predicados se pueden desplazar hacia abajo son UNION, GROUP BY y tablas derivadas (expresiones de tabla anidadas o expresiones de tabla comunes).

Ejemplo-Decorrelación

En un entorno de base de datos particionada, el compilador puede reescribir la siguiente consulta, que está diseñada para encontrar todos los empleados que están trabajando en proyectos de programación y que están mal pagados.

   select p.projno, e.empno, e.lastname, e.firstname,
       e.salary+e.bonus+e.comm as compensation
     from employee e, project p
     where
       p.empno = e.empno and
       p.projname like '%PROGRAMMING%' and
       e.salary+e.bonus+e.comm <
         (select avg(e1.salary+e1.bonus+e1.comm)
           from employee e1, project p1
           where
             p1.projname like '%PROGRAMMING%' and
             p1.projno = p.projno and
             e1.empno = p1.empno)

Debido a que esta consulta está correlacionada y debido a que es poco probable que tanto PROJECT como EMPLOYEE se particionen en PROJNO, la difusión de cada proyecto en cada partición de base de datos es posible. Además, la subconsulta tendría que evaluarse muchas veces.

El compilador puede volver a escribir la consulta como se indica a continuación:

Determine la lista distinta de empleados que trabajan en proyectos de programación y llámelo DIST_PROJS. Debe ser distinto para asegurarse de que la agregación se realiza sólo una vez para cada proyecto:
```
   with dist_projs(projno, empno) as
     (select distinct projno, empno
       from project p1
       where p1.projname like '%PROGRAMMING%')
```

Una DIST_PROJS con la tabla EMPLOYEE para obtener la remuneración promedio por proyecto, AVG_PER_PROJ:

   avg_per_proj(projno, avg_comp) as
     (select p2.projno, avg(e1.salary+e1.bonus+e1.comm)
       from employee e1, dist_projs p2
       where e1.empno = p2.empno
       group by p2.projno)

La consulta reescrita es:

   select p.projno, e.empno, e.lastname, e.firstname,
       e.salary+e.bonus+e.comm as compensation
     from project p, employee e, avg_per_prog a
     where
       p.empno = e.empno and
       p.projname like '%PROGRAMMING%' and
       p.projno = a.projno and
       e.salary+e.bonus+e.comm < a.avg_comp

Esta consulta calcula el avg_comp por proyecto (avg_per_proj). A continuación, el resultado se puede difundir a todas las particiones de base de datos que contienen la tabla EMPLOYEE.