Las consultas SQL con clausula WITH

Nota: la select que pones en la cclausula WITH solo se ejecuta una vez, y reduce considerablemente el tiempo de ejecución.

Las consultas SQL complejas que usan subquerys pueden ejecutarse de manera mas rapida con el uso de tablas globales temporales.

Desde las versiones de Oracle 9i R2 ya se implemento una utilidad que ayuda a simplificar las subconsultas complejas con el comando WITH. El comando WITH es una utilidad que materializa las subconsultas(Genera una temporal)

Consideraciones:

Se recomienda su uso si la subconsulta se ejecuta varias veces

Se crea automaticamente una temporal

No funciona en consultas recursivas

Estructura:

WITH

   nommbre_subconsulta

AS

  (El sql que se materializará)

SELECT

  (SQL en el que se usará la subconsulta);

 

Veamos el siguiente ejemplo:

Tenemos la tabla de clientes y la tabla de ventas, deseamos obtener por cliente el total vendido, cantidad de facuras, total de montos pendientes, cantidad de facturas pendientes y que el promedio de ventas sea mayor a 15000.

SQL implementado con subconsultas

Select C.Cliente_Id,C.Nombre_Completo,

(Select Sum(V.Capital) From  Ventas V Where V.Cliente_Id=C.Cliente_Id) Monto_Ventas,

(Select Count(*) From  Ventas V Where V.Cliente_Id=C.Cliente_Id) Cantidad_Ventas,

(Select Avg(V.Capital) From  Ventas V Where V.Cliente_Id=C.Cliente_Id) Promedio_Ventas,

(Select Sum(V.Capital) From  Ventas V Where V.Cliente_Id=C.Cliente_Id And Pagada='N') Monto_Pendiente,

(Select Count(*) From  Ventas V Where V.Cliente_Id=C.Cliente_Id And Pagada='N') Cantidad_Pendiente

From Clientes C

Where (Select Avg(V.Capital) From  Ventas V Where V.Cliente_Id=C.Cliente_Id)>15000;

Ahora vemos el ejemplo con la clausula WITH

With T_Ventas --Nombre de la subconsulta

As( --Seccion de la subconsulta

    Select  V.Cliente_Id,

            Sum(V.Capital)  Monto_Ventas,

            Count(*)        Cantidad_Ventas,

            Avg(V.Capital)  Promedio_Ventas,

            Sum(V.Capital)  Monto_Pendiente,

            Count(*)        Cantidad_Pendiente   

    From Ventas V

    Group By V.Cliente_Id

)

--Sección en donde hacemos uso de la subconsulta

Select  C.Cliente_Id,

        C.Nombre_Completo,

        Vt.Monto_Ventas,

        Vt.Cantidad_Ventas,

        Vt.Promedio_Ventas,

        Vt.Monto_Pendiente,

Vt.Cantidad_Pendiente

From Clientes C

Inner Join T_Ventas Vt On C.Cliente_Id=Vt.Cliente_Id

Where Vt.Promedio_Ventas>15000;

Lo único nuevo es el uso de la cláusula WITH por que de ahí el join y condición con T_Ventas se maneja como si fuera una tabla.

Conculsión:

Con la introducción de la cláusula WITH simplificamos el uso de consultas SQL que usan subconsultas, ademas de que los tiempos de respuesta de la consulta son mejores con WITH que con el uso de subsonsultas.

Fuentes. 

Artículo:   "Sentencia WITH para simplicar SQL complejos" Publicado en http://oracle-y-yo.blogspot.com/ por Víctor Endara Manosalvas el 05/07/2011. Consultado el 03/12/2021.

URL: http://oracle-y-yo.blogspot.com/2011/07/sentencia-with-para-simplicar-sql.html