OFFSET en SQL Oracle : Pagination de Résultats et Exemples Pratiques

La clause OFFSET en SQL Oracle est un outil indispensable pour contrôler la pagination des résultats de vos requêtes. Introduite avec Oracle 12c, elle permet de sauter un nombre défini de lignes avant de retourner les données souhaitées. Maîtriser OFFSET est essentiel pour tout développeur ou administrateur de bases de données qui travaille sur des interfaces utilisateur, des API REST ou des rapports paginés en entreprise.

Définition et utilisation de la clause OFFSET

La clause OFFSET est une instruction SQL qui indique au moteur Oracle de sauter un certain nombre de lignes au début d’un jeu de résultats avant de commencer à retourner les données. Elle est utilisée en combinaison avec la clause FETCH FIRST (ou FETCH NEXT) afin d’implémenter une pagination efficace et lisible.

Avant l’arrivée d’Oracle 12c, les développeurs devaient recourir à des sous-requêtes imbriquées avec ROWNUM ou la fonction analytique ROW_NUMBER() pour obtenir un comportement similaire. Ces approches restaient fonctionnelles mais difficiles à lire et à maintenir. La clause OFFSET standardisée (conforme au standard SQL:2008) simplifie considérablement le code.

Cas d’usage en entreprise

• Interfaces web paginées : afficher les produits d’un catalogue par tranches de 20 éléments par page.
• API REST : retourner des enregistrements par lots avec des paramètres page et limit.
• Rapports métier : exporter des données volumineuses en plusieurs segments pour éviter les surcharges mémoire.
• Tableaux de bord : afficher les 10 meilleures ventes en commençant à partir d’un rang précis.

Syntaxe de OFFSET en SQL Oracle

La syntaxe complète de la clause OFFSET en Oracle est la suivante :

SELECT colonne1, colonne2, ...
FROM nom_table
[WHERE condition]
[ORDER BY colonne ASC|DESC]
OFFSET n ROWS
FETCH FIRST | NEXT m ROWS ONLY | WITH TIES;

Explication des paramètres essentiels

• OFFSET n ROWS : indique le nombre de lignes à ignorer (n doit être un entier positif ou nul). Si n = 0, aucune ligne n’est ignorée.
• FETCH FIRST m ROWS ONLY : limite le nombre de lignes retournées à m après le saut.
• FETCH NEXT m ROWS ONLY : synonyme de FETCH FIRST, souvent utilisé pour une meilleure lisibilité dans un contexte de pagination (page suivante).
• WITH TIES : inclut les lignes supplémentaires qui partagent la même valeur de tri que la dernière ligne retournée. Utile pour éviter les coupures arbitraires.
• ORDER BY : obligatoire en pratique pour garantir un ordre déterministe. Sans lui, les résultats de la pagination sont imprévisibles.

⚠️ Note de version : La clause OFFSET combinée à FETCH est disponible uniquement à partir d’Oracle Database 12c Release 1 (12.1). Sur les versions antérieures, utilisez ROW_NUMBER() ou ROWNUM.

Exemples pratiques de OFFSET en SQL Oracle

Exemple 1 – Pagination d’un catalogue de produits

Contexte métier : vous développez une boutique en ligne. La table PRODUITS contient 500 références. Vous souhaitez afficher la troisième page d’un catalogue paginé à 10 produits par page (soit les lignes 21 à 30).

-- Affichage de la page 3 d'un catalogue produits (10 articles par page)
-- Page 1 : OFFSET 0, Page 2 : OFFSET 10, Page 3 : OFFSET 20
SELECT
    PRODUIT_ID,
    NOM_PRODUIT,
    PRIX_UNITAIRE,
    CATEGORIE
FROM PRODUITS
WHERE STATUT = 'ACTIF'
ORDER BY NOM_PRODUIT ASC
OFFSET 20 ROWS          -- On saute les 20 premières lignes (pages 1 et 2)
FETCH NEXT 10 ROWS ONLY; -- On récupère les 10 lignes suivantes (page 3)

Résultat attendu : Oracle ignore les 20 premiers produits actifs triés par nom, puis retourne exactement les 10 suivants. La formule générale pour calculer l’offset est : OFFSET (numero_page - 1) * taille_page ROWS.

Exemple 2 – Classement des meilleurs vendeurs avec gestion des ex-aequo

Contexte métier : vous gérez un service commercial. Vous souhaitez afficher les vendeurs classés du 6ème au 10ème rang par chiffre d’affaires, tout en incluant les éventuels ex-aequo sur la 10ème position.

