| 订阅 | 在线投稿
分享
 
 
 

奇怪的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- 王朝网络 版权所有