ROWNUM与ORDER BY联用时对结果集的影响

john_77

不错，收藏

cemail

学习了，太细了，佩服

淡若煙飛

    当ORDER BY字段为PRIMARY KEY时, 此时Oracle是先ORDER BY再对记录ROWNUM

为什么为这样呢？
呵呵，这里有个小"关子"

请看测试:
SQL> select * from a;

        ID
----------
         1
        10
         2
        20
         5
        50

6 rows selected.

SQL> alter table a add constraint con_t_pk primary key(id);

Table altered.

SQL> select * from a;

        ID
----------
         1
         2
         5
        10
        20
        50

6 rows selected.

SQL>
--发现加了主键后查询结果就自动排序了

SQL> set autot traceonly
SQL> select * from a;

6 rows selected.


Execution Plan
----------------------------------------------------------
Plan hash value: 1857226538

-----------------------------------------------------------------------------
| Id  | Operation        | Name     | Rows  | Bytes | Cost (%CPU)| Time     |
-----------------------------------------------------------------------------
|   0 | SELECT STATEMENT |          |     6 |    78 |     1   (0)| 00:00:01 |
|   1 |  INDEX FULL SCAN | CON_T_PK |     6 |    78 |     1   (0)| 00:00:01 |
-----------------------------------------------------------------------------

Note
-----
   - dynamic sampling used for this statement


Statistics
----------------------------------------------------------
          0  recursive calls
          0  db block gets
          2  consistent gets
          0  physical reads
          0  redo size
        476  bytes sent via SQL*Net to client
        385  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          6  rows processed

SQL>
--原来是走了INDEX FULL SCAN

SQL> select * from a where rownum<4 order by id;


Execution Plan
----------------------------------------------------------
Plan hash value: 1925392830

-----------------------------------------------------------------------------
| Id  | Operation        | Name     | Rows  | Bytes | Cost (%CPU)| Time     |
-----------------------------------------------------------------------------
|   0 | SELECT STATEMENT |          |     3 |    39 |     1   (0)| 00:00:01 |
|*  1 |  COUNT STOPKEY   |          |       |       |            |          |
|   2 |   INDEX FULL SCAN| CON_T_PK |     6 |    78 |     1   (0)| 00:00:01 |
-----------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - filter(ROWNUM<4)

Note
-----
   - dynamic sampling used for this statement


Statistics
----------------------------------------------------------
          7  recursive calls
          0  db block gets
         20  consistent gets
          0  physical reads
          0  redo size
        452  bytes sent via SQL*Net to client
        385  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          3  rows processed

SQL>
--那么为什么select * from a where rownum<4 order by id;是先order by 主键再选取rownum<4的原因就一目了然了！

--即使有主键,如果不使用INDEX FULL SCAN会如何呢？
SQL> select /*+full(a)*/ * from a where rownum<4 order by id;


Execution Plan
----------------------------------------------------------
Plan hash value: 418636105

----------------------------------------------------------------------------
| Id  | Operation           | Name | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------
|   0 | SELECT STATEMENT    |      |     3 |    39 |     4  (25)| 00:00:01 |
|   1 |  SORT ORDER BY      |      |     3 |    39 |     4  (25)| 00:00:01 |
|*  2 |   COUNT STOPKEY     |      |       |       |            |          |
|   3 |    TABLE ACCESS FULL| A    |     6 |    78 |     3   (0)| 00:00:01 |
----------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - filter(ROWNUM<4)

Note
-----
   - dynamic sampling used for this statement


Statistics
----------------------------------------------------------
          7  recursive calls
          0  db block gets
         22  consistent gets
          0  physical reads
          0  redo size
        452  bytes sent via SQL*Net to client
        385  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          1  sorts (memory)
          0  sorts (disk)
          3  rows processed

SQL> set autotrace off
SQL> select /*+full(a)*/ * from a where rownum<4 order by id;

        ID
----------
         1
         2
        10

SQL>
--发现走TABLE ACCESS FULL时结果还是先取rownum<4再order by id

所以“当ORDER BY字段为PRIMARY KEY时, 此时Oracle是先ORDER BY再对记录ROWNUM”这句话有些欠妥，
至于为什么有这个现象的原因,想必大家应该都比较清楚了！

淡若煙飛

原帖由 zhangfengh 于 2008-3-28 14:59 发表
把你的a表再加几个字段，看看select * from a时是不是还用INDEX FULL SCAN

？

不是，但select * from a where rownum<4 order by id是走INDEX FULL SCAN的，呵呵！
这个有关系么？我也没有一定会走INDEX FULL SCAN呀.

我想说的是:先执行order by 还是 rownum< 不是一定的，应该根据执行计划而定！

DragonBill

原帖由 zhangfengh 于 2008-3-28 15:16 发表
因为你的a表只有id这一个字段，而且主键就建立在它上面，所以select *时才会用到INDEX FULL SCAN，在索引中能够得到所有的结果，如果加几个字段，索引中得不到所有的结果，就不一定用INDEX FULL SCAN了

pingping1982917

大家可以看一下TOM的高效设计，其中有几页是专门讲rownum和order by 的.

wangfans

1，对LZ的细致发现研究很支持
2，其实也没这么玄，查看下执行计划就知道为什么会出现这种情况了

棉花糖ONE

http://www.hellodba.com/Doc/Oracle_ROWNUM.htm
转一篇好文

谁

只因为整理辛苦就可以加精华呀。。。
翻了翻，以前精华也不多，这个版人气也很足，加精理由似乎有些牵强

以上纯属个人看法，偶尊重版主的决定

btw：楼主辛苦

haibo_li

支持。辛苦LZ