Oracle Pagination: Analyse de la fonction ROW_NUMBER() VS ROWNUM

Je pensais que cette question est intéressante, alors j'ai essayé un peu les choses.

J'ai table appelée large_t, qui contient environ 1,1 M lignes.

Alors j'ai deux questions:

select * 
from
(
  select rownum rnum, a.*
  from (
         select owner, object_name, object_id
         from large_t
         order by object_id
       ) a
  where rownum   <= 30      
) where rnum > 20;

Et

select *
from
(
select owner, object_name, object_id,
       row_number() over (order by object_id) rnum
from large_t
) where rnum > 20 and rnum <= 30;

Si vous regardez les plans de deux requêtes génèrent, la première a une opération:

SORT ORDER BY STOPKEY

Tandis que l'analyse de la requête contient une opération appelée

WINDOW SORT PUSHED RANK

L'ORDRE de TRI PAR STOPKEY est plus efficace en opération de tri qu'un simple ORDER BY. Je ne suis pas sûr de savoir comment une FENÊTRE de TRI POUSSÉ RANG fonctionne, mais il semble fonctionner dans un mode similaire.

Regarder v$sql_workarea après l'exécution de deux requêtes, les deux ne demandait qu'une sort_area de 4096 octets.

En revanche, si j'ai exécuté la requête sans pagination requête:

select owner, object_name, object_id
from large_t
order by object_id

Alors le tri surface nécessaire est de 37M, prouvant de la sorte dans les deux requêtes est d'environ la même.

Normalement, si vous voulez efficacement retour le TOP N d'une requête triée, vous voulez un index sur la colonne de tri - prévenir Oracle devoir de tri à tous. Donc, j'ai créé un index sur OBJECT_ID, puis explique à la fois les requêtes de nouveau.

Ce temps, le premier de requête utilisé l'index et retourné en 0,2 secondes, tandis que la deuxième requête n'utilisez pas le nouvel indice et a été beaucoup plus lent.

Donc ma conclusion de ce rapide peu d'analyse, c'est que dans le cas général à l'aide de rownum de filtrer ou de l'analytique de la fonction row_number à la fois un rendement à peu près la même. Cependant, la rownum exemple lancé automatiquement à l'aide de l'index j'ai créé sur la table lorsque la fonction row_number n'a pas. Peut-être que je pourrais l'obtenir pour utiliser l'index, avec quelques notes - qui est quelque chose que vous pouvez expérimenter avec.

Pour générer vos propres résultats empiriques:

-- Create test table
CREATE TABLE test_large_tab (
  tlt_id   NUMBER,
  tlt_data VARCHAR2(50)
);

-- Load with data
BEGIN
   FORALL i IN 1 .. 1000000
      INSERT INTO test_large_tab
      (
       tlt_id,
       tlt_data
      )
      VALUES
      (
       i,
       TO_CHAR(sysdate-i, 'FMMon ddth, YYYY')
      );
END;

Bien sûr, vous pouvez augmenter la taille de la table en fonction de vos fins de test!

Définir la durée et à exécuter deux requêtes contre la grande table.

Modifier la structure de la table en fonction de vos test que vous voudrez peut-être une des colonnes indexée en fonction de votre requête trop etc. mais essentiellement c'est un test simple et ne vous prendra pas de temps à s'exécuter.

Si les deux sortent à peu près les mêmes timings puis utilisez le plus lisible (et donc supportable) version.