Oracle分页查询格式（十三）

最新推荐文章于 2024-03-29 06:33:19 发布
最新推荐文章于 2024-03-29 06:33:19 发布
阅读量54 收藏
点赞数
文章标签： 数据库 java 大数据

前几天有个网友问到，为什么这个系列的大部分例子中都没有包括查询条件。

其实分页只是一种标准的写法，分页嵌套的业务SQL才是实现查询功能的关键，而这部分可能会很简单，也可能会复杂的很。因此很难模拟各种复杂的业务SQL在分页中的表现。如果要分析分页查询，只能依旧不同业务SQL的特性进行分类，比如：包括GROUP BY操作、包含UNION ALL查询、通过全表扫描获取记录、通过索引扫描获取记录等等。

而且要先假定一些前提，既然要讨论分页的效率，那么分页的SQL一般返回的数据量会比较大或者返回记录数未知。如果业务SQL只是返回10条以内的记录，那么根本没有分页的必要；如果业务SQL本身就可以在秒级以内获取到结果，那么也不用太关心分页后的性能问题。

因此这前面实际上讨论的是返回大数据量的情况下，分页所能带来的性能优势。

至于网友提到的，大部分SQL没有包含查询条件，其实这里是简化SQL后的结果。如果SQL的查询条件可以快速的定位结果，并返回少量的数据，那么这种情况本身效率就很高，使用分页并不会带来进一步的性能提高；如果索引返回大量的数据，或者由于数据量太大，根本不会使用索引，而采用全表扫描的情况，那么这种情况其实完全可以用单表的全表扫描来模拟。

不过Oracle确实有一种表结构非常适合满足索引查询条件的排序分页，这就是HASH排序聚簇表：

SQL> CREATE CLUSTER C_HASH_SORT
2 (ID NUMBER, CREATED DATE SORT)
3 HASHKEYS 100000 SIZE 1125;

Cluster created.

SQL> CREATE TABLE T_HASH_SORT
2 (ID NUMBER,
3 OWNER VARCHAR2(30),
4 OBJECT_NAME VARCHAR2(30),
5 OBJECT_TYPE VARCHAR2(30),
6 CREATED DATE SORT)
7 CLUSTER C_HASH_SORT (ID, CREATED);

Table created.

SQL> CREATE TABLE T_NORMAL
2 (ID NUMBER,
3 OWNER VARCHAR2(30),
4 OBJECT_NAME VARCHAR2(30),
5 OBJECT_TYPE VARCHAR2(30),
6 CREATED DATE);

Table created.

SQL> INSERT INTO T_HASH_SORT
2 SELECT *
3 FROM
4 (
5 SELECT MOD(ROWNUM, 100000) ID,
6 A.OWNER,
7 OBJECT_NAME,
8 OBJECT_TYPE,
9 A.CREATED
10 FROM DBA_OBJECTS A, DBA_DB_LINKS
11 )
12 ORDER BY ID, CREATED;

2476775 rows created.

SQL> COMMIT;

Commit complete.

SQL> INSERT INTO T_NORMAL
2 SELECT *
3 FROM
4 (
5 SELECT MOD(ROWNUM, 100000) ID,
6 A.OWNER,
7 OBJECT_NAME,
8 OBJECT_TYPE,
9 A.CREATED
10 FROM DBA_OBJECTS A, DBA_DB_LINKS
11 )
12 ORDER BY ID, CREATED;

2476775 rows created.

SQL> COMMIT;

Commit complete.

SQL> SET TIMING ON
SQL> SET AUTOT ON
SQL> SELECT *
2 FROM
3 (
4 SELECT ROWNUM RN, A.*
5 FROM
6 (
7 SELECT ID, OWNER, OBJECT_TYPE, CREATED
8 FROM T_NORMAL
9 WHERE ID = 11232
10 ORDER BY CREATED
11 ) A
12 WHERE ROWNUM <= 20
13 )
14 WHERE RN > 10;

RN ID OWNER OBJECT_TYPE CREATED
---------- ---------- -------------------- ------------------------------ --------------
11 11232 SYS JAVA CLASS 11-6月 -08
12 11232 SYS JAVA CLASS 11-6月 -08
13 11232 PUBLIC SYNONYM 11-6月 -08
14 11232 SYS JAVA CLASS 11-6月 -08
15 11232 CTXSYS TABLE 11-6月 -08
16 11232 ORDSYS JAVA RESOURCE 11-6月 -08
17 11232 MDSYS PACKAGE BODY 11-6月 -08
18 11232 PUBLIC SYNONYM 11-6月 -08
19 11232 PUBLIC SYNONYM 11-6月 -08
20 11232 JIANGSU15 INDEX 12-6月 -08

