Furos no SQL

Um dos problemas com os quais mais quebrei a cabeça foi o de encontrar números vagos numa chave primária. A tabela usava como chave apenas um número e para que ele não crescesse indefinidamente (e porque era comum entrarem e saírem registros) eu precisava achar posições vagas para os registros novos.

A solução funcionava, mas era um emaranhado de consultas aninhadas e espertezas numéricas.

Pois bem, com as funções analíticas as coisas ficaram muito mais fáceis!

Suponha, para a consulta abaixo, que eu tenha uma tabela de números de produtos e que quero reaproveitar um número quando um produto desaparecer. Usando a função LEAD(), posso saber, para cada número, qual o próximo número na tabela. Fazendo um join com a própria tabela e fazendo um joguinho rápido com rownum, posso enumerar todos os valores livres, mesmo que os números sejam maiores que o número de registros na tabela. É o caso de haver 100 registros com números do tipo 8 posições; a consulta só vai falhar se o maior furo for maior que o número de registros. Nesse caso, basta achar uma tabela maior para alimentar o rownum (e supondo que seja mesmo necessário achar todos os valores vagos de uma só vez).

select numero1+livre
from
 (select numero numero1, 
         lead(numero, 1, null) over (order by numero) numero2 
  from produtos) p1
 inner join (select rownum livre from produtos) p2 
   on livre < numero2 - numero1
order by 1

A coluna numero2 tem o próximo valor ocupado e a diferença entre numero2 e numero1 é o número de lugares vagos mais um. No select externo, numero1+livre produz os valores disponíveis.