Oracle分页查询格式指的是什么呢

今天就跟大家聊聊有关Oracle分页查询格式指的是什么呢，可能很多人都不太了解，为了让大家更加了解，小编给大家总结了以下内容，希望大家根据这篇文章可以有所收获。

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

综合来说，除了SQL嵌套可以少写一层外，并没有什么特别的优点来代替标准分页函数的写法。

不过上一篇测试所有的数据都是通过全表扫描得到的，如果在排序字段上存在索引，这两种不同的分页查询效率如何呢，还是继续进行测试：

SQL> ALTER TABLE T MODIFY OBJECT_NAME NOT NULL; 

表已更改。

SQL> CREATE INDEX IND_T_OBJECT_NAME ON T (OBJECT_NAME);

索引已创建。

为了Oracle可以利用这个索引，将索引列置为非空，首先测试标准分页SQL语句：

SQL> SELECT OBJECT_ID, OBJECT_NAME
  2  FROM
  3  (
  4     SELECT ROWNUM RN, OBJECT_ID, OBJECT_NAME
  5     FROM
  6     (
  7             SELECT OBJECT_ID, OBJECT_NAME FROM T
  8             ORDER BY OBJECT_NAME
  9     )
 10     WHERE ROWNUM <= 20
 11  )
 12  WHERE RN >= 11;

 OBJECT_ID OBJECT_NAME
---------- ------------------------------
     17869 /1005bd30_LnkdConstant
     17870 /1005bd30_LnkdConstant
     17869 /1005bd30_LnkdConstant
     17870 /1005bd30_LnkdConstant
     17869 /1005bd30_LnkdConstant
     17870 /1005bd30_LnkdConstant
     17869 /1005bd30_LnkdConstant
     17870 /1005bd30_LnkdConstant
     17869 /1005bd30_LnkdConstant
     17870 /1005bd30_LnkdConstant

已选择10行。

已用时间:  00: 00: 00.05

Execution Plan
----------------------------------------------------------
   0      SELECT STATEMENT ptimizer=CHOOSE (Cost=826 Card=20 Bytes=1840)
   1    0   VIEW (Cost=826 Card=20 Bytes=1840)
   2    1     COUNT (STOPKEY)
   3    2       VIEW (Cost=826 Card=4584838 Bytes=362202202)
   4    3         TABLE ACCESS (BY INDEX ROWID) OF 'T' (Cost=826 Card=4584838 Bytes=132960302)
   5    4           INDEX (FULL SCAN) OF 'IND_T_OBJECT_NAME' (NON-UNIQUE) (Cost=26 Card=4584838)

Statistics
----------------------------------------------------------
          0  recursive calls
          0  db block gets
         15  consistent gets
          3  physical reads
          0  redo size
        578  bytes sent via SQL*Net to client
        503  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
         10  rows processed

在标准SQL中为了使用索引和NESTED LOOP连接方式，一般还要加上FIRST_ROWS提示，现在还没有加上FIRST_ROWS提示，Oracle就使用了索引全扫描代替了全表扫描，而且效率相当的高，只需要0.5秒就返回了结果。

再看分析函数的表现：

SQL> SELECT OBJECT_ID, OBJECT_NAME
  2  FROM
  3  (
  4     SELECT OBJECT_NAME, OBJECT_ID, 
  5             ROW_NUMBER() OVER(ORDER BY OBJECT_NAME) RN
  6     FROM T
  7  )
  8  WHERE RN BETWEEN 11 AND 20;

 OBJECT_ID OBJECT_NAME
---------- ------------------------------
     17869 /1005bd30_LnkdConstant
     17870 /1005bd30_LnkdConstant
     17869 /1005bd30_LnkdConstant
     17870 /1005bd30_LnkdConstant
     17869 /1005bd30_LnkdConstant
     17870 /1005bd30_LnkdConstant
     17869 /1005bd30_LnkdConstant
     17870 /1005bd30_LnkdConstant
     17869 /1005bd30_LnkdConstant
     17870 /1005bd30_LnkdConstant

已选择10行。

已用时间:  00: 01: 09.17

Execution Plan
----------------------------------------------------------
   0      SELECT STATEMENT ptimizer=CHOOSE (Cost=826 Card=4584838 Bytes=421805096)
   1    0   VIEW (Cost=826 Card=4584838 Bytes=421805096)
   2    1     WINDOW (NOSORT) (Cost=826 Card=4584838 Bytes=132960302)
   3    2       TABLE ACCESS (BY INDEX ROWID) OF 'T' (Cost=826 Card=4584838 Bytes=132960302)
   4    3         INDEX (FULL SCAN) OF 'IND_T_OBJECT_NAME' (NON-UNIQUE) (Cost=26 Card=4584838)