10 rows selected.

Elapsed: 00:00:00.11

Execution Plan
----------------------------------------------------------
Plan hash value: 1455441750

-------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 20 | 1380 | 3213 (1)| 00:00:45 |
|* 1 | VIEW | | 20 | 1380 | 3213 (1)| 00:00:45 |
|* 2 | COUNT STOPKEY | | | | | |
| 3 | VIEW | | 106 | 5936 | 3213 (1)| 00:00:45 |
|* 4 | SORT ORDER BY STOPKEY| | 106 | 5936 | 3213 (1)| 00:00:45 |
|* 5 | TABLE ACCESS FULL | T_NORMAL | 106 | 5936 | 3212 (1)| 00:00:45 |
-------------------------------------------------------------------------------------

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

1 - filter("RN">10)
2 - filter(ROWNUM<=20)
4 - filter(ROWNUM<=20)
5 - filter("ID"=11232)

Note
-----
- dynamic sampling used for this statement


Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
9638 consistent gets
0 physical reads
0 redo size
1099 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 *
2 FROM
3 (
4 SELECT ROWNUM RN, A.*
5 FROM
6 (
7 SELECT ID, OWNER, OBJECT_TYPE, CREATED
8 FROM T_HASH_SORT
9 WHERE ID = 11232
10 ORDER BY CREATED
11 ) A
12 WHERE ROWNUM <= 20
13 )
14 WHERE RN > 10;

RN ID OWNER OBJECT_TYPE CREATED
---------- ---------- -------------------- ------------------------------ --------------
11 11232 SYS JAVA CLASS 11-6月 -08
12 11232 SYS JAVA CLASS 11-6月 -08
13 11232 PUBLIC SYNONYM 11-6月 -08
14 11232 PUBLIC SYNONYM 11-6月 -08
15 11232 CTXSYS TABLE 11-6月 -08
16 11232 ORDSYS PROCEDURE 11-6月 -08
17 11232 MDSYS TYPE 11-6月 -08
18 11232 SYS JAVA CLASS 11-6月 -08
19 11232 SYSMAN TRIGGER 11-6月 -08
20 11232 JIANGSU15 INDEX 12-6月 -08

10 rows selected.

Elapsed: 00:00:00.00

Execution Plan
----------------------------------------------------------
Plan hash value: 156907859

------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 20 | 1380 | 5 (100)| 00:00:01 |
|* 1 | VIEW | | 20 | 1380 | 5 (100)| 00:00:01 |
|* 2 | COUNT STOPKEY | | | | | |
| 3 | VIEW | | 2293 | 125K| 5 (100)| 00:00:01 |
|* 4 | TABLE ACCESS HASH| T_HASH_SORT | 2293 | 125K| | |
------------------------------------------------------------------------------------

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

1 - filter("RN">10)
2 - filter(ROWNUM<=20)
4 - access("ID"=11232)

Note
-----
- dynamic sampling used for this statement


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

为了避免Oracle的PARSE以及缓存对SQL的影响，上面的两个查询都是第二次执行的结果。

比较了一个普通表和HASH SORT聚簇表在分页情况下的性能差距，普通表需要9000多的逻辑读，而HASH SORT CLUSTER表仅仅需要5个逻辑读。显然后者更适合这种指定一个查询条件并排序的分页查询。

当然这是全表扫描的情况，如果普通表建立了复合索引，可以极大的提高查询的效率：

SQL> CREATE INDEX IND_T_NORMAL ON T_NORMAL (ID, CREATED);

Index created.

Elapsed: 00:00:04.18
SQL> SELECT *
2 FROM
3 (
4 SELECT ROWNUM RN, A.*
5 FROM
6 (
7 SELECT ID, OWNER, OBJECT_TYPE, CREATED
8 FROM T_NORMAL
9 WHERE ID = 11232
10 ORDER BY CREATED
11 ) A
12 WHERE ROWNUM <= 20
13 )
14 WHERE RN > 10;

RN ID OWNER OBJECT_TYPE CREATED
---------- ---------- -------------------- ------------------------------ --------------
11 11232 SYS JAVA CLASS 11-6月 -08
12 11232 SYS JAVA CLASS 11-6月 -08
13 11232 PUBLIC SYNONYM 11-6月 -08
14 11232 SYS JAVA CLASS 11-6月 -08
15 11232 CTXSYS TABLE 11-6月 -08
16 11232 ORDSYS JAVA RESOURCE 11-6月 -08
17 11232 MDSYS PACKAGE BODY 11-6月 -08
18 11232 PUBLIC SYNONYM 11-6月 -08
19 11232 PUBLIC SYNONYM 11-6月 -08
20 11232 JIANGSU15 INDEX 12-6月 -08

