oracle分页显示方法
一、使用rownum分页显示方式
方式1: SELECT * FROM (SELECT ROWNUM r, a.* FROM b$i_exch_info a WHERE ROWNUM <= 10) WHERE r >= 5; 方式2: SELECT * FROM (SELECT ROWNUM r, a.* FROM b$i_exch_info a) WHERE r BETWEEN 5 AND 10; 方式3: SELECT * FROM b$i_exch_info WHERE ROWNUM <= 10 MINUS SELECT * FROM b$i_exch_info WHERE ROWNUM < 5; 二、使用分析函数row_number分页显示 SELECT * FROM (SELECT e.*, row_number() over(ORDER BY g3e_fid) r FROM b$i_exch_info e) a WHERE a.r >= 5 AND a.r <= 10;注意事项
1.
--10g及10g之后才可以使用rownum=1SELECT * FROM user_objects WHERE /*object_id <100 AND*/ ROWNUM = 1;--之前的版本 SELECT * FROM user_objects WHERE object_id <100 AND ROWNUM <= 1; 2.rownum采用大于号>时 其值必须小于1,否则查询无结果SELECT * FROM user_objects WHERE ROWNUM >1; >= 时其值必须小于或等于1,否则查询无结果SELECT * FROM user_objects WHERE ROWNUM >=2; = 时其只能等于1,否则查询无结果SELECT * FROM user_objects WHERE ROWNUM =2; 3.ROWNUM 和Order BY在使用ROWNUM 时,只有当Order By 的字段是主键时,查询结果才会先排序再计算ROWNUM:g3e_ano是主键SELECT g3e_ano,g3e_username FROM g3e_attribute WHERE ROWNUM <= 5 ORDER BY g3e_ano;1 备注1002 组件序号1008 组件序号1009 组件序号1010 组件序号--以下查询因为ORDER BY的g3e_username不是主键,所以执行时是先线取出该表的6条数据,再对g3e_username排序SELECT g3e_ano,g3e_username FROM g3e_attribute WHERE ROWNUM <= 5 ORDER BY g3e_username;111003 设施特征唯一号113203 设施特征唯一号50110 设施特征唯一号1510103 设施特征唯一号112003 设施特征唯一号--如果需要对非主键字段先排序再去取前n 条数据,可以通过子查询的方式实现:select g3e_ano, g3e_username from (select g3e_ano, g3e_username from g3e_attribute order by g3e_username) where rownum <= 5;--每页按10条记录输出(如果被排序的字段有重复值,使用rownum会出现一个问题):--观察下面两个语句的输出结果会发现其中55461451和55461209是在两个查询中都出现了。而fid在表中都是唯一记录的,--说明这个输出结果是错误的错误原因:SORT (ORDER BY STOPKEY)这种快速排序方法由于是根据数据分组来选择数据的,不是根据整个表的数据进行排序,所以N值不同,数据的分组也不同,导致结果在数据的排序字段值都相等时,输出结果的顺序就会因为N 值不同而不同。SELECT * FROM (SELECT ROWNUM r, a.* FROM (SELECT name, g3e_fid FROM b$l_interest_info a ORDER BY name) a WHERE ROWNUM <= 10) WHERE r >= 1;1 王家宅 554610792 王家宅 554612063 王家宅 554612074 王家宅 554612535 王家宅 554612466 王家宅 554612097 王家宅 554617838 王家宅 554616469 王家宅 5546158610 王家宅 55461451SELECT * FROM (SELECT ROWNUM r, a.* FROM (SELECT name, g3e_fid FROM b$l_interest_info a ORDER BY name) a WHERE ROWNUM <= 20) WHERE r >= 11;11 王家宅 5699048512 王家宅 5699036813 王家宅 5698186214 王家宅 5698186115 王家宅 5698180716 王家宅 5698180617 王家宅 5698180118 王家宅 5546164619 王家宅 5546145120 王家宅 55461209解决办法:1、让查询计划避免“SORT (ORDER BY STOPKEY)”,采用“SORT (ORDER BY)”,使数据排序不受ROWNUM 的影响。但这样会使所有数据都做排序:SELECT * FROM (SELECT a.*, ROWNUM r FROM (SELECT name, g3e_fid FROM b$l_interest_info a ORDER BY name) a) WHERE r <= 10 AND r >= 1;SELECT * FROM (SELECT a.*, ROWNUM r FROM (SELECT name, g3e_fid FROM b$l_interest_info a ORDER BY name) a) WHERE r <= 20 AND r >= 11;2、在排序时,加上一个或多个字段(如主键字段、ROWID),使排序结果具有唯一性:SELECT * FROM (SELECT ROWNUM r, a.* FROM (SELECT name, g3e_fid FROM b$l_interest_info a ORDER BY name,g3e_fid) a WHERE ROWNUM <= 10) WHERE r >= 1; SELECT * FROM (SELECT ROWNUM r, a.* FROM (SELECT name, g3e_fid FROM b$l_interest_info a ORDER BY name,g3e_fid) a WHERE ROWNUM <= 20) WHERE r >= 11;3、对排序字段建立索引,并强制使用索引。这样就能利用索引已经建立好的排序结果:CREATE INDEX idx_b$l_interest_info_name ON b$l_interest_info(name);ALTER INDEX idx_b$l_interest_info_name REBUILD;SELECT * FROM (SELECT ROWNUM r, a.* FROM (SELECT /*+index(a idx_b$l_interest_info_name)*/ name, g3e_fid FROM b$l_interest_info a WHERE a.name IS NOT NULL ORDER BY name) a WHERE ROWNUM <= 10) WHERE r >= 1; SELECT * FROM (SELECT ROWNUM r, b.* FROM (SELECT /*+index(a idx_b$l_interest_info_name)*/ a.name, a.g3e_fid FROM b$l_interest_info a WHERE a.name IS NOT NULL ORDER BY a.name) b WHERE ROWNUM <= 20) WHERE r >= 11;