同事遇到一个奇怪的问题,在使用下面分页导记录时发现分页后的记录跟总的记录数匹配(总的记录数大概25000),但是有些记录不存在,有些重复:
SELECT *
FROM (SELECT row_.*, rownum rn
FROM (select --省略几百行
order by sdate, mname, blevel asc) row_
WHERE rownum <= 10000)
WHERE RN > 0
SELECT *
FROM (SELECT row_.*, rownum rn
FROM (select --省略几百行
order by sdate, mname, blevel asc) row_
WHERE rownum <= 20000)
WHERE RN > 10000
SELECT *
FROM (SELECT row_.*, rownum rn
FROM (select --省略几百行
order by sdate, mname, blevel asc) row_
WHERE rownum <= 30000)
WHERE RN > 20000
可能原因:
1 分页1--10000,10001--20000,20000--30000的sql执行计划不同,造成结果集记录的顺序不同(这个也许有可能,没有验证过)。
2 排序算法(原来的3个字段(sdate, mname, blevel)不能保证唯一性)
能解释的现象:
1 总的记录数是一样的:因为总的记录数是不变的,不管记录的顺序怎么变,3段加起来的和是相等的
2 3段记录里有相同的记录,又有些记录不存在,分3段取时,记录集里记录的顺序是不确定的,可能第一次取到了,第二次因为记录顺序的变化,rownum变化了,又归到了那个区间
解决方法:
在原有的3个排序字段后,增加一个唯一性的排序字段(如no),当然,这是有代价的:
order by sdate, mname, blevel asc,no
关于rownum是怎么产生的(网上有不少的文章,下面是摘录):
rownum是在where条件过滤之后,在任何排序(order by)或聚集(aggregation)之前赋给行的。同时,只有当rownum被分配给行后才会递增。rownum的初始值为1。rownum在查询中产生后就不再变化:
select * from emp where ROWNUM <= 5 order by sal desc;
该语句的目的是想返回top 5薪水最高的员工信息,但根据rownum的产生原理,rownum在order by之前就已经产生,所以该语句并不能起到top 5的作用,正确的语法如下:
select * from (select * from emp order by sal desc) where ROWNUM <= 5;
关于COUNT STOPKEY 和SORT ORDER BY STOPKEY需要了解其机制。
可以通过下面的查询类似模拟一下(增加hints是为了打乱结果集里记录顺序,类似模拟select order by xx 多次运行记录的顺序不一样)
如下的语句如果按emp.job排序取前八条记录,7698 BLAKE,7566 JONES,7782 CLARK其中之一都有可能取到,都可能取不到(从直观想象,3条记录都是MANAGER,rownum都可能是7 8 9):
---------------------
作者:大鹏_James
来源:CSDN
原文:https://blog.csdn.net/fu_pengfei_love822/article/details/71968824
版权声明:本文为博主原创文章,转载请附上博文链接!
做过Oracle分页的人都知道由于Oracle中没有像MySql中limit函数以及SQLServer中的top关键字等,所以只能通过伪列的方式去满足分页功能,在此,不谈分页方法,只从根本上去介绍这两个伪列的用法与原理,同样还是以scott用户的emp表为例;
一、ROWNUM伪列
ROWNUM伪列是Oracle首先进行查询获取到结果集之后在加上去的一个伪列,这个伪列对符合条件的结果添加一个从1开始的序列号,先看一个例子:
SELECT ROWNUM,empno,ename,job FROM emp WHERE deptno = 30;
结果如下:
可以看到确实添加了一列从1开始的序列号,那么有了这个伪列,就可以完成好多提取数据的工作,比如提取emp表中前5条数据,SQL如下:
SELECT ROWNUM,empno,ename,job FROM emp WHERE ROWNUM < 6;
ROWNUM是动态的,也就是必须先有查询到的结果集,然后再给这个结果集加上一个列,比如给结果集中的第一条数据的ROWNUM的值为1,依次类推,如果此时这样写:
SELECT ROWNUM,empno,ename,job FROM emp WHERE ROWNUM > 5 AND ROWNUM <= 10;
当生成结果集时,Oracle首先会产生一条ROWNUM为1的记录,显然不符合条件,那么同样会继续产生第二条数据,同样标识ROWNUM为1,该条记录同样继续被过滤掉,后续生成的ROWNUM依然为1,因此上述查询语句不会有任何查询结果,所以如果想要使上述结果有满足条件的结果集,必须使用子查询,代码如下:
SELECT * FROM (SELECT ROWNUM nums,emp.* FROM emp) WHERE nums > 5 AND nums <= 10;
子查询部分固定表里面数据的记录编号,外层查询过滤子查询里面固定的记录编号就可以实现Oracle分页!
二、ROWID伪列
同ROWNUM伪列不同的是,它是物理存在的,ROWID是一种数据类型,它使用基于64为编码的18个字符来唯一标识一条记录物理位置的一个ID,类似于Java中一个对象的哈希码,都是为了唯一标识对应对象的物理位置,需要注意的是ROWID虽然可以在表中进行查询,但是其值并未存储在表中,所以不支持增删改操作,下面看个例子:
SELECT ROWNUM,ROWID,empno,ename,job FROM emp WHERE ROWNUM <= 5;
结果如下:
可以看到ROWID确实由18个字符组成,组成结构如下:
数据对象编号 文件编号 块编号 行编号
OOOOOO FFF BBBBBB RRR
至于ROWID的作用,由于ROWID用来唯一标识表中数据的唯一性,所以可以利用这个特性去除重复,举个例子,首先运行下述两行代码:
CREATE TABLE dept_bak AS SELECT * FROM dept;
INSERT INTO dept_bak SELECT * FROM dept;
得到一个如下的数据库表
很明显,数据有重复的,但是ROWID肯定不会重复的,那么就可以利用这个特性去重,简单示例代码如下:
DELETE FROM dept_bak WHERE ROWID NOT IN( SELECT MIN(ROWID) FROM dept_bak GROUP BY DEPTNO);
除了上述之外还有好多作用,详情参照Oracle官方文档!,在此不多做叙述!
---------------------
作者:yu102655
来源:CSDN
原文:https://blog.csdn.net/yu102655/article/details/52370542
版权声明:本文为博主原创文章,转载请附上博文链接!
在Oracle下ROWNUM函数是对查询结果按照顺序分配自增行序号,所以很多人都利用这个特点进行分页操作,即rownum between 30 and 60,这种限制返回行数的小窍门在普通情况下都很正常,但如果要对返回结果在进行排序就变得乱套了。
如下查询:
select page.* from
( select rownum page_id,
id,
unit_code,
name,
code
FROM lb_sys_twork page
WHERE state=1
ORDER BY unit_code,
code) Page
where page.page_id between 10
AND 31
这是一个网上很常用的查询模式,虽然我在子查询中加入了ORDER BY unit_code,code,并限制返回记录集的第10条到30条但是返回集并没有按照我所想的那样进行排序,查其原因发现rownum page_id这个伪字段并不是在最终返回结果集中生成,而是在引用它的子查询中已经返回,如此情况,在加了order by后返回的结果集也就不是按照1,2,3。。。这样的顺序排列了。我尝试将rownum page_id放在最外层,如下:
select rownum page_id, page.* from
( select id,
unit_code,
name,
code
FROM lb_sys_twork page
WHERE state=1
ORDER BY unit_code,
code) Page
where page.page_id between 10
AND 31
这时查询出现错误,说是找不到Page_id这个字段,我将page_id的字段别名去掉,直接用rownum来做字段,结果集为空。到这时我才恍然大悟为什么把rownum叫做伪字段,rownum是在结果集进行输出时自动加入的一列,按照这种原理,如果你用Where rownum=10来限制返回的行数,将会得到的是一个空结果集,rownum>10等等也不行。看来把rownum放在子集还是放在最外层都不行,难道没办法了吗?
还是Google帮了忙找到了这片文章,其中介绍了MINUS这个减法集合运算符,MINUS是找到两个给定的数据集合之间的差异,即找到一个数据集合,该集合的数据是仅存在于前一个数据集而后一个数据集中不存在的,相当于集合1-集合2,利用此函数,我可以让集合1返回的是前50条数据,而集合2返回的是前30条,50-30就相当于返回的第30-50的数据。查询SQL如下:
SELECT rownum,
page.*
FROM ( select Page.*
FROM lb_sys_twork page
WHERE page.state=1
ORDER BY unit_code,
code) Page
WHERE rownum < 50 MINUS SELECT rownum,
page.*
FROM ( select Page.*
FROM lb_sys_twork page
WHERE page.state=1
ORDER BY unit_code,
code) Page
WHERE rownum < 30