Oracle分页查询格式（八）

Oracle分页查询格式（八） 2006-10-28 00:00:00

分类： Linux

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


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

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

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

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操作使用这种方法可能导致查询结果的数量小于查询开始限定的数量。而对于另外两种集操作，这种方法可能会导致错误的查询结果。