错误现象：开发中发现一条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倒序扫描的，所以让我们产生了多余的错觉。
闽公网安备 35060202000074号