使用倒序索引提升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

 



http://www.linuxidc.com/Linux/2013-07/87433.htm

评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值