Oracle分页查询的方法是什么

本篇内容介绍了“Oracle分页查询的方法是什么”的有关知识，在实际案例的操作过程中，不少人都会遇到这样的困境，接下来就让小编带领大家学习一下如何处理这些情况吧！希望大家仔细阅读，能够学有所成！

前面提到了对于集操作和聚集操作，使用标准的分页函数没有太大的意义，下面通过几篇文章专门讨论集操作和聚集操作的情况。这里首先讨论集操作的情况。

当查询需要分页时，大多数情况都会包含排序操作，因为如果缺少排序操作，很难保证分页返回的数据是连续不重复的。

因此这里只考虑包含排序的情况：

SQL> CREATE TABLE T AS SELECT * FROM DBA_TABLES;

表已创建。

SQL> CREATE TABLE T1 AS SELECT * FROM DBA_INDEXES;

表已创建。

SQL> CREATE INDEX IND_T_OWNER ON T(OWNER);

索引已创建。

SQL> CREATE INDEX IND_T1_OWNER ON T1(OWNER);

索引已创建。

SQL> EXEC DBMS_STATS.GATHER_TABLE_STATS(USER, 'T')

PL/SQL 过程已成功完成。

SQL> EXEC DBMS_STATS.GATHER_TABLE_STATS(USER, 'T1')

PL/SQL 过程已成功完成。

SQL> SET AUTOT ON
SQL> SELECT /*+ FIRST_ROWS */ OWNER, NAME
2 FROM
3 (
4 SELECT ROWNUM RN, OWNER, NAME
5 FROM
6 (
7 SELECT OWNER, TABLE_NAME NAME
8 FROM T
9 UNION ALL
10 SELECT OWNER, INDEX_NAME NAME
11 FROM T1
12 ORDER BY OWNER
13 )
14 WHERE ROWNUM <= 20
15 )
16 WHERE RN > 10;

OWNER NAME
------------------------------ ------------------------------
CTXSYS DR$SECTION_GROUP
CTXSYS DR$THS_BT
CTXSYS DR$THS_FPHRASE
CTXSYS DR$THS_PHRASE
CTXSYS DR$THS
CTXSYS DR$SQE
CTXSYS SYS_IOT_OVER_26472
CTXSYS DR$INDEX_OBJECT
CTXSYS DR$POLICY_TAB
CTXSYS DR$INDEX_PARTITION

已选择10行。

Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=HINT: FIRST_ROWS (Cost=17 Card=20 Bytes=940)
1 0 VIEW (Cost=17 Card=20 Bytes=940)
2 1 COUNT (STOPKEY)
3 2 VIEW (Cost=17 Card=2877 Bytes=97818)
4 3 SORT (ORDER BY STOPKEY) (Cost=7 Card=2877 Bytes=76522)
5 4 UNION-ALL
6 5 TABLE ACCESS (FULL) OF 'T' (Cost=3 Card=1157 Bytes=30082)
7 5 TABLE ACCESS (FULL) OF 'T1' (Cost=4 Card=1720 Bytes=46440)

Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
44 consistent gets
0 physical reads
0 redo size
639 bytes sent via SQL*Net to client
503 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 */ OWNER, NAME
2 FROM
3 (
4 SELECT ROWNUM RN, OWNER, NAME
5 FROM
6 (
7 SELECT OWNER, TABLE_NAME NAME
8 FROM T
9 UNION ALL
10 SELECT OWNER, INDEX_NAME NAME
11 FROM T1
12 ORDER BY OWNER
13 )
14 )
15 WHERE RN > 10 AND RN <=20 ;

OWNER NAME
------------------------------ ------------------------------
CTXSYS DR$THS_BT
CTXSYS DR$THS_FPHRASE
CTXSYS DR$THS_PHRASE
CTXSYS DR$THS
CTXSYS DR$SQE
CTXSYS SYS_IOT_OVER_26472
CTXSYS DR$INDEX_OBJECT
CTXSYS DR$POLICY_TAB
CTXSYS DR$PART_STATS
CTXSYS DR$STATS

