Oracle ROWNUM用法和分页查询总结

**********************************************************************************************************

[转载]

Oracle的分页查询语句基本上可以按照本文给出的格式来进行套用。

Oracle分页查询格式（一）：http://yangtingkun.itpub.net/post/468/100278

Oracle分页查询格式（二）：http://yangtingkun.itpub.net/post/468/101703

Oracle分页查询格式（三）：http://yangtingkun.itpub.net/post/468/104595

Oracle分页查询格式（四）：http://yangtingkun.itpub.net/post/468/104867

Oracle分页查询格式（五）：http://yangtingkun.itpub.net/post/468/107934

Oracle分页查询格式（六）：http://yangtingkun.itpub.net/post/468/108677

Oracle分页查询格式（七）：http://yangtingkun.itpub.net/post/468/109834

Oracle分页查询格式（八）：http://yangtingkun.itpub.net/post/468/224557

Oracle分页查询格式（九）：http://yangtingkun.itpub.net/post/468/224409

Oracle分页查询格式（十）：http://yangtingkun.itpub.net/post/468/224823

Oracle分页查询的排序问题：http://yangtingkun.itpub.net/post/468/112274 

Oracle官网连接查询优化的说明：http://docs.oracle.com/cd/E11882_01/server.112/e16638/optimops.htm#i36235
NESTED LOOP/HASH JOIN/SORT MERGE JOIN的区别：http://jewfinkl.blog.163.com/blog/static/14076982012431052316/

**********************************************************************************************************

根据以上文章进行了如下的总结。

ROWNUM

可能都知道ROWNUM只适用于小于或小于等于，如果进行等于判断，那么只能等于1，不能进行大于的比较。
ROWNUM是oracle系统顺序分配为从查询返回的行的编号，返回的第一行分配的是1，第二行是2，依此类推。
ROWNUM总是从1开始，不管当前的记录是否满足查询结果，ROWNUM返回的值都是1，如果这条记录的值最终满足所有的条件，那么ROWNUM会递加，下一条记录的ROWNUM会返回2，否则下一条记录的ROWNUM仍然返回1。
理解了这一点，就清楚为什么一般的ROWNUM大于某个值或等于某个不为1的值是无法返回结果的，因此对于每条记录的ROWNUM都是1，而ROWNUM为1不满足查询的结果，所以下一条记录的ROWNUM不会递增，仍然是1，因此所有的记录都不满足条件。

分页查询格式1
在查询的最外层控制分页的最小值和最大值。查询语句如下：

SELECT * FROM 
(
SELECT A.*, ROWNUM RN 
FROM (SELECT * FROM TABLE_NAME) A 
)
WHERE RN BETWEEN 21 AND 40

分页查询格式2

SELECT * FROM 
(
SELECT A.*, ROWNUM RN 
FROM (SELECT * FROM TABLE_NAME) A 
WHERE ROWNUM <= 40
)
WHERE RN >= 21

分页查询格式3
考虑到多表联合的情况，如果不介意在系统中使用HINT的话，可以将分页的查询语句改写为：

SELECT /*+ FIRST_ROWS */ * FROM 
(
SELECT A.*, ROWNUM RN 
FROM (SELECT * FROM TABLE_NAME) A 
WHERE ROWNUM <= 40
)
WHERE RN >= 21

效率问题
对比这两种写法，绝大多数的情况下，第2个查询的效率比第1个高得多。
这是由于CBO优化模式下，Oracle可以将外层的查询条件推到内层查询中，以提高内层查询的执行效率。对于第2个查询语句，第二层的查询条件WHERE ROWNUM <= 40就可以被Oracle推入到内层查询中，这样Oracle查询的结果一旦超过了ROWNUM限制条件，就终止查询将结果返回了。
而第1个查询语句，由于查询条件BETWEEN 21 AND 40是存在于查询的第三层，而Oracle无法将第三层的查询条件推到最内层（即使推到最内层也没有意义，因为最内层查询不知道RN代表什么）。因此，对于第1个查询语句，Oracle最内层返回给中间层的是所有满足条件的数据，而中间层返回给最外层的也是所有数据。数据的过滤在最外层完成，显然这个效率要比第一个查询低得多。
上面分析的查询不仅仅是针对单表的简单查询，对于最内层查询是复杂的多表联合查询或最内层查询包含排序的情况一样有效。

