oracle使用倒序索引提升ORDER BY DESC性能

使用倒序索引（INDEX DESC），可以大幅提升带有order by desc子句的SQL语句性能。

 另一种方式就是创建一个倒序索引，如：
create index t_idx on table_name（time  desc）;
这种创建时，索引最左边的值就是最晚的时间插入的数据，好像数据就是往左增长一样，此时索引从左往右扫描的时候就是最大的时间了，也是比较实用的。

一、场景

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

索引方式	createindex idx_test_t_id_DESCon test_t(owner,object_iddesc)
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

索引方式	createindex idx_test_t_id_DESCon 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

 

本文同步在子矜优化团队官方论坛上发布，链接地址：http://dba.oraclefans.cn/discuz/forum.php?mod=viewthread&tid=1208&fromuid=21