How Does ROWNUM Work in Oracle Pagination Queries?

Understanding ROWNUM in Oracle Pagination

Question 1: Why Empty Results with ROWNUM Range?

The query Select * From Person Where rownum > 100 and rownum < 200 returns an empty result set because ROWNUM assigns numbers to rows as they are retrieved. The WHERE clause filters after ROWNUM has assigned numbers. Rows with ROWNUM greater than 100 are only considered after the first 100 rows have already been processed and assigned ROWNUM values.

Question 2: Simplified ROWNUM Queries in Oracle 12c

Oracle 12c's Top-n Row Limiting feature provides a more straightforward approach to pagination. The syntax Select ... FROM ... WHERE rownum BETWEEN lowerBound AND upperBound directly selects rows within the specified range, eliminating the need for subqueries.

Question 3: Omitting the ROWNUM Column

To avoid displaying the ROWNUM column, explicitly list the desired columns in the outer query's SELECT list. Alternatively, create a view to encapsulate the query or use SQL*Plus's NOPRINT command to suppress unwanted output.

Question 4: Achieving Accurate Pagination with ROWNUM

Accurate pagination with ROWNUM hinges on correct query construction. Crucially, order the results using a unique identifier (primary key) to ensure consistent row selection across pages. While ordering by ROWNUM itself doesn't negate FIRST_ROWS(N), using a unique identifier guarantees the retrieval of the intended rows.

The above is the detailed content of How Does ROWNUM Work in Oracle Pagination Queries?. For more information, please follow other related articles on the PHP Chinese website!