`

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的使用技巧

    ROWNUM 是 Oracle 中的一种伪列，它可以根据返回记录生成一个序列化的数字。利用 ROWNUM，我们可以生产一些原先难以实现的结果输出，但是因为它是伪列的特殊性，在使用时需要注意一些事项，以免掉入“陷阱”。 特殊...

    oracle rownum 学习

    Oracle ROWNUM学习 Oracle ROWNUM是Oracle...ROWNUM是一个非常有用的伪字段，可以用于限制查询返回的总行数、实现分页、限制查询返回的第一行记录等。但是，需要注意ROWNUM的限制和使用子查询来解决一些查询问题。

    ORACLE 中ROWNUM用法总结

    在Oracle数据库中，`ROWNUM`是一个非常有用的伪列，用于限制查询结果的行数，尤其是在处理大数据量或进行分页查询时。然而，`ROWNUM`的使用并不直观，尤其是当涉及到比较运算符（如`&gt;`, `&gt;=`, `=`等）时，容易引发...

    oracle rownum 的使用 和sqlserver有区别的！

    ### Oracle ROWNUM 使用详解与 SQL Server 区别 在数据库操作中，ROWNUM 是一个非常重要的伪列，主要用于返回查询结果的行序号。它在 Oracle 数据库中有着广泛的应用场景，尤其是在需要对查询结果进行分页显示、...

    对于 Oracle 的 rownum 问题

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

    Oracle分页(limit方式的运用)

    通过以上介绍，我们可以看到在Oracle中使用`OFFSET`与`FETCH FIRST`结合`ROWNUM`可以有效地实现分页查询。这种方法不仅简单易用，而且能够满足大多数场景下的需求。当然，在实际应用中还需要根据具体情况进行调整和...

    oracle的分页查询

    本文将讲解 Oracle 中的分页查询，包括使用 ROWNUM 伪列和 ORDER BY 子句对查询结果进行排序和分页。 一、使用 ROWNUM 伪列实现分页查询 在 Oracle 中，ROWNUP 伪列是一个特殊的列，可以用来实现分页查询。例如，...

    Oracle的分页查询语句 Oracle分页的存储过程

    Oracle数据库在处理大数据量查询时，分页查询是一种常见的优化策略，目的是限制返回结果集的大小，加快查询速度，提供更好的用户体验。本文将详细探讨Oracle的分页查询语句及其存储过程。 首先，Oracle的分页查询...

    Oracle的SQL分页实践

    使用JDBC时，可以结合LIMIT和OFFSET子句（Oracle不支持LIMIT，但可以通过ROWNUM模拟）来实现分页，而在Hibernate中，可以使用Criteria API或HQL的`setFirstResult`和`setMaxResults`方法来实现相同的功能。...

    SSH+ORACLE好用分页

    在Oracle数据库中，可以使用内置的SQL分页函数，如ROWNUM或ROW_NUMBER()，结合子查询和临时表，实现高效的分页查询。另外，Oracle还支持索引优化，通过创建索引可以显著提高分页查询的性能。 总的来说，"SSH+ORACLE...

    oracle rownum 使用技术.pdf

    ### Oracle ROWNUM 使用技术详解 #### 一、ROWNUM简介与特性 ROWNUM是Oracle数据库中的一个特殊列，用于为查询结果集中的每一行分配一个唯一的序号。这一功能非常强大，尤其在处理诸如“获取前N条记录”或“实现...

    Oracle 3种分页SQL方法比较

    总结来说，ROWNUM是最基础的分页方式，适合小数据量或无需排序的情况；ROWNUM结合子查询能保证有序分页，但效率较低；Oracle 12c的FETCH NEXT WITH OFFSET是更现代的分页方式，兼顾可读性和性能。在实际应用中，应...

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

    Oracle数据库中的Rownum是一个非常重要的特性，尤其在处理大量数据和实现分页显示时显得极其关键。Rownum是一个伪列，它并不是数据库表中实际存在的列，而是Oracle在执行查询时动态生成的，用来表示查询结果集中每一...

    oracle 分页完整代码

    在Oracle数据库中，分页查询是一项非常常见...总之，Oracle的分页可以通过ROWNUM伪列配合Java的JDBC实现，也可以借助ORM框架进行更高级的封装。理解这些概念和技巧，对于开发过程中处理大规模数据的分页查询至关重要。

    Oracle真分页。源码

    传统的ROWNUM分页方式在某些场景下可能无法满足需求，特别是在数据量过大或需要跨多行进行排序时，会出现效率低下甚至错误的结果。因此，“Oracle真分页”概念应运而生，它旨在提供一种更高效、更准确的分页解决方案...

    oracle sql分页语句

    在Oracle数据库系统中，实现分页查询通常使用ROWNUM伪列或者结合RANK()、DENSE_RANK()、ROW_NUMBER()等窗口函数。这里我们将详细探讨这些方法及其应用场景。 首先，ROWNUM是一个特殊的伪列，它在查询执行时为每一行...

    Oracle&JSP分页和Oracle分页

    总结来说，Oracle和JSP结合实现分页涉及以下几个步骤：在Oracle中构造分页查询，使用JDBC在Servlet中执行查询，然后在JSP页面中展示数据。理解并熟练掌握这些步骤对于开发高效、用户友好的数据驱动网站至关重要。在...

    java web与Oracle数据的分页功能

    2. **构造SQL查询语句**：Oracle数据库支持使用`ROWNUM`进行分页查询。基本的SQL模板为： ```sql SELECT * FROM (SELECT t.*, ROWNUM rnum FROM (YOUR_SELECT_QUERY) t WHERE ROWNUM ) WHERE rnum &gt;= :startRow `...

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