10 rows selected.

Elapsed: 00:00:00.01

Execution Plan
----------------------------------------------------------
Plan hash value: 1590327436

-------------------------------------------------------------------------------------------
|Id | Operation | Name |Rows | Bytes | Cost (%CPU)| Time |
-------------------------------------------------------------------------------------------
| 0| SELECT STATEMENT | | 20| 1380 | 4 (0)| 00:00:01 |
|* 1| VIEW | | 20| 1380 | 4 (0)| 00:00:01 |
|* 2| COUNT STOPKEY | | | | | |
| 3| VIEW | | 25| 1400 | 4 (0)| 00:00:01 |
| 4| TABLE ACCESS BY INDEX ROWID| T_NORMAL | 25| 1400 | 4 (0)| 00:00:01 |
|* 5| INDEX RANGE SCAN | IND_T_NORMAL | 25| | 3 (0)| 00:00:01 |
-------------------------------------------------------------------------------------------

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

1 - filter("RN">10)
2 - filter(ROWNUM<=20)
5 - access("ID"=11232)

Note
-----
- dynamic sampling used for this statement


Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
6 consistent gets
0 physical reads
0 redo size
1099 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

可以看到，通过建立复合索引可以显著的提高普通表针对分页查询的性能。但是性能仍然比HASH SORT CLUSTER要差。不过利用索引的方式灵活性更高，因为HASH SORT CLUSTER表只针对指定查询，而建立索引则可以根据查询的不同以不同的方式创建。

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/14270146/viewspace-669369/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/14270146/viewspace-669369/

cuanzhong6087
关注
  • 0
    点赞
  • 踩
  • 0
    收藏
    觉得还不错? 一键收藏
  • 0
    评论