观察上面格式1和格式2二者的执行计划可以发现，两个执行计划唯一的区别就是格式2的查询在COUNT这步使用了STOPKEY，也就是说，Oracle将ROWNUM <= 20推入到查询内层，当符合查询的条件的记录达到STOPKEY的值，则Oracle结束查询。因此，可以预见，采用第二种方式，在翻页的开始部分查询速度很快，越到后面，效率越低，当翻到最后一页，效率应该和第一种方式接近。

分页查询语句之所以可以很快的返回结果，是因为它的目标是最快的返回第一条结果。如果每页有20条记录，目前翻到第5页，那么只需要返回前100条记录都可以满足查询的要求了，也许还有几万条记录也符合查询的条件，但是由于分页的限制，在当前的查询中可以忽略这些数据，而只需尽快的返回前100条数据。这也是为什么在标准分页查询语句中经常会使用FIRST_ROWS提示的原因。
对于行操作，可以在得到结果的同时将结果直接返回给上一层调用。但是对于结果集操作，Oracle必须得到结果集中所有的数据，因此分页查询中所带的ROWNUM信息不起左右。如果最内层的子查询中包含了下面这些操作中的一个以上，则分页查询语句无法体现出任何的性能优势：UNION、UNION ALL、MINUS、INTERSECT、GROUP BY、DISTINCT、UNIQUE以及聚集函数如MAX、MIN和分析函数等。

Oracle10g的新功能GROUP BY STOPKEY，使得Oracle10g解决了GROUP BY操作分页效率低的问题。在10g以前，Oracle的GROUP BY操作必须完全执行完，才能将结果返回给用户。但是Oracle10g增加了GROUP BY STOPKEY执行路径，使得用户在执行GROUP BY操作时，可以根据STOPKEY随时中止正在运行的操作。这使得标准分页函数对于GROUP BY操作重新发挥了作用。

除了这些操作以外，分页查询还有一个很明显的特点，就是处理的页数越小，效率就越高，越到后面，查询速度越慢。
分页查询用来提高返回速度的方法都是针对数据量较小的前N条记录而言。无论是索引扫描，NESTED LOOP连接，还是ORDER BY STOPKEY，这些方法带来性能提升的前提都是数据量比较小，一旦分页到了最后几页，会发现这些方法不但没有办法带来性能的提升，而且性能比普通查询还要低得多。这一点，在使用分页查询的时候，一定要心里有数。
分页查询一般情况下，很少会翻到最后一篇，如果只是偶尔碰到这种情况，对系统性能不会有很大的影响，但是如果经常碰到这种情况，在设计分页查询时应该给予足够的考虑。

多表联合
下面简单讨论一下多表联合的情况。对于最常见的等值表连接查询，CBO一般可能会采用两种连接方式NESTED LOOP和HASH JOIN（MERGE JOIN效率比HASH JOIN效率低，一般CBO不会考虑）。
一般对于大表查询情况下，HASH JOIN的效率要比NESTED LOOP高很多，所以CBO一般默认会选择HASH JOIN.

但是如果分页查询的内层是这种连接查询的话，使用NESTED LOOP可以更快的得到前N条记录。
在这里，由于使用了分页，因此指定了一个返回的最大记录数，NESTED LOOP在返回记录数超过最大值时可以马上停止并将结果返回给中间层，而HASH JOIN必须处理完所有结果集（MERGE JOIN也是）。那么在大部分的情况下，对于分页查询选择NESTED LOOP作为查询的连接方法具有较高的效率（分页查询的时候绝大部分的情况是查询前几页的数据，越靠后面的页数访问几率越小）。

