| 導購 | 订阅 | 在线投稿
分享
 
 
 

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

來源:互聯網  2008-06-01 02:12:09  評論

錯誤現象:開發中發現一條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;

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

16 513 2005-09-13 11:37:31

至此,大家可以發現485總是排在第11位,這樣就驗證了ROWNUM是在ORDER BY之前就取得了。前面有一個查詢是走INDEX倒序掃描的,所以讓我們産生了多余的錯覺。

錯誤現象:開發中發現一條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; 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 16 513 2005-09-13 11:37:31 至此,大家可以發現485總是排在第11位,這樣就驗證了ROWNUM是在ORDER BY之前就取得了。前面有一個查詢是走INDEX倒序掃描的,所以讓我們産生了多余的錯覺。
󰈣󰈤
王朝萬家燈火計劃
期待原創作者加盟
 
 
 
>>返回首頁<<
 
 
 
 
 熱帖排行
 
 
 
靜靜地坐在廢墟上,四周的荒凉一望無際,忽然覺得,淒涼也很美
© 2005- 王朝網路 版權所有