关闭

Oracle中的SQL分页查询原理和方法详解

标签: SQL分页OracleOracle分页查找Oracle分页原理
29559人阅读 评论(8) 收藏 举报
分类:

转载请注明出处:http://blog.csdn.net/anxpp/article/details/51534006,谢谢!

    本文分析并介绍Oracle中的分页查找的方法。

    Oracle中的表,除了我们建表时设计的各个字段,其实还有两个字段(此处只介绍2个),分别是ROWID(行标示符)和ROWNUM(行号),即使我们使用DESCRIBE命令查看表的结构,也是看不到这两个列的描述的,因为,他们其实是只在数据库内部使用的,所以也通常称他们为伪列(pseudo column)。

    下面我们先建表并添加一些数据来验证上面的说明。

    建表:

  1. create table users(
  2. id integer primary key,
  3. name nvarchar2(20)
  4. )

    插入数据:

  1. insert into users(id,name) values(1,'tom');
  2. insert into users(id,name) values(2,'cat');
  3. insert into users(id,name) values(3,'bob');
  4. insert into users(id,name) values(4,'anxpp');
  5. insert into users(id,name) values(5,'ez');
  6. insert into users(id,name) values(6,'lily');

    使用describe命令查看表结构:

    01

    可以看到,确实只有建表时的两个字段。

    但我们可以查询的时候,查找到伪列的值:

  1. select rowid,rownum,id,name from users;

    结果:

    02

    这个rowid我们一般用不到,Oracle数据库内部使用它来存储行的物理位置,是一个18位的数字,采用base-64编码。

    而这个rownum,我们也正是使用它来进行分页查询的,它的值,就是表示的该行的行号。

    对于分页,我们只要想办法可以查询到从某一起始行到终止行就可以的,分页的逻辑可以放到程序里面。

    于是,我们理所当然会想到如下语句查询第2页的数据(每页2条数据,页码从1开始,所以起始行的行号为 (页码-1)*每页长度+1=3,终止行的行号为 页码*每页长度=4):

  1. select * from users where rownum>=3 rownum <= 4;

    哈哈!是不是发现没有任何结果,原因很简单,Oracle机制就是这样的:因为第一条数据行号为1,不符合>=3的条件,所以第一行被去掉,之前的第二行变为新的第一行(即这个行号不是写死的,可以理解为是动态的),如此下去,一直到最后一行,条件始终没法满足,所以就一条数据也查不出来。

    既然找到了原因,解决方法也就很明显了,我们只要将行号查询出来生成一个结果集,然后再从这个结果集中,选择行号大于我们设定的那个值就可以了,上面的分页查找正确的写法应该是这样:

  1. select id,name from(
  2. select rownum rn,u.* from users u) ua
  3. where ua.rn between 3 and 4;

    上面的语句还可以优化:>=不能用,但是<=是可以的,我们不需要在子查询中将结果全部查出来,首先使用终止行筛选子查询的结果,SQL如下:

  1. select id,name from(
  2. select rownum rn,u.* from users u where rownum<=4) ua
  3. where ua.rn >= 3;

    结果:

    03

    很多时候,我们并不是盲目的分页查找的,二十按某一个或多个字段的升序或降序分页,即包含 order by 语句的分页查询,我们先看一下 order by 的查询结果中rownum是怎样的:

  1. select rownum,id,name from users order by name;

    结果:

    04

    可以看到,我们说行号完全是动态的,也是不准确的,这时候的行号并不是经过 order by 后新结果的增序行号。

    但有了上面的嵌套查询的经验,这里也可以好好应用一下,怎么做呢:先查找出排序好的结果集,然后应用上面的方法得到最终结果,sql如下:

  1. select id,name from(
  2. (select rownum rn,uo.* from
  3. (select * from users u order by name) uo
  4. where rownum<=4)) ua
  5. where ua.rn>=3;

    按照上面的结果,正确的分页结果应该是id为2和5的,看下结果:

    05

    OK,结果正确。

    其实连表查询之类的,也是差不多的,多点嵌套而已,掌握了原理,随便分析一下就能写出对应的SQL了,而编写SQL时,我们也得动动脑子,毕竟SQL也是由优劣之分的。

