Ejemplo de reescritura del compilador: fusión de operaciones

Una sentencia SELECT que utiliza vistas puede restringir el orden de unión de la tabla y también puede introducir la unión redundante de tablas. Si las vistas se fusionan durante la reescritura de consultas, estas restricciones se pueden eliminar.

Supongamos que tiene acceso a las dos vistas siguientes que se basan en la tabla EMPLOYEE: una que muestra los empleados que tienen un alto nivel de formación y la otra que muestra los empleados que ganan más de 35.000 dólares al año:

   create view emp_education (empno, firstnme, lastname, edlevel) as
     select empno, firstnme, lastname, edlevel
       from employee
       where edlevel > 17

   create view emp_salaries (empno, firstname, lastname, salary) as
     select empno, firstnme, lastname, salary
       from employee
       where salary > 35000

La siguiente consulta escrita por el usuario lista aquellos empleados que tienen un alto nivel de educación y que ganan más de 35.000 dólares al año:

   select e1.empno, e1.firstnme, e1.lastname, e1.edlevel, e2.salary
     from emp_education e1, emp_salaries  e2
     where e1.empno = e2.empno

Durante la reescritura de consultas, estas dos vistas se pueden fusionar para crear la consulta siguiente:

   select e1.empno, e1.firstnme, e1.lastname, e1.edlevel, e2.salary
     from employee e1, employee e2
     where
       e1.empno = e2.empno and
       e1.edlevel > 17 and
       e2.salary  > 35000

Al fusionar las sentencias SELECT de las dos vistas con la sentencia SELECT escrita por el usuario, el optimizador puede considerar más opciones al seleccionar un plan de acceso. Además, si las dos vistas que se han fusionado utilizan la misma tabla base, es posible que se realice una reescritura adicional.

Ejemplo: subconsulta para unir transformaciones

El compilador de SQL y XQuery tomará una consulta que contenga una subconsulta, como por ejemplo:

   select empno, firstnme, lastname, phoneno
     from employee
     where workdept in
       (select deptno
         from department
         where deptname = 'OPERATIONS')

y conviértalo en una consulta de unión con el formato:

   select distinct empno, firstnme, lastname, phoneno
     from employee emp, department dept
     where
       emp.workdept = dept.deptno and
       dept.deptname = 'OPERATIONS'

Una unión es generalmente mucho más eficiente de ejecutar que una subconsulta.

Ejemplo: eliminación de unión redundante

Las consultas a veces tienen uniones innecesarias.

   select e1.empno, e1.firstnme, e1.lastname, e1.edlevel, e2.salary
     from employee e1,
          employee e2
    where e1.empno = e2.empno
      and e1.edlevel > 17
      and e2.salary  > 35000

El compilador de SQL y XQuery puede eliminar la unión y simplificar la consulta para:

   select empno, firstnme, lastname, edlevel, salary
     from employee
     where
       edlevel > 17 and
       salary  > 35000

El ejemplo siguiente presupone que existe una restricción referencial entre las tablas EMPLOYEE y DEPARTMENT en el número de departamento. En primer lugar, se crea una vista.

   create view peplview as
     select firstnme, lastname, salary, deptno, deptname, mgrno
       from employee e department d
       where e.workdept = d.deptno

A continuación, una consulta como la siguiente:

   select lastname, salary
     from peplview

se convierte en:

   select lastname, salary
     from employee
     where workdept not null

Tenga en cuenta que en esta situación, incluso si sabe que la consulta se puede volver a escribir, es posible que no pueda hacerlo porque no tiene acceso a las tablas subyacentes. Es posible que sólo tenga acceso a la vista (mostrada anteriormente). Por lo tanto, el gestor de bases de datos debe realizar este tipo de optimización.

La redundancia en uniones de integridad referencial probablemente se produce cuando:

Las vistas se definen con uniones
Las consultas se generan automáticamente

Ejemplo-Agregación compartida

El uso de varias funciones dentro de una consulta puede generar varios cálculos que tardan tiempo. La reducción del número de cálculos necesarios mejora el plan. El compilador toma una consulta que utiliza varias funciones, como las siguientes:

   select sum(salary+bonus+comm) as osum,
       avg(salary+bonus+comm) as oavg,
       count(*) as ocount
     from employee

y lo transforma:

   select osum, osum/ocount ocount
     from (
       select sum(salary+bonus+comm) as osum,
           count(*) as ocount
         from employee
     ) as shared_agg

Esta reescritura reduce a la mitad el número necesario de sumas y recuentos.