[原创]降序索引失效问题

SQL> set autot traceonly exp
SQL> select owner,object_type
2 from t
3 where owner between 'T' and 'Z'
4 and object_type is not null
5 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'

这时候，执行计划是全表扫描，不走降序索引。

方法一：

SQL> select /*+ index(t,t_idx_desc) */ owner,object_type
2 from t
3 where owner between 'T' and 'Z'
4 and object_type is not null
5 order by owner desc,object_type asc;

Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=2 Card=1 Bytes=28)
1 0 INDEX (RANGE SCAN) OF 'T_IDX_DESC' (NON-UNIQUE) (Cost=2 Ca

方法二：

SQL> alter session set optimizer_mode=all_rows;

会话已更改。

SQL> select owner,object_type
2 from t
3 where owner between 'T' and 'Z'
4 and object_type is not null
5 order by owner desc,object_type asc;

Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=ALL_ROWS (Cost=2 Card=1 Bytes=28)
1 0 INDEX (RANGE SCAN) OF 'T_IDX_DESC' (NON-UNIQUE) (Cost=2 Ca

SQL> alter session set optimizer_mode=choose;

会话已更改。

SQL> select owner,object_type
2 from t
3 where owner between 'T' and 'Z'
4 and object_type is not null
5 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'

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/593324/viewspace-376155/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/593324/viewspace-376155/