本文介绍如何使用基于函数的索引减少索引空间占用并提高查询效率。通过对比传统索引和函数索引，展示了在特定场景下函数索引的优势。
基于函数的索引除了对使用内置函数（如UPPER、LOWER等）的查询显然有帮助外、还可以用来有选择的只对表中的某些行建立索引。
前期准备
从这里可以下载big_table.sql文件。
http://pan.baidu.com/s/1nvk3Ged
如不方便下载，可以直接从这里copy.
create table big_table
as
select rownum id, a.*
from all_objects a
where 1=0
/
alter table big_table nologging;
declare
l_cnt number;
l_rows number := &1;
begin
insert /*+ append */
into big_table
select rownum, a.*
from all_objects a
where rownum <= &1;
l_cnt := sql%rowcount;
commit;
while (l_cnt < l_rows)
loop
insert /*+ APPEND */ into big_table
select rownum+l_cnt,
OWNER, OBJECT_NAME, SUBOBJECT_NAME, OBJECT_ID, DATA_OBJECT_ID,
OBJECT_TYPE, CREATED, LAST_DDL_TIME, TIMESTAMP, STATUS,
TEMPORARY, GENERATED, SECONDARY, NAMESPACE, EDITION_NAME
from big_table
where rownum <= l_rows-l_cnt;
l_cnt := l_cnt + sql%rowcount;
commit;
end loop;
end;
/
alter table big_table add constraint
big_table_pk primary key(id);
exec dbms_stats.gather_table_stats( user, 'BIG_TABLE', estimate_percent=> 1);进入文件所在的目录，执行如下命令，创建一个1000 000行的表。
F:\BaiduYunDownload>sqlplus / as sysdba
SQL*Plus: Release 11.2.0.1.0 Production on 星期日 9月 25 17:36:10 2016
Copyright (c) 1982, 2010, Oracle. All rights reserved.
连接到:
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
SQL> @big_table.sql 1000000
表已创建。
表已更改。
原值 3: l_rows number := &1;
新值 3: l_rows number := 1000000;
原值 9: where rownum <= &1;
新值 9: where rownum <= 1000000;
PL/SQL 过程已成功完成。
表已更改。
PL/SQL 过程已成功完成。
实验开始
更新temporary列，在此将Y变成N，以及把N变为Y：
SQL> update big_table set temporary = decode(temporary,'N','Y','N');
已更新1000000行。
SQL> commit;
提交完成。检查Y与N的比例
SQL> select temporary , cnt,
2 round((ratio_to_report(cnt) over())*100,2) rtr
3 from(
4 select temporary, count(*) cnt
5 from big_table
6 group by temporary
7 )
8 /
T CNT RTR
- ---------- ----------
Y 997548 99.75
N 2452 .25如果对temporary使用传统索引，会发现索引有1 000 000个条目，占用差不多14MB的空间，其高度为3：
SQL> create index processed_flag_idx
2 on big_table(temporary);
索引已创建。
SQL> analyze index processed_flag_idx
2 validate structure;
索引已分析
SQL> select name, btree_space, lf_rows, height
2 from index_stats;
NAME BTREE_SPACE LF_ROWS HEIGHT
------------------------------ ----------- ---------- ----------
PROCESSED_FLAG_IDX 14536160 1000000 3通过这个索引获取任何数据都会带来3个I/O才能到达子叶块。这个索引不仅很宽，还很高。要得到第一个未处理的记录，必须至少执行4个I/O（其中3个是对索引的I/O，另外一个是对表的I/O）。
怎么能让索引更小呢？而且更新期间开销更小呢？
采用基于函数的索引，我们可以编写一个函数，如果不想对某个给定行家索引，则这个函数就返回NULL；而对想加索引的行则返回一个非NULL值。（B*树索引对于完全为NULL的键没有对应的条目）
例如，我们只对列值为N的记录感兴趣，所以只对这些记录加索引：
SQL> drop index processed_flag_idx;
索引已删除。
SQL> create index processed_flag_idx
2 on big_table( case temporary when 'N' then 'N' end);
索引已创建。
SQL> analyze index processed_flag_idx
2 validate structure;
索引已分析
SQL> select name, btree_space, lf_rows, height
2 from index_stats;
NAME BTREE_SPACE LF_ROWS HEIGHT
------------------------------ ----------- ---------- ----------
PROCESSED_FLAG_IDX 48032 2452 2这就有很大不同了，这个索引只有48K大小，而不是14MB。高度也有所降低，与先前那个更高的索引相比，使用这个索引能少执行一个I/O。
下面进行测试，通过使用函数索引进行查询。
注意：函数索引，必须使用函数表达式，才能通过索引访问。如(case temporary when ‘N’ then ‘N’ end)=’N’.（中间的打印记录省略了）
（关于autotrace，参看官网）
SQL> set timing on;
SQL> set autotrace on;
SQL> select * from big_table where (case temporary when 'N' then 'N' end)='N';
已用时间: 00: 00: 19.98
执行计划
----------------------------------------------------------
Plan hash value: 64562289
--------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost(%CPU)| Time |
--------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 9991 | 1004K| 541 (0)| 00:00:07 |
| 1 | TABLE ACCESS BY INDEX ROWID| BIG_TABLE | 9991 | 1004K| 541 (0)| 00:00:07 |
|* 2 | INDEX RANGE SCAN | PROCESSED_FLAG_IDX | 2452 | | 5 (0)| 00:00:01 |
--------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access(CASE "TEMPORARY" WHEN 'N' THEN 'N' END ='N')
统计信息
----------------------------------------------------------
38 recursive calls
0 db block gets
842 consistent gets
0 physical reads
0 redo size
154484 bytes sent via SQL*Net to client
2208 bytes received via SQL*Net from client
165 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
2452 rows processed可以看到，I/O总共842次。
如果没有使用函数索引访问呢？
可以看到总I/O增加，共计15091 次。
SQL> select * from big_table where temporary='N';
已用时间: 00: 00: 17.64
执行计划
----------------------------------------------------------
Plan hash value: 3993303771
-------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 499K| 49M| 4070 (1)| 00:00:49 |
|* 1 | TABLE ACCESS FULL| BIG_TABLE | 499K| 49M| 4070 (1)| 00:00:49 |
-------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("TEMPORARY"='N')
统计信息
----------------------------------------------------------
0 recursive calls
0 db block gets
15091 consistent gets
0 physical reads
0 redo size
154484 bytes sent via SQL*Net to client
2208 bytes received via SQL*Net from client
165 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
2452 rows processed关于cost和执行时间，asktom
本文参考《Oracle_Database_9i10g11g编程艺术深入数据库体系结构》第2版，Thomas Kyte著，苏金国 王小振等译，422~424页
扫一扫
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
