Oracle分页查询与解决方案

weixin_44563350 2019-04-08 10:24:15

1.ROWNUM:
概念：OORACLE使用ROWNUM是一个伪列，数据库提取记录才会生成的数值1,2,3，4 作用是用于实现ORACLE的分页必须使用子查询实现

执行流程(带条件) : a.查询EMPLOYEES表，生成EMOLYEES伪列
b.根据分页条件判断该ROWNUM是否与该条件匹配
c.条件匹配，取出该条条件
d.生成第二个ROWNUM重复r操作

2.别名问题
由于ROWNUM的WHERE判断执行在SELECT关键字之前。当前查询中的ROWNUM别名不能用于条件做判断，别名只可以用一外部条件判断

3.ROWID是数据库保存记录时候生成的真实物理地址，唯一不变
作用：数据库操作记录使用
索引值→ROWID→将ROWID换成算成一行数据的物理地址→得到一行数据
如：提取员工表的前三行数据

如：提取4行之后的数据
错误例子：SELECT ROWNUM，EMP.* FROM EMP WHERE ROWNUM > 3 --错误
生成第一个ROWNUM，进行条件判断是不符合，无法提取结果，结果为NULL
解决方案：先查询带ROWNUM的伪表SELECT ROWNUM，EMP.* FROM EMP查询伪表，选出4条以后的数据SELECT * FROM(SELECT ROWNUM R,EMP.* FROM EMP) RE WHERE RE.R > 3

1.提取工资排行前三的员工

虽然查询了结果，但是似乎并不是前工资排名前三的员工，
原因分析：WHERE条件的执行在ORACLE BY 之前，页就是先生成了ROWNUM之后才进行，显然者时候序号已经生成好
正确的是SELECT * FROM EMP ORACLE BY SALARY DESC先排序，SELECT ROWNUM,E.* FROM (SELECT * FROM EMP ORACLE BY SAL DESC) E WHERE ROWNUM BETWEEN 1 AND 4

2.提取6到10的记录数据效率

我要提取的数据是6到10之前的数据，把不需要用到的数据过滤掉，提高效率后的写法，只进行提取分页(表数据多是效率会极低)。
如图下。

...全文

902 17 打赏收藏转发到动态举报

写回复

用AI写文章

17 条回复

切换为时间正序

请发表友善的回复…

发表回复

卖水果的net 2019-09-10

打赏
举报

引用 16 楼小飞鱼smd 的回复:

我看视频测试结果用 rowid 时最快

引用 17 楼小飞鱼smd 的回复:

select * from emp where rowid in (select rid from (select rownum rn, rid from (select rowid rid from emp) where rownum < 5) where rn > = 2)

分页的查询，基本上都会有对特定字段排序的需求。

小飞鱼smd 2019-09-09

打赏
举报

select * from emp where rowid in (select rid from (select rownum rn, rid from (select rowid rid from emp) where rownum < 5) where rn > = 2)

小飞鱼smd 2019-09-09

打赏
举报

我看视频测试结果用 rowid 时最快

卖水果的net 2019-04-13

打赏
举报

引用 12 楼 AHUA1001 的回复:

关于分页查询，有一个必须注意的问题，就是先排序，再取行，否则会有问题。另外，分页中用到的大于号小于号，有点过时了，应该用BETWEEN。

这两种写法，几乎是在同一时间出现的。从性能上来讲，这两种分页查询的差异表现如下： between 的写法，查询每页的时间是一样的。先小于，再大于的写法，第一页的速度是最快的，第二页要慢一些，第三页再慢一些，最后一页的性能和 between是一样的。

minsic78 2019-04-12

打赏
举报

引用 12 楼 AHUA1001 的回复:

关于分页查询，有一个必须注意的问题，就是先排序，再取行，否则会有问题。另外，分页中用到的大于号小于号，有点过时了，应该用BETWEEN。

用between代码倒是好看了，性能完了。排序也是，排序了就需要额外的优化手段。如果你说的问题是翻页后数据重复的问题，那么排序还需要加在唯一性很好的字段上。

AHUA1001 2019-04-12

打赏
举报

关于分页查询，有一个必须注意的问题，就是先排序，再取行，否则会有问题。
另外，分页中用到的大于号小于号，有点过时了，应该用BETWEEN。

卖水果的net 2019-04-09

打赏
举报

引用 8 楼 minsic78 的回复:

例子可以举一个，当然比较极端，如果表里有数据同时deptno,sal为空，那么这条索引没法用到这种分页SQL中，当然从业务上来讲，这个不可能，但是如果这两个字段上都没有not null约束，或者主键约束，那么即使业务上数据不可能全为空，oracle还是不会选择这条索引。

看上一楼

卖水果的net 2019-04-09

打赏
举报


-- 这个实验结果，你还满意不

