Desenvolvimento

Ξ Deixe um comentário

Oracle – Substituindo subqueries por funções analíticas – Um Exemplo prático

publicado por Paulo Planez

Muitas vezes nos vemos na necessidade de identificar o último registro de uma tabela baseando-se numa regra específica. Veja o exemplo abaixo:

Criando a Tabela de exemplo:

Create table plz_nota_fiscal
   (nr_nota_fiscal      number
   ,Tipo_nota_fiscal    number
   ,Data_nota_fiscal    date
   ,Valor_nota_fiscal   number);
   
create index plz_nf01 on plz_nota_fiscal (tipo_nota_fiscal);

Inserindo um conjunto de registros para testes:

DECLARE
   V_MOD NUMBER;
BEGIN
   FOR x IN 1..10 LOOP
      FOR y IN 1..50 LOOP
         --> Modificador para diferenciar números
         if x = 1 then
            v_mod := 5;
         elsif x = 2 then
            v_mod := 13;
         elsif x = 3 then
            v_mod := 19;
         elsif x = 4 then
            v_mod := 43;
         else
            v_mod := 198;
         end if;
         --> Insere a nota
         insert into PLZ_NOTA_FISCAL
            (nr_nota_fiscal
            ,tipo_nota_fiscal
            ,data_nota_fiscal
            ,valor_nota_fiscal)
         values
            (y+v_mod
            ,x
            ,sysdate+y+x
            ,y+x);
      END LOOP;
   END LOOP;
   commit;
END;
/

Agora, precisa identificar somente as últimas notas geradas para cada tipo específico de nota:

Select nr_nota_fiscal
  from PLZ_NOTA_FISCAL
 where tipo_nota_fiscal = 3
   and nr_nota_fiscal = (select MAX(nr_nota_fiscal)
                           from PLZ_NOTA_FISCAL
                          where tipo_nota_fiscal = 3)

Agora, vejamos como fica o plano de ação para esta query:

-------------------------------------------------------------------------------------------------
| Id  | Operation                     | Name            | Rows  | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT              |                 |     1 |    26 |     2   (0)| 00:00:01 |
|*  1 |  TABLE ACCESS BY INDEX ROWID  | PLZ_NOTA_FISCAL |     1 |    26 |     1   (0)| 00:00:01 |
|*  2 |   INDEX RANGE SCAN            | PLZ_NF01        |     2 |       |     1   (0)| 00:00:01 |
|   3 |   SORT AGGREGATE              |                 |     1 |    26 |            |          |
|   4 |    TABLE ACCESS BY INDEX ROWID| PLZ_NOTA_FISCAL |    50 |  1300 |     1   (0)| 00:00:01 |
|*  5 |     INDEX RANGE SCAN          | PLZ_NF01        |     2 |       |     1   (0)| 00:00:01 |
-------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - filter("NR_NOTA_FISCAL"= (SELECT MAX("NR_NOTA_FISCAL") 
                                   FROM "PLZ_NOTA_FISCAL"
                                  WHERE "TIPO_NOTA_FISCAL"=3))
   2 - access("TIPO_NOTA_FISCAL"=3)
   5 - access("TIPO_NOTA_FISCAL"=3)

Agora vamos testar de outra maneira, só que desta vez usando as funções analíticas disponíveis no Oracle:

select tipo_nota_fiscal
     , ultimo_numero
  from (select nr_nota_fiscal
             , tipo_nota_fiscal
             , data_nota_fiscal
             , valor_nota_fiscal
             , MAX(nr_nota_fiscal) over (partition by tipo_nota_fiscal) ultimo_numero
          from PLZ_NOTA_FISCAL)
 where nr_nota_fiscal   = ultimo_numero
   and tipo_nota_fiscal = 3

Agora, vejamos como fica o plano de acesso para esta query:

-------------------------------------------------------------------------------------------------
| Id  | Operation                     | Name            | Rows  | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT              |                 |    50 |  1950 |     1   (0)| 00:00:01 |
|*  1 |  VIEW                         |                 |    50 |  1950 |     1   (0)| 00:00:01 |
|   2 |   WINDOW BUFFER               |                 |    50 |  1300 |     1   (0)| 00:00:01 |
|   3 |    TABLE ACCESS BY INDEX ROWID| PLZ_NOTA_FISCAL |    50 |  1300 |     1   (0)| 00:00:01 |
|*  4 |     INDEX RANGE SCAN          | PLZ_NF01        |     2 |       |     1   (0)| 00:00:01 |
-------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   1 - filter("NR_NOTA_FISCAL"="ULTIMO_NUMERO")
   4 - access("TIPO_NOTA_FISCAL"=3)

Resultado da comparação entre os planos de acesso:

Na busca usando subquery, ele gerou um custo maior e um plano de acesso onde acessou duas vezes a mesma tabela e o mesmo índice.

Na busca usando funções analíticas, ele gerou um custo menor e um plano de acesso simplificado.

