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

转自： http://www.iteye.com/articles/2516
正如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操作少了很多。


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      [e-mail]


已用时间:  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      [e-mail]


已用时间:  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性能