Oracle 的sql陷阱（1）rownum和order by一起使用

最新推荐文章于 2024-03-26 09:30:52 发布
最新推荐文章于 2024-03-26 09:30:52 发布
阅读量956 收藏
点赞数
文章标签： 数据库
原文链接：https://yq.aliyun.com/articles/308494
版权
#Oracle 的sql陷阱（1）rownum和order by一起使用
rownum和order by一起使用可能会遇到取数不准确的问题，客户遇到了，我也测试了下，临时解决办法是使用嵌套查询，先排序出来结果再rownum，这种效率不高，当然最高效的是oracle优化器自己知道如何去取你要的数据，但是有时却不是100%准确，因为他不是一个100%的公式逻辑。
##客户遇到的问题
某天，客户突然发现取到的数据不对了，生成数据不对了，客户的情况如下:
以前一直正常执行，且有类似业务结构，仅表名不同的表也还取数正常。
###遇到问题的表T_WCPB现在的执行计划

SQL> SELECT DJH0  FROM ERP_CC.T_WCPB WHERE ROWNUM = 1 ORDER BY DJH0 desc;
DJH0

CB15081800001
Execution Plan
----------------------------------------------------------
Plan hash value: 678339681
------------------------------------------------------------------------------------
| Id  | Operation           | Name       | Rows  | Bytes | Cost (%CPU)| Time       |
------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT       |       |     1 |    14 |     3  (34)| 00:00:01 |
|   1 |  SORT ORDER BY           |       |     1 |    14 |     3  (34)| 00:00:01 |
|*  2 |   COUNT STOPKEY        |       |       |       |        |       |
|   3 |    INDEX FAST FULL SCAN| PK_T_WCPB |     1 |    14 |     2   (0)| 00:00:01 |
------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
   2 - filter(ROWNUM=1)
从执行计划中我们看到oracle通过主键索引快速扫描取到所需数据后“COUNT STOPKEY”，再进行的排序“SORT ORDER BY”，rownum排在前面，肯定不是我们想要的。但是话说回来，你想要的是排序后的rownum，如果你想要的是rownum<n 的结果再排序呢？oracle 到底该如何决定先取数还是先排序？

###匪夷所思
ROWNUM = 1 两个表取到的一个是该表的最大值，一个是最小值

SQL> SELECT DJH1  FROM ERP_CC.T_WXSCKD WHERE ROWNUM = 1;
DJH1
-------------
CK17081600121
Execution Plan
----------------------------------------------------------
Plan hash value: 577535013
--------------------------------------------------------------------------------
-----
| Id  | Operation          | Name        | Rows  | Bytes | Cost (%CPU)| Time
    |
--------------------------------------------------------------------------------
-----
|   0 | SELECT STATEMENT      |         |      1 |     14 |      2   (0)| 00:00
:01 |
|*  1 |  COUNT STOPKEY          |         |        |        |         |
    |
|   2 |   INDEX FAST FULL SCAN| PK_T_WXSCKD |      1 |     14 |      2   (0)| 00:00
:01 |
--------------------------------------------------------------------------------
-----
Predicate Information (identified by operation id):
---------------------------------------------------
   1 - filter(ROWNUM=1)
Statistics
----------------------------------------------------------
      0  recursive calls
      0  db block gets
      7  consistent gets
      0  physical reads
      0  redo size
    533  bytes sent via SQL*Net to client
    524  bytes received via SQL*Net from client
      2  SQL*Net roundtrips to/from client
      0  sorts (memory)
      0  sorts (disk)
      1  rows processed
SQL> SELECT DJH0  FROM ERP_CC.T_WCPB WHERE ROWNUM = 1;
DJH0
-------------
CB15081800001
Execution Plan
----------------------------------------------------------
Plan hash value: 44983662
--------------------------------------------------------------------------------
---
| Id  | Operation          | Name      | Rows  | Bytes | Cost (%CPU)| Time
  |
--------------------------------------------------------------------------------
---
|   0 | SELECT STATEMENT      |       |    1 |    14 |    2   (0)| 00:00:0
1 |
|*  1 |  COUNT STOPKEY          |       |      |      |           |
  |
|   2 |   INDEX FAST FULL SCAN| PK_T_WCPB |    1 |    14 |    2   (0)| 00:00:0
1 |
--------------------------------------------------------------------------------
---
Predicate Information (identified by operation id):
---------------------------------------------------
   1 - filter(ROWNUM=1)
Statistics
----------------------------------------------------------
      1  recursive calls
      0  db block gets
      7  consistent gets
      0  physical reads
      0  redo size
    533  bytes sent via SQL*Net to client
    524  bytes received via SQL*Net from client
      2  SQL*Net roundtrips to/from client
      0  sorts (memory)
      0  sorts (disk)
      1  rows processed
