`

Oracle ROWNUM分页总结

 
阅读更多 
首先ROWNUM只适用于小于或小于等于，如果进行等于判断，那么只能等于1，不能进行大于的比较。
ROWNUM是oracle系统顺序分配为从查询返回的行的编号，返回的第一行分配的是1，第二行是2，依此类推。
ROWNUM总是从1开始，不管当前的记录是否满足查询结果，ROWNUM返回的值都是1，如果这条记录的值最终满足所有的条件，
那么ROWNUM会递加，下一条记录的ROWNUM会返回2，否则下一条记录的ROWNUM仍然返回1。

理解了这一点，就清楚为什么一般的ROWNUM大于某个值或等于某个不为1的值是无法返回结果的，
因此对于每条记录的ROWNUM都是1，而ROWNUM为1不满足查询的结果，所以下一条记录的ROWNUM不会递增，
仍然是1，因此所有的记录都不满足条件。

DROP TABLE TT3;
CREATE TABLE TT3 AS SELECT * FROM DBA_OBJECTS WHERE ROWNUM <10;
--ROWNUM < N是永远成立的
SELECT OWNER,OBJECT_NAME,ROWNUM FROM TT3 WHERE ROWNUM <10;
OWNER                          OBJECT_NAME                     ROWNUM
------------------------------ ---------------------------------------------------
SYS                            ICOL$                           1
SYS                            I_USER1                         2
SYS                            CON$                            3
SYS                            UNDO$                           4
SYS                            C_COBJ#                         5
SYS                            I_OBJ#                          6
SYS                            PROXY_ROLE_DATA$                7
SYS                            I_IND1                          8
SYS                            I_CDEF2                         9
--ROWNUM > N是不成立的
SELECT OWNER,OBJECT_NAME,ROWNUM FROM TT3 WHERE ROWNUM > 1;

OWNER                          OBJECT_NAME                     ROWNUM
------------------------------ ---------------------------------------------------

--可以看出ROWNUM并不是按照排序后的结果然后分配ROWNUM,而是一开始取10条记录,再排序
SQL> select * from
  2  (
  3  select to_number(object_id),rownum rn from t2000 order by to_number(object_id)
  4  ) where rn <= 10;

TO_NUMBER(OBJECT_ID)         RN
-------------------- ----------
                   3          6
                  15          4
                  20          1
                  25          7
                  28          3
                  29          5
                  40         10
                  41          8
                  46          2
                  54          9    
--解决上面的问题，需要采用下面的sql,先排完序,外层再过滤条数

select *
  from (select A.*, rownum rn
          from (select to_number(object_id)
                  from t2000
                 order by to_number(object_id)) A)
 where rn <= 10;

--通过执行计划,看分页排序的效率
--以下写法都保证了页面分页记录不重复

--第一种
SQL> SELECT *
  2    FROM (SELECT A.*, ROWNUM RN
  3            FROM (SELECT TO_NUMBER(OBJECT_ID)
  4                    FROM T2000
  5                   ORDER BY TO_NUMBER(OBJECT_ID)) A
  6           WHERE ROWNUM <= 10)
  7   WHERE RN >= 2;

已选择9行。


执行计划
----------------------------------------------------------
Plan hash value: 1198689731

------------------------------------------------------------------------------------------
| Id  | Operation                | Name  | Rows  | Bytes |TempSpc| Cost (%CPU)| Time     |
------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT         |       |    10 |   260 |       |  1056   (1)| 00:00:13 |
|*  1 |  VIEW                    |       |    10 |   260 |       |  1056   (1)| 00:00:13 |
|*  2 |   COUNT STOPKEY          |       |       |       |       |            |          |
|   3 |    VIEW                  |       |   109K|  1395K|       |  1056   (1)| 00:00:13 |
|*  4 |     SORT ORDER BY STOPKEY|       |   109K|  1395K|  2168K|  1056   (1)| 00:00:13 |
|   5 |      TABLE ACCESS FULL   | T2000 |   109K|  1395K|       |   528   (1)| 00:00:07 |

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

   1 - filter("RN">=2)
   2 - filter(ROWNUM<=10)
   4 - filter(ROWNUM<=10)
--第二种

SQL> SELECT *
  2    FROM (SELECT A.*, ROWNUM RN
  3            FROM (SELECT TO_NUMBER(OBJECT_ID)
  4                    FROM T2000
  5                   ORDER BY TO_NUMBER(OBJECT_ID)) A)
  6   WHERE RN BETWEEN 1 AND 10;

已选择10行。


执行计划
----------------------------------------------------------
Plan hash value: 1961136492

---------------------------------------------------------------------------------------
| Id  | Operation             | Name  | Rows  | Bytes |TempSpc| Cost (%CPU)| Time     |
---------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT      |       |   109K|  2791K|       |  1056   (1)| 00:00:13 |
|*  1 |  VIEW                 |       |   109K|  2791K|       |  1056   (1)| 00:00:13 |
|   2 |   COUNT               |       |       |       |       |            |          |
|   3 |    VIEW               |       |   109K|  1395K|       |  1056   (1)| 00:00:13 |
|   4 |     SORT ORDER BY     |       |   109K|  1395K|  2168K|  1056   (1)| 00:00:13 |
|   5 |      TABLE ACCESS FULL| T2000 |   109K|  1395K|       |   528   (1)| 00:00:07 |
---------------------------------------------------------------------------------------

--排序列不唯一所带来的问题
如果用来排序的列不唯一，也就是存在值相等的行，可能会造成第一次在前10条返回记录中，
某行数据出现了，而第二次在11到第20条记录中，某行数据又出现了。一条数据重复出现两次，
就必然意味着有数据在两次查询中都不会出现。
其实造成这个问题的原因很简单，是由于排序列不唯一造成的。Oracle这里使用的排序算法不具有稳定性，
也就是说，对于键值相等的数据，这种算法完成排序后，不保证这些键值相等的数据保持排序前的顺序。
解决这个问题其实也很简单。有两种方法可以考虑。
1)在使用不唯一的字段排序时，后面跟一个唯一的字段。
一般在排序字段后面跟一个主键就可以了，如果表不存在主键，跟ROWID也可以。这种方法最简单，且对性能的影响最小。
2)另一种方法就是使用前面给出过多次的BETWEEN AND的方法。
这种方式由于采用表数据的全排序，每次只取全排序中的某一部分数据，因此不会出现上面提到的重复数据问题。
但是正是由于使用了全排序，而且ROWNUM信息无法推到查询内部，导致这种写法的执行效率很低

--以下为实验例子
DROP TABLE TT5;
CREATE TABLE  TT5 AS SELECT * FROM DBA_OBJECTS WHERE ROWNUM <10;
INSERT INTO TT5  SELECT * FROM TT5;
INSERT INTO TT5  SELECT * FROM TT5;
INSERT INTO TT5  SELECT * FROM TT5;
COMMIT;
--共产生了72条数据
admin@ORCL> SELECT COUNT(*) FROM TT5;

  COUNT(*)
----------
        72

--查询是否有重复的ROWID，若有则代表重复
SELECT RD FROM 
(
SELECT RD FROM 
(SELECT ROWNUM RN,ROWID RD, TT.* FROM (SELECT * FROM TT5 ORDER BY OBJECT_NAME)TT WHERE ROWNUM <= 10) A
 WHERE A.RN >= 1
UNION ALL
SELECT RD FROM 
(SELECT ROWNUM RN,ROWID RD, TT.* FROM (SELECT * FROM TT5 ORDER BY OBJECT_NAME)TT WHERE ROWNUM <= 20) A
 WHERE A.RN >= 11
UNION ALL
SELECT RD FROM 
(SELECT ROWNUM RN,ROWID RD, TT.* FROM (SELECT * FROM TT5 ORDER BY OBJECT_NAME)TT WHERE ROWNUM <= 30) A
 WHERE A.RN >= 21
UNION ALL
SELECT RD FROM 
(SELECT ROWNUM RN,ROWID RD, TT.* FROM (SELECT * FROM TT5 ORDER BY OBJECT_NAME)TT WHERE ROWNUM <= 40) A
 WHERE A.RN >= 31
 UNION ALL
SELECT RD FROM 
(SELECT ROWNUM RN,ROWID RD, TT.* FROM (SELECT * FROM TT5 ORDER BY OBJECT_NAME)TT WHERE ROWNUM <= 50) A
 WHERE A.RN >= 41
 UNION ALL
SELECT RD FROM 
(SELECT ROWNUM RN,ROWID RD, TT.* FROM (SELECT * FROM TT5 ORDER BY OBJECT_NAME)TT WHERE ROWNUM <= 60) A
 WHERE A.RN >= 51
UNION ALL
SELECT RD FROM 
(SELECT ROWNUM RN,ROWID RD, TT.* FROM (SELECT * FROM TT5 ORDER BY OBJECT_NAME)TT WHERE ROWNUM <= 71) A
 WHERE A.RN >= 61
) A GROUP BY RD HAVING COUNT(*)>1;

RD
------------------
AAAPB2AAEAAAFS1AAt
AAAPB2AAEAAAFS1AAu
AAAPB2AAEAAAFS1AAf
AAAPB2AAEAAAFS1AA1
AAAPB2AAEAAAFS1AAh

--下面的方式不会产生重复值，因为数据的取值顺序不会发生变化
SELECT RD
  FROM (SELECT RD
          FROM (SELECT ROWNUM RN, ROWID RD, TT.*
                  FROM (SELECT * FROM TT5 ORDER BY OBJECT_NAME) TT) A
         WHERE A.RN BETWEEN 1 AND 10
        UNION ALL
        SELECT RD
          FROM (SELECT ROWNUM RN, ROWID RD, TT.*
                  FROM (SELECT * FROM TT5 ORDER BY OBJECT_NAME) TT) A
         WHERE A.RN BETWEEN 11 AND 20
        UNION ALL
        SELECT RD
          FROM (SELECT ROWNUM RN, ROWID RD, TT.*
                  FROM (SELECT * FROM TT5 ORDER BY OBJECT_NAME) TT) A
         WHERE A.RN BETWEEN 21 AND 30
        UNION ALL
        SELECT RD
          FROM (SELECT ROWNUM RN, ROWID RD, TT.*
                  FROM (SELECT * FROM TT5 ORDER BY OBJECT_NAME) TT) A
         WHERE A.RN BETWEEN 31 AND 40
        UNION ALL
        SELECT RD
          FROM (SELECT ROWNUM RN, ROWID RD, TT.*
                  FROM (SELECT * FROM TT5 ORDER BY OBJECT_NAME) TT) A
         WHERE A.RN BETWEEN 41 AND 50
        UNION ALL
        SELECT RD
          FROM (SELECT ROWNUM RN, ROWID RD, TT.*
                  FROM (SELECT * FROM TT5 ORDER BY OBJECT_NAME) TT) A
         WHERE A.RN BETWEEN 51 AND 60
        UNION ALL
        SELECT RD
          FROM (SELECT ROWNUM RN, ROWID RD, TT.*
                  FROM (SELECT * FROM TT5 ORDER BY OBJECT_NAME) TT) A
         WHERE A.RN BETWEEN 61 AND 70) A
 GROUP BY RD
HAVING COUNT(*) > 1;

 

分享到：
| Oracle JOB实现多线程插入
评论
发表评论

您还没有登录,请您登录后再发表评论

相关推荐

    ROWNUM的使用技巧

    oracle 分页查询 使用ROWNUM技巧及陷阱

    oracle rownum 学习

    内涵oracle rownum的详细讲解。

    Oracle中使用Rownum分页详细例子

    主要介绍了Oracle中使用Rownum分页详细例子,本文将分别展示使用rownum伪列和row_number()分析函数来完成Oracle数据分页操作的具体使用方法,需要的朋友可以参考下

    oracle rownum 使用技术.pdf

    oracle rownum 使用小技巧 里面包含分页

    对于 Oracle 的 rownum 问题

    对于 Oracle 的 rownum 问题，很多资料都说不支持&gt;,&gt;=,=,between...and，只能用以上符号(&lt;、、!=)，并非说用&gt;,&gt;=,=,between..and 时会提示SQL语法错误，而是经常是查不出一条记录来，还会出现似乎是莫名其妙的结果来...

    Oracle Rownum的使用与JSP分页显示的实现.pdf

    Oracle Rownum的使用与JSP分页显示的实现.pdf

    oracle 使用rownum的三种分页方式

    rownum是Oracle数据库中的一个特有关键字，返回的是一个数字代表记录的行号。这篇文章主要介绍了oracle 使用rownum的三种分页方式,需要的朋友可以参考下

    oracle的分页查询

    使用数据库时有时我们会需要分页查询，但是在oracle中使用查询条件时又不可以使用大于号&gt;。

    oracle的rownum深入解析

    本人最近在使用oracle的rownum实现分页显示的时候，对rownum做了进一步的分析和研究。现归纳如下，希望能给大家带来收获。 对于rownum来说它是oracle系统顺序分配为从查询返回的行的编号，返回的第一行分配的是1，第...

    asp.net使用oracle分页存储过程查询数据

    写好oracle的连接字符串和查询语句，调用程序中的方法可以很方便的实现分页功能。该方法中，将参数连接字符串，查询的sql语句，指定每页显示多少行，调用成功后，会返回页数，行数，还有查询的结果数据集。 使用...

    oracle 分页语句

    select * from (select a.*,rownum rn from (select * from tablename) a where rownum) where rn&gt;2

    Oracle数据库分页的集中方法(三种方法)

    在 做项目中用到了分页,下面说一下oracle分页的方法; 采用伪列 rownum  查询前10条记录 [sql] select * from t_user t where ROWNUM （大于1的数值）、&gt;=（大于或等于1的数值）、=（大于或等于1的数值），否则无...

    Oracle分页查询性能优化代码详解

    对于数据库中表的数据的 Web 显示，如果没有展示顺序的需要，而且因为满足条件的记录如此之多，就不得不对数据进行分页处理。常常用户并不是对所有数据都感兴趣的，或者大部分情况下，他们只看前几页。 通常有以下...

    oracle分页操作

    ROWNUM 和RN &gt;= 21控制分页查询的每页的范围。 上面给出的这个分页查询语句，在大多数情况拥有较高的效率。分页的目的就是控制输出结果集大小，将结果尽快的返回。在上面的分页查询语句中，这种考虑主要体现在WHERE...

    Oracle中使用伪列rownum实现分页查询

    伪列rownum rownum是查询返回的结果集中行的序号，可以使用它来限制查询返回的行数 从emp表中查询薪水排在前三位的记录 select * from( select * from emp order by sal desc ) where rownum&lt;=3 结果如下： ...

    Oracle中实现分页查询的SQL命令

    Oracle中实现分页查询的SQL命令 //curPage是当前页面，pageCount是每页显示行数 //rownum是伪列，相当于表中每一列的标识列（可以理解为行号）,需要显式的提取出来并取一个别名

    Oracle实现分页查询的SQL语法汇总

    本文实例汇总了Oracle实现分页查询的SQL语法，整理给大家供大家参考之用，详情如下： 1.无ORDER BY排序的写法。(效率最高) 经过测试，此方法成本最低，只嵌套一层，速度最快！即使查询的数据量再大，也几乎不受影响...

    oracle一条sql语句分页

    oracle 中没有像其他数据库那样用top来实现分页，但oracle提供了rownum 通过它也可以实现分页方法。

Magicbox
Global site tag (gtag.js) - Google Analytics