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

使用倒序索引（INDEX DESC），可以大幅提升带有order by desc子句的SQL语句性能。 一、场景1、表名：test_t，有一字段名为object_id2、总数据量：580000行，segment_size：72MB3、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 desc5、希望借助倒序索引，提升order by object_id desc的性能

二、B*树倒序索引（DESC）比 B*树索引（默认为ASC升序）带来的性能提升对比1、采用B*树倒序索引（DESC），COST：4，执行计划走的INDEX RANGE SCANSQL语句：select * from test_t where owner="SYS" and object_id > 50000 order by object_id desc

索引方式

createindex 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

索引方式

createindex 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 desc1、采用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

更多Oracle相关信息见Oracle 专题页面 http://www.linuxidc.com/topicnews.aspx？tid=12Oracle触发器中增删改查本表MySQL慢查询开启及分析方法相关资讯 ORDER BY DESC 本文评论查看全部评论（0）