#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？