16
0
查看评论
发表评论
* 以上用户言论只代表其个人观点,不代表CSDN网站的观点或立场

Oracle ROWNUM用法和分页查询总结

分页查询格式1 在查询的最外层控制分页的最小值和最大值。查询语句如下: SELECT * FROM ( SELECT A.*, ROWNUM RN FROM (SELECT * FROM TABL...
  • fw0124
  • fw0124
  • 2015-01-15 13:56
  • 105796

ORACLE分页查询SQL语法——最高效的分页

--1:无ORDER BY排序的写法。(效率最高) --(经过测试,此方法成本最低,只嵌套一层,速度最快!即使查询的数据量再大,也几乎不受影响,速度依然!) SELECT *   FRO...
  • Honey_Potter
  • Honey_Potter
  • 2016-11-02 22:51
  • 28932

数据库分页大全及Oracle中分页详解

数据库分页大全(oracle利用解析函数row_number高效分页)   Mysql分页采用limt关键字 select * from t_order limit 5,10; #返回第6-15行数据...
  • u010708434
  • u010708434
  • 2013-12-03 14:02
  • 3164

数据库分页大全(oracle利用解析函数row_number高效分页)

数据库分页大全(oracle利用解析函数row_number高效分页)   Mysql分页采用limt关键字 select * from t_order limit 5,10; #返回第6-15行数据...
  • zzjjiandan
  • zzjjiandan
  • 2014-03-04 11:07
  • 8470

oracle分页查询

分页查询 数据库表结构及记录如下: 1.根据rowid来分: 16:31:48 SQL> select * from passvehicleinfo p where rowid in ( sel...
  • centre10
  • centre10
  • 2011-09-03 21:22
  • 9857

Oracle 的几种 分页查询

这是我从网上总结出来的,记下来避免忘记 第一种方法  速度会快点,在tab1这层先过滤掉了一部分数据,方法成本最低,只嵌套一层,速度最快,即使查询的数据量再大,也几乎不受影响,速度依然 第二种方法 ...
  • qq1009475063
  • qq1009475063
  • 2016-01-28 11:09
  • 1138

oracle 分页效率 比较

Oracle数据库中的优化器又叫查询优化器(Query Optimizer)。它是SQL分析和执行的优化工具, 它负责生成、制定SQL的执行计划。Oracle的优化器有两种,基于规则的优化器(RBO)...
  • u011278012
  • u011278012
  • 2016-07-25 16:23
  • 1565

【Oracle】——大数据下分页方法

最近工作非常的轻松,只是做了一些小功能oracle显示分页,但是要求随着数据量的加大,不能影响系统的查询速度,这也就是要求小编在所有的解决方案中选择最好的一项。 小编先提供两种常用的分页方法 方法一、...
  • jiadajing267
  • jiadajing267
  • 2017-03-17 13:32
  • 1010

Oracle 分页数据重复的问题

oracle分页采用三层嵌套+rownum分页时,如果有order by,就会有一个小坑,一不留神就掉进去了。 前置条件:分页脚本中存在order by 问题暴露:分页时好时坏 问题本质:ord...
  • yygg329405
  • yygg329405
  • 2016-12-22 10:41
  • 1043

Oracle常见SQL分页实现方案介绍

在Oracle中,用SQL来实现分页有很多种实现方式,但有些语句可能并不是很通用,只能用在一些特殊场景之中;以下介绍三种比较通用的实现方案;在以下各种实现中,ROWNUM是一个最核心的关键词,在查询时...
  • sfdev
  • sfdev
  • 2008-08-20 14:44
  • 18056
    个人资料
    • 访问:747911次
    • 积分:5460
    • 等级:
    • 排名:第5713名
    • 原创:96篇
    • 转载:1篇
    • 译文:0篇
    • 评论:291条
    博客专栏
    其他信息