在写这篇文章之前，对分页查询的认识就是经典的三层嵌套：第①层：获得需要的数据，第②层：用rownum限制展示数据的上限，第③层：用rownum的别名rn限制展示数据的下限。

在生产中遇见一个两层嵌套满足分页查询的SQL，兴奋之余拿出来与大家分享。分三步走1：创建测试表；2：经典分页查询展示SQL及执行计划；3：两层嵌套分页查询展示SQL及执行计划

1、创建测试表

SQL>CREATE TABLE TEST_A(ID NUMBER NOT NULL,SYSID NUMBER,STATUS NUMBER,INFO VARCHAR2(2000));
SQL>INSERT INTO TEST_A
SELECT ROWNUM,ROWNUM+1,TRUNC(DBMS_RANDOM.VALUE(1,9)) ,RPAD('*',2000,'*') FROM DUAL CONNECT BY ROWNUM<=100000; SQL> ALTER table test_a add primary key(id);
SQL>create index ind_status_sysid on test_a(status,sysid);

说明：TEST_A中ID是主键，status字段取值[1,8]且均匀分布。
要求：取TEST_A中status=5的记录，按sysid排序，展示1-1000行。

2、经典分页查询展示SQL及执行计划

SQL> select /*+gather_plan_statistics*/* from
(select rownum rn ,t.* from
(select * from test_a where status=5 order by sysid )t
where rownum<=1000) where rn >=1;

分析：
第①层获得status=5且按sysid排序的数据
第②层取返回数据的上限：rownum<=1000 第③层取返回数据的下限：rn>=1

查看SQL的执行计划

SQL> select * from table(dbms_xplan.display_cursor('','','allstats last'));

PLAN_TABLE_OUTPUT

SQL_ID 2dtgu07a19fhk, child number 0

select /*+gather_plan_statistics*/* from (select rownum rn ,t.* from
(select t.id,t.sysid,t.status from test_a t where status=5 order by
sysid )t where rownum<=1000) where rn >=1

Plan hash value: 503264694

---

| Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers |

| 0 | SELECT STATEMENT | | 1 | | 1000 |00:00:00.01 | 957 |
|* 1 | VIEW | | 1 | 1000 | 1000 |00:00:00.01 | 957 |
|* 2 | COUNT STOPKEY | | 1 | | 1000 |00:00:00.01 | 957 |
| 3 | VIEW | | 1 | 11186 | 1000 |00:00:00.01 | 957 |
| 4 | TABLE ACCESS BY INDEX ROWID | TEST_A | 1 | 11186 | 1000 |00:00:00.01 | 957 |

|* 5 | INDEX RANGE SCAN | IND_STATUS_SYSID | 1 | | 1000 |00:00:00.01 | 71 |

Predicate Information (identified by operation id):

1 - filter("RN">=1)
2 - filter(ROWNUM<=1000)
5 - access("STATUS"=5)

Note

• dynamic statistics used: dynamic sampling (level=2)

30 rows selected.

Elapsed: 00:00:00.04

分析：
Oracle满足条件的数据有1W多条，但是Oracle只取了1000条数据，从A-Rows中可以看出，大大提高了SQL的性能。
因为Oracle在执行SQL的时候会将查询条件推入到内层查询中，查到满足条件的1000条记录，就会返回，在执行计划COUNT STOPKEY上展示出来。

3：两层嵌套分页查询展示SQL及执行计划

SQL> select /*+gather_plan_statistics*/* from (select row_number()over(order by sysid) rn,t.* from test_a t where status=5) where rn between 1 and 1000;

SQL> select * from table(dbms_xplan.display_cursor('','','allstats last'));

PLAN_TABLE_OUTPUT

SQL_ID cj0ds74naryn3, child number 0

select /*+gather_plan_statistics*/* from (select row_number()over(order
by sysid) rn,t.* from test_a t where status=5) where rn between 1 and
1000

Plan hash value: 1970407823

---

| Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers |

| 0 | SELECT STATEMENT | | 1 | | 1000 |00:00:00.01 | 957 |
|* 1 | VIEW | | 1 | 1000 | 1000 |00:00:00.01 | 957 |
|* 2 | WINDOW NOSORT STOPKEY | | 1 | 11186 | 1000 |00:00:00.01 | 957 |
| 3 | TABLE ACCESS BY INDEX ROWID | TEST_A | 1 | 11186 | 1000 |00:00:00.01 | 957 |

|* 4 | INDEX RANGE SCAN | IND_STATUS_SYSID | 1 | | 1000 |00:00:00.01 | 71 |

Predicate Information (identified by operation id):

1 - filter(("RN">=1 AND "RN"<=1000))  
2 - filter(ROW\_NUMBER() OVER ( ORDER BY "SYSID")<=1000)  
4 - access("STATUS"=5)

Note

• dynamic statistics used: dynamic sampling (level=2)

29 rows selected.

Elapsed: 00:00:00.03

分析：两层嵌套也达到了，查到满足1000条记录就返回的要求，逻辑读的消耗同样是957。
以上两种分页查询同时满足了取1000条符合条件的流水的目的，且效率高，可直接拿来用在生产中。