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

#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

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

   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;

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  recursive calls

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;

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)

|*  2 |   COUNT STOPKEY     |               |       |       |

|       |

|   3 |    TABLE ACCESS FULL| tab1 |     1 |     3 |     2   (0)

     19  recursive calls

     13  consistent gets

    523  bytes sent via SQL*Net to client

    520  bytes received via SQL*Net from client

      4  sorts (memory)

SYS@orcl1>

沒有主鍵，rownum=1 取到的是最小值；加上排序，取到的也是1，執行計劃看出來，是先取數再排序的。

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

此時執行sql

有主鍵，rownum=1 取到的仍是最小值；

SYS@orcl1>set autot on

       100

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

      5  consistent gets

      1  sorts (memory)

再加上排序，雖然rown=1一個條件是取到是最小值，但是先走的排序再取的rownum，這是我們預期的。

再來看看 rownum <50的情況

SYS@orcl1>select STUID from  tab1 where rownum<50 order by 1 desc;

    99

    98

    97

    96

    95

    94

    93

    92

    91

    90

    89

    88

    87

    86

    85

    84

    83

    82

    81

    80

    79

    78

    77

    76

    75

    74

    73

    72

    71

    70

    69

    68

    67

    66

    65

    64

    63

    62

    61

    60

    59

    58

    57

    56

    55

    54

    53

    52

49 rows selected.

|   0 | SELECT STATEMENT        |           |    49 |   147 |     1     (0)| 00

|   2 |   INDEX FULL SCAN DESCENDING| PK_STUID |    49 |   147 |     1     (0)| 00

   1 - filter(ROWNUM<50)

       1379  bytes sent via SQL*Net to client

    553  bytes received via SQL*Net from client

      5  SQL*Net roundtrips to/from client

     49  rows processed

##對于客戶情況，先對該情況做一個sql調整，使用嵌套查詢

SYS@orcl1>select * from (select STUID from prvflat_stuinfo_part order by 1 desc ) where rownum=1;

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

|   3 |    INDEX FULL SCAN DESCENDING| PK_STUID |   100 |   300 |     1   (0)| 0

      1  consistent gets

#這個是時候，oracle邏輯似乎非常明顯，隻要order的字段是主鍵，就是先排序再取rownum，真的是這樣嗎？測試看來是正确的，但是客戶遇到的又是怎樣情況？難道是個别表的bug？