Pagination Under Ties and Null Ship Dates
A logistics company runs its customer-facing order tracker on Oracle Database 19c. The ORDERS table holds 486,000 rows and has columns ORDER_ID (NUMBER, primary key), CUSTOMER_ID, STATUS, SHIP_DATE (DATE, nullable), and PRIORITY (VARCHAR2(10)). About 3,100 orders have not shipped, so SHIP_DATE is NULL. The tracker shows 25 orders per page, newest shipment first. Users complain that page 1 is dominated by unshipped orders even though those have no ship date. A junior developer wrote SELECT ORDER_ID, SHIP_DATE FROM orders ORDER BY ship_date DESC and wrapped it with WHERE ROWNUM <= 25 in the same query block. Page 2 sometimes repeats rows that appeared on page 1. The product owner wants pagination that stays stable when many orders share the same SHIP_DATE, since overnight batch feeds create 40 to 60 ties per day. The DBA will not allow a materialized view or a new index for this release, so the team must fix the query, not the data.
1. Explain why unshipped orders dominate page 1, and rewrite the query so page 1 shows 25 shipped-or-not rows with unshipped orders last.
Read the worked answer
Oracle's default null placement depends on sort direction. For ORDER BY ship_date DESC, nulls sort first (NULLS FIRST is the default for descending sorts), so the 3,100 unshipped rows occupy the top of the result. The fix is explicit null placement plus a deterministic tie-breaker, because 40 to 60 orders can share a ship date:
SELECT order_id, customer_id, ship_date, priority
FROM orders
ORDER BY ship_date DESC NULLS LAST, order_id DESC
OFFSET 0 ROWS FETCH NEXT 25 ROWS ONLY;
ORDER_ID is unique, so the sort key is total and page boundaries cannot drift. Without it, two rows with the same ship date can swap between executions and appear on both pages. The explicit DESC on order_id also documents the tie rule. Do not rely on NVL(ship_date, DATE '9999-12-31'): it works but hides the column from any index on ship_date and obscures intent. Since no new index is allowed, accept the sort cost; the row-limiting clause stops the sort from materializing the entire 486,000-row result where the optimizer can use a stopkey plan.
2. The developer proposes SELECT ORDER_ID, SHIP_DATE FROM orders WHERE ROWNUM <= 25 ORDER BY ship_date DESC. Explain what the query actually returns and why, then give the correct pattern.
Read the worked answer
ROWNUM is assigned as rows are produced by the query block, before ORDER BY is evaluated. The predicate WHERE ROWNUM <= 25 therefore keeps an arbitrary 25 rows from the table, usually the first blocks read, and only then sorts those 25 rows. The output is 25 rows that are not the newest shipments, and the set can change between runs as the storage layout or plan changes. In older Oracle versions this pattern is a common pagination bug; in 12c and later the row-limiting clause is clearer and safer.
Correct pattern 1:
SELECT order_id, ship_date
FROM orders
ORDER BY ship_date DESC NULLS LAST, order_id DESC
OFFSET 0 ROWS FETCH NEXT 25 ROWS ONLY;
Correct pattern 2, for code that must also run on older releases:
SELECT *
FROM (
SELECT order_id, ship_date
FROM orders
ORDER BY ship_date DESC NULLS LAST, order_id DESC
)
WHERE ROWNUM <= 25;
The ORDER BY must be inside the inline view so the outer ROWNUM numbers already-sorted rows. Moving the ORDER BY to the outer query does not help; the predicate has already filtered.
3. The product owner now asks for the top 25 including every order tied with the 25th. Which clause applies, what must be true, and what operational consequence should the team accept?
Read the worked answer
Use FETCH FIRST 25 ROWS WITH TIES. It returns all rows whose ORDER BY values equal those of the 25th row, so a tie at position 25 can return 26 to 84 rows or more. Oracle rejects the clause without an ORDER BY because the tie set would be undefined. Example:
SELECT order_id, ship_date
FROM orders
ORDER BY ship_date DESC NULLS LAST
FETCH FIRST 25 ROWS WITH TIES;
The consequence is a variable page size. The application must not assume exactly 25 rows: it should compute the next offset from the number of rows actually returned, or the next page will skip or duplicate rows. There is also a design tension: adding order_id to ORDER BY makes every row unique and defeats WITH TIES, while leaving it out means the order within the tie set is not guaranteed. Choose deliberately. For an interactive tracker that must show exactly 25 rows, keep ORDER BY ship_date DESC NULLS LAST, order_id DESC with FETCH NEXT 25 ROWS ONLY. For a completeness-sensitive batch export where every order shipped on the cutoff date must be included, use WITH TIES and let the row count vary. Do not mix the two requirements in one query.