数据库 索引 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性能