Social Icons

27 de dez de 2013

Por que usar Funções analíticas no Oracle Database?

Olá pessoal,

   No artigo de hoje (último do ano de 2013), vou comentar sobre Funções analíticas e darei um exemplo de como elas podem ser úteis para resolver problemas comuns e otimizar a performance de instruções SQL.

      As funções analíticas foram introduzidas no Oracle 8i e normalmente são utilizadas para executar tarefas que antes eram realizadas por stored procedures ou funções (criadas em PL/SQL), e resolver problemas comuns, como por exemplo, transformar em colunas o resultado de múltiplas linhas (exemplo que será demonstrado mais adiante) ou classificar os valores de uma determinada coluna dentro de cada grupo de um conjunto de linhas (ver exemplos dos links das referências ao final do artigo).
    
     As funções analíticas muitas vezes são confundidas com funções de agregação, portanto, é importante entender que a diferença principal entre elas é que as primeiras retornam múltiplas linhas para cada grupo dentro de uma instrução SQL, enquanto que, as segundas, retornam apenas uma linha para o grupo todo. A grande maioria das funções analíticas podem ser usadas como funções agregadas, e vice-versa. Uma restrição, é que as funções analíticas podem ser usadas somente na lista de colunas ou na cláusula ORDER BY de instruções SELECT.

     Apesar de muitas vezes as funções analíticas não serem muito fáceis de usar, normalmente elas são mais performáticas do que stored procedures ou funções que criamos para resolver o mesmo problema. Além disso, é mais fácil usar algo que já está pronto e é bom, do que tentar reinventar a roda e correr o risco dessa roda sair meio quadrada!

     Alguns exemplos de funções analíticas: AVG, FIRST, LAST, LISTAGG, MAX, MIN, PERCENT_RANK e RANK. Para ver outras funções, consulte as referências.

     Segue abaixo um exemplo de instrução SQL (que eu demonstro nos treinamentos de SQL Tuning) para apresentar uma lista de pedidos e itens de pedidos, que deverá conter a data, código do pedido e código de cada produto (item) do pedido:

      SELECT      P.DT_PEDIDO,
                 P.CD_PEDIDO,
                 I.CD_PRODUTO
     FROM        ECOMMERCE.PEDIDO P
     INNER JOIN  ECOMMERCE.ITEM_PEDIDO I 
            ON   P.CD_PEDIDO = I.CD_PEDIDO
     WHERE       P.CD_PEDIDO between 1900 and 1960
     ORDER BY    1;

    Podemos observar que a instrução SQL acima é bem simples e que o seu resultado também é (ver abaixo o resultado parcial, contendo as 10 primeiras linhas, de um total de 60 que foram retornadas):

DT_PEDIDO                 CD_PEDIDO    CD_PRODUTO
------------------------- ---------------- ------------------
19/09/2008 19:44:38         1956               1 
23/09/2008 19:44:38         1919               1 
23/09/2008 19:44:38         1919               2 
23/09/2008 19:44:38         1919               3 
24/09/2008 19:44:38         1915               3 
24/09/2008 19:44:38         1915               4 
27/09/2008 19:44:38         1960               1 
27/09/2008 19:44:38         1960               
27/09/2008 19:44:38         1960               3 
27/09/2008 19:44:38         1960               

   No resultado parcial acima podemos observar que a maior parte dos pedidos possui mais de um produto. O pedido de número 1919 possui os produtos de códigos 1, 2 e 3. O pedido de número 1915 possui os produtos de códigos 3 e 4. O produto 1960 possui os produtos de códigos 1, 2, 3 e 4. Na minha opinião, a visualização deste resultado não está muito boa. Não seria mais fácil de ler e entender este resultado se todos os produtos do mesmo pedido retornassem na mesma linha? Eu acredito que sim, portanto, vou transformar o resultado, agrupando os produtos de cada pedido (3º coluna).
  
     Essa transformação será feita inicialmente sem utilizar uma função analítica. Criaremos uma função chamada concatenate_list para agrupar os valores de cada pedido e veremos em seguida o tempo de execução de uma instrução SQL que irá chamá-la. Segue abaixo o código de criação da função concatenate_list e a instrução SQL que executaremos para chamá-la:

     create or replace FUNCTION ECOMMERCE.concatenate_list 
                            (p_cursor IN  SYS_REFCURSOR)
  RETURN  VARCHAR2
  IS
    l_return  VARCHAR2(32767); 
    l_temp    VARCHAR2(32767);
  BEGIN
    LOOP
      FETCH p_cursor
      INTO  l_temp;
      EXIT WHEN p_cursor%NOTFOUND;
      l_return := l_return || ',' || l_temp;
    END LOOP;

    RETURN LTRIM(l_return, ',');
  END;
   
  SELECT          P.DT_PEDIDO,
                  P.CD_PEDIDO,
                  I.PRODUTOS
  FROM            ECOMMERCE.PEDIDO P
  INNER JOIN  (SELECT I1.CD_PEDIDO,            
                      ECOMMERCE.concatenate_list(
                         CURSOR(SELECT I2.CD_PRODUTO 
                               FROM   ECOMMERCE.ITEM_PEDIDO I2
                               WHERE  I2.CD_PEDIDO = I1.CD_PEDIDO
                               ORDER BY 1)) AS PRODUTOS
               FROM        ECOMMERCE.ITEM_PEDIDO I1
               GROUP BY    I1.CD_PEDIDO) I
      ON       P.CD_PEDIDO = I.CD_PEDIDO
  WHERE        P.CD_PEDIDO between 1900 and 1960
  ORDER BY     1;

   Após executar o SQL acima, veremos o resultado parcial abaixo:
  
