基于函数的索引

/*从Oracle8i第1版(8.1.5)推出以来，我们可以利用基于函数的索引,

function-based index，简称为FBI,函数索引的两个主要作用：

1、只对限定的行创建索引,节约空间，提高检索速度：

我们可以利用函数索引只把我们经常关心的某一部分数据进行索引，而其他

的数据没有进入B*树索引，这样我们就可以节省了索引空间。

2、 优化WHERE子句中使用了函数的sql语句：

因为在以前版本，在WHERE子句中使用函数会使在这个表上创建的索引没

法利用，从而难以提高这个语句的性能。*/

一、标准索对于值为NULL的数据不进行索引

create table test ( id int );

create index idx_test on test(id);

insert into test select null from all_objects;

analyze index idx_test validate structure;

select name, lf_rows from index_stats;

/*

NAME LF_ROWS

------------------------- --------

IDX_TEST 0
*/

/*索引是空的。表中有49016行，而在索引结构中只有0行，因为关键字全部为

NULL。现在我如果插入一个非NULL值：*/

insert into test values ( 1 );

commit;

analyze index idx_test validate structure;

select name, lf_rows from index_stats;

/*

NAME LF_ROWS

------------------------- ---------

IDX_TEST 1

*/

/*最终有了一个索引项。接下来我让所有值都不为NULL：*/

update test set id = rownum;

commit;

analyze index idx_test validate structure;

select name, lf_rows from index_stats;

/*

NAME LF_ROWS

------------------------- ---------

IDX_TEST 49017

*/

/*所有行都在检索出来了。*/

二、只对限定的行创建索引,节约空间，提高检索速度：

为了进行有选择的检索，我可以使用带有判定条件的CASE（或DECODE等）函数

来索引我们最关心的内容。下面的例子对所有ID取值小于50的行进行检索。表

有许多行，而且大多数行的ID值都大于50。*/

truncate table test;

drop index idx_test;

create index idx_test_2 on test(case when id<50 then id end);

begin

for i in 1..10000 loop

insert into test values(i);

end loop;

end;

/

commit;

analyze index idx_test_2 compute statistics;

analyze table test compute statistics;

analyze index idx_test_2 validate structure;

select name, lf_rows from index_stats;

/*

NAME LF_ROWS

------------------------- ----------

IDX_TEST_2 49

*/

/*但是在执行查询的时候，oracle没有利用基于函数的索引，除非人为干预添

加hints。*/

select * from test where id=30;

/*

ID

----------

30

已用时间: 00: 00: 00.04

执行计划
----------------------------------------------------------

0 SELECT STATEMENT Optimizer=CHOOSE (Cost=17 Card=1 Bytes=4)

1 0 TABLE ACCESS (FULL) OF 'TEST' (TABLE) (Cost=17 Card=1 Byte

s=4)

*/

alter system flush shared_pool;

select * from test where id=300;

/*

ID

----------

300

已用时间: 00: 00: 00.17

执行计划

----------------------------------------------------------

0 SELECT STATEMENT Optimizer=CHOOSE (Cost=17 Card=1 Bytes=4)

1 0 TABLE ACCESS (FULL) OF 'TEST' (TABLE) (Cost=17 Card=1 Byte

s=4)

*/

/*无论查询的是索引范围内，还是索引范围外的数据，oracle都用的是全表扫

描*/

alter system flush shared_pool;

/*添加hints再执行索引范围内的数据检索就利用了索引*/

select /*+index(test)*/ * from test where id=30;

/*

ID

----------

30

已用时间: 00: 00: 00.09

执行计划

----------------------------------------------------------

0 SELECT STATEMENT Optimizer=CHOOSE (Cost=2 Card=1 Bytes=4)

1 0 TABLE ACCESS (BY INDEX ROWID) OF 'TEST' (TABLE) (Cost=2 Ca

rd=1 Bytes=4)

2 1 INDEX (FULL SCAN) OF 'IDX_TEST_2' (INDEX) (Cost=1 Card=49)

*/

alter system flush shared_pool;

/*不在索引范围内的数据如果利用hints强制索引查找是找不到数据的*/

select /*+index(test idx_test_2)*/ * from test where id=300;

未选定行

已用时间: 00: 00: 00.03

执行计划
----------------------------------------------------------

0 SELECT STATEMENT Optimizer=CHOOSE (Cost=2 Card=1 Bytes=4)

1 0 TABLE ACCESS (BY INDEX ROWID) OF 'TEST' (TABLE) (Cost=2 Ca

rd=1 Bytes=4)

2 1 INDEX (FULL SCAN) OF 'IDX_TEST_2' (INDEX) (Cost=1 Card=49)

三、优化WHERE子句中使用了函数的sql语句：

Drop index idx_test_2;

create index idx_test_3 on test(id);

analyze index idx_test_3 compute statistics;

analyze table test compute statistics;

select id from test where lower(id)= '30'; --(这里要注意单引号的作用)

/*

ID

----------

30

执行计划

----------------------------------------------------------

0 SELECT STATEMENT Optimizer=CHOOSE (Cost=6 Card=100 Bytes=300

)

1 0 TABLE ACCESS (FULL) OF 'TEST' (TABLE) (Cost=6 Card=100 Byt

es=300)

优化器没有选择索引检索。

*/

/*下面我们创建函数索引*/

drop index idx_test_3;

create index idx_test_4 on test(lower(id));

analyze index idx_test_4 compute statistics;

analyze table test compute statistics;

select id from test where lower(id)= '30';

ID

----------

30

执行计划

----------------------------------------------------------

0 SELECT STATEMENT Optimizer=CHOOSE (Cost=2 Card=1 Bytes=4)

1 0 TABLE ACCESS (BY INDEX ROWID) OF 'TEST' (TABLE) (Cost=2 Ca

rd=1 Bytes=4)

2 1 INDEX (RANGE SCAN) OF 'IDX_TEST_4' (INDEX) (Cost=1 Card=

1)

由优化器来对SQL语句中的表达式进行解析，并且和FBI索引上面的表达

式进行对比。这里，老的版本的oracle对SQL函数的大小写时敏感的。因

此要求SQL语句中使用的函数和创建FBI索引得时候的那个SQL函数的大

小写一致(我的测试环境是10g，已经对大小写不敏感了，但是我们在二的例

子中where和索引内容不一致，所以需要人为干预指定优化方式)，否则无

法利用这个FBI索引。

FBI索引必须遵守下面的规则：

a、 必须使用基于成本的优化器，而且创建后必须对索引进行分析

b、 不能存储NULL值。因为任何函数在任何情况下都不能返回NULL值。

[@more@]

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/18921899/viewspace-1016774/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/18921899/viewspace-1016774/