使用倒序索引提升ORDER BY DESC性能
使用倒序索引提升ORDER BY DESC性能
使用倒序索引（INDEX DESC），可以大幅提升带有order by desc子句的SQL语句性能。
一、场景
1、表名：test_t，有一字段名为object_id
2、总数据量：580000行，segment_size：72MB
3、Where条件（Owner=’SYS’ and Object_id>50000）的行数：32472行
4、SQL语句：select * from test_t where owner='SYS' and object_id > 50000 order by object_id desc
5、希望借助倒序索引，提升order by object_id desc的性能
二、B*树倒序索引（DESC）比 B*树索引（默认为ASC升序）带来的性能提升对比
1、采用B*树倒序索引（DESC），COST：4，执行计划走的INDEX RANGE SCAN
SQL语句：select * from test_t where owner='SYS' and object_id > 50000 order by object_id desc
索引方式
create index idx_test_t_id_DESC on test_t(owner,object_id desc)
COST
与执行计划
Description
Object owner
Object name
Cost
Cardinality
Bytes
SELECT STATEMENT, GOAL = FIRST_ROWS
4
94298
9806992
TABLE ACCESS BY INDEX ROWID
SYS
TEST_T
4
94298
9806992
INDEX RANGE SCAN
SYS
IDX_TEST_T_ID_DESC
3
1
2、采用B*树索引（默认为ASC），COST：94103，执行计划走的INDEX RANGE SCAN DESENDING
索引方式
create index idx_test_t_id_DESC on test_t(owner,object_id)
COST
与执行计划
Description
Object owner
Object name
Cost
Cardinality
Bytes
SELECT STATEMENT, GOAL = FIRST_ROWS
94103
93792
9097824
TABLE ACCESS BY INDEX ROWID
SYS
TEST_T
94103
93792
9097824
INDEX RANGE SCAN DESCENDING
SYS
IDX_TEST_T_ID_ASC
287
93792
总结：
索引方式
COST
执行计划
(owner,object_id desc)
4
INDEX RANGE SCAN
(owner,object_id)
94103
INDEX RANGE SCAN DESENDING
三、实际测试过程
SQL语句：select * from test_t where owner='SYS' and object_id > 50000 order by object_id desc
1、采用B*树倒序索引（DESC）测试过程与结果
（1）test_t表上创建(OWNER+OBJECT_ID DESC)的倒序索引
SQL>create index idx_test_t_id_DESC on test_t(owner,object_id desc)
（2）重新收集统计信息
SQL> execdbms_stats.gather_table_stats(ownname=>'SYS',tabname => 'TEST_T',estimate_percent=> 20 , cascade=> TRUE );
（3）SQL执行计划与COST
Description
Object owner
Object name
Cost
Cardinality
Bytes
SELECT STATEMENT, GOAL = FIRST_ROWS
4
94298
9806992
TABLE ACCESS BY INDEX ROWID
SYS
TEST_T
4
94298
9806992
INDEX RANGE SCAN
SYS
IDX_TEST_T_ID_DESC
3
1
2、B*树索引（默认为ASC升序）测试过程与结果
（1）test_t表上创建(OWNER+OBJECT_ID)的普通默认ASC索引
SQL>create index idx_test_t_id_ASC on test_t(owner,object_id)
（2）重新收集统计信息
SQL> execdbms_stats.gather_table_stats(ownname=>'SYS',tabname => 'TEST_T',estimate_percent=> 20 , cascade=> TRUE );
（3）SQL执行计划与COST
Description
Object owner
Object name
Cost
Cardinality
Bytes
SELECT STATEMENT, GOAL = FIRST_ROWS
94103
93792
9097824
TABLE ACCESS BY INDEX ROWID
SYS
TEST_T
94103
93792
9097824
INDEX RANGE SCAN DESCENDING
SYS
IDX_TEST_T_ID_ASC
287
93792
