奇怪的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;

至此，大家可以发现485总是排在第11位，这样就验证了ROWNUM是在ORDER BY之前就取得了。前面有一个查询是走INDEX倒序扫描的，所以让我们产生了多余的错觉。