为何倒序索引没被使用

提问：

难道索引有优先级
drop table t;
create table t as select * from dba_objects;
create index t_idx on t(owner, object_type);
create index desc_t_idx on t(owner desc,object_type asc);
exec dbms_stats.gather_index_stats( user, 'DESC_T_IDX' );
set autotrace traceonly explain
select owner, object_type from t
where owner between 'T' and 'Z' and object_type is not null
order by owner DESC, object_type ASC;
set autotrace off

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

---------------------------------------------------------------------------
| Id  | Operation         | Name  | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |       |   332 |  9296 |     4  (25)| 00:00:01 |
|   1 |  SORT ORDER BY    |       |   332 |  9296 |     4  (25)| 00:00:01 |
|*  2 |   INDEX RANGE SCAN| T_IDX |   332 |  9296 |     3   (0)| 00:00:01 |
---------------------------------------------------------------------------

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

   2 - access("OWNER">='T' AND "OWNER"<='Z')
       filter("OBJECT_TYPE" IS NOT NULL AND
              SYS_OP_DESCEND("OWNER")<=HEXTORAW('ABFF')  AND
              SYS_OP_DESCEND("OWNER")>=HEXTORAW('A5FF') )

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

在有普通索引和降序索引时，每次都会用普通索引，出上面那个执行计划


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

-------------------------------------------------------------------------------
| Id  | Operation        | Name       | Rows  | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------------
|   0 | SELECT STATEMENT |            |  1427 | 39956 |     2   (0)| 00:00:01 |
|*  1 |  INDEX RANGE SCAN| DESC_T_IDX |  1427 | 39956 |     2   (0)| 00:00:01 |
-------------------------------------------------------------------------------

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

   1 - access(SYS_OP_DESCEND("OWNER")>=HEXTORAW('A5FF')  AND
              SYS_OP_DESCEND("OWNER")<=HEXTORAW('ABFF') )
       filter("OBJECT_TYPE" IS NOT NULL AND
              SYS_OP_UNDESCEND(SYS_OP_DESCEND("OWNER"))>='T' AND
              SYS_OP_UNDESCEND(SYS_OP_DESCEND("OWNER"))<='Z' AND
              SYS_OP_DESCEND("OWNER")<=HEXTORAW('ABFF')  AND
              SYS_OP_DESCEND("OWNER")>=HEXTORAW('A5FF') )

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

只有把普通索引删除后，才会使用降序索引
按Cost应该使用降序索引更低啊，难道普通索引优先级更高

回答：

如果索引创建,设某字段为倒序，则Oracle用创建function index的方式来创建该索引，即为该function字段在原表上另建一新字段.
SQL> create table t as select * from dba_objects;
Table created.
SQL> create index t_idx on t(owner, object_type);
Index created.
SQL> create index desc_t_idx on t(owner desc, object_type asc);
Index created.
SQL> select INDEX_NAME, TABLE_NAME, COLUMN_NAME, COLUMN_POSITION, DESCEND from dba_ind_columns where table_name='T';

INDEX_NAME           TABLE_NAME      COLUMN_NAME     COLUMN_POSITION DESCEND
-------------------- --------------- --------------- --------------- ------------
T_IDX                         T               OWNER                            1                           ASC
T_IDX                         T               OBJECT_TYPE                   2                           ASC
DESC_T_IDX              T               SYS_NC00014$                1                           DESC
DESC_T_IDX              T               OBJECT_TYPE                   2                           ASC

SQL> select column_name from dba_tab_cols where table_name='T' andhidden_column='YES';

COLUMN_NAME
---------------
SYS_NC00014$

而对于function index，只有当其不为NULL时，才会被Oracle使用.所以作如下修改后，该倒序索引就被自动引用了

SQL> alter table t modify (object_type null);

Table altered.

SQL> exec dbms_stats.gather_index_stats(user,'DESC_T_IDX');

PL/SQL procedure successfully completed.

SQL> exec dbms_stats.gather_table_stats('SYS','T');

PL/SQL procedure successfully completed.

SQL> set autotrace traceonly explain
SQL> select owner, object_type from t
where owner between 'T' and 'Z' and object_type is not null
order by owner DESC, object_type ASC  2    3  
  4  ;

Execution Plan
----------------------------------------------------------
Plan hash value: 3844546568

-------------------------------------------------------------------------------
| Id  | Operation        | Name       | Rows  | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------------
|   0 | SELECT STATEMENT |            |  1105 | 16575 |     6   (0)| 00:00:01 |
|*  1 |  INDEX RANGE SCAN|DESC_T_IDX|  1105 | 16575 |     6   (0)| 00:00:01 |
-------------------------------------------------------------------------------

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

   1 - access(SYS_OP_DESCEND("OWNER")>=HEXTORAW('A5FF')  AND
              SYS_OP_DESCEND("OWNER")<=HEXTORAW('ABFF') )
       filter(SYS_OP_UNDESCEND(SYS_OP_DESCEND("OWNER"))>='T' AND
              SYS_OP_UNDESCEND(SYS_OP_DESCEND("OWNER"))<='Z' AND "OBJECT_TYPE" IS NOT
              NULL)