今天在查看awr报告的时候，有一句很简单的sql语句引起了我的注意，因为它排在SQL Order by Reads的第2位。

Physical Reads	Executions	Reads per Exec	%Total	Elapsed Time (s)	%CPU	%IO	SQL Id	SQL Module
13,092,700	0		9.47	294.8	52.7	41.06	6src3hcd9mpt3	T.O.A.D.

SQL Text

select * from mo1_memo where entity_id in (11889308, 11889311, 11888262, 11888261, 11889301) order by memo_date desc

这条语句看起来很简单，自己印象中这个表中有一个相关的索引。 INDEX_NAME                               INDEX_TYPE UNIQUENES PAR COLUMN_LIST                     TABLE_TYPE STATUS   NUM_ROWS LAST_ANAL G ------------------------------ ---------- ---------- --------- --- ------------------------------ ---------- ------ ---------- --------- - MO1_MEMO_1IX                              FUNCTION-BASED NORMAL  NONUNIQUE YES SYS_NC00031$,ENTITY_TYPE_ID,APP_ID TABLE      N/A     554899259 01-APR-15 N                                                                                                   MO1_MEMO_2IX                              NORMAL     NONUNIQUE YES MEMO_EXTERNAL_ID               TABLE      N/A     478847583 01-APR-15 N MO1_MEMO_PK                               NORMAL     UNIQUE    YES MEMO_ID,APP_ID,ENTITY_KEY,PERIOD_KEY TABLE      N/A     554900387 01-APR-15 N 有一个基于函数的索引，我们可以通过exp 或者dbms_metadata来得到相关的语句，发现索引是类似下面的形式。 create index MO1_MEMO_1IX on MO1_MEMO(entity_id desc,entity_type_id,app_id); 这是一个降序索引。对于这种降序索引，会在表中创建一个隐藏列。 SQL> select owner,column_name from all_tab_cols where table_name='MO1_MEMO' and hidden_column='YES'; APPO     SYS_NC00031$ 有了这些信息，感觉应该是可以走索引扫描的。 但是得到的执行计划中却走了全表扫描，对一个数据量5亿多数据的表走全表扫描，杀伤力是很大的。 但是奇怪的是使用下面两种形式就没有任何问题，索引都能正常启用。 select * from mo1_memo where entity_id in (11889308) order by memo_date desc  select * from mo1_memo where entity_id =11889308 order by memo_date desc  一般来说降序索引在其值不为空的情况会启用，根据目前的表结构来看entity_type_id和app_id有着not null constraint，所以应该能够启用才对。 带着这个问题，我在11g的环境中简单模拟了一把。 SQL> create table test as select *from all_objects where rownum Table created. SQL> desc test  Name                                      Null?    Type  ----------------------------------------- -------- ----------------------------  OWNER                                     NOT NULL VARCHAR2(30)  OBJECT_NAME                               NOT NULL VARCHAR2(30)  SUBOBJECT_NAME                                     VARCHAR2(30)  OBJECT_ID                                 NOT NULL NUMBER  DATA_OBJECT_ID                                     NUMBER  OBJECT_TYPE                                        VARCHAR2(19)  CREATED                                   NOT NULL DATE  LAST_DDL_TIME                             NOT NULL DATE  TIMESTAMP                                          VARCHAR2(19)  STATUS                                             VARCHAR2(7)  TEMPORARY                                          VARCHAR2(1)  GENERATED                                          VARCHAR2(1)  SECONDARY                                          VARCHAR2(1)  NAMESPACE                                 NOT NULL NUMBER  EDITION_NAME                                       VARCHAR2(30) SQL> create index idx_test on test(object_id desc,object_name,object_type); Index created. SQL> select object_id from test where rownum  OBJECT_ID ----------    2880583    2880575 SQL> set autot trace exp stat 下面两种情况的执行计划是一致的。 但是在12c的环境中，结果却明显不同，可见再优化器内部对于这种场景已经做了优化。 SQL> create table test as select *from all_objects where rownum Table created. SQL> desc test  Name                                      Null?    Type  ----------------------------------------- -------- ----------------------------  OWNER                                     NOT NULL VARCHAR2(128)  OBJECT_NAME                               NOT NULL VARCHAR2(128)  SUBOBJECT_NAME                                     VARCHAR2(128)  OBJECT_ID                                 NOT NULL NUMBER  DATA_OBJECT_ID                                     NUMBER  OBJECT_TYPE                                        VARCHAR2(23)  CREATED                                   NOT NULL DATE  LAST_DDL_TIME                             NOT NULL DATE  TIMESTAMP                                          VARCHAR2(19)  STATUS                                             VARCHAR2(7)  TEMPORARY                                          VARCHAR2(1)  GENERATED                                          VARCHAR2(1)  SECONDARY                                          VARCHAR2(1)  NAMESPACE                                 NOT NULL NUMBER  EDITION_NAME                                       VARCHAR2(128)  SHARING                                            VARCHAR2(13)  EDITIONABLE                                        VARCHAR2(1)  ORACLE_MAINTAINED                                  VARCHAR2(1) SQL> create index idx_test on test(object_id desc,object_name,object_type); Index created. SQL> select object_id from test where rownum  OBJECT_ID ----------      10359      10358 SQL> set autot trace exp stat SQL> select object_name from test where object_id in(10359,10358); Execution Plan ---------------------------------------------------------- Plan hash value: 3459894390 ------------------------------------------------------------------------------ | Id  | Operation         | Name     | Rows  | Bytes | Cost (%CPU)| Time     | ------------------------------------------------------------------------------ |   0 | SELECT STATEMENT  |          |     2 |   158 |     3   (0)| 00:00:01 | |   1 |  INLIST ITERATOR  |          |       |       |            |          | |*  2 |   INDEX RANGE SCAN| IDX_TEST |     2 |   158 |     3   (0)| 00:00:01 | ------------------------------------------------------------------------------ Predicate Information (identified by operation id): ---------------------------------------------------    2 - access(SYS_OP_DESCEND("OBJECT_ID")=HEXTORAW('3CFDFBC4FF') OR               SYS_OP_DESCEND("OBJECT_ID")=HEXTORAW('3CFDFBC3FF'))        filter(SYS_OP_UNDESCEND(SYS_OP_DESCEND("OBJECT_ID"))=10358 OR               SYS_OP_UNDESCEND(SYS_OP_DESCEND("OBJECT_ID"))=10359) 所以技术的进步总是一点一滴，新版本中已经做了修复，但是目前来看11g还是主流，所以我们在创建降序索引的时候还是需要注意，避免一些不必要的情况发生。