Cláusula WITH (para tunar queries)

     Pessoal, segue abaixo um pequeno tutorial e exemplo de como utilizar a cláusula WITH no Oracle Database.

    A cláusula WITH, existente no Oracle Database a partir da versão 9i release 2, é genericamente conhecida como Commom Table Expression (CTE) e faz parte do padrão ANSI SQL 99. Ela pode ser utilizada para otimizar a performance de consultas SQL , possibilitando a reutilização de blocos de subquery ou tabelas que são referenciadas N vezes dentro da mesma query, criando uma espécie de tabela temporária. Essa tabela temporária existe somente no escopo da instrução SQL em que a cláusula WITH está contida e ela é armazenada em memória ou em um tablespace temporário, que possui acesso mais rápido que um tablespace comum.

    A cláusula WITH permite a redução do uso de recursos do Banco de Dados ao executar queries complexas ou remotas que referenciam na mesma instrução uma ou mais tabelas, N vezes. Isso é muito comum em queries que geram relatórios e que possuem muitas subqueries. Segue abaixo 1 exemplo de query que consulta dados no schema de exemplo HR do Oracle 10G, alterada para utilizar a cláusula WITH.

   
    1- QUERY ORIGINAL:    

        SELECT         D.DEPARTMENT_NAME,
                               SUM(E.SALARY) AS DEPT_TOTAL
        FROM            HR.EMPLOYEES E
        INNER JOIN  HR.DEPARTMENTS D
                ON          E.DEPARTMENT_ID = D.DEPARTMENT_ID
        GROUP BY    D.DEPARTMENT_NAME
        HAVING        SUM(E.SALARY)   >  (
                                              SELECT  SUM(DEPT_TOTAL)/COUNT(*)
                                              FROM    (
                                                        SELECT      D.DEPARTMENT_NAME,
                                                                           SUM(E.SALARY) AS DEPT_TOTAL
                                                        FROM        HR.EMPLOYEES E
                                                        INNER JOIN  HR.DEPARTMENTS D
                                                            ON        E.DEPARTMENT_ID = D.DEPARTMENT_ID
                                                        GROUP BY    D.DEPARTMENT_NAME))                                                       
        ORDER BY    department_name;

    2- QUERY ALTERADA COM CLÁUSULA WITH:

WITH
          DEPT_COSTS AS (
                          SELECT          D.DEPARTMENT_NAME,
                                                  SUM(E.SALARY) AS DEPT_TOTAL
                          FROM             HR.EMPLOYEES E
                          INNER JOIN  HR.DEPARTMENTS D
                              ON              E.DEPARTMENT_ID = D.DEPARTMENT_ID
                          GROUP BY    D.DEPARTMENT_NAME),
          AVG_COST AS (
                          SELECT         SUM(DEPT_TOTAL)/COUNT(*) AS DEPT_AVG
                          FROM            HR.DEPT_COSTS)
        SELECT         *
        FROM           DEPT_COSTS
        WHERE         DEPT_TOTAL >  ( SELECT  DEPT_AVG
                                                          FROM    AVG_COST)
        ORDER BY   department_name;

     Observem que na query 2, blocos de SQL redundantes foram eliminados através da criação da tabela temporária DEPT_COSTS. Em testes que eu fiz, o custo médio de execução dessa query caiu aproximadamente 30%, após utilizar a cláusula WITH (query 2).