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

13 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
  6. Parabéns!!!
    Por favor, agradeço se for possível alguma dica... em um projeto sobre futebol, usando o WITH pode dá certo.

    Tabelas resumidas são:

    *Jogador
    ncod_jogador
    cnome_joagador

    *Frequencias
    ncod_jogador
    nFrequencia
    dt_Frequencia

    As consultas que preciso são apenas duas, organizadas pelo total de frequência e assiduidade, sendo essa segunda ordem mais complexa, exemplo:

    Cada mês são 4 jogos, e as frequências são puxadas mês atual + os dois anteriores, a ordem por assiduidade deve ser comparada sempre a cada frequência anterior.

    Tipo dois atletas estão com 8 freq, sendo que o primeiro faltou o último jogo 07/04 e o segundo não, então o segundo já fica na frente na ordem, caso continuassem empatados, precisaria comparar frequências anteriores até chegar em uma que um faltou e esse ficará abaixo na ordem da relação.

    Abaixo o select inicial que falta ordenar por assiduidade:

    select
    frequencias.ncod_jogador,
    frequencias.nfrequencia,
    jogador.cnome_jogador
    sum(nfrequencia) as Total_Freq

    from frequencias
    left join jogador on (frequencias.ncod_jogador = jogador.ncod_jogador)
    where CAST(frequencias.dt_frequencia as date)
    BETWEEN cast( dateadd (day, -90,current_date) as date) and cast(current_date as date)

    Group by
    frequencias.ncod_jogador,
    frequencias.nfrequencia,
    jogador.cnome_jogador
    Order by Total_Freq DESC;

    ResponderExcluir
    Respostas
    1. Fazer esse tipo de análise leva bastante tempo e eu dei apenas uma lida rápida no seu problema. Pelo pouco que entendi, acredito que você não precisaria usar a cláusula WITH. Acredito que funções analíticas, tais como TOP, RANK etc irão te ajudar mais. Tem um artigo que explica o básico sobre funções analíticas aqui no blog. Sugiro você dar uma olhada nele, nas referências dele e pesquisar mais a fundo no Google.

      Excluir
  7. Eu já tinha visto o uso no Postgresql mas nunca no oracle. Realmente muito bom e já estou usando em minhas querys. Obrigado grande Fábio pelo artigo. Excelente.

    ResponderExcluir

 

LINKS ÚTEIS

Total de visualizações de página

Seguidores

Meu One Drive (antigo Sky Drive)