Statistics
----------------------------------------------------------
          0  recursive calls
          0  db block gets
    3197229  consistent gets
     118443  physical reads
          0  redo size
        578  bytes sent via SQL*Net to client
        503  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
         10  rows processed

SQL> SELECT OBJECT_ID, OBJECT_NAME
  2  FROM
  3  (
  4     SELECT OBJECT_NAME, OBJECT_ID, 
  5             ROW_NUMBER() OVER(ORDER BY OBJECT_NAME) RN
  6     FROM T
  7  )
  8  WHERE RN BETWEEN 11 AND 20;

 OBJECT_ID OBJECT_NAME
---------- ------------------------------
     17869 /1005bd30_LnkdConstant
     17870 /1005bd30_LnkdConstant
     17869 /1005bd30_LnkdConstant
     17870 /1005bd30_LnkdConstant
     17869 /1005bd30_LnkdConstant
     17870 /1005bd30_LnkdConstant
     17869 /1005bd30_LnkdConstant
     17870 /1005bd30_LnkdConstant
     17869 /1005bd30_LnkdConstant
     17870 /1005bd30_LnkdConstant

已选择10行。

已用时间:  00: 00: 10.65

Execution Plan
----------------------------------------------------------
   0      SELECT STATEMENT ptimizer=CHOOSE (Cost=826 Card=4584838 Bytes=421805096)
   1    0   VIEW (Cost=826 Card=4584838 Bytes=421805096)
   2    1     WINDOW (NOSORT) (Cost=826 Card=4584838 Bytes=132960302)
   3    2       TABLE ACCESS (BY INDEX ROWID) OF 'T' (Cost=826 Card=4584838 Bytes=132960302)
   4    3         INDEX (FULL SCAN) OF 'IND_T_OBJECT_NAME' (NON-UNIQUE) (Cost=26 Card=4584838)

Statistics
----------------------------------------------------------
          0  recursive calls
          0  db block gets
    3197229  consistent gets
      43319  physical reads
          0  redo size
        578  bytes sent via SQL*Net to client
        503  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
         10  rows processed

如果说第一次执行是由于大量物理读没有缓存，导致执行时间达到了1分钟的话，那么第二次执行仍旧高得离谱的三百多万的逻辑读，就很说明问题了。执行时间居然要10秒多，比全表扫描效率还低，看执行计划就知道，这次STOP KEY没有被推到分析函数的窗口排序中，导致Oracle扫描了所有的记录。

这对于分页来说，绝对是不可接受的。不过这是在9i的环境下进行的测试：

SQL> SELECT * FROM V$VERSION;

BANNER
----------------------------------------------------------------
Oracle9i Enterprise Edition Release 9.2.0.4.0 - Production
PL/SQL Release 9.2.0.4.0 - Production
CORE    9.2.0.3.0       Production
TNS for Linux: Version 9.2.0.4.0 - Production
NLSRTL Version 9.2.0.4.0 - Production

看看10g中Oracle是否解决了这个问题：

SQL> SELECT * FROM V$VERSION;

BANNER
----------------------------------------------------------------
Oracle Database 10g Enterprise Edition Release 10.2.0.3.0 - 64bi
PL/SQL Release 10.2.0.3.0 - Production
CORE    10.2.0.3.0      Production
TNS for Linux: Version 10.2.0.3.0 - Production
NLSRTL Version 10.2.0.3.0 - Production

SQL> SELECT OBJECT_ID, OBJECT_NAME
  2  FROM
  3  (
  4     SELECT OBJECT_NAME, OBJECT_ID, 
  5             ROW_NUMBER() OVER(ORDER BY OBJECT_NAME) RN
  6     FROM T
  7  )
  8  WHERE RN BETWEEN 11 AND 20;

 OBJECT_ID OBJECT_NAME
---------- ------------------------------
     30166 /1000e8d1_LinkedHashMapValueIt
     30165 /1000e8d1_LinkedHashMapValueIt
     30166 /1000e8d1_LinkedHashMapValueIt
     30165 /1000e8d1_LinkedHashMapValueIt
     30165 /1000e8d1_LinkedHashMapValueIt
     30165 /1000e8d1_LinkedHashMapValueIt
     30165 /1000e8d1_LinkedHashMapValueIt
     30165 /1000e8d1_LinkedHashMapValueIt
     30165 /1000e8d1_LinkedHashMapValueIt
     30165 /1000e8d1_LinkedHashMapValueIt