DT_PEDIDO                   CD_PEDIDO  PRODUTOS      
------------------------     ------------     --------------
19/09/2008 19:44:38         1956               1             
23/09/2008 19:44:38         1919               1,2,3         
24/09/2008 19:44:38         1915               3,4           
27/09/2008 19:44:38         1960               1,2,3,4       

   O que você achou? Não ficou melhor? Agora temos como resultado 1 linha por pedido e na coluna PRODUTOS temos a relação de todos os produtos do pedido relacionado, separados pelo caractere vírgula. O tempo médio de execução deste SQL (executado 4 vezes) foi de 0,065s. Agora que tal tentarmos melhorar este tempo usando a função analítica LISTAGG
  
     A função LISTAGG foi criada no Oracle 11G, portanto, em versões anteriores você deverá ainda deverá utilizar a opção anterior (função concatenate_list ou função similar) ou usar uma função não documentada e sem suporte, chamada WM_CONCAT. Segue abaixo o código da instrução SQL anterior, substituindo o uso da função customizada concatenate_list  pela função analítica LISTAGG:
  
    SELECT    P.DT_PEDIDO,
              P.CD_PEDIDO,
              LISTAGG(I.CD_PRODUTO, ',')
                    within group (ORDER BY 1) PRODUTOS
   FROM       ECOMMERCE.ITEM_PEDIDO I
   INNER JOIN ECOMMERCE.PEDIDO P
       ON     I.CD_PEDIDO = P.CD_PEDIDO
   WHERE      P.CD_PEDIDO = 1960
   GROUP BY   P.DT_PEDIDO, P.CD_PEDIDO
   ORDER BY   1;


    Ao executar a instrução SQL acima, o resultado foi igual ao do SQL anterior, porém existem 2 vantagens em utilizá-la.  A 1ª é que a gente não precisou criar nenhuma função extra (função concatenate_list). A 2ª e principal vantagem foi o ganho no tempo de execução, que caiu para 0.043s (na média de 4 execuções). Resumindo, conseguimos obter o mesmo resultado em um tempo 33,84% mais rápido

     Se você ainda não conhecia uma função analítica, minha dica é que você pesquise mais sobre o assunto e passe a utilizá-las sempre que possível! Se você conhecia e já está convencido de que em geral elas são mais performáticas, não há mais desculpas para deixar de usá-las!


Bom pessoal, por hoje é só! 
  
[]s


Referências:
   - Introduction to Analytic Functions
   - [Oracle] Funções analíticas – Parte 1
   - WM_CONCAT

3 comentários:

  1. Num vejo a hora de poder aplicar essa função. Já tive que criar funções similares de concatenação mas, esse LISTAGG é mamão com açucar! Show de bola esse post!

    ResponderExcluir
    Respostas
    1. Cesare, também passei por situação similar no 10G. Lá existia a função WM_CONCAT, mas na época que precisei utilizar eu não conhecia ela!

      []s

      Excluir
    2. Cesare, também passei por situação similar no 10G. Lá existia a função WM_CONCAT, mas na época que precisei utilizar eu não conhecia ela!

      []s

      Excluir

 

Meus últimos Links Favoritos

Suporte remoto alunos

Seguidores

Meu One Drive (antigo Sky Drive)