起因某个群友提了个问题：

查询公司工资最高的3个人
select * from emp
where rownum <= 3
order by sal desc --这样为什么出来的结果是错的？
于是有人答道：

select * from emp
where rownum <= 3
是先按照存储的顺序，查到rownum <= 3的记录，这个结果再按照 sal desc 排序

select *
from（select rownum,t.* from emp t
order by sal desc
）
where rownum <= 3 就正确。

然后我生产环境中用类似语句测试下结果。居然两个语句结果一致：

select rowid,a.* from t_house a where rownum<=3
order by a.create_at ;

select * from(select rowid,a.* from t_house a
order by a.create_at
)where rownum <= 3;

因为这个字段create_at有索引，后来选个没索引的字段进行排序，发现结果2者有很大的区别。没索引的字段结果符合之前的推理，但有索引的字段查询结果是一样的。

于是提出疑问，并对应再测试环境中进行测试。

create table test_index (id number,money number,name varchar(20));
insert into test_index values(1,100,'aa');
insert into test_index values(2,200,'bb');
insert into test_index values(3,300,'cc');
insert into test_index values(4,400,'dd');
insert into test_index values(5,500,'ff');
insert into test_index values(6,600,'gg');
create index test_ind on test_index(money);
select rowid,a.* from test_index a where rownum<=3
order by a.money desc;

select * from(select rowid,a.* from test_index a
order by a.money desc
)where rownum <= 3;

但结果显示，加索引和不加索引结果是一样的。为什么生产环境中就有不一样的结果。寻求高手下，给出答案。

首先数据库参数：

首先数据库里面的某个参数是这样的：
SQL> show parameter optimizer;

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
optimizer_dynamic_sampling           integer     2
optimizer_features_enable            string      10.2.0.1
optimizer_index_caching              integer     0
optimizer_index_cost_adj             integer     100
optimizer_mode                       string      ALL_ROWS
optimizer_secure_view_merging        boolean     TRUE

对应的解释是：

Optimizer_mode=all_rows

优化器将寻找能够在最短的时间内完成语句的执行计划。

设置为这种CBO模式以后，将保证消耗的所有计算资源最小，尽管有时查询结束以后没有结果返回。all_rows 的优化模式更倾向于全表扫描，而不是全索引扫描和利用索引排序，因此这种优化模式适合于数据查看实时性不是那么强的数据仓库、决策支持系统和面向批处理的数据库（batch-oriented databases）等。

然后我们对应再同一数据库中重做测试，然后对应查看执行计划：

SQL> set autotrace traceonly
SQL> select rowid,a.* from test_index a where rownum<=3
2 order by a.money desc;

执行计划
----------------------------------------------------------
Plan hash value: 4234024215

--------------------------------------------------------------------------------
--

--------------------------------------------------------------------------------
--

| 0 | SELECT STATEMENT | | 3 | 150 | 4 (25)| 00:00:01
|

| 1 | SORT ORDER BY | | 3 | 150 | 4 (25)| 00:00:01
|

|* 2 | COUNT STOPKEY | | | | |
|

| 3 | TABLE ACCESS FULL| TEST_INDEX | 6 | 300 | 3 (0)| 00:00:01
|

--------------------------------------------------------------------------------
--

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

2 - filter(ROWNUM<=3)

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

统计信息
----------------------------------------------------------
          0 recursive calls
          0 db block gets
          7 consistent gets
          0 physical reads
          0 redo size
        837 bytes sent via SQL*Net to client
        520 bytes received via SQL*Net from client
          2 SQL*Net roundtrips to/from client
          1 sorts (memory)
          0 sorts (disk)
          3 rows processed

select rowid,a.* from t_house a where rownum<=3
2 order by a.create_at ;

执行计划
----------------------------------------------------------
Plan hash value: 225567018

----------------------------------------------------------------------------------------------------
| Id | Operation                    | Name                | Rows | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT             |                     |     3 |   525 |     4   (0)| 00:00:01 |
|* 1 | COUNT STOPKEY               |                     |       |       |            |          |
|   2 |   TABLE ACCESS BY INDEX ROWID| T_HOUSE             |   385K|    64M|     4   (0)| 00:00:01 |
|   3 |    INDEX FULL SCAN           | IDX_HOUSE_CREATE_AT |     4 |       |     3   (0)| 00:00:01 |
----------------------------------------------------------------------------------------------------

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

1 - filter(ROWNUM<=3)

统计信息
----------------------------------------------------------
          0 recursive calls
          0 db block gets
          6 consistent gets
          0 physical reads
          0 redo size
       4339 bytes sent via SQL*Net to client
        520 bytes received via SQL*Net from client
          2 SQL*Net roundtrips to/from client
          0 sorts (memory)
          0 sorts (disk)
          3 rows processed

于是发现同样的排序语句，再查询的ROWS行数量大的时候，会因为执行计划的选择产生偏差。

数据量大的时候，ROWNUM截取数据，实际是截取了索引中的数据排序。而ROWS影响了执行计划走索引还是全表。

结论：select rowid,a.* from test_index a where rownum<=3
order by a.money desc;

查询的结果可能因为数据量，是否有索引等因素等出不同的结果。假如要求准确的数据，最好加子查询或者使用ROW_NUMBER函数。

ITPub博客

索引影响排序的问题。

最新文章