10 rows selected.

Elapsed: 00:00:02.04

Execution Plan
----------------------------------------------------------
Plan hash value: 3047187157

-----------------------------------------------------------------------------------------
| Id  | Operation                | Name | Rows  | Bytes |TempSpc| Cost (%CPU)| Time     |
-----------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT         |      |  4969K|   436M|       | 41652   (1)| 00:09:44 |
|*  1 |  VIEW                    |      |  4969K|   436M|       | 41652   (1)| 00:09:44 |
|*  2 |   WINDOW SORT PUSHED RANK|      |  4969K|   132M|   342M| 41652   (1)| 00:09:44 |
|   3 |    TABLE ACCESS FULL     | T    |  4969K|   132M|       | 17375   (1)| 00:04:04 |
-----------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - filter("RN">=11 AND "RN"<=20)
   2 - filter(ROW_NUMBER() OVER ( ORDER BY "OBJECT_NAME")<=20)

Statistics
----------------------------------------------------------
          0  recursive calls
          0  db block gets
      52137  consistent gets
          0  physical reads
          0  redo size
        725  bytes sent via SQL*Net to client
        492  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          1  sorts (memory)
          0  sorts (disk)
         10  rows processed

SQL> SELECT /*+ FIRST_ROWS */ OBJECT_ID, OBJECT_NAME
  2  FROM
  3  (
  4     SELECT OBJECT_NAME, OBJECT_ID, 
  5             ROW_NUMBER() OVER(ORDER BY OBJECT_NAME) RN
  6     FROM T
  7  )
  8  WHERE RN BETWEEN 11 AND 20;

 OBJECT_ID OBJECT_NAME
---------- ------------------------------
     30165 /1000e8d1_LinkedHashMapValueIt
     30166 /1000e8d1_LinkedHashMapValueIt
     30165 /1000e8d1_LinkedHashMapValueIt
     30166 /1000e8d1_LinkedHashMapValueIt
     30165 /1000e8d1_LinkedHashMapValueIt
     30166 /1000e8d1_LinkedHashMapValueIt
     30165 /1000e8d1_LinkedHashMapValueIt
     30166 /1000e8d1_LinkedHashMapValueIt
     30165 /1000e8d1_LinkedHashMapValueIt
     30166 /1000e8d1_LinkedHashMapValueIt

10 rows selected.

Elapsed: 00:00:00.00

Execution Plan
----------------------------------------------------------
Plan hash value: 3257002816

-----------------------------------------------------------------------------------------
|Id |Operation                     |Name             |Rows  |Bytes|Cost (%CPU)|Time     |
-----------------------------------------------------------------------------------------
|  0|SELECT STATEMENT              |                 | 4969K| 436M| 3679K  (1)|14:18:35 |
|* 1| VIEW                         |                 | 4969K| 436M| 3679K  (1)|14:18:35 |
|* 2|  WINDOW NOSORT STOPKEY       |                 | 4969K| 132M| 3679K  (1)|14:18:35 |
|  3|   TABLE ACCESS BY INDEX ROWID|T                | 4969K| 132M| 3679K  (1)|14:18:35 |
|  4|    INDEX FULL SCAN           |IND_T_OBJECT_NAME| 4969K|     |11703   (1)|00:02:44 |
-----------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - filter("RN">=11 AND "RN"<=20)
   2 - filter(ROW_NUMBER() OVER ( ORDER BY "OBJECT_NAME")<=20)

Statistics
----------------------------------------------------------
          1  recursive calls
          0  db block gets
         16  consistent gets
          0  physical reads
          0  redo size
        755  bytes sent via SQL*Net to client
        492  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
         10  rows processed

10g中表的结构与数据量和9i完全一致，但是默认情况下，Oracle并没有选择使用索引扫描的方式。如果在SQL中加上FIRST_ROWS提示，那么Oracle选择索引扫描，并以接近0秒的速度将结果返回。

对比9i和10g采用分析函数分页的执行计划可以发现，92的执行计划为WINDOW (NOSORT)，而102为WINDOW NOSORT STOPKEY。显然Oracle在10g解决了9i存在的问题，这也是在上一篇文章中提到的，Oracle可能会不断完善分析函数的功能。

如果总结一下，10g中使用分析函数来进行分页，已经没有什么问题了，但是在9i中，用分析函数的方式进行分页，可能会带来严重的性能问题。

看完上述内容，你们对Oracle分页查询格式指的是什么呢有进一步的了解吗？如果还想了解更多知识或者相关内容，请关注亿速云行业资讯频道，感谢大家的支持。