HASH JOIN中第一步也就是第一张表的全表扫描是无法应用STOPKEY的，这就是NESTED LOOP比HASH JOIN优势的地方。
但是，如果恰好第一张表很小，对这张表的全扫描的代价极低，会显得HASH JOIN效率更高。
如果两张表的大小相近，或者Oracle错误的选择了先扫描大表，则使用HASH JOIN的效率就会低得多。

因此对于表连接来说，在写分页查询的时候，可以考虑增加FIRST_ROWS提示，它会导致CBO选择NESTED LOOP，有助于更快的将查询结果返回。
其实，不光是表连接，对于所有的分页查询都可以加上FIRST_ROWS提示。
不过需要注意的时，分页查询的目标是尽快的返回前N条记录，因此，无论是ROWNUM还是FIRST_ROWS机制都是提高前几页的查询速度，
对于分页查询的最后几页，采用HASH JOIN的方式，执行效率几乎没有任何改变，而采用NESTED LOOP方式，则效率严重下降，而且远远低于HASH JOIN的方式。

排序列不唯一所带来的问题
如果用来排序的列不唯一，也就是存在值相等的行，可能会造成第一次在前10条返回记录中，某行数据出现了，而第二次在11到第20条记录中，某行数据又出现了。一条数据重复出现两次，就必然意味着有数据在两次查询中都不会出现。
其实造成这个问题的原因很简单，是由于排序列不唯一造成的。Oracle这里使用的排序算法不具有稳定性，也就是说，对于键值相等的数据，这种算法完成排序后，不保证这些键值相等的数据保持排序前的顺序。
解决这个问题其实也很简单。有两种方法可以考虑。
1）在使用不唯一的字段排序时，后面跟一个唯一的字段。
一般在排序字段后面跟一个主键就可以了，如果表不存在主键，跟ROWID也可以。这种方法最简单，且对性能的影响最小。
2）另一种方法就是使用前面给出过多次的BETWEEN AND的方法。
这种方式由于采用表数据的全排序，每次只取全排序中的某一部分数据，因此不会出现上面提到的重复数据问题。
但是正是由于使用了全排序，而且ROWNUM信息无法推到查询内部，导致这种写法的执行效率很低

测试结果
下面做一些测试，按照如下步骤准备数据：

CREATE TABLE T AS SELECT * FROM DBA_USERS;
CREATE TABLE T1 AS SELECT * FROM DBA_SOURCE;
ALTER TABLE T ADD CONSTRAINT PK_T PRIMARY KEY (USERNAME);
ALTER TABLE T1 ADD CONSTRAINT FK_T1_OWNER FOREIGN KEY (OWNER) REFERENCES T(USERNAME);
CREATE INDEX IND_T1_OWNER ON T1(OWNER);
EXEC DBMS_STATS.GATHER_TABLE_STATS(USER, 'T')
EXEC DBMS_STATS.GATHER_TABLE_STATS(USER, 'T1')
set autotrace traceonly
set timing on

现在表格T中有37行数据，表格T1中有623K行数据。

比较格式1和格式2的查询计划

--查询语句1
SELECT * FROM   
(  
SELECT A.*, ROWNUM RN   
FROM (SELECT * FROM T1) A   
)  
WHERE RN BETWEEN 21 AND 40;
--查询语句2
SELECT * FROM   
(  
SELECT A.*, ROWNUM RN   
FROM (SELECT * FROM T1) A   
WHERE ROWNUM <= 40  
)  
WHERE RN >= 21;

--查询语句1
SELECT * FROM   
(  
SELECT A.*, ROWNUM RN   
FROM (SELECT * FROM T, T1 WHERE T.USERNAME = T1.OWNER) A   
WHERE ROWNUM <= 40  
)  
WHERE RN >= 21;
--查询语句2
SELECT /*+ FIRST_ROWS */ * FROM   
(  
SELECT A.*, ROWNUM RN   
FROM (SELECT * FROM T, T1 WHERE T.USERNAME = T1.OWNER) A   
WHERE ROWNUM <= 40  
)  
WHERE RN >= 21;
--或者
SELECT * FROM   
(  
SELECT A.*, ROWNUM RN   
FROM (SELECT /*+ USE_NL(T T1) */ * FROM T, T1 WHERE T.USERNAME = T1.OWNER) A   
WHERE ROWNUM <= 40  
)  
WHERE RN >= 21;

