Paginação de resultados no Oracle

Certa vez deparei-me com um problema aparentemente simples: era preciso gerar um relatório paginado que, ademais, exibisse a cada página o primeiro e o último registros. O sistema era escrito em Java e a interface em JSP. Eu gosto de separar bem as atribuições de cada camada, então não estava disposto a fazer no Java nada além de simplesmente percorrer o ResultSet e exibir os dados.

Após algumas tentativas criei uma consulta com a estrutura da seguinte:

select ub.*, rownum rb from (
 select ua.*, rownum ra from (
  select nome, cpf from usuario
  where nome like 'B%' 
  order by nome asc
 ) ua order by nome desc
) ub
where ra=1 or rownum=1 or ra between 10 and 20
order by nome asc

Nesse caso, a consulta retorna uma lista de usuários (cujos nomes iniciem com B) com seus respectivos CPFs e ordenados por nome. A cláusula where indica que são pesquisados todos os registros entre o décimo e o vigésimo, além do primeiro e o último. O select da consulta é o mais de dentro, os outros apenas fazem uma pequena ginástica para paginar o resultado.

Como a consulta retorna o primeiro e o último registros, é possível colocar no cabeçalho uma indicação como "Registros 10 a 20 (Beatriz a Bianca) de 81 (Bárbara a Bruna)", sem que seja preciso executar nenhuma consulta adicional.

Não é difícil criar uma classe para automaticamente envolver qualquer consulta com os selects externos, mas o melhor mesmo é colocar as consultas dentro de uma procedure. Esse tipo de select, eu descobri, é igual ou melhor a simplesmente contar os registros e depois selecionar os interessantes. Se não está claro o que ele faz, eis um passo-a-passo:

1. O select interno realiza a consulta;
2. O select do meio inverte a seleção e agrega o rownum (ra tem a numeração na ordem correta, embora os registros fiquem na ordem invertida);
3. O select externo inverte novamente a seleção e filtra os registros interessantes.

Essa ginástica toda acontece porque o Oracle atribui o rownum aos registros e depois os ordena. Se não fosse por isso, bastariam dois selects. Apesar de contente com a solução, eu achava que ainda era possível simplificar. Descobri então, as funções analíticas. Não vou explicar o que fazem; vou pular direto para a solução.

select u.*, rownum rb from (
 select nome, cpf,
        row_number() over (order by nome asc) ra
 from usuario 
 where nome like 'B%'
 order by nome desc
) u
where ra=1 or rownum=1 or ra between 10 and 20
order by nome asc

A função row_number() produz um número para cada registro, conforme a ordenação indicada pela cláusula over. O select interno ordena os registros ao contrário, mas o row_number() over (order by nome asc) os conta na ordem direta. O select externo inverte a seleção e agrega um rownum. Como o rownum é atribuido antes da ordenação, o registro com rb=1 é o último (porque é o primeiro registro do select interno). A coluna ra já está na ordem direta, então podemos usá-la para selecionar o primeiro registro e os da página atual (entre 10 e 20).