Connected to Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 
Connected as zc2bmes_user@10.255.30.9/test

SQL> 
SQL> set timing on;
SQL> drop index ix_emp;
drop index ix_emp
ORA-01418: 指定的索引不存在
SQL> select count(*) from emp;
  COUNT(*)
----------
  14221312
Executed in 2.886 seconds
SQL> select count(*) from emp where deptno is null;
  COUNT(*)
----------
    819200
Executed in 2.366 seconds
SQL> select count(*) from emp where sal is null;
  COUNT(*)
----------
   6094848
Executed in 2.861 seconds
SQL> select count(*) from emp where deptno = 'DEV';
  COUNT(*)
----------
   7700480
Executed in 2.548 seconds
SQL> select count(*) from ind where table_name = 'EMP';
  COUNT(*)
----------
         0
Executed in 0.037 seconds
SQL> explain plan for SELECT * FROM
  2  (SELECT rownum rn, emp.* FROM EMP WHERE DEPTNO='DEV' ORDER BY SAL DESC )
  3    WHERE ROWNUM <= 20;
Explained
Executed in 0.015 seconds
SQL> select * from table(dbms_xplan.display());
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
Plan hash value: 1035293170
--------------------------------------------------------------------------------
| Id  | Operation               | Name | Rows  | Bytes |TempSpc| Cost (%CPU)| Ti
--------------------------------------------------------------------------------
|   0 | SELECT STATEMENT        |      |    20 | 10740 |       |   841K  (1)| 02
|*  1 |  COUNT STOPKEY          |      |       |       |       |            |
|   2 |   VIEW                  |      |  6401K|  3278M|       |   841K  (1)| 02
|*  3 |    SORT ORDER BY STOPKEY|      |  6401K|  3198M|  4167M|   841K  (1)| 02
|   4 |     COUNT               |      |       |       |       |            |
|*  5 |      TABLE ACCESS FULL  | EMP  |  6401K|  3198M|       |   132K  (1)| 00
--------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
   1 - filter(ROWNUM<=20)
   3 - filter(ROWNUM<=20)
   5 - filter("DEPTNO"='DEV')
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
Note
-----
   - dynamic sampling used for this statement (level=2)
23 rows selected
Executed in 0.213 seconds
SQL> rollback;
Rollback complete
Executed in 0.002 seconds
SQL> SELECT * FROM
  2  (SELECT rownum rn, emp.* FROM EMP WHERE DEPTNO='DEV' ORDER BY SAL DESC )
  3    WHERE ROWNUM <= 20;

  -- 这里的数据我删除了
20 rows selected
Executed in 7.728 seconds
SQL> create index ix_emp on emp(deptno, sal);
Index created
Executed in 16.774 seconds
SQL> exec dbms_stats.gather_table_stats(user,'EMP',cascade => true);
PL/SQL procedure successfully completed
Executed in 16.812 seconds
SQL> explain plan for SELECT * FROM
  2  (SELECT rownum rn, emp.* FROM EMP WHERE DEPTNO='DEV' ORDER BY SAL DESC )
  3    WHERE ROWNUM <= 20;
Explained
Executed in 0.006 seconds
SQL> select * from table(dbms_xplan.display());
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
Plan hash value: 2189114292
--------------------------------------------------------------------------------
| Id  | Operation                       | Name   | Rows  | Bytes | Cost (%CPU)|
--------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                |        |    20 | 10740 |    18   (0)|
|*  1 |  COUNT STOPKEY                  |        |       |       |            |
|   2 |   VIEW                          |        |    20 | 10740 |    18   (0)|
|   3 |    COUNT                        |        |       |       |            |
|   4 |     TABLE ACCESS BY INDEX ROWID | EMP    |  7714K|  1611M|    18   (0)|
|*  5 |      INDEX RANGE SCAN DESCENDING| IX_EMP |    20 |       |     3   (0)|
--------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
   1 - filter(ROWNUM<=20)
   5 - access("DEPTNO"='DEV')
18 rows selected
Executed in 0.194 seconds
SQL> rollback;
Rollback complete
Executed in 0.002 seconds
SQL> SELECT * FROM
  2  (SELECT rownum rn, emp.* FROM EMP WHERE DEPTNO='DEV' ORDER BY SAL DESC )
  3    WHERE ROWNUM <= 20;
  -- 这里的数据我删除了
20 rows selected
Executed in 0.364 seconds

SQL>

minsic78 2019-04-09

打赏
举报

引用 7 楼卖水果的net 的回复:

[quote=引用 6 楼 minsic78 的回复:] [quote=引用 5 楼卖水果的net 的回复:] [quote=引用 3 楼 minsic78 的回复:] 问个问题下面这个语句怎么优化到秒出（假设：emp表上千万，满足deptno条件的过五百万）： SELECT * FROM (SELECT rownum rn, emp.* FROM EMP WHERE DEPTNO='DEVELOPER' ORACLE BY SAL DESC ) WHERE ROWNUM <= 20

