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

錯誤現象:開發中發現一條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 SERVER中對查詢結果隨機排序
譯:SQL SERVER中對查詢結果隨機排序 Randomly Sorting Query Results 查詢結果隨機排序 Q. How can I randomly sort query results?問:怎樣才能對查詢結果隨機排序?A. To randomly order rows, or to return x nu...查看完整版>>SQL SERVER中對查詢結果隨機排序
 
不同平台上My Sql的對比結果
My SQL測試結果 這個文件中包含了不同基准測試的結果。 測試結果後括號中的數字表示精確測試中執行的SQL命令的數目。一個測試可以有很多不同的參數,這裏只給出一個大致的樣子。請查看源碼以獲得更多的信息。 注意,...查看完整版>>不同平台上My Sql的對比結果
 
通過一條sql語句訪問不同數據庫服務器中的數據庫對象的方法
在我們做數據庫程序開發的時候,經常會遇到這種情況:需要將一個數據庫服務器中的數據導入到另一個數據庫服務器的表中。通常我們會使用這種方法:先把一個數據庫中的數據取出來放到某出,然後再把這些數據一條條插入...查看完整版>>通過一條sql語句訪問不同數據庫服務器中的數據庫對象的方法
 
PL/SQL學習之oracle排序系列二(上)
  上一期講了Oracle在什麽情況下需要排序,這次我們把注重力集中到與排序相關的幾個內存組件    PGA:    The Process Global Area,它是屬于私有內存段,段內的內容只對本進程可見,這不同于sga的共享內存...查看完整版>>PL/SQL學習之oracle排序系列二(上)
 
PL/SQL學習之oracle排序系列二(下)
  下一次我們將分享自動治理PGA    set constraint,alter session set constraint,有條件的unique限制    set constraint 子句是用來設置deferrable constraint的狀態的,可以設置constraint的狀態爲immed...查看完整版>>PL/SQL學習之oracle排序系列二(下)
 
PL/SQL學習之oracle排序系列二(上)
  上一期講了oracle在什麽情況下需要排序,這次我們把注意力集中到與排序相關的幾個內存組件  PGA:  The Process Global Area,它是屬于私有內存段,段內的內容只對本進程可見,這不同于sga的共享內存段。  ...查看完整版>>PL/SQL學習之oracle排序系列二(上)
 
PL/SQL學習之oracle排序系列二(下)
  下一次我們將分享自動管理PGA  set constraint,alter session set constraint,有條件的unique限制  set constraint 子句是用來設置deferrable constraint的狀態的,可以設置constraint的狀態爲immediate或de...查看完整版>>PL/SQL學習之oracle排序系列二(下)
 
谷歌欲利用+1按鈕對搜索結果重新排序
 北京時間8月30日晚間消息,谷歌正在嘗試將+1按鈕變爲一種“衆包”工具,從而實現對搜索結果的重新排序,同時更好地打擊互聯網垃圾信息。   谷歌這樣做並不令人驚訝,不過這一舉措仍將給谷歌搜索引擎增...查看完整版>>谷歌欲利用+1按鈕對搜索結果重新排序
 
自由是遙遠的,不自由卻是當下的。 它像水一樣消失在 愛情_男女情感
自由是遙遠的,不自由卻是當下的。 它像水一樣消失在 愛情_男女情感
  自由是遙遠的,不自由卻是當下的。 它像水一樣消失在水裏。生命的無常雖然會讓看似無辜的人喪生和失散,但用愛築起的心靈之橋是永遠不會倒塌的。一個男人應該像男人一樣有擔當,既然作出了選擇,就別再張望,藕斷...查看完整版>>自由是遙遠的,不自由卻是當下的。 它像水一樣消失在 愛情_男女情感
 
 
回到王朝網路移動版首頁