SQL>


###测试
测试表 tab1 ，开始没有设置主键，100记录
SYS@orcl1>select STUID from  tab1 where rownum=1;
     STUID
----------
     1
SYS@orcl1>select STUID from  tab1 where rownum=1 order by 1 desc;
     STUID
----------
     1
Execution Plan
----------------------------------------------------------
Plan hash value: 1612508337
--------------------------------------------------------------------------------
------------
| Id  | Operation        | Name           | Rows  | Bytes | Cost (%CPU)
| Time       |
--------------------------------------------------------------------------------
------------
|   0 | SELECT STATEMENT    |               |     1 |     3 |     3  (34)
| 00:00:01 |
|   1 |  SORT ORDER BY        |               |     1 |     3 |     3  (34)
| 00:00:01 |
|*  2 |   COUNT STOPKEY     |               |       |       |
|       |
|   3 |    TABLE ACCESS FULL| tab1 |     1 |     3 |     2   (0)
| 00:00:01 |
--------------------------------------------------------------------------------
------------
Predicate Information (identified by operation id):
---------------------------------------------------
   2 - filter(ROWNUM=1)
Statistics
----------------------------------------------------------
     19  recursive calls
      0  db block gets
     13  consistent gets
      0  physical reads
      0  redo size
    523  bytes sent via SQL*Net to client
    520  bytes received via SQL*Net from client
      2  SQL*Net roundtrips to/from client
      4  sorts (memory)
      0  sorts (disk)
      1  rows processed
SYS@orcl1>

没有主键，rownum=1 取到的是最小值；加上排序，取到的也是1，执行计划看出来，是先取数再排序的。

添加主键alter table tab1 add constraint pk_STUID  primary key(STUID);

此时执行sql
SYS@orcl1>select STUID from  tab1 where rownum=1;
     STUID
----------
     1

有主键，rownum=1 取到的仍是最小值；
SYS@orcl1>set autot on
SYS@orcl1>select STUID from  tab1 where rownum=1 order by 1 desc;
     STUID
----------
       100
Execution Plan
----------------------------------------------------------
Plan hash value: 1084965663
--------------------------------------------------------------------------------
--------
| Id  | Operation            | Name     | Rows  | Bytes | Cost (%CPU)| Ti
me     |
--------------------------------------------------------------------------------
--------
|   0 | SELECT STATEMENT        |           |     1 |     3 |     1     (0)| 00
:00:01 |
|*  1 |  COUNT STOPKEY            |           |       |       |        |
       |
|   2 |   INDEX FULL SCAN DESCENDING| PK_STUID |     1 |     3 |     1     (0)| 00
:00:01 |
--------------------------------------------------------------------------------
--------
Predicate Information (identified by operation id):
---------------------------------------------------
   1 - filter(ROWNUM=1)
Statistics
----------------------------------------------------------
      1  recursive calls
      0  db block gets
      5  consistent gets
      0  physical reads
      0  redo size
    523  bytes sent via SQL*Net to client
    520  bytes received via SQL*Net from client
      2  SQL*Net roundtrips to/from client
      1  sorts (memory)
      0  sorts (disk)
      1  rows processed
再加上排序，虽然rown=1一个条件是取到是最小值，但是先走的排序再取的rownum，这是我们预期的。


再来看看 rownum <50的情况
SYS@orcl1>select STUID from  tab1 where rownum<50 order by 1 desc;
     STUID
----------
       100
    99
    98
    97
    96
    95
    94
    93
    92
    91
    90
     STUID
----------
    89
    88
    87
    86
    85
    84
    83
    82
    81
    80
    79
     STUID
----------
    78
    77
    76
    75
    74
    73
    72
    71
    70
    69
    68
     STUID
----------
    67
    66
    65
    64
    63
    62
    61
    60
    59
    58
    57
     STUID
----------
    56
    55
    54
    53
    52
49 rows selected.
Execution Plan
----------------------------------------------------------
Plan hash value: 1084965663
--------------------------------------------------------------------------------
--------
| Id  | Operation            | Name     | Rows  | Bytes | Cost (%CPU)| Ti
me     |
--------------------------------------------------------------------------------
--------
|   0 | SELECT STATEMENT        |           |    49 |   147 |     1     (0)| 00
:00:01 |
|*  1 |  COUNT STOPKEY            |           |       |       |        |
       |
|   2 |   INDEX FULL SCAN DESCENDING| PK_STUID |    49 |   147 |     1     (0)| 00
:00:01 |
--------------------------------------------------------------------------------
--------
Predicate Information (identified by operation id):
---------------------------------------------------
   1 - filter(ROWNUM<50)
