奇怪的SQL:排序方法不同但結果卻是一樣的

2024-07-21 02:41:55

字體：大中小

來源：轉載

供稿：網友

錯誤現象：開發中發現一條SQL出現問題，唯一的不同之處就是GMT_CREATE的排序方法不同，但得到的結果卻是一樣的，下面是這句SQL。

@>select rw ,id from
2 (select rownum rw, ID from CMM_MESSAGE t where t.TOPIC_ID=197 and
t.STATUS=0 order by t.topic_id,t.status,t.GMT_CREATE ) tt
3 where tt.ID=485;

RW ID
---------- ----------
11 485

@>
@>select rw ,id from
2 (select rownum rw, ID from CMM_MESSAGE t where t.TOPIC_ID=197 and
t.STATUS=0 order by t.topic_id,t.status,t.GMT_CREATE DESC) tt
3 where tt.ID=485;

RW ID
---------- ----------
11 485

嘗試著把中間的子查詢單獨拿出來運行。發現結果是正確的：

@>select rownum rw, ID from CMM_MESSAGE t where t.TOPIC_ID=197 and
t.STATUS=0 order by t.topic_id,t.status,t.GMT_CREATE desc ;

RW ID
---------- ----------
1 485
2 484
3 483
4 482
5 481
6 480
7 444
8 418
9 416
10 320
11 275

11 rows selected.

@>select rownum rw, ID from CMM_MESSAGE t where t.TOPIC_ID=197 and
t.STATUS=0 order by t.topic_id,t.status,t.GMT_CREATE;

RW ID
---------- ----------
1 275
2 320
3 416
4 418
5 444
6 480
7 481
8 482
9 483
10 484
11 485

我們可以發現這個結果很容易讓人產生錯覺，好像Oracle是有問題的，子查詢中的結果正確，但是整個語句是不正確的。

大家都知道ROWNUM是在取數據的時候就確定了的，ORDER BY是最后才執行的。這個語句本身的寫法就是錯誤的。那為什么子查詢中產生了正確的結果，而整個語句是錯誤的呢？讓我們再來看看執行計劃。

1* select rownum rw, ID,gmt_create from

CMM_MESSAGE t where t.TOPIC_ID=197 and t.STATUS=0 order by

t.topic_id,t.status,t.GMT_CREATE @>/

RW ID GMT_CREATE
---------- ---------- -------------------
1 275 2005-09-05 13:09:24
2 320 2005-09-05 14:34:02
3 416 2005-09-08 11:18:22
4 418 2005-09-08 11:24:15
5 444 2005-09-08 16:25:05
6 480 2005-09-09 19:46:01
7 481 2005-09-09 19:50:36
8 482 2005-09-09 19:50:47
9 483 2005-09-09 19:50:54
10 484 2005-09-09 19:51:15
11 485 2005-09-09 19:51:23
12 488 2005-09-12 11:14:25
13 489 2005-09-12 11:15:00
14 490 2005-09-12 11:15:23
15 491 2005-09-12 11:15:41

15 rows selected.

Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=2 Card=3 Bytes=45)
1 0 COUNT
2 1 INDEX (RANGE SCAN) OF 'CMM_MESSAGE_TPID_ST_CR_ID_IND' (N
ON-UNIQUE) (Cost=2 Card=3 Bytes=45)

發現走了INDEX掃描。

1* select rownum rw, ID,gmt_create from

CMM_MESSAGE t where t.TOPIC_ID=197 and t.STATUS=0 order by

t.topic_id,t.status,t.GMT_CREATE desc @>/

RW ID GMT_CREATE
---------- ---------- -------------------
1 491 2005-09-12 11:15:41
2 490 2005-09-12 11:15:23
3 489 2005-09-12 11:15:00
4 488 2005-09-12 11:14:25
5 485 2005-09-09 19:51:23
6 484 2005-09-09 19:51:15
7 483 2005-09-09 19:50:54
8 482 2005-09-09 19:50:47
9 481 2005-09-09 19:50:36
10 480 2005-09-09 19:46:01
11 444 2005-09-08 16:25:05
12 418 2005-09-08 11:24:15
13 416 2005-09-08 11:18:22
14 320 2005-09-05 14:34:02
15 275 2005-09-05 13:09:24

15 rows selected.

Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=2 Card=3 Bytes=45)
1 0 COUNT
2 1 INDEX (RANGE SCAN DESCENDING) OF 'CMM_MESSAGE_TPID_ST_CR
_ID_IND' (NON-UNIQUE) (Cost=2 Card=3 Bytes=45)

我們可以發現走了INDEX倒敘掃描，這樣就印證了我們的結論。我們再看

select">admintools@DEVE>select rw ,id from

2 (select rownum rw, ID from CMM_MESSAGE t where t.TOPIC_ID=197 and

3 t.STATUS=0 order by t.topic_id,t.status,t.GMT_CREATE DESC) tt
4 where tt.ID=485;

RW ID
---------- ----------
11 485

Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=6 Card=3 Bytes=78)
1 0 VIEW (Cost=6 Card=3 Bytes=78)
2 1 SORT (ORDER BY) (Cost=6 Card=3 Bytes=45)
3 2 COUNT
4 3 INDEX (RANGE SCAN) OF 'CMM_MESSAGE_TPID_ST_CR_ID_IND
' (NON-UNIQUE) (Cost=2 Card=3 Bytes=45)

當變成子查詢后，走的是INDEX正序掃描，然后再排序。這樣我們就知道了為什么查詢的結果總是一樣的原因了。

接下來，為了進一步驗證我們的觀點，我在子查詢中加入提示，讓他走FTS.結果如下：

1* select /*+full(t)*/ rownum rw, ID,gmt_create from CMM_MESSAGE t where
t.TOPIC_ID=197 and t.STATUS=0 order by t.topic_id,t.status,t.GMT_CREATE desc @>/

RW ID GMT_CREATE
---------- ---------- -------------------
15 491 2005-09-12 11:15:41
14 490 2005-09-12 11:15:23
13 489 2005-09-12 11:15:00
12 488 2005-09-12 11:14:25
11 485 2005-09-09 19:51:23
10 484 2005-09-09 19:51:15
9 483 2005-09-09 19:50:54
8 482 2005-09-09 19:50:47
7 481 2005-09-09 19:50:36
6 480 2005-09-09 19:46:01
5 444 2005-09-08 16:25:05
4 418 2005-09-08 11:24:15
3 416 2005-09-08 11:18:22
2 320 2005-09-05 14:34:02
1 275 2005-09-05 13:09:24

select /*+full(t)*/ rownum rw, ID,gmt_create from CMM_MESSAGE t where
2 t.TOPIC_ID=197 and t.STATUS=0 order by t.topic_id,t.status,t.GMT_CREATE;