Oracle ROWNUM para limitar consultas

Márcio d'Ávila, 16 de setembro de 2006.
Categoria: Banco de Dados: Oracle

Tom Kyte é provavelmente o mais didático autor de livros e artigos sobre bancos de dados Oracle, com enfoque para o desenvolvedor. Trabalhando para a Oracle desde 1993, ele é hoje um vice-presidente no grupo de Setor Público da empresa. Tom é autor de livros consagrados como “Expert Oracle Database Architecture: 9i and 10g Programming Techniques and Solutions” (Apress, 2005) e “Effective Oracle by Design” (Oracle Press, 2003); mantenedor da famosa seção de perguntas-e-respostas Ask Tom no portal da Oracle (desde 2000) e da coluna de mesmo nome na revista Oracle Magazine.

Creio que a principal característica de Tom Kyte é apresentar soluções práticas eficazes, explicando de forma clara e objetiva os conceitos, mecanismos e recursos do banco de dados Oracle — mesmo os mais obscuros e complexos — envolvidos.

Na edição de Setembro de 2006 em que a revista OraMag comemora 20 anos, Tom nos presenteia com um extrato de seu livro “Effective Oracle by Design” esclarecendo um tema freqüente de perguntas: como realizar consultas top-N e paginação no banco de dados Oracle. Como sempre, claro e preciso.

Vou resumir aqui os principais pontos.

Análise Top-N

É vantajoso e eficiente usar o recurso surgido no Oracle9i de consultas Top-N, onde se faz uma sub-consulta (view em-linha) ordenada pelo critério desejado, como fonte para uma consulta externa onde se usa a pseudo-coluna ROWNUM para limitar a quantidade de linhas no resultado.

A regra geral fica assim:

select *
  from
( select *
    from tabela
   order by critério )
 where ROWNUM <= :N;

O otimizador do Oracle tira proveito da informação do número de linhas (N) desejado e utiliza um método de clasificação especializado (SORT ORDER BY STOPKEY) que mantém na memória apenas N linhas, com grande economia de recursos de memória, espaço temporário em disco e CPU.

Mesmo se o critério for sobre uma coluna não indexada, a consulta Top-N lê a tabela inteira (TABLE ACCESS FULL) — porque é necessário — mas não precisa manter tudo em espaço temporário para classificar a tabela inteira, como aconteceria se você fizesse a tarefa de selecionar apensa N linhas programaticamente utilizando um cursor, como no contra-exemplo a seguir (PL/SQL):

declare
    cursor c_tab is -- sem top-N
        select *
        from tabela
        order by critério;
    l_rec c_tab%rowtype;
    N number := 10;
begin
    open c_tab;
    for i in 1 .. N
    loop
        fetch c_tag into l_rec;
        exit when c_tab%notfound;
    end loop;
    close c_tab;
end;
/

Paginação

Consultas paginadas, muito comuns no ambiente web, são caracterizadas pela exibição gradual de resultados, uma certa quantidade por vez (na web, por página). Se a cada página são exibidas M linhas resultantes, na primeira página exibem-se as linhas 1 a M, na página 2 as linhas M+1 a 2*M, na página P as linhas (P-1)*M+1 a P*M. Para isso, basta fazer uma consulta top-N para selecionar as primeiras P*M linhas (LINHA_FINAL), aninhada em uma consulta mais externa que descarte as (P-1)*M primeiras linhas (LINHA INICIAL).

A forma geral é assim:

select *
  from ( select /*+ FIRST_ROWS(n) */
  topn.*, ROWNUM rnum
      from ( ...sua_consulta_entra_aqui...,
             order by critério, ROWID ) topn
      where ROWNUM <= :LINHA_FINAL )
where rnum  > :LINHA_INICIAL;

A pseudo-coluna ROWID ao final dos critérios de ordenação é necessária para garantir o caráter determinístico dos resultados a cada paginação, caso os valores existentes na consulta para o critério de ordenação desejada não sejam únicos. Exemplo: uma consulta de funcionários ordenada por salário, quando existem pessoas com salários iguais.

Leia a íntegra da coluna em Ask Tom - On ROWNUM and Limiting Results (em inglês), por Tom Kyte, Oracle Magazine Setembro-Outubro 2006.


Firefox - A web de volta
Creative Commons License

© 2003-2011, Márcio d'Ávila, mhavila.com.br, direitos reservados. O texto e código-fonte apresentados podem ser referenciados, distribuídos e utilizados, desde que expressamente citada esta fonte e o crédito do(s) autor(es). A informação aqui apresentada, apesar de todo o esforço para garantir sua precisão e correção, é oferecida "como está", sem quaisquer garantias explícitas ou implícitas decorrentes de sua utilização ou suas conseqüências diretas e indiretas.