Statistics
----------------------------------------------------------
      1  recursive calls
      0  db block gets
      5  consistent gets
      0  physical reads
      0  redo size
       1379  bytes sent via SQL*Net to client
    553  bytes received via SQL*Net from client
      5  SQL*Net roundtrips to/from client
      0  sorts (memory)
      0  sorts (disk)
     49  rows processed
SYS@orcl1>
##对于客户情况，先对该情况做一个sql调整，使用嵌套查询
SYS@orcl1>select * from (select STUID from prvflat_stuinfo_part order by 1 desc ) where rownum=1;
     STUID
----------
       100
Execution Plan
----------------------------------------------------------
Plan hash value: 825904777
--------------------------------------------------------------------------------
---------
| Id  | Operation             | Name    | Rows    | Bytes | Cost (%CPU)| T
ime    |
--------------------------------------------------------------------------------
---------
|   0 | SELECT STATEMENT         |        |     1 |    13 |     1   (0)| 0
0:00:01 |
|*  1 |  COUNT STOPKEY             |        |    |    |         |
    |
|   2 |   VIEW                 |        |     1 |    13 |     1   (0)| 0
0:00:01 |
|   3 |    INDEX FULL SCAN DESCENDING| PK_STUID |   100 |   300 |     1   (0)| 0
0:00:01 |
--------------------------------------------------------------------------------
---------
Predicate Information (identified by operation id):
---------------------------------------------------
   1 - filter(ROWNUM=1)
Statistics
----------------------------------------------------------
      1  recursive calls
      0  db block gets
      1  consistent gets
      0  physical reads
      0  redo size
    523  bytes sent via SQL*Net to client
    520  bytes received via SQL*Net from client
      2  SQL*Net roundtrips to/from client
      0  sorts (memory)
      0  sorts (disk)
      1  rows processed
SYS@orcl1>


#这个是时候，oracle逻辑似乎非常明显，只要order的字段是主键，就是先排序再取rownum，真的是这样吗？测试看来是正确的，但是客户遇到的又是怎样情况？难道是个别表的bug？

weixin_33701251
关注
  • 0
    点赞
  • 踩
  • 0
    收藏
    觉得还不错? 一键收藏
  • 0
    评论
