Social Icons

1 de out. de 2010

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).
   

9 comments:

  1. parabéns, estamos precisando testar numa situação parecida. Não sei se ajuda, mas foi legal a dica ..

    ResponderExcluir
  2. Legal, fico contente que o artigo tenha te ajudado! Abs.

    ResponderExcluir
  3. Muito Bom!
    Super útil! Principalmente na sintuação em que é preciso verificar uma série de consistências nas consultas, nas quais são feitas várias subqueries, várias vezes cada uma.
    Essa, realmente, é uma solução!
    Ótima solução.

    ResponderExcluir
  4. Sensacional, Fábio! Artigo de fácil entendimento e de grande utilidade. Obrigada.

    ResponderExcluir
  5. Fábio, boa tarde, tudo bem?
    Parabéns pelo post.
    Me tira uma dúvida. Percebo que a cláusula WITH é interessante quando você usa subqueries redundantes.
    Em qual ponto isso seria interessante utilizar a cláusula WITH num bloco PL/SQL, substituindo Cursores por isso, sem ter subqueries redundantes?
    Que eu saiba, a cláusula WITH deverá ser usada, de qualquer forma, num retorno em um ROWTYPE, array, etc.
    Abs e parabéns mais uma vez pelo site!!!
    Maia.

    ResponderExcluir
    Respostas
    1. Maia, não sei se entendi muito bem o contexto do seu problema, mas vou responder do jeito que entendi. Não há sentido em usar a cláusula WITH se vc não usa uma ou mais tabelas N vezes dentro de uma instrução SQL.

      Qto ao substituir um cursor por uma cláusula WITH, se vc puder fazer isso, faça. EVITE SEMPRE PL/SQL se vc puder fazer o mesmo com SQL PURO. Quando vc cria um cursor vc está usando um bloco PL/SQL. Se vc não puder evitar o cursor, mas tiver uma tabela que é referenciada N vezes dentro da instrução SQL, utilize a cláusula WITH (por que não?), com certeza vc irá otimizá-la!

      []s

      Excluir
    2. Fábio, muito obrigado.
      Era isso mesmo que você entendeu. Tem uma pessoa, que se diz "responsável" por tunning de uma empresa, teimando que o WITH pode ser usado sem referenciarmos N vezes uma tabela ou X vezes a mesma subquery. Mas enfim!!!
      Abs e sucesso!
      Maia.

      Excluir

 

LINKS ÚTEIS

Total de visualizações de página

Seguidores

Meu One Drive (antigo Sky Drive)