Oracle教程(十一)分页查询
Felix的小黑板
09-26 329
Oracle中的分页查询
热门推荐
m0_71406734的博客
05-11 1万+
Oracle中的分页查询
oracle11g学习笔记(八)
houjinxin的专栏
04-12 920
一、oracle的分页 oracle的分页一共有三中方式（这里只有一种） 1.rownum 分页 select * from  (select a.*,rownum rn from    (select * from emp) a   where rownum  )  where rn>10; 这条语句是用来将11-15条记录提取出来,拆分这条语句 1.将所有想要的结果
oracle11g
asddasddeedd的博客
02-22 749
一、分页查询语法 SELECT * FROM ( SELECT a.*, ROWNUM rn FROM ( SELECT * FROM ( ) ) a WHERE ROWNUM <= 3 ) -- 结束索引 WHERE rn > 0 -- 开始索引（1开始） // 分页时传入参数（页数（currentPage） 行数（pageSize）） Long start = (currentPage - 1) * pageSize; // 开始索引 Long end = star
oracle分页查询sql
01-15
### Oracle分页查询详解 #### 一、分页查询的重要性 在数据库操作中，分页查询是一项非常重要的技术。尤其当处理大量数据时，一次性获取所有数据不仅会消耗大量的网络带宽，还可能导致前端页面加载缓慢，用户体验...
Oracle分页查询性能优化代码详解
01-19
对于数据库中表的数据的 Web 显示，如果没有展示顺序的需要，而且因为满足条件的记录如此之多，就不得不对数据进行分页处理。常常用户并不是对所有数据都感兴趣的，或者大部分情况下，他们只看前几页。 通常有以下...
Oracle分页查询的实例详解
12-15
以下是对Oracle分页查询的实例详解： 1. **Oracle分页查询基础** Oracle分页查询通常涉及ROWNUM伪列的使用。ROWNUM在查询执行过程中为每一行分配一个唯一的数字，从1开始递增。在以下两个示例中，我们可以看到两种...
详解oracle分页查询的基础原理
09-09
本篇文章将深入解析Oracle分页查询的基础原理，并通过实例来展示其实现方法。 首先，Oracle为每个表自动生成了两个伪列：ROWID和ROWNUM。ROWID是用于标识表中每一行的物理地址，它对于数据库内部操作至关重要，但在...
oracle数据库分页查询
chuang777的博客
03-04 853
oracle 11g 分页 替换sql种的limit SELECT * FROM ( select ROWNUM AS RN,a.* from CONFIG_INFO a WHERE ROWNUM<=10)WHERE rn>5;
oracle 使用rownum的三种分页方式
12-16
rownum是Oracle数据库中的一个特有关键字，返回的是一个数字代表记录的行号。 基础知识：rownum只能做<或者<=的条件查询，如果要rownum进行51到100这样的范围判断，需要先查询出每一行的rownum，再用那个序号做判断 获取51到100的数据 三种分页的写法： 1.使用minus，原理就是查询出前100行的数据 减去 查询出前50行的数据 select * from DATA_TABLE_SQL where rownum<=100 minus select * from DATAT_ABLE_SQL where rownum<=50 2.查询出所有数据的rownum，然后
Struts1.2+oracle11g的登录后分页显示程序
03-19
Struts1.2+oracle11g的登录后分页显示程序，供大家一起共同分享学习。
Oracle数据库分页查询
皮一下很开心的猴头
07-27 510
Oracle数据库分页的三种方法 -- 不能对ROWNUM使用&gt;（大于1的数值）、&gt;=（大于或等于1的数值）、=（大于或等于1的数值），否则无结果 -- 所以直接用只能从1开始 -- rownum &gt;10 没有记录，因为第一条不满足去掉的话，第二条的rownum又成了1，所以永远没有满足条件的记录。 select * from student where rownum&gt;=...
深入解析Oracle数据库中的分页查询原理及应用实战案例
最新发布
专注于数据库技术分享，包含但不限于Oracle，MySQL，PostgreSQL，ElasticSearch及国产数据库等
03-29 603
ROWNUM是一个Oracle内建的伪列，它会为每个查询返回的行分配一个从1开始的连续行号。然而，由于ROWNUM是在Oracle处理查询结果的过程中逐行分配的，并且在满足WHERE子句过滤条件之后才进行分配，所以直接对ROWNUM进行条件判断可能无法达到预期的分页效果，尤其是在涉及到排序和多个表连接时。在Oracle数据库中执行分页查询，尤其是大规模数据检索时，需要高效且精确地定位所需数据范围。的表，我们要查询第3页的数据，每页大小为10行，按照。指定从何处开始提取行（即跳过的行数），
Oracle分页查询
William_TWG的博客
08-25 7274
rownum 在做分段查询的时候，在mysql中可以使用limit a,b 这种方式进行分段查询，同样的分页查询也是类似的道理，但是在ORACL中就不太一样了，如果在ORACLE中使用limit 就会报错命令语句未正确结束，造成这个问题的原因就是在ORACLE中不存在limit，那么同样的要想实现limit分段查询的可以使用ORACLE中的关键字rownum 用法 下面就用一个实际的例子举例说明 例如我现在要查询一些符合条件的数据，那么我使用了如下的SQL进行查询，查询出了总计103条数据 但是现
Oracle分页查询语句
山,在那边
01-16 6833
 Oracle的分页查询语句基本上可以按照本文给出的格式来进行套用。分页查询格式：SELECT * FROM (SELECT A.*, ROWNUM RN FROM (SELECT * FROM TABLE_NAME) A WHERE ROWNUM )WHERE RN >= 21其中最内层的查询SELECT * FROM TABLE_NAME表示不进行翻页的原始查询语句。ROWNUM
Oracle分页查询SQL优化及常用sql优化规则
桃子的博客
04-27 1876
1.一个分页查询语句引发的思考2.优化分析过程3.Rowid和Rownum的区别4.多表关联查询对比5.SQL优化常用规则1.一个分页语句引发的思考执行计划显示耗时是2.491秒，这是Oracle预估时间，实际在PL/SQL中的执行时间是大于20s。2.优化分析过程全表查询单列进行排序与对全列进行排序的效率对比运用单列排序分页3.Rownum和Rowid的区别Rownum 是逻辑地址。表示查询某条...
Oracle查询和表管理
宏微的博客
12-31 607
Oracle函数 Oracle查询 Oracle表管理
oracle分页性能不同,oracle高效分页
weixin_39636079的博客
04-02 206
什么是分页查询对于基于Web的应用而言，对查询的结果集进行分页是一个比较常见的需求。假设浏览器界面每页可以显示10条记录，最初界面显示头10条记录给用户，当终端用户点击“下一页”按钮时，界面显示接下来的10条记录。一般来说，Web后台服务程序并不是一次性的把所有符合条件的记录都返回给浏览器，再由浏览器应用程序对查询结果进行分页。现在的普遍做法都是：当用户要浏览下一页时，浏览器重新从WEB后台服务器...
优化Oracle分页查询策略：高效与效率对比
本文将重点讲解Oracle分页查询的基本格式、效率优化以及在不同情况下的使用策略。 首先，Oracle的分页查询通常采用嵌套查询的方式实现，核心格式如下： ```sql SELECT * FROM (SELECT A.*, ROWNUM RN FROM (SELECT...
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值