数据库 索引 Oracle 10g SQL性能优化-使用函数索引来解决普通索引不发生作用的问题...

[b]转自: [url]http://www.iteye.com/articles/2516[/url]
正如Oracle 10g SQL性能优化-使用索引提高数据库select语句的性能的例子一样,对于所测试的app_user表,3百万条记录,对于select * from scott.app_user where abs(user_id) = 100000;这条sql,直接在user_id上建索引对这条SQL的访问是没有时间上的改进的,因为在这样的情况下,本质上B树上存的是user_id的值,而不是abs(user_id)的值,所以Oracle采取了全表扫描的方式来访问数据库。

解决这样问题的方法就是在app_user表上建一个abs(user_id)的函数索引,相当于多了一个虚拟列abs(user_id),然后在这个虚拟列上建索引;可以看到执行计划相差较大,从统计信息分析,IO操作少了很多。[/b]


1. 概要
昨天在Oracle 10g SQL性能优化-使用索引提高数据库select语句的性能说明了索引对select语句的巨大性能提高,今天,来看一种情况,就是在where语句中,在索引列上有函数操作时,普通的索引并不会发生作用,需要使用函数索引来影响执行计划;

2. 测试基本环境:
* 硬件:T60笔记本,T2500的CPU,2G内存

* 操作系统:Windows xp

* Oracle版本:Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 – Production

* 主要的SQL测试在一个app_user表上进行,表格有3,000,000条数据;

* 为消除缓存等各方面的影响,每次要select之前都重启Oracle服务,保证缓存是空的;

3. 测试表格定义和记录数
下面是app_user的定义和记录数,3百万记录数已经具有一定的代表性;

注意:看到红色部分,这里user_id上已经有主键;

hetaoblog@ORCL>SELECT DBMS_METADATA.GET_DDL(‘TABLE’,'APP_USER’,'SCOTT’) FROM DUAL;


DBMS_METADATA.GET_DDL(‘TABLE’,'APP_USER’,'SCOTT’)

——————————————————————————–


CREATE TABLE “SCOTT”.”APP_USER”

( “USER_ID” NUMBER(*,0),

“USER_NAME” VARCHAR2(20),

“GENDER” CHAR(1),

“EMAIL” VARCHAR2(30),

CONSTRAINT “UNIQUE_EMAIL” UNIQUE (“EMAIL”)

USING INDEX PCTFREE 10 INITRANS 2 MAXTRANS 255

TABLESPACE “USERS” ENABLE,

CONSTRAINT “UNIQUE_NAME” UNIQUE (“USER_NAME”)

USING INDEX PCTFREE 10 INITRANS 2 MAXTRANS 255

TABLESPACE “USERS” ENABLE,

CONSTRAINT “APP_USER_PK” PRIMARY KEY (“USER_ID”)

USING INDEX PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS

TABLESPACE “USERS” ENABLE

) PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 NOCOMPRESS LOGGING

TABLESPACE “USERS” ENABLE ROW MOVEMENT


hetaoblog@ORCL>select count(*) from scott.app_user;


COUNT(*)

———-

3000017

4. 测试SQL和过程
4.1测试SQL为:select * from scott.app_user where abs(user_id) = 100000

4.2 测试用例1【无函数索引】
这时,为避免缓存影响,重启数据库,然后执行下面的查询:

hetaoblog@ORCL>select * from scott.app_user where abs(user_id) = 100000;


USER_ID USER_NAME GENDER EMAIL

———- —————————————- —— ————————————————————

100000 user100000 M user100000@qq.com


已用时间: 00: 00: 09.36


执行计划

———————————————————-

Plan hash value: 2096499096


——————————————————————————

| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |

——————————————————————————

| 0 | SELECT STATEMENT | | 197 | 8865 | 4039 (4)| 00:00:49 |

|* 1 | TABLE ACCESS FULL| APP_USER | 197 | 8865 | 4039 (4)| 00:00:49 |

——————————————————————————


Predicate Information (identified by operation id):

—————————————————


1 – filter(ABS(“USER_ID”)=100000)


Note

—–

– dynamic sampling used for this statement


统计信息

———————————————————-

432 recursive calls

0 db block gets

18021 consistent gets

17879 physical reads

0 redo size

613 bytes sent via SQL*Net to client

385 bytes received via SQL*Net from client

2 SQL*Net roundtrips to/from client

5 sorts (memory)

0 sorts (disk)

1 rows processed

4.3测试用例2【有函数索引】
下面添加在user_id上的函数索引,对于user_id的abs函数建索引

hetaoblog@ORCL>create index app_user_abs_id on app_user(abs(user_id));


索引已创建。


已用时间: 00: 00: 44.20


添加索引后,重启数据库,再次做查询:

hetaoblog@ORCL>select * from scott.app_user where abs(user_id) = 100000;


USER_ID USER_NAME GENDER EMAIL

———- —————————————- —— ————————————————————

100000 user100000 M user100000@qq.com


已用时间: 00: 00: 00.96


执行计划

———————————————————-

Plan hash value: 319304378


———————————————————————————————–

| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |

———————————————————————————————–

| 0 | SELECT STATEMENT | | 1 | 45 | 76 (2)| 00:00:01 |

| 1 | TABLE ACCESS BY INDEX ROWID| APP_USER | 1 | 45 | 76 (2)| 00:00:01 |

|* 2 | INDEX RANGE SCAN | APP_USER_ABS_ID | 9927 | | 3 (0)| 00:00:01 |

———————————————————————————————–


Predicate Information (identified by operation id):

—————————————————


2 – access(ABS(“USER_ID”)=100000)


Note

—–

– dynamic sampling used for this statement


统计信息

———————————————————-

561 recursive calls

0 db block gets

172 consistent gets

377 physical reads

116 redo size

613 bytes sent via SQL*Net to client

385 bytes received via SQL*Net from client

2 SQL*Net roundtrips to/from client

6 sorts (memory)

0 sorts (disk)

1 rows processed

5. 结论分析:
正如Oracle 10g SQL性能优化-使用索引提高数据库select语句的性能的例子一样,对于所测试的app_user表,3百万条记录,对于select * from scott.app_user where abs(user_id) = 100000;这条sql,直接在user_id上建索引对这条SQL的访问是没有时间上的改进的,因为在这样的情况下,本质上B树上存的是user_id的值,而不是abs(user_id)的值,所以Oracle采取了全表扫描的方式来访问数据库。

解决这样问题的方法就是在app_user表上建一个abs(user_id)的函数索引,相当于多了一个虚拟列abs(user_id),然后在这个虚拟列上建索引;可以看到执行计划相差较大,从统计信息分析,IO操作少了很多。


结果对照表:

SQL运行时间 执行计划主要步骤 一致读 物理读
有函数索引 0. 96秒 INDEX RANGE SCAN 172 377
无函数索引 09.36秒 TABLE ACCESS FULL 18021 17879


这里以abs函数做例子,其他函数也是一样的情况,比如upper(),lower(),instr(),length(),substr(),instr(),to_date(),trunc(),to_char()等,当在相关列上有函数调用后,普通的索引并不会发生作用,需要使用函数索引来改变查询计划,提高SQL性能
评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

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

抵扣说明:

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

余额充值