可以看到默认是采用hash join,改用nested loop join方式似乎效率并没有明显提高，但是这是由于表T比较小只有34行，所以hash join的第一步即使对T进行全表扫描而无法应用stopkey，效率也很高。

ALTER TABLE T MODIFY(USER_ID NULL, ACCOUNT_STATUS NULL, DEFAULT_TABLESPACE NULL,
TEMPORARY_TABLESPACE NULL, CREATED NULL, PROFILE NULL);
INSERT INTO T(USERNAME) SELECT ('USER' || LEVEL) FROM DUAL CONNECT BY LEVEL < 100000;
COMMIT;
EXEC DBMS_STATS.GATHER_TABLE_STATS(USER, 'T')

然后重新测试语句1，会发现现在oracle已经改成用nested loop join了。
因此现在语句1和语句2的效果等同了。可以使用 USE_HASH(T T1) HINT强制使用hash join，结果做下对比，会发现hash join的效率低于nested loop join，读数据发生的IO（consistent gets+physical reads）大大增加了.
可以看到CBO是相当智能了。

含排序的查询
含排序操作的分页查询。可以简单的将查询分为两种不同情况，第一种排序列就是索引列，这种可以利用索引读取，第二种排序列没有索引。
第一种情况又可以细分为：完全索引扫描和通过索引扫描定位到表记录两种情况。无论是那种情况，都可以通过索引的全扫描来避免排序的产生。
第二种情况下，排序不可避免，但是利用给出分页格式，Oracle不会对所有数据进行排序，而是只排序前N条记录。

--查询语句1，排序列就是索引列.注意这里需要加上OWNER IS NOT NULL,否则由于OWNER列不是NOT NULL,会导致索引无法使用。
SELECT * FROM
(
SELECT A.*, ROWNUM RN
FROM (SELECT * FROM T1 WHERE OWNER IS NOT NULL ORDER BY OWNER) A
WHERE ROWNUM <= 40
)
WHERE RN >= 21;

--查询语句2，排序列没有索引
SELECT * FROM
(
SELECT A.*, ROWNUM RN
FROM (SELECT * FROM T1 ORDER BY NAME) A
WHERE ROWNUM <= 40
)
WHERE RN >= 21;


--查询语句3，排序列没有索引
SELECT * FROM
(
SELECT A.*, ROWNUM RN
FROM (SELECT * FROM T1 ORDER BY NAME) A
)
WHERE RN BETWEEN 21 AND 40;

tony@ORCL1> CREATE TABLE TEST AS SELECT ROWNUM ID, A.* FROM DBA_OBJECTS A;
Table created.

tony@ORCL1> EXEC DBMS_STATS.GATHER_TABLE_STATS(USER, 'TEST');
PL/SQL procedure successfully completed.

tony@ORCL1> COLUMN OBJECT_NAME FORMAT A30
tony@ORCL1> SELECT * FROM
  2  (
  3  SELECT A.*, ROWNUM RN
  4  FROM (SELECT ID, OWNER, OBJECT_NAME FROM TEST WHERE OWNER IS NOT NULL ORDER BY OWNER) A
  5  WHERE ROWNUM <= 10
  6  )
  7  WHERE RN >= 1;

        ID OWNER                          OBJECT_NAME                            RN
---------- ------------------------------ ------------------------------ ----------
     69170 APEX_030200                    WWV_FLOW_INIT_HTP_BUFFER                1
     69179 APEX_030200                    WWV_HTF                                 2
     69178 APEX_030200                    WWV_FLOW_LANG                           3
     69177 APEX_030200                    WWV_FLOW_UTILITIES                      4
     69176 APEX_030200                    VC4000ARRAY                             5
     69175 APEX_030200                    WWV_FLOW_SECURITY                       6
     69174 APEX_030200                    WWV_FLOW                                7
     69173 APEX_030200                    HTMLDB_ITEM                             8
     69172 APEX_030200                    WWV_FLOW_GLOBAL                         9
     69171 APEX_030200                    WWV_FLOW_IMAGE_PREFIX                  10

