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

• ← Back: NULL Handling
• Next: INNER JOIN →