Agora, Vamos fazer o teste retornando o último número de notas fiscais para cada tipo existente:

Primeiro, com a subquery:

Select nr_nota_fiscal
  from PLZ_NOTA_FISCAL
 where (tipo_nota_fiscal, nr_nota_fiscal) in (select tipo_nota_fiscal
                                                   , MAX(nr_nota_fiscal)
                                                from PLZ_NOTA_FISCAL
                                               group by tipo_nota_fiscal)

Vejamos o plano e acesso para esta query:

--------------------------------------------------------------------------------------------------
| Id  | Operation                      | Name            | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT               |                 | 25000 |  1562K|     5  (40)| 00:00:01 |
|*  1 |  FILTER                        |                 |       |       |            |          |
|   2 |   HASH GROUP BY                |                 | 25000 |  1562K|     5  (40)| 00:00:01 |
|   3 |    NESTED LOOPS                |                 |       |       |            |          |
|   4 |     NESTED LOOPS               |                 | 25000 |  1562K|     3   (0)| 00:00:01 |
|   5 |      TABLE ACCESS FULL         | PLZ_NOTA_FISCAL |   500 | 13000 |     3   (0)| 00:00:01 |
|*  6 |      INDEX RANGE SCAN          | PLZ_NF01        |    50 |       |     0   (0)| 00:00:01 |
|   7 |     TABLE ACCESS BY INDEX ROWID| PLZ_NOTA_FISCAL |    50 |  1900 |     0   (0)| 00:00:01 |
--------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - filter("NR_NOTA_FISCAL"=MAX("NR_NOTA_FISCAL"))
   6 - access("TIPO_NOTA_FISCAL"="TIPO_NOTA_FISCAL")

Agora, usando a função analítica:

select tipo_nota_fiscal
     , ultimo_numero
  from (select nr_nota_fiscal
             , tipo_nota_fiscal
             , data_nota_fiscal
             , valor_nota_fiscal
             , MAX(nr_nota_fiscal) over (partition by tipo_nota_fiscal) ultimo_numero
          from PLZ_NOTA_FISCAL)
 where nr_nota_fiscal   = ultimo_numero;
 
 ---------------------------------------------------------------------------------------
 | Id  | Operation           | Name            | Rows  | Bytes | Cost (%CPU)| Time     |
 ---------------------------------------------------------------------------------------
 |   0 | SELECT STATEMENT    |                 |   500 | 19500 |     4  (25)| 00:00:01 |
 |*  1 |  VIEW               |                 |   500 | 19500 |     4  (25)| 00:00:01 |
 |   2 |   WINDOW SORT       |                 |   500 | 13000 |     4  (25)| 00:00:01 |
 |   3 |    TABLE ACCESS FULL| PLZ_NOTA_FISCAL |   500 | 13000 |     3   (0)| 00:00:01 |
 ---------------------------------------------------------------------------------------
 
 Predicate Information (identified by operation id):
 ---------------------------------------------------
     1 - filter("NR_NOTA_FISCAL"="ULTIMO_NUMERO")

Resultado da comparação entre os planos de acesso:

Na busca pela subquery, ele retornou um número bem maior de linhas por causa da cardinalidade entre elas, fazendo acesso full na tabela.

Na busca pela função analítica, ele retornou o número exato de bytes existentes na tabela por não gerar cardinalidade, o que resulta num custo menor, apesar do acesso full.

Apesar da pouca diferença do custo, a diferença no consumo de CPU bem como no número de linhas e bytes processados foi significativo.

Conclusão

Substituir a subquery por uma função analítica nestes casos pode gerar um ganho significativo de desempenho, especialmente se esse comando é executando dentro de um loop.

Artigos Relacionados

Autor

Graduado em administração com especialização em Finanças, atua desde 1990 com sistemas de informação, em sua maioria focado em sistemas de gestão financeira. ♪

Atuou com sistemas de informação proprietários dos mais variados como Sistemas de gestão de Grãos, Sistemas de controle financeiro para transações eletrônicas e, no segmento de governo, com Sistemas Financeiros e de Sanidade Animal e Vegetal e com sistemas para processamento de Malha Fiscal. ♪

Atua por mais de 15 anos com sistemas de gestão (ERP) cobrindo ciclos operacionais como “Quote to Cash” e “Purchase to Pay” utilizando software da Oracle Corporation (Oracle EBS) em empresas como Globo, Motorola, Alcoa e Dell. ♪

Possui como linha de estudo e pesquisa a economicidade dos sistemas de informação, de modo a extrair destes o máximo de benefícios com o mínimo de recursos.♪

Contato: paulo.planez@gmail.com

Paulo Planez

Comentários

Deixe uma resposta

O seu endereço de e-mail não será publicado. Campos obrigatórios são marcados com *

Siga-nos!

Patrocínio

SUPER PROMOÇÃO

Facebook

Facebook By Weblizar Powered By Weblizar

Inscreva-se em nossa Newsletter