Oracle SQL Paging

When Oracle implements paging, a rownum function needs to be introduced, and rownum can record an ID-like field.

The following is a collection of several commonly used SQL paging algorithm, the database with the EMP in Oracle as an example. The query results are as follows:

Sql> select * from EMP;

EMPNO ename JOB MGR hiredate SAL COMM DEPTNO

----- ---------- --------- ----- ----------- --------- --------- ------

7369 SMITH Clerk 7902 1980/12/17 800.00 20

7499 ALLEN salesman 7698 1981/2/20 1600.00 300.00 30

7521 WARD salesman 7698 1981/2/22 1250.00 500.00 30

7566 JONES MANAGER 7839 1981/4/2 2975.00 20

7654 MARTIN salesman 7698 1981/9/28 1250.00 1400.00 30

7698 BLAKE MANAGER 7839 1981/5/1 2850.00 30

7782 CLARK MANAGER 7839 1981/6/9 2450.00 10

7788 SCOTT ANALYST 7566 1987/4/19 3000.00 20

7839 KING President 1981/11/17 5000.00 10

7844 TURNER salesman 7698 1981/9/8 1500.00 0.00 30

7876 ADAMS Clerk 7788 1987/5/23 1100.00 20

7900 JAMES Clerk 7698 1981/12/3 950.00 30

7902 FORD ANALYST 7566 1981/12/3 3000.00 20

7934 MILLER Clerk 7782 1982/1/23 1300.00 10

Rows selected
Paging Method 1: Using nested queries

Sql> SELECT * FROM (select t1.*, rownum as n from emp t1 where rownum <=) where n >= 10;

EMPNO ename JOB MGR hiredate SAL COMM DEPTNO N

----- ---------- --------- ----- ----------- --------- --------- ------ ----------

7844 TURNER salesman 7698 1981/9/8 1500.00 0.00 30 10

7876 ADAMS Clerk 7788 1987/5/23 1100.00 20 11

7900 JAMES Clerk 7698 1981/12/3 950.00 30 12

7902 FORD ANALYST 7566 1981/12/3 3000.00 20 13

7934 MILLER Clerk 7782 1982/1/23 1300.00 10 14

Paging Method 2: Using minus

Sql> SELECT * from emp where rownum <= minus select * from emp where rownum < 10;

EMPNO ename JOB MGR hiredate SAL COMM DEPTNO

----- ---------- --------- ----- ----------- --------- --------- ------

7844 TURNER salesman 7698 1981/9/8 1500.00 0.00 30

7876 ADAMS Clerk 7788 1987/5/23 1100.00 20

7900 JAMES Clerk 7698 1981/12/3 950.00 30

7902 FORD ANALYST 7566 1981/12/3 3000.00 20

7934 MILLER Clerk 7782 1982/1/23 1300.00 10

In method Two, if the query statement after minus executes where rownum > 10 o'clock, why can't I query any records?

RowNum is a pseudo-column that adds to the result set, which is the result set followed by a column (emphasis: a result set before adding a pseudo-column).

Simply put, the rownum is the serial number that matches the conditional result. It always starts at 1. Cannot use ">" for RowNum. It is related to the mechanism of rownum implementation. Also, you cannot use rownum = N (the natural number of n>1). The rownum are all starting from 1.

Paging Method 3: Using rowID

Sql> SELECT * from emp where rowid in (select Rid from (select rowID as RIDs, rownum as ID from emp t where RowNum < () where ID >= 10);

EMPNO ename JOB MGR hiredate SAL COMM DEPTNO

----- ---------- --------- ----- ----------- --------- --------- ------

7876 ADAMS Clerk 7788 1987/5/23 1100.00 20

7844 TURNER salesman 7698 1981/9/8 1500.00 0.00 30

7900 JAMES Clerk 7698 1981/12/3 950.00 30

7902 FORD ANALYST 7566 1981/12/3 3000.00 20

7934 MILLER Clerk 7782 1982/1/23 1300.00 10

Method four: Using Analytic functions

Sql> SELECT * FROM (select t.*, Row_number () over (order by ename) as num from emp t) where num between and 15;

EMPNO ename JOB MGR hiredate SAL COMM DEPTNO NUM

----- ---------- --------- ----- ----------- --------- --------- ------ ----------

7934 MILLER Clerk 7782 1982/1/23 1300.00 10 10

7788 SCOTT ANALYST 7566 1987/4/19 3000.00 20 11

7369 SMITH Clerk 7902 1980/12/17 800.00 20 12

7844 TURNER salesman 7698 1981/9/8 1500.00 0.00 30 13

7521 WARD salesman 7698 1981/2/22 1250.00 500.00 30 14

Method Five: After all records are queried using the rownum pseudo-column, the outer layer uses between and or greater than

Sql> SELECT * FROM (select t.*, rownum as num from emp t) where num between and 15;

EMPNO ename JOB MGR hiredate SAL COMM DEPTNO NUM

----- ---------- --------- ----- ----------- --------- --------- ------ ----------

7844 TURNER salesman 7698 1981/9/8 1500.00 0.00 30 10

7876 ADAMS Clerk 7788 1987/5/23 1100.00 20 11

7900 JAMES Clerk 7698 1981/12/3 950.00 30 12

7902 FORD ANALYST 7566 1981/12/3 3000.00 20 13

7934 MILLER Clerk 7782 1982/1/23 1300.00 10 14

Oracle SQL Paging