Tuesday, January 17, 2006
Como Encontrar Gaps em uma Sequência?
Os desenvolvedores usam uma sequence para preencher a pk de uma tabela, só que alguém andou com a sequence em alguns momentos e a pk da tabela, que tem que ser sequencial, ficou com furos, exemplo :
protocolo_inscricao
1
3
4
5
6
10
13
Há alguma função que me diga quais os números estão faltando porque foram pulados pelo avanço indevido da sequence ?
Exitem outros métodos, mas o que eu escolhi para apresentar foi o seguinte: gerar uma "baseline" com todos os números em sequência. Procurei gerar essa tabela de forma mais leve possível - através da tabela dual. Podemos gerar milhões (literalmente) com pouco esforço.
A partir desta tabela completa, basta entrelaçar com a sequência quebrada ou seja a diferença do conjunto completo pelo quebrado.
Veja na figura como identificar os gaps. Na primeira coluna teremos o conjunto sequencial completo, na segunda o quebrado e a zona cinza os gaps.
Como se pode notar, o conjunto dos gaps é formado pelos números { 2, 7, 8, 9, 11, 12 }
Prova!
Como chegar a esse resultado? Com a figura e as afirmações:
a) entrelaçar a tabela completa com a sequência quebrada ( OUTER JOIN )
b) diferença do conjunto completo pelo quebrado ( MINUS )
Basta:
a)
SQL> with seq as (
2 select level l
3 from dual connect by level <= ( select max(x) from t )
4 )
5 select l, x, decode(x, null, l ) falta
6 from seq left outer join t on ( l = x )
7 order by 1
8 /
L X FALTA
------------- ------------- -------------
1 1
2 2
3 3
4 4
5 5
6 6
7 7
8 8
9 9
10 10
11 11
12 12
13 13
13 rows selected.
b)
SQL> with seq as (
2 select level l
3 from dual connect by level <= ( select max(x) from t )
4 )
5 select * from seq
6 minus
7 select * from t
8 /
L
-------------
2
7
8
9
11
12
6 rows selected.
Labels: how to
Comments:
<< Home
Excelente método para identificação de gaps. Só tenho uma pergunta, porque a Oracle (e todos as autoridades no assunto) defende/aceita que a sequência pule números ou que números não seja reaproveitados?
Eu já procurei o motivo por muito tempo mas desisti. Eu sempre achava a desculpa que as pessoas interpretavam sequência de maneira errada, sequência é um gerador de número único que não é necessariamente número sequencial.
O mesmo vigor que é aplicado para defender o gap na sequência é aplicado para que não seja usado alguma função/procedure para buscar o último número + 1 para que esse então seja usado como número sequencial. Porque disto ? Eu acredito que é mais fácil um raio lhe acertar do que dois usuários ao mesmo tempo enviar commit para banco e o banco processar os dois ao mesmo tempo para que a primary key seja violada.
Essa é só a minha opnião de novato no assunto. Deve ter algum motivo lógico por trás do código que impossibilite essa sequência.
Será que você pode recomendar como sequêcias devem ser criadas ? Por exemplo, quais clausulas devem sempre constar. CACHE, NOCHACE e etc
Obrigado !
Eu já procurei o motivo por muito tempo mas desisti. Eu sempre achava a desculpa que as pessoas interpretavam sequência de maneira errada, sequência é um gerador de número único que não é necessariamente número sequencial.
O mesmo vigor que é aplicado para defender o gap na sequência é aplicado para que não seja usado alguma função/procedure para buscar o último número + 1 para que esse então seja usado como número sequencial. Porque disto ? Eu acredito que é mais fácil um raio lhe acertar do que dois usuários ao mesmo tempo enviar commit para banco e o banco processar os dois ao mesmo tempo para que a primary key seja violada.
Essa é só a minha opnião de novato no assunto. Deve ter algum motivo lógico por trás do código que impossibilite essa sequência.
Será que você pode recomendar como sequêcias devem ser criadas ? Por exemplo, quais clausulas devem sempre constar. CACHE, NOCHACE e etc
Obrigado !
porque a Oracle (e todos as autoridades no assunto) defende/aceita que a sequência pule números ou que números não seja reaproveitados?
Porque o desenho da sequence é esse. O objeto sequence não é um gerador sequencial numérico, ele é um gerador sequencial de números únicos.
No Concept traz até uma precaução sobre números sequenciais:
Caution: If your application can never lose sequence numbers,
then you cannot use Oracle sequences, and you may choose to
store sequence numbers in database tables. Be careful when
implementing sequence generators using database tables. Even in
a single instance configuration, for a high rate of sequence values
generation, a performance overhead is associated with the cost of
locking the row that stores the sequence value.
Eu já procurei o motivo por muito tempo mas desisti. Eu sempre achava a desculpa que as pessoas interpretavam sequência de maneira errada, sequência é um gerador de número único que não é necessariamente número sequencial.
Falou tudo! Interpretou corretamente. "sequência é um gerador de número único que não é necessariamente número sequencial." perfeito!
O mesmo vigor que é aplicado para defender o gap na sequência é aplicado para que não seja usado alguma função/procedure para buscar o último número + 1 para que esse então seja usado como número sequencial. Porque disto ? Eu acredito que é mais fácil um raio lhe acertar do que dois usuários ao mesmo tempo enviar commit para banco e o banco processar os dois ao mesmo tempo para que a primary key seja violada.
Se sua aplicação depende disso. Go for it! Não dá para evitar. Faça a serialização na ponta do insert. Por que é exatamente isso que vai acontecer. Serialização! Cuidado com os trovões ;) Se a aplicação é altamente transacional, por exemplo, sistema de controle de pedágio - imagina quando é férias e todas as catracas eletrônicas estão recebendo carros ao mesmo tempo, não será difícil a contenção em sua freelist (se voce tiver uma só) - agora imagine com uma função buscando número sequencial "garantido" n+1. Ok, tudo é desenho de sistema voce pode contornar essa situação de muitas maneiras, basta entender o que cada ferramente faz. Não vamos usar uma furadeira para martelar um prego.
Será que você pode recomendar como sequêcias devem ser criadas ? Por exemplo, quais clausulas devem sempre constar. CACHE, NOCHACE e etc
Eu sempre crio com CACHE e um número bem generoso para evitar parse na sequence - 20 definitivamente é um valor pequeno.
Sugiro leitura do concept.
Post a Comment
Porque o desenho da sequence é esse. O objeto sequence não é um gerador sequencial numérico, ele é um gerador sequencial de números únicos.
No Concept traz até uma precaução sobre números sequenciais:
Caution: If your application can never lose sequence numbers,
then you cannot use Oracle sequences, and you may choose to
store sequence numbers in database tables. Be careful when
implementing sequence generators using database tables. Even in
a single instance configuration, for a high rate of sequence values
generation, a performance overhead is associated with the cost of
locking the row that stores the sequence value.
Eu já procurei o motivo por muito tempo mas desisti. Eu sempre achava a desculpa que as pessoas interpretavam sequência de maneira errada, sequência é um gerador de número único que não é necessariamente número sequencial.
Falou tudo! Interpretou corretamente. "sequência é um gerador de número único que não é necessariamente número sequencial." perfeito!
O mesmo vigor que é aplicado para defender o gap na sequência é aplicado para que não seja usado alguma função/procedure para buscar o último número + 1 para que esse então seja usado como número sequencial. Porque disto ? Eu acredito que é mais fácil um raio lhe acertar do que dois usuários ao mesmo tempo enviar commit para banco e o banco processar os dois ao mesmo tempo para que a primary key seja violada.
Se sua aplicação depende disso. Go for it! Não dá para evitar. Faça a serialização na ponta do insert. Por que é exatamente isso que vai acontecer. Serialização! Cuidado com os trovões ;) Se a aplicação é altamente transacional, por exemplo, sistema de controle de pedágio - imagina quando é férias e todas as catracas eletrônicas estão recebendo carros ao mesmo tempo, não será difícil a contenção em sua freelist (se voce tiver uma só) - agora imagine com uma função buscando número sequencial "garantido" n+1. Ok, tudo é desenho de sistema voce pode contornar essa situação de muitas maneiras, basta entender o que cada ferramente faz. Não vamos usar uma furadeira para martelar um prego.
Será que você pode recomendar como sequêcias devem ser criadas ? Por exemplo, quais clausulas devem sempre constar. CACHE, NOCHACE e etc
Eu sempre crio com CACHE e um número bem generoso para evitar parse na sequence - 20 definitivamente é um valor pequeno.
Sugiro leitura do concept.
<< Home