加了 deptno, sal 的联合索引后，应该很快能出结果。 [/quote] 其实不一定

[/quote] 举个例子，最好贴一下你的执行过程。[/quote] 例子可以举一个，当然比较极端，如果表里有数据同时deptno,sal为空，那么这条索引没法用到这种分页SQL中，当然从业务上来讲，这个不可能，但是如果这两个字段上都没有not null约束，或者主键约束，那么即使业务上数据不可能全为空，oracle还是不会选择这条索引。

卖水果的net 2019-04-09

打赏
举报

引用 6 楼 minsic78 的回复:

[quote=引用 5 楼卖水果的net 的回复:] [quote=引用 3 楼 minsic78 的回复:] 问个问题下面这个语句怎么优化到秒出（假设：emp表上千万，满足deptno条件的过五百万）： SELECT * FROM (SELECT rownum rn, emp.* FROM EMP WHERE DEPTNO='DEVELOPER' ORACLE BY SAL DESC ) WHERE ROWNUM <= 20

加了 deptno, sal 的联合索引后，应该很快能出结果。 [/quote] 其实不一定

[/quote] 举个例子，最好贴一下你的执行过程。

minsic78 2019-04-09

打赏
举报

引用 5 楼卖水果的net 的回复:

[quote=引用 3 楼 minsic78 的回复:] 问个问题下面这个语句怎么优化到秒出（假设：emp表上千万，满足deptno条件的过五百万）： SELECT * FROM (SELECT rownum rn, emp.* FROM EMP WHERE DEPTNO='DEVELOPER' ORACLE BY SAL DESC ) WHERE ROWNUM <= 20

加了 deptno, sal 的联合索引后，应该很快能出结果。 [/quote] 其实不一定

卖水果的net 2019-04-09

打赏
举报

引用 3 楼 minsic78 的回复:

问个问题下面这个语句怎么优化到秒出（假设：emp表上千万，满足deptno条件的过五百万）： SELECT * FROM (SELECT rownum rn, emp.* FROM EMP WHERE DEPTNO='DEVELOPER' ORACLE BY SAL DESC ) WHERE ROWNUM <= 20

加了 deptno, sal 的联合索引后，应该很快能出结果。

minsic78 2019-04-09

打赏
举报

引用 10 楼卖水果的net 的回复:

[quote=引用 8 楼 minsic78 的回复:] 例子可以举一个，当然比较极端，如果表里有数据同时deptno,sal为空，那么这条索引没法用到这种分页SQL中，当然从业务上来讲，这个不可能，但是如果这两个字段上都没有not null约束，或者主键约束，那么即使业务上数据不可能全为空，oracle还是不会选择这条索引。

看上一楼[/quote] 是我想茬了，因为已经有了where deptno='DEV'这个条件……

卖水果的net 2019-04-08

打赏
举报

加到 100，好招点人过来。

卖水果的net 2019-04-08

打赏
举报

总结的挺好！

minsic78 2019-04-08

打赏
举报

那个，拷贝的时候没注意，where条件后的ORACLE BY应该是ORDER BY……

minsic78 2019-04-08

打赏
举报

问个问题

下面这个语句怎么优化到秒出（假设：emp表上千万，满足deptno条件的过五百万）： SELECT * FROM (SELECT rownum rn, emp.* FROM EMP WHERE DEPTNO='DEVELOPER' ORACLE BY SAL DESC ) WHERE ROWNUM <= 20

昨天做完项目后让测试测试了一把，测试说分页查询貌似不起作用，翻到第4页以后，看到的数据结果都是相同的。当时我就觉得很纳闷，不可能啊，分页组件应该是好的，咋可能有问题呢。带着疑问，我打开了自己的ide，在...

SQL中分页解决方案 涉及主流的DB Oracle、SQL Server、Mysql

Oracle分页查询是经常用到的sql，有时客户会反映查询数据不变的情况，这是由于Oracle的排序机理造成的不稳定性，下面就如何解决给出具体方法。 (2017-12-31于南京) 前提最近在进行sql优化的时候，有条sql...

主题：Oracle数据库分页查询数据时数据错误解析及解决方法。一、问题描述使用Oracle数据库的小伙伴可能或多或少会接触到对某表做数据分页查询的情况。在数据分页查询时，可能出现分页数据重复，甚至连翻好几页...

当我在用javaee编写条件分页查询的时候，平时开发中老是写差不多的代码，比较繁琐，故自己总结了一套解决方案。这是一套专门针对Oracle数据库和SpringMvc和Mybatis框架中条件、分页查询的方案。本文重点不是讲分页，...