O Enigma da Data Vigente
A noção de vigência de registros é muito comum nos sistemas de informação. É fato que alguns profissionais preferem determinadas modelagens a outras e, na minha opinião, não acho que seja o caso de se criar muitas conjecturas de qual é a melhor forma de se modelar.
Sobre esse tópico em especial, tive uma experiência bem interessante quando fui chamado para tentar dar maior performance num processo de desempenho bastante comprometido.
Gostaria de dividir com vocês.
Pra simularmos resumidamente a modelagem com que me deparei na ocasião criei essa instrução CTAS:
21:03:28 SQL> CREATE TABLE tst_vig AS
21:03:28 2 SELECT MOD(ROWNUM - 1, 1000) + 1 ID,
21:03:28 3 DATE '1980-01-01' + FLOOR((ROWNUM - 1) / 1000) * 10 dt,
21:03:28 4 ROUND(dbms_random.value * 1000, 2) vlr
21:03:28 5 FROM dual
21:03:28 6 CONNECT BY LEVEL <= 10000;
Table created.
Elapsed: 00:00:00.24
21:03:29 SQL>Temos um campo ID que seria a chave da tabela não fosse o fato de termos recorrência da chave com início de vigências diferentes no campo que chamei de DT.
Para cada vigência em questão temos um valor correspondente que pode aumentar ou diminuir a cada nova vigência. O início de uma vigência sempre finaliza a vigência anterior fato que nos permite abrir mão de um campo “FIM_VIGENCIA”.
Analisemos o caso do ID 1 como ficou:
21:03:29 SQL> SELECT id, dt, vlr
21:03:29 2 FROM TST_VIG t
21:03:29 3 WHERE t.id = 1
21:03:29 4 ORDER BY t.dt DESC;
ID DT VLR
---------- ---------- ----------
1 31/03/1980 797,5
1 21/03/1980 137,31
1 11/03/1980 793,35
1 01/03/1980 899,46
1 20/02/1980 870,92
1 10/02/1980 704,71
1 31/01/1980 311,92
1 21/01/1980 269,7
1 11/01/1980 415,03
1 01/01/1980 766,38
10 rows selected.
Elapsed: 00:00:00.03
21:03:29 SQL>Podemos perceber que há variação dos valores conforme o tempo passa. O valor atual para o registro de ID 1 seria, então, 797,50, pois é o valor cuja data de vigência tem o início mais recente.
O processo em questão deveria tirar uma fotografia diária de todos os valores vigentes no dia corrente para realizar integração com uma base auxiliar de aplicações WEB.
Neste caso, deveríamos, para o ID 1, enviar somente o registro:
ID DT VLR
---------- ---------- ----------
1 31/03/1980 797,5Para recuperarmos a data vigente não tem segredo; uma simples função MAX de agregação resolve. O que já não acontece para o campo de valor, necessariamente.
21:03:29 SQL> SELECT /* errado */
21:03:29 2 id, MAX(dt) dt_vigente, MAX(vlr) vlr_vigente
21:03:29 3 FROM TST_VIG t
21:03:29 4 WHERE t.id = 1
21:03:29 5 GROUP BY ID;
ID DT_VIGENTE VLR_VIGENTE
---------- ---------- -----------
1 31/03/1980 899,46
1 row selected.
Elapsed: 00:00:00.02
21:03:29 SQL>Pois é, isso é fácil de se perceber. Logo de cara nos damos conta que não é uma situação tão trivial assim, pois devemos considerar o preço cujo registro tem a data mais recente.
Eis a solução adotada e que apresentava performance questionável. Vou ativar o trace do SQLPLUS para nos dar alguma ideia do que estaria ocorrendo.
21:03:29 SQL> set autot on;
21:03:29 SQL>
21:03:29 SQL> SELECT id, dt, vlr
21:03:29 2 FROM TST_VIG t
21:03:29 3 WHERE t.id = 1
21:03:29 4 AND dt = (SELECT MAX(dt)
21:03:29 5 FROM TST_VIG
21:03:29 6 WHERE t.id = ID);
ID DT VLR
---------- ---------- ----------
1 31/03/1980 797,5
1 row selected.
Elapsed: 00:00:00.03
Execution Plan
----------------------------------------------------------
Plan hash value: 3436695474
--------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 39 | 24 (0)| 00:00:01 |
|* 1 | HASH JOIN | | 1 | 39 | 24 (0)| 00:00:01 |
| 2 | VIEW | VW_SQ_1 | 1 | 22 | 12 (0)| 00:00:01 |
| 3 | HASH GROUP BY | | 1 | 12 | 12 (0)| 00:00:01 |
|* 4 | TABLE ACCESS FULL| TST_VIG | 10 | 120 | 12 (0)| 00:00:01 |
|* 5 | TABLE ACCESS FULL | TST_VIG | 10 | 170 | 12 (0)| 00:00:01 |
--------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - access("DT"="MAX(DT)" AND "T"."ID"="ITEM_1")
4 - filter("ID"=1)
5 - filter("T"."ID"=1)
Statistics
----------------------------------------------------------
2 recursive calls
0 db block gets
71 consistent gets
0 physical reads
0 redo size
678 bytes sent via SQL*Net to client
544 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed
21:03:29 SQL>A solução atende funcionalmente a necessidade de negócio e neste nosso cenário reduzido não apresenta leituras lógicas comprometedoras.
No cenário real, no entanto, tínhamos cerca de 800 milhões de registros com uma granularidade de aproximadamente 160 milhões de chaves distintas.
Diferentemente do que está representado aqui neste exemplo, tínhamos uma chave primária na tabela e índices que auxiliavam na subquery.
Mas o que me chamou a atenção quando analisei o plano de execução foi o fato de ter que percorrer a tabela inteira mais os acessos dos índices para selecionar os registros corretos.
Este tipo de situação deve ser realmente evitada, pois é exatamente esse tipo de acesso que aumenta as leituras lógicas.
Na minha caixa de ferramentas sempre levo comigo as funções analíticas. Elas são muito úteis em diversos casos e geralmente servem de apoio às estratégias de otimização de performance.
Vasculhando a minha caixa encontrei a função FIRST_VALUE que permitiu otimizar as leituras lógicas.
Vejamos uma abordagem inicial para essa função. Como toda função analítica, na requer a cláusula GROUP BY:
21:03:29 SQL> SELECT id,
21:03:29 2 dt,
21:03:29 3 vlr,
21:03:29 4 FIRST_VALUE(vlr) OVER(PARTITION BY ID ORDER BY dt DESC) vlr_vigente
21:03:29 5 FROM TST_VIG t
21:03:29 6 WHERE t.id = 1
21:03:29 7 ORDER BY t.dt DESC;
ID DT VLR VLR_VIGENTE
---------- ---------- ---------- -----------
1 31/03/1980 797,5 797,5
1 21/03/1980 137,31 797,5
1 11/03/1980 793,35 797,5
1 01/03/1980 899,46 797,5
1 20/02/1980 870,92 797,5
1 10/02/1980 704,71 797,5
1 31/01/1980 311,92 797,5
1 21/01/1980 269,7 797,5
1 11/01/1980 415,03 797,5
1 01/01/1980 766,38 797,5
10 rows selected.
Elapsed: 00:00:00.07
Execution Plan
----------------------------------------------------------
Plan hash value: 445364442
------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 10 | 170 | 12 (0)| 00:00:01 |
| 1 | WINDOW SORT | | 10 | 170 | 12 (0)| 00:00:01 |
|* 2 | TABLE ACCESS FULL| TST_VIG | 10 | 170 | 12 (0)| 00:00:01 |
------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - filter("T"."ID"=1)
Statistics
----------------------------------------------------------
1 recursive calls
0 db block gets
34 consistent gets
0 physical reads
0 redo size
972 bytes sent via SQL*Net to client
544 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
1 sorts (memory)
0 sorts (disk)
10 rows processed
21:03:29 SQL>Podemos verificar que o resultset ainda não é o esperado mas temos um bom começo. Já reduzimos o acesso à tabela e já conseguimos destacar o valor vigente.
Só precisamos fazer mais algum ajuste e teremos o resultset desejado:
21:03:29 SQL> SELECT ID, MAX(dt) dt_vigente, vlr_vigente
21:03:29 2 FROM (SELECT id,
21:03:29 3 dt,
21:03:29 4 FIRST_VALUE(vlr) OVER(PARTITION BY ID ORDER BY dt DESC) vlr_vigente
21:03:29 5 FROM TST_VIG t
21:03:29 6 WHERE t.id = 1)
21:03:29 7 GROUP BY ID, vlr_vigente;
ID DT_VIGENTE VLR_VIGENTE
---------- ---------- -----------
1 31/03/1980 797,5
1 row selected.
Elapsed: 00:00:00.02
Execution Plan
----------------------------------------------------------
Plan hash value: 4081778672
--------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 26 | 12 (0)| 00:00:01 |
| 1 | HASH GROUP BY | | 1 | 26 | 12 (0)| 00:00:01 |
| 2 | VIEW | | 10 | 260 | 12 (0)| 00:00:01 |
| 3 | WINDOW SORT | | 10 | 170 | 12 (0)| 00:00:01 |
|* 4 | TABLE ACCESS FULL| TST_VIG | 10 | 170 | 12 (0)| 00:00:01 |
--------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
4 - filter("T"."ID"=1)
Statistics
----------------------------------------------------------
1 recursive calls
0 db block gets
34 consistent gets
0 physical reads
0 redo size
694 bytes sent via SQL*Net to client
544 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
1 sorts (memory)
0 sorts (disk)
1 rows processed
21:03:29 SQL>Pronto. Chegamos ao resultado esperado. Diminuímos LIOs. Diminuímos o custo da execução, mas isso também não é uma análise final pois não estamos com o índice adequado e estamos trabalhando com uma pequena fatia da tabela.
Esta análise é apenas para entendermos outras alternativas utilizando álgebras diferentes para atingir o mesmo resultado.
Essa abordagem ainda não me deixou satisfeito, pois temos no plano mostrado o método WINDOW SORT para aplicar o FIRST_VALUE e depois o HASH GROUP BY para aplicar o MAX.
Fiquei pensando se não existiria a forma agregadora da função FIRST_VALUE. Funções polivalentes são razoavelmente comuns no Oracle Server.
Se houvesse uma forma agregadora desta função, sua sintaxe seria mais ou menos assim:
21:03:29 SQL> SELECT /* pseudocodigo */
21:03:30 2 ID,
21:03:30 3 MAX(dt) dt_vigente,
21:03:30 4 FIRST_VALUE(vlr) WITHIN GROUP(ORDER BY dt DESC) vlr_vigente
21:03:30 5 FROM TST_VIG t
21:03:30 6 WHERE t.id = 1
21:03:30 7 GROUP BY ID;
FIRST_VALUE(vlr) WITHIN GROUP(ORDER BY dt DESC) vlr_vigente
*
ERROR at line 4:
ORA-30484: missing window specification for this function
Elapsed: 00:00:00.02
21:03:30 SQL>Como podemos ver o Oracle não tem esse suporte, então o que fazer?
Bem, eu sou viciado em documentação da Oracle e se tem uma coisa que aprendi rapidamente trabalhando com essa tecnologia é: não subestime o Oracle Server.
Se você precisa de uma solução para um problema, você certamente não foi o primeiro.
Vasculhando um pouco melhor a documentação da Oracle, mais especificamente o SQL Reference, encontrei a função que precisava.
Segue a implementação que utilizei da função FIRST
21:03:30 SQL> SELECT ID,
21:03:30 2 MAX(dt) dt_vigente,
21:03:30 3 MAX(vlr) KEEP(DENSE_RANK FIRST ORDER BY dt DESC) vlr_vigente
21:03:30 4 FROM TST_VIG t
21:03:30 5 WHERE t.id = 1
21:03:30 6 GROUP BY ID;
ID DT_VIGENTE VLR_VIGENTE
---------- ---------- -----------
1 31/03/1980 797,5
1 row selected.
Elapsed: 00:00:00.02
Execution Plan
----------------------------------------------------------
Plan hash value: 3303131835
--------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 17 | 12 (0)| 00:00:01 |
| 1 | SORT GROUP BY NOSORT| | 1 | 17 | 12 (0)| 00:00:01 |
|* 2 | TABLE ACCESS FULL | TST_VIG | 10 | 170 | 12 (0)| 00:00:01 |
--------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - filter("T"."ID"=1)
Statistics
----------------------------------------------------------
1 recursive calls
0 db block gets
34 consistent gets
0 physical reads
0 redo size
694 bytes sent via SQL*Net to client
544 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed
21:03:30 SQL>Podemos ver que ela atende a necessidade do negócio, e reduz os métodos utilizados no plano de execução, além de tornar o código mais elegante (pelo menos na minha opinião)
Se entendermos um pouco mais a fundo a implementação da função, veremos que a sua função antagônica LAST também poderia ser usada.
Para ilustrar fiz uma implementação errada da função FIRST para esse caso, pois retirei a cláusula DESC do GROUP BY:
21:03:30 SQL> SELECT /* errado */
21:03:30 2 ID,
21:03:30 3 MAX(dt) dt_vigente,
21:03:30 4 MAX(vlr) KEEP(DENSE_RANK FIRST ORDER BY dt) vlr_vigente
21:03:30 5 FROM TST_VIG t
21:03:30 6 WHERE t.id = 1
21:03:30 7 GROUP BY ID;
ID DT_VIGENTE VLR_VIGENTE
---------- ---------- -----------
1 31/03/1980 766,38
1 row selected.
Elapsed: 00:00:00.02
21:03:30 SQL>O valor retornado é justamente o valor mais antigo, pois invertemos a ordenação. Neste caso ao invés de usar o FIRST poderíamos utilizar a função LAST:
21:03:30 SQL> SELECT ID,
21:03:30 2 MAX(dt) dt_vigente,
21:03:30 3 MAX(vlr) KEEP(DENSE_RANK LAST ORDER BY dt) vlr_vigente
21:03:30 4 FROM TST_VIG t
21:03:30 5 WHERE t.id = 1
21:03:30 6 GROUP BY ID;
ID DT_VIGENTE VLR_VIGENTE
---------- ---------- -----------
1 31/03/1980 797,5
1 row selected.
Elapsed: 00:00:00.01
21:03:30 SQL>E chegamos ao mesmo resultado com um pouco menos de instruções (eu não me apego muito a esse tipo de coisa, mas acaba virando uma brincadeira interessante).
Agora recriaremos a tabela com um número maior de registros (1 milhão) e a chave primária que faltou. Coletaremos as estatísticas.
21:03:30 SQL> DROP TABLE tst_vig;
Table dropped.
Elapsed: 00:00:00.03
21:03:30 SQL>
21:03:30 SQL> CREATE TABLE tst_vig AS
21:03:30 2 SELECT MOD(ROWNUM - 1, 1000) + 1 ID,
21:03:30 3 DATE '1980-01-01' + FLOOR((ROWNUM - 1) / 1000) * 10 dt,
21:03:30 4 ROUND(dbms_random.value * 1000, 2) vlr
21:03:30 5 FROM dual
21:03:30 6 CONNECT BY LEVEL <= 1000000;
Table created.
Elapsed: 00:00:18.45
21:03:48 SQL>
21:03:48 SQL> alter table tst_vig add constraint tst_vig_pk primary key(id, dt);
Table altered.
Elapsed: 00:00:04.86
21:03:53 SQL> analyze table tst_vig compute statistics;
Table analyzed.
Elapsed: 00:00:09.55
21:04:03 SQL>Habilitei o trace com o traceonly, pois não quero inflar meu spool e desta vez não selecionei chave nenhuma. Vai ter que ser full mesmo, ok?
21:04:03 SQL> set autot trace;Fiz algumas execuções das três implementações várias vezes seguidas para que todos possam se beneficiar do BUFFER CACHE. Prezamos muito pelo fair play neste blog.
21:04:03 SQL> ...
21:04:06 SQL> SELECT id, dt, vlr
21:04:06 2 FROM TST_VIG t
21:04:06 3 WHERE dt = (SELECT MAX(dt)
21:04:06 4 FROM TST_VIG
21:04:06 5 WHERE t.id = ID);
1000 rows selected.
Elapsed: 00:00:00.35
Execution Plan
----------------------------------------------------------
Plan hash value: 3103771927
--------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1000 | 36000 | 1696 (3)| 00:00:01 |
|* 1 | HASH JOIN | | 1000 | 36000 | 1696 (3)| 00:00:01 |
| 2 | VIEW | VW_SQ_1 | 1000 | 22000 | 855 (4)| 00:00:01 |
| 3 | HASH GROUP BY | | 1000 | 10000 | 855 (4)| 00:00:01 |
| 4 | INDEX FAST FULL SCAN| TST_VIG_PK | 1000K| 9765K| 832 (1)| 00:00:01 |
| 5 | TABLE ACCESS FULL | TST_VIG | 1000K| 13M| 838 (1)| 00:00:01 |
--------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - access("DT"="MAX(DT)" AND "T"."ID"="ITEM_1")
Note
-----
- this is an adaptive plan
Statistics
----------------------------------------------------------
1 recursive calls
0 db block gets
6096 consistent gets
0 physical reads
0 redo size
15262 bytes sent via SQL*Net to client
588 bytes received via SQL*Net from client
6 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1000 rows processed
21:04:06 SQL>
21:04:06 SQL> SELECT ID, MAX(dt) dt_vigente, vlr_vigente
21:04:06 2 FROM (SELECT id,
21:04:06 3 dt,
21:04:06 4 FIRST_VALUE(vlr) OVER(PARTITION BY ID ORDER BY dt DESC) vlr_vigente
21:04:06 5 FROM TST_VIG t)
21:04:06 6 GROUP BY ID, vlr_vigente;
1000 rows selected.
Elapsed: 00:00:01.56
Execution Plan
----------------------------------------------------------
Plan hash value: 4081778672
----------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes |TempSpc| Cost (%CPU)| Time |
----------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1000 | 25000 | | 5797 (1)| 00:00:01 |
| 1 | HASH GROUP BY | | 1000 | 25000 | | 5797 (1)| 00:00:01 |
| 2 | VIEW | | 1000K| 23M| | 5774 (1)| 00:00:01 |
| 3 | WINDOW SORT | | 1000K| 13M| 22M| 5774 (1)| 00:00:01 |
| 4 | TABLE ACCESS FULL| TST_VIG | 1000K| 13M| | 838 (1)| 00:00:01 |
----------------------------------------------------------------------------------------
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
3021 consistent gets
0 physical reads
0 redo size
15278 bytes sent via SQL*Net to client
588 bytes received via SQL*Net from client
6 SQL*Net roundtrips to/from client
1 sorts (memory)
0 sorts (disk)
1000 rows processed
21:04:08 SQL>
21:04:08 SQL> SELECT ID,
21:04:08 2 MAX(dt) dt_vigente,
21:04:08 3 MAX(vlr) KEEP(DENSE_RANK LAST ORDER BY dt) vlr_vigente
21:04:08 4 FROM TST_VIG t
21:04:08 5 GROUP BY ID;
1000 rows selected.
Elapsed: 00:00:00.67
Execution Plan
----------------------------------------------------------
Plan hash value: 1457491003
------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1000 | 14000 | 862 (4)| 00:00:01 |
| 1 | SORT GROUP BY | | 1000 | 14000 | 862 (4)| 00:00:01 |
| 2 | TABLE ACCESS FULL| TST_VIG | 1000K| 13M| 838 (1)| 00:00:01 |
------------------------------------------------------------------------------
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
3021 consistent gets
0 physical reads
0 redo size
15278 bytes sent via SQL*Net to client
588 bytes received via SQL*Net from client
6 SQL*Net roundtrips to/from client
1 sorts (memory)
0 sorts (disk)
1000 rows processed
21:04:09 SQL>Enfim, fica um pouco mais claro a diferença entre as estratégias em termos de diminuição de custo e economia de leituras lógicas.
No caso real, o tempo de processamento caiu de +8h para 70min
Por hoje é só.
Obrigado por ler até o fim.
21:04:11 SQL> DROP TABLE tst_vig;
Table dropped.
Elapsed: 00:00:00.08
21:04:12 SQL> exit
Excelente !!!! Isso sim é demonstrar a teoria posta em prática ! Parabéns pelo post !
Curti demais