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.
É 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;
/
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.
© 2003-2020, 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.