Paging with Oracle

The Old Guard: ROWNUM

Here, rownum takes the center stage, assigning a unique number each time Oracle retrieves a row. However, like grandma's flip phone, it's limited:

• No built-in ordering: rows get their rownum in the order Oracle retrieves them, not necessarily the way you intend!
• Iffy performance: Extensive offsets start dragging against performance. Oracle has to fetch all rows up to the last one you need.

The New Hotness - OFFSET...FETCH (Oracle 12c and onwards)

Oracle 12c introduced SQL savvies to FETCH and OFFSET clauses. They make paginating a breeze:

-- Paging the new way, but let's play a game. You fetch, I offset or vice versa?
SELECT * FROM your_table 
ORDER BY your_column
OFFSET :offset ROWS FETCH NEXT :size ROWS ONLY;


It's both charming and readable; just like you'd find in SQL Server and PostgreSQL.

Applying a Fine Comb: ROW_NUMBER()

For more complex ordering needs, try using ROW_NUMBER:

-- Yes, SQL knows numbers. It can count rows, can you count sheep?
SELECT your_data.*
FROM (
     SELECT your_table.*, ROW_NUMBER() OVER (ORDER BY your_column) AS rn
     FROM your_table
) your_data
WHERE rn BETWEEN :start_row AND :end_row;


It proves a godsend with partitioning and assures consistent sorting for reliable pagination.

Performance Pattern: Use hints and indexing

Vast records? Meet the Oracle hint "first_rows"! It prioritizes your desire for the first set of results.

But when you use Oracle 12c syntax, indexing your ordered columns can save you from an unintended full index scan:

-- Full is never fun, without an index of course 😉 
SELECT * FROM your_table 
ORDER BY your_column
OFFSET :offset ROWS FETCH NEXT :size ROWS ONLY;


The importance of consistency

Always ensure that your users receive the same record sequence with consecutive page accesses. ORDER BY is your guard against "phantom reads" where records elude pages.

Navigating through potential pitfalls

In a dynamic data environment, remember fresh data might insert anywhere in the record sequence between paginated fetches. You wouldn't want records to ghost or double on your pages, would you?

Also, bear in mind, concurrent users can cause similar inconsisitences.