奇怪的SQL:排序方法不同但结果却是一样的

浏览：46日期：2023-08-05

错误现象：开发中发现一条SQL出现问题，唯一的不同之处就是GMT_CREATE的排序方法不同，但得到的结果却是一样的，下面是这句SQL。

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

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

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

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

尝试着把中间的子查询单独拿出来运行。发现结果是正确的：

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

RW ID---------- ----------1 4852 4843 4834 4825 4816 4807 4448 4189 41610 32011 275

11 rows selected.

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

RW ID---------- ----------1 2752 3203 4164 4185 4446 4807 4818 4829 48310 48411 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:242 320 2005-09-05 14:34:023 416 2005-09-08 11:18:224 418 2005-09-08 11:24:155 444 2005-09-08 16:25:056 480 2005-09-09 19:46:017 481 2005-09-09 19:50:368 482 2005-09-09 19:50:479 483 2005-09-09 19:50:5410 484 2005-09-09 19:51:1511 485 2005-09-09 19:51:2312 488 2005-09-12 11:14:2513 489 2005-09-12 11:15:0014 490 2005-09-12 11:15:2315 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 COUNT2 1 INDEX (RANGE SCAN) OF 'CMM_MESSAGE_TPID_ST_CR_ID_IND' (NON-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:412 490 2005-09-12 11:15:233 489 2005-09-12 11:15:004 488 2005-09-12 11:14:255 485 2005-09-09 19:51:236 484 2005-09-09 19:51:157 483 2005-09-09 19:50:548 482 2005-09-09 19:50:479 481 2005-09-09 19:50:3610 480 2005-09-09 19:46:0111 444 2005-09-08 16:25:0512 418 2005-09-08 11:24:1513 416 2005-09-08 11:18:2214 320 2005-09-05 14:34:0215 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 COUNT2 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) tt4 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 COUNT4 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 wheret.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:4114 490 2005-09-12 11:15:2313 489 2005-09-12 11:15:0012 488 2005-09-12 11:14:2511 485 2005-09-09 19:51:2310 484 2005-09-09 19:51:159 483 2005-09-09 19:50:548 482 2005-09-09 19:50:477 481 2005-09-09 19:50:366 480 2005-09-09 19:46:015 444 2005-09-08 16:25:054 418 2005-09-08 11:24:153 416 2005-09-08 11:18:222 320 2005-09-05 14:34:021 275 2005-09-05 13:09:24

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

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

上一条：对系统默认的约束名和索引名进行重命名下一条：用VB6读写数据库中的图片

相关文章：
1. C语言强制类型转换怎么做？2. 教你如何用Eclipse创建一个Maven项目3. 聊聊在Servlet中怎么上传文件4. 详解大数据处理引擎Flink内存管理5. 分析ABA问题的本质及其解决办法6. Flink支持哪些数据类型?7. 浅谈ArrayList和LinkedList到底谁更快8. 浅析NIO系列之TCP9. 小白必看toString(),String.valueOf,(String)强转10. 详解ArrayList的扩容机制