索引影响排序的问题。

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

查询公司工资最高的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

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

| Id  | Operation           | Name       | Rows  | Bytes | Cost (%CPU)| Time
 |

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

|   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博客 ” ，链接：http://blog.itpub.net/27053600/viewspace-746227/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/27053600/viewspace-746227/