Comment limiter le nombre de lignes retournées par une requête Oracle ?

Il existe depuis la version 12c de la base de données une clause permettant de limiter le nombre de lignes retournées par une requête.

Pendant longtemps, le système de gestion de bases de données Oracle n'a pas fourni de clauses permettant de limiter le nombre d'enregistrements sélectionnés dans une requête. D'autres systèmes prennent en charge cette fonctionnalité comme MySQL avec la clause LIMIT. Il existe depuis la version 12c une clause gérant ce cas et pour les versions antérieures une solution pour pallier cette absence.

Depuis la version 12c, une clause permettant de limiter les lignes récupérées a été ajoutée. Il s'agit de la clause FETCH. Par défaut, on peut récupérer les premiers enregistrements d'une requête.

SELECT monChamp FROM maTable ORDER BY monChamp DESC FETCH FIRST 5 ROWS ONLY;

Le mot-clé OFFSET permet d'indiquer une limite inférieure. Ce cas est très répandu avec notamment les systèmes de pagination des résultats de recherche présents sur les sites internet. Pour récupérer les 10 premiers enregistrements à partir du 20e, on utilise la requête suivante :

SELECT monChamp FROM maTable ORDER BY monChamp DESC OFFSET 20 FETCH FIRST 10 ROWS ONLY;

Remplacer le mot-clé ONLY par le mot-clé WITH TIES récupère le nombre de lignes indiquées mais ajoute également les doublons de la dernière ligne de résultat.

SELECT monChamp FROM maTable ORDER BY monChamp DESC FETCH FIRST 5 ROWS WITH TIES;

Cette clause accepte également les pourcentages comme unité. On peut ainsi récupérer les lignes dont la valeur du champ est située dans un pourcentage indiqué.

SELECT monChamp FROM maTable ORDER BY monChamp FETCH FIRST 20 PERCENT ROWS ONLY;

Si vous travaillez sur une base de données exécutant une version antérieure à la version 12c du système Oracle, le champ ROWNUM peut être utilisé pour sélectionner uniquement certaines lignes. Il faut cependant faire attention car ce champ est évalué avant la clause ORDER BY. Il faut donc utiliser une sous-requête pour pouvoir récupérer les premiers résultats d'une requête.

SELECT * FROM 
 (SELECT * FROM maTable ORDER BY monChamp DESC) 
WHERE ROWNUM <= 5;

Si vous souhaitez ajouter une limite inférieure à votre requête, il est nécessaire d'utiliser deux sous-requêtes car le champ ROWNUM est modifié par la première sous-requête.

SELECT * FROM 
 (SELECT a.*, ROWNUM rnum FROM 
 (SELECT * FROM maTable ORDER BY monChamp DESC) a
 WHERE ROWNUM <= :10)
WHERE rnum >= 20;