基於函式的索引

/*從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@]