Skip to content

Oracle FETCH & OFFSET (Pagination) 📑

Mentor's Note: If you search for "Java" on Google, you don't see 10 million results on one page. You see the Top 10. FETCH is how we tell Oracle: "Only give me the first few rows" or "Skip the first 10 and give me the next 10." 💡

🌟 The Scenario: The Leaderboard 🏆

  • The Data: 1,000 players.
  • The Need: You only want to announce the Top 3 winners.
  • The Process: Sort them by score, and then "Fetch" only 3 rows.

💻 1. FETCH FIRST (The Top-N Query)

This is the modern way (Oracle 12c and higher).

SELECT product_name, price 
FROM products 
ORDER BY price DESC 
FETCH FIRST 5 ROWS ONLY;

💻 2. OFFSET (Skipping Rows)

Used for "Page 2" or "Page 3" of an application.

-- Skip the top 10 products and show the next 5
SELECT product_name, price 
FROM products 
ORDER BY price DESC 
OFFSET 10 ROWS 
FETCH NEXT 5 ROWS ONLY;

🏗️ Architect's Note: ROWNUM vs FETCH 🛡️

Old Oracle code uses WHERE ROWNUM <= 5. - The Problem: ROWNUM happens before sorting. If you use it, you might get a random 5 rows that aren't the top 5! - The Solution: Modern FETCH handles the sorting logic internally and is much safer and easier to read. Always prefer FETCH in new Oracle applications.

📈 Learning Path