已选择10行。

Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=HINT: FIRST_ROWS (Cost=17 Card=2877 Bytes=135219)
1 0 VIEW (Cost=17 Card=2877 Bytes=135219)
2 1 COUNT
3 2 VIEW (Cost=17 Card=2877 Bytes=97818)
4 3 SORT (ORDER BY) (Cost=7 Card=2877 Bytes=76522)
5 4 UNION-ALL
6 5 TABLE ACCESS (FULL) OF 'T' (Cost=3 Card=1157 Bytes=30082)
7 5 TABLE ACCESS (FULL) OF 'T1' (Cost=4 Card=1720 Bytes=46440)

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

从执行SQL的统计中看到，由于集操作的存在，导致了Oracle没有办法将ROWNUM信息推入到查询内部，导致标准分页方式的效率和其他分页方式效率差别不大。

当存在排序操作，且集操作为UNION ALL操作时，可以改写SQL语句为：

SQL> SELECT /*+ FIRST_ROWS */ OWNER, NAME
2 FROM
3 (
4 SELECT ROWNUM RN, OWNER, NAME
5 FROM
6 (
7 SELECT OWNER, NAME
8 FROM
9 (
10 SELECT OWNER, TABLE_NAME NAME
11 FROM T
12 ORDER BY OWNER
13 )
14 WHERE ROWNUM <= 20
15 UNION ALL
16 SELECT *
17 FROM
18 (
19 SELECT OWNER, TABLE_NAME NAME
20 FROM T1
21 ORDER BY OWNER
22 )
23 WHERE ROWNUM <= 20
24 ORDER BY OWNER
25 )
26 WHERE ROWNUM <= 20
27 )
28 WHERE RN > 10;

OWNER NAME
------------------------------ ------------------------------
CTXSYS DR$POLICY_TAB
CTXSYS DR$INDEX_OBJECT
CTXSYS SYS_IOT_OVER_26472
CTXSYS DR$SQE
CTXSYS DR$THS
CTXSYS DR$THS_PHRASE
CTXSYS DR$THS_FPHRASE
CTXSYS DR$THS_BT
CTXSYS DR$SECTION_GROUP
CTXSYS DR$SECTION

已选择10行。

Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=HINT: FIRST_ROWS (Cost=1654 Card=20 Bytes=940)
1 0 VIEW (Cost=1654 Card=20 Bytes=940)
2 1 COUNT (STOPKEY)
3 2 VIEW (Cost=1654 Card=40 Bytes=1360)
4 3 SORT (ORDER BY STOPKEY) (Cost=1652 Card=40 Bytes=1360)
5 4 UNION-ALL
6 5 COUNT (STOPKEY)
7 6 VIEW (Cost=826 Card=1157 Bytes=39338)
8 7 TABLE ACCESS (BY INDEX ROWID) OF 'T' (Cost=826 Card=1157 Bytes=30082)
9 8 INDEX (FULL SCAN) OF 'IND_T_OWNER' (NON-UNIQUE) (Cost=26 Card=1157)
10 5 COUNT (STOPKEY)
11 10 VIEW (Cost=826 Card=1720 Bytes=58480)
12 11 TABLE ACCESS (BY INDEX ROWID) OF 'T1' (Cost=826 Card=1720 Bytes=39560)
13 12 INDEX (FULL SCAN) OF 'IND_T1_OWNER' (NON-UNIQUE) (Cost=26 Card=1720)

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

进行了上面的等价改写，查询的逻辑读大大的减少。其实，这里使用的方法就是人为的将ROWNUM推入到UNION ALL操作的每个子查询中，使用这种方法保证查询结果正确的同时，提高了查询的效率。

不过上面给出的改写方法只对包含排序的UNION ALL操作有效。而其他集操作不能使用这种方法，比如UNION操作使用这种方法可能导致查询结果的数量小于查询开始限定的数量。而对于另外两种集操作，这种方法可能会导致错误的查询结果。

“Oracle分页查询的方法是什么”的内容就介绍到这里了，感谢大家的阅读。如果想了解更多行业相关的知识可以关注亿速云网站，小编将为大家输出更多高质量的实用文章！