龙空技术网

详解rownum与rowid分页及数据访问开销对比

波波说运维 163

前言:

而今小伙伴们对“sql分页查询公式”大概比较讲究,咱们都需要分析一些“sql分页查询公式”的相关文章。那么小编也在网络上汇集了一些有关“sql分页查询公式””的相关资讯,希望朋友们能喜欢,小伙伴们一起来学习一下吧!

概述

这段时间在研究优化方面的内容,刚好看到分页这块,所以顺便整理了下rowid和rownum的区别。

1、rownum

rownum是伪列,是在获取查询结果集后再加上去的 (获取一条记录加一个rownum)。对符合条件的结果添加一个从1开始的序列号。

实例:

--查询结果有9条select rownum,e.* from emp e where rownum<10

rownum是动态的,必有查询结果,然后再给查询的结果集添加上这个列。 例如:第一条记录的rownum是1 ,第二条是2,以此类推。

---查询结果为空集select rownum,e.* from emp e where rownum>5 

当产生结果集时,oracle会产生一条rownum为1的记录,显然不符合条件;那么就会产生第二条记录,同样rownum=1,也不符合记录; 一直下去,导致最后上述sql产生的结果集时空集。

如果需要查询到结果,需要使用子查询:

--查询第5行到第10行的数据(错误写法)select rownum, empno from (select rownum rn , empno from emp) a where a.rownum > 5 and a.rownum < 10;--查询第5行到第10行的数据(正确写法)select rownum, empno from (select rownum rn , empno from emp where rownum<10) a where a.rn>5;
rowid

rowid是物理存在的,实际存在的一个列,是一种数据类型。 基于64为编码的18个字符来唯一标识的一条记录的物理位置的一个ID。

唯一标识出对应的存储的物理位置, 类似hashcode值。

注意:rowid并未存储在表中,所以不支持增删改操作,只能用户查询。

网上的写法(错误):

select empno from emp where rowid > 5 and rowid < 10;

这里查询第5行到第10行的数据网上很多写法是直接rowid去做判断,实际上有问题的(rowid又不是数字,数据类型不一样)

分页IO对比

oracle数据库一般采用rownum来进行分页,常用分页语法有如下两种:

1、直接通过rownum分页:

select*from( select a.*,rownum rn from (select*from product a where company_id=? orderby status) a whererownum<=20)where rn>10;

数据访问开销=索引IO+索引全部记录结果对应的表数据IO

2、采用rowid分页语法

优化原理是通过纯索引找出分页记录的ROWID,再通过ROWID回表返回数据,要求内层查询和排序字段全在索引里。

create index myindex on product(company_id,status);select b.* from (select * from (select a.*, rownum rn from (selectrowid rid, status from product a where company_id = ? orderby status) a whererownum <= 20) where rn > 10) a, product b wherea.rid = b.rowid;

数据访问开销=索引IO+索引分页结果对应的表数据IO

3、实例:

一个公司产品有1000条记录,要分页取其中20个产品,假设访问公司索引需要50个IO,2条记录需要1个表数据IO。

那么按第一种ROWNUM分页写法,需要550(50+1000/2)个IO,按第二种ROWID分页写法,只需要60个IO(50+20/2);

关于rowid和rownum方面的内容就介绍到这了,大家也可以自己测试下。

后面会分享更多关于devops和DBA方面内容,感兴趣的朋友可以关注下!!

标签: #sql分页查询公式