-- Classement des vendeurs du 6e au 10e rang par chiffre d'affaires
-- WITH TIES permet d'inclure les ex-aequo sur la dernière position
SELECT
    V.VENDEUR_ID,
    V.NOM        || ' ' || V.PRENOM AS VENDEUR,
    SUM(C.MONTANT_VENTE)            AS CA_TOTAL
FROM VENDEURS V
JOIN COMMANDES C ON V.VENDEUR_ID = C.VENDEUR_ID
WHERE EXTRACT(YEAR FROM C.DATE_COMMANDE) = 2024
GROUP BY V.VENDEUR_ID, V.NOM, V.PRENOM
ORDER BY CA_TOTAL DESC
OFFSET 5 ROWS               -- On ignore les 5 premiers (top 1 à 5)
FETCH NEXT 5 ROWS WITH TIES; -- On prend les rangs 6 à 10, ex-aequo inclus

Résultat attendu : Oracle retourne les vendeurs classés de la 6ème à la 10ème position en termes de chiffre d’affaires 2024. L’option WITH TIES garantit qu’un vendeur ex-aequo avec le 10ème ne sera pas exclu arbitrairement du rapport.

Erreurs courantes avec OFFSET en Oracle

Erreur fréquente : utiliser OFFSET sans ORDER BY

L’une des erreurs les plus répandues consiste à écrire une requête paginée sans clause ORDER BY. Oracle n’interdit pas syntaxiquement cette écriture, mais le comportement devient totalement imprévisible.

-- ❌ MAUVAISE PRATIQUE : OFFSET sans ORDER BY
SELECT PRODUIT_ID, NOM_PRODUIT
FROM PRODUITS
OFFSET 10 ROWS
FETCH NEXT 10 ROWS ONLY;
-- L'ordre des lignes n'est pas garanti : les résultats peuvent changer
-- d'une exécution à l'autre selon le plan d'exécution Oracle.

-- ✅ BONNE PRATIQUE : toujours ajouter ORDER BY
SELECT PRODUIT_ID, NOM_PRODUIT
FROM PRODUITS
ORDER BY PRODUIT_ID ASC  -- Ordre déterministe garanti
OFFSET 10 ROWS
FETCH NEXT 10 ROWS ONLY;

Solution : Ajoutez systématiquement une clause ORDER BY avec une colonne unique (ou une combinaison de colonnes unique) pour garantir un tri stable et des pages cohérentes d’une requête à l’autre. Une clé primaire est souvent le meilleur choix.

Résumé

2 bonnes pratiques Oracle à retenir

1. Indexez la colonne utilisée dans ORDER BY : pour des tables volumineuses, un index sur la colonne de tri évite un tri complet (full table sort) et améliore drastiquement les performances de la pagination.
2. Évitez les offsets très élevés sur de grandes tables : un OFFSET 100000 ROWS force Oracle à lire et ignorer 100 000 lignes avant de retourner les résultats. Préférez une pagination par clé (keyset pagination) avec une clause WHERE colonne > derniere_valeur pour les très grands volumes.

Aller plus loin

Pour approfondir vos compétences en SQL Oracle et compléter votre maîtrise de la pagination et du filtrage des résultats, nous vous recommandons ces sujets connexes :

• La clause FETCH FIRST en Oracle : le complément naturel d’OFFSET pour limiter le nombre de lignes retournées, avec des exemples détaillés sur l’option WITH TIES.
• La fonction analytique ROW_NUMBER() en Oracle : l’alternative classique pour numéroter les lignes et implémenter la pagination sur Oracle 11g et versions antérieures.
• La pseudo-colonne ROWNUM en Oracle : comprenez ses particularités et ses pièges pour migrer vos anciennes requêtes vers la syntaxe moderne OFFSET / FETCH.

Sur le même thème

• Clause WITH Oracle SQL : CTE et sous-requêtes nommées
• Les sous-requêtes SQL Oracle : Guide complet et exemples
• Gestion des transactions SQL Oracle – Cours complet
• Clause WHERE en SQL Oracle – Filtrer vos données
• HAVING en SQL Oracle : filtrer les groupes efficacement
• GROUP BY en SQL Oracle : Guide Complet et Exemples

Veuillez laisser ce champ vide

Un nouveau tutoriel SQL par semaine

Nous ne spammons pas ! Consultez notre politique de confidentialité pour plus d’informations.

Vérifiez votre boite de réception ou votre répertoire d’indésirables pour confirmer votre abonnement.