- 浏览: 5615 次
最新评论
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操作使用这种方法可能导致查询结果的数量小于查询开始限定的数量。而对于另外两种集操作,这种方法可能会导致错误的查询结果。
分类: 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操作使用这种方法可能导致查询结果的数量小于查询开始限定的数量。而对于另外两种集操作,这种方法可能会导致错误的查询结果。
发表评论
-
ORCALE 密码修改
2015-09-24 09:43 279运行 cmd sqlplus/nolog c ... -
oracle证书
2015-09-24 09:41 0https://localhost:1158/em/con ... -
orcale个人总结
2015-07-02 10:08 0orcale 密码找回方法----------------- ... -
orcale配置中遇到的问题
2015-07-02 09:54 01 oracle登录错误:ORA-28000: ... -
orcale安装
2015-07-02 09:17 0Oracle11g----Win7 32位安装图例 h ... -
Oracle分页查询格式(十一)
2015-07-24 12:11 313Oracle分页查询格式(十一) 2009-06-05 22: ... -
Oracle分页查询语句(七)
2015-07-31 10:19 341【转载】 yangtingkun: Oracle分页查询语句( ... -
Oracle分页查询语句(六)
2015-07-31 10:18 411Oracle分页查询语句(六) 2006-06-03 00:0 ... -
Oracle分页查询语句(五)
2015-07-31 10:18 254【转载】 yangtingkun: Oracle分页查询语句( ... -
Oracle分页查询语句(四)
2015-07-31 10:18 344【转载】 yangtingkun: Oracle分页查询语句( ... -
Oracle分页查询语句(三)
2015-07-24 12:12 264Oracle分页查询语句(三) 2006-05-27 00:0 ... -
Oracle分页查询语句(二)
2015-07-24 12:12 311Oracle分页查询语句(二) 2006-05-23 00:0 ... -
Oracle分页查询语句(一)
2015-07-24 12:12 329【转载】 yangtingkun: Oracl ...
相关推荐
Oracle分页查询Oracle分页查询Oracle分页查询Oracle分页查询
ORACLE分页查询效率
oracle分页查询语句sql
oracle分页查询并返回总记录数据的存储过程
解决Oracle分页查询中排序与效率问题解决Oracle分页查询中排序与效率问题解决Oracle分页查询中排序与效率问题解决Oracle分页查询中排序与效率问题
msql和oracle分页查询语句笔记
Oracle的分页查询语句以及Oracle分页的存储过程
java实现oracle分页策略完整版,可当做参考类使用,有详细注释,适合初学者。
oracle分页查询常用方法 提供三种方法
Oracle分页查询(很不错的介绍) 保证不会让你失望
分页查询是Oracle数据库比较重要的一个知识点!希望这个对你有参照作用。
本文将结合作者近日工作中,在ORACLE数据库分页查询时,遇到一个小问题,为大家讲解如何解决Oracle分页查询中排序与效率问题。
使用数据库时有时我们会需要分页查询,但是在oracle中使用查询条件时又不可以使用大于号>。
Oracle 分页实例 带分页控件用法 Oracle 实现数据列表分页。
oracle分页存储过程,oracle分页存储过程
oracle&jsp简单实现分页,注意到Oracle包