Oracle数据库rownum和row_number的不同点
09-10
在Oracle数据库中，`ROWNUM` 和 `ROW_NUMBER()` 是两个不同的概念，它们在处理结果集的排序和分页方面有着显著的区别。 首先，`ROWNUM` 是Oracle数据库中的一个伪列，它在查询执行的过程中动态生成。当查询执行时，...
Oracle中使用Rownum分页详细例子
09-10
同时，`ROWNUM`有一个限制，即不能直接与`BETWEEN`操作符一起使用，必须嵌套在子查询中，而`ROW_NUMBER()`则可以直接与`BETWEEN`配合，使查询更简洁。 总的来说，选择哪种方法取决于具体的需求和数据的特性。在处理...
SQL语句中ROWNUM和ORDER BY一起使用时的注意事项
【圆圆的世界】_CSDN专栏
04-24 2137
简要介绍下，是从一个问题中发现这个问题
SQL中rownum和order by的执行顺序的问题
极客神殿
04-18 7167
在一个SQL中，如果同时使用rownum和order by，会有一个先后顺序的问题。比如select id1,id2 from t_tablename where rownum<3 order by c_date desc ，这个语句会先从结果集中取前三条记录，再对这3天记录按日期排序。如果设计者向先对日期排序，再取前三天记录，那么就不会得到正确的结果。所以rownum和order by一起使用的时
oracle rownum与order by
ningning的专栏
10-16 1869
对于rownum来说它是oracle系统顺序分配为从查询返回的行的编号，返回的第一行分配的是1，第二行是2，依此类推，这个伪字段可以用于限制查询返回的总行数，而且rownum不能以任何表的名称作为前缀。
oracle rownum和order by的使用
c_afield的博客
07-19 1518
在oracle中，rownum常用来进行分页或查找指定行的数据，简答来说就是通过rownum关键字来生成一个伪列，这个伪列的内容就是查询出的数据的排序编号，这样就可以通过嵌套子查询来使用rownum进行查找指定行的数据了。简单来说，rownum生成伪列的操作在order by排序之前，如果想要先排序再编号的话，就必须在子查询先进行order by排序操作，然后在外层查询进行rownum操作，同样举两个例子。rownum经常配合其他操作一起使用，这里介绍一下和order by一起使用需要注意的问题。
order by 和 rownum = 1连用，得不到想要的结果
qq_37375667的博客
12-24 1387
select t.CREATE_TIME from table_name t where rownum = 1 order by t.CREATE_TIME desc; 正确的写法： select * from (select t.* from LMS_PROCESS_CHECK t order by t.CREATE_TIME desc) where rownum = 1;
【转】 解析Oracle的ROWNUM
weixin_34088838的博客
05-16 313
对于rownum来说它是Oracle系统顺序分配为从查询返回的行的编号，返回的第一行分配的是1，第二行是2，依此类推，这个伪字段可以用于限制查询返回的总行数，而且rownum不能以任何表的名称作为前缀。 举例说明：例如表：student(学生)表，表结构为：ID　　　    char(6)　　　　　 --学号name　　　　VARCHAR2(10)　　　--姓名 create table stud...
oracle_SQL中rowid与rownum的使用
11-07
2. **优化查询**：与`ORDER BY`一起使用时，先筛选`ROWNUM`再排序可以提高效率，避免全表排序。 3. **联合查询**：在多个表的连接查询中，通过`ROWNUM`过滤结果可以减少不必要的数据处理。 五、ROWID应用示例 `...
oracle中rownum和row_number()
12-16
row_number()over(partition by col1 order by col2)表示根据col1分组，在分组内部根据col2排序，而此函数计算的值就表示每组内部排序后的顺序编号（组内连续的唯一的）。 与rownum的区别在于：使用rownum进行排序的...
mysql oracle和sqlserver分页查询实例解析
09-09
本篇文章将详细解析MySQL、Oracle和SQL Server三种主流数据库系统的分页查询方法。 首先，MySQL的分页查询是最为简洁的。它使用`LIMIT`关键字配合`OFFSET`（或`FIRSTINDEX`）来实现分页。基本语法为`SELECT o.* ...
避坑，Oracle中rownum与order by的执行顺序
专注Java后端技术干货、项目源码总结分享，期待您的关注。
05-06 2657
「场景分析」 今天遇到这样一个场景：在列表展示数据的时候，使用rownum进行分页，并用order by 对某些字段进行排序。 于是写出了下面的模拟查询sql： selectrownumrn,t.*fromsys_depttorderbyt.dept_namedesc; 从上面的截图中可以发现： rownum完全是乱序的，因此最终以这种方式分页排序获取的数据列表不能达到预期的效果。 「原因分析」 若order by 的基准字段是表的PrimaryKey，则查...
Oracle中rownum与order by排序问题
aboboo520的博客
09-15 9782
今天发现一个bug，一个页面数据展示，既要求分页又要求以某一个字段降序排序，第一直觉就是使用rownum分页，用order by排序。写完代码后，本以为可以完全没有bug啦，但今天测试人员教过过去一看，竟然排序有问题。于是乎就发现了一个问题。以下截图说明问题：发现问题没得，rownum和seq完全是乱序的，所以这种方式分页排序肯定是有问题的，遂采用如下方式实现最终效果：
orcale分页语句查询没有结果显示，rownum和order by冲突
谜男专栏
11-15 736
order by，rownum
ORACLE中 使用order by与rownum排序造成分页查询结果不正确mybatis
最新发布
wsx9172的博客
03-26 270
使用mybatis分页查询oracle数据库时，同时使用了order by和rownum，多个分页结果相同oracle版本11.2.0.4.0。
rownum与order by 子句
萝卜&青菜的博客
09-11 1065
一、 rownum的特点 rownum的特点是在select语句查询过程中获得一条符合条件的数据行放入结果集合中时，oracle系统就会自动编一个号，即一个rownum值。这里要强调一点的是，一个结果集合里的任何时刻，都是有一个rownum值为1的数据行的，其后的数据行编号依次为2，3，。。。。以此类推的。如果那一条rownum值为1的数据行从结果集合里剔除了，跟在其后为2的数据行自...
Rownum与Order by
haiross的专栏
01-26 2923
Rownum与Order by rownum是数据表的一个虚列，是随着select获取到的数据集合而依次生成的数据项目。借助rownum，很多复杂的SQL需求都得以解决。但是，rownum绝不是一个数据列，使用它的时候一定要注意rownum的生成原理和特性。本篇就从rownum与order by结合，谈使用rownum的一个注意点。     简单的说，rownum是在使用selec
oracle 分页 rownum 和 order by
秋林风声 学习笔记
11-17 1660
Oracle的分页是通过rownum实现的。 rownum是一个伪列，是oracle系统自动为查询返回结果的每行分配的编号，第一行为1，第二行为2，以此类推。。。。 一个oracle分页，至少要包含三层（除非不用order by，暂时可以用2层实现），模板为 select temp2.* from(            select rownum num,temp1.* from(

“相关推荐”对你有帮助么？

  • 非常没帮助
  • 没帮助
  • 一般
  • 有帮助
  • 非常有帮助
提交
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值