oracle反向索引原因,oracle9i有有正向索引和反向索引，使用反向查询，不会自动使用反向索引...

oracle9i

测试：

create table t as select * from all_objects;

CREATE INDEX IND_T ON T(OWNER,OBJECT_TYPE);

CREATE INDEX desc_ind_t ON T(OWNER desc ,OBJECT_TYPE asc);

exec dbms_stats.gather_index_stats(user,IND_T);

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

1、正常查询：

set autotrace traceonly explain

SQL> select owner,object_type from hr.t

2  where owner between 'T' and 'Z'

3  and object_type is not null

4  order by owner desc,object_type asc;

Execution Plan

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

0      SELECT STATEMENT Optimizer=CHOOSE

1    0   SORT (ORDER BY)

2    1     INDEX (RANGE SCAN) OF 'IND_T' (NON-UNIQUE)

使用的owner desc,object_type asc;但是：  INDEX (RANGE SCAN) OF 'IND_T' (NON-UNIQUE)

2、加hint

SQL>  select /*+index(T DESC_T_IDX)*/owner,object_type from hr.t

2   where owner between 'T' and 'Z'

3   and object_type is not null

4   order by owner desc,object_type asc;

Execution Plan

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

0      SELECT STATEMENT Optimizer=CHOOSE (Cost=7 Card=1 Bytes=28)

1    0   INDEX (RANGE SCAN) OF 'DESC_T_IDX' (NON-UNIQUE) (Cost=4 Ca

rd=1 Bytes=28)

可以看到没有了：SORT (ORDER BY)，并且使用了'DESC_T_IDX'

反向索引。

3、

SQL> drop index ind_t;

索引已丢弃。

SQL> select owner,object_type from hr.t

2  where owner between 'T' and 'Z'

3  and object_type is not null

4  order by owner desc,object_type asc;

Execution Plan

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

0      SELECT STATEMENT Optimizer=CHOOSE

1    0   SORT (ORDER BY)

2    1     TABLE ACCESS (FULL) OF 'T'

结果使用的fts。

不知道为什么那个索引没有认出来？还是要分析？