10 rows selected.

tony@ORCL1> SELECT * FROM
  2  (
  3  SELECT A.*, ROWNUM RN
  4  FROM (SELECT ID, OWNER, OBJECT_NAME FROM TEST WHERE OWNER IS NOT NULL ORDER BY OWNER) A
  5  WHERE ROWNUM <= 20
  6  )
  7  WHERE RN >= 11;

        ID OWNER                          OBJECT_NAME                            RN
---------- ------------------------------ ------------------------------ ----------
     69180 APEX_030200                    WWV_HTP                                11
     69179 APEX_030200                    WWV_HTF                                12
     69178 APEX_030200                    WWV_FLOW_LANG                          13
     69177 APEX_030200                    WWV_FLOW_UTILITIES                     14
     69176 APEX_030200                    VC4000ARRAY                            15
     69175 APEX_030200                    WWV_FLOW_SECURITY                      16
     69174 APEX_030200                    WWV_FLOW                               17
     69173 APEX_030200                    HTMLDB_ITEM                            18
     69172 APEX_030200                    WWV_FLOW_GLOBAL                        19
     69171 APEX_030200                    WWV_FLOW_IMAGE_PREFIX                  20

10 rows selected.
--可以看到，有多个ID在两次查询中都出现了。
--通过加上ID作为排序列解决这个问题。

tony@ORCL1> SELECT * FROM
  2  (
  3  SELECT A.*, ROWNUM RN
  4  FROM (SELECT ID, OWNER, OBJECT_NAME FROM TEST WHERE OWNER IS NOT NULL ORDER BY OWNER, ID) A
  5  WHERE ROWNUM <= 10
  6  )
  7  WHERE RN >= 1;

        ID OWNER                          OBJECT_NAME                            RN
---------- ------------------------------ ------------------------------ ----------
     69170 APEX_030200                    WWV_FLOW_INIT_HTP_BUFFER                1
     69171 APEX_030200                    WWV_FLOW_IMAGE_PREFIX                   2
     69172 APEX_030200                    WWV_FLOW_GLOBAL                         3
     69173 APEX_030200                    HTMLDB_ITEM                             4
     69174 APEX_030200                    WWV_FLOW                                5
     69175 APEX_030200                    WWV_FLOW_SECURITY                       6
     69176 APEX_030200                    VC4000ARRAY                             7
     69177 APEX_030200                    WWV_FLOW_UTILITIES                      8
     69178 APEX_030200                    WWV_FLOW_LANG                           9
     69179 APEX_030200                    WWV_HTF                                10

10 rows selected.

tony@ORCL1> SELECT * FROM
  2  (
  3  SELECT A.*, ROWNUM RN
  4  FROM (SELECT ID, OWNER, OBJECT_NAME FROM TEST WHERE OWNER IS NOT NULL ORDER BY OWNER, ID) A
  5  WHERE ROWNUM <= 20
  6  )
  7  WHERE RN >= 11;

        ID OWNER                          OBJECT_NAME                            RN
---------- ------------------------------ ------------------------------ ----------
     69180 APEX_030200                    WWV_HTP                                11
     69181 APEX_030200                    ESCAPE_SC                              12
     69182 APEX_030200                    WWV_FLOW_META_DATA                     13
     69183 APEX_030200                    WWV_FLOW_TEMPLATES_UTIL                14
     69184 APEX_030200                    WWV_RENDER_CALENDAR2                   15
     69185 APEX_030200                    WWV_RENDER_CHART2                      16
     69186 APEX_030200                    WWV_FLOW_CHECK                         17
     69187 APEX_030200                    WWV_RENDER_REPORT3                     18
     69188 APEX_030200                    WWV_FLOW_PAGE_CACHE_API                19
     69189 APEX_030200                    WWV_FLOW_RENDER_QUERY                  20

10 rows selected.