基於自定義函式的索引在使用時,需要注意因修改函式導致的索引狀態變化。
需要關注兩點：
*** user_indexes.FUNCIDX_STATUS
*** create or replace 方式修改函式後，雖然索引狀態有效，但索引本身因為沒有重建，仍返回原值，需要關注。

版本：
SQL> select * from v$version;

BANNER
----------------------------------------------------------------
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Prod
PL/SQL Release 10.2.0.1.0 - Production
CORE 10.2.0.1.0 Production
TNS for Linux: Version 10.2.0.1.0 - Production
NLSRTL Version 10.2.0.1.0 - Production

1,建立測試表

drop table test;

create table test(x int,y int);

insert into test
select rownum,rownum from dual connect by level<20;

commit;

create or replace function f(x in int) return int deterministic
as
begin
return x;
end;
/

create index idx_test_x on test(f(x));

select index_type, status, funcidx_status
from user_indexes
where index_name = 'IDX_TEST_X';

INDEX_TYPE STATUS FUNCIDX_STATUS
--------------------------- -------- --------------
FUNCTION-BASED NORMAL VALID ENABLED

select x,y from test where f(x)=5;

X Y
--- ---
5 5

2,create or replace方式修改函式

create or replace function f(x in int) return int deterministic
as
begin
return x-1;
end;
/

select index_type, status, funcidx_status
from user_indexes
where index_name = 'IDX_TEST_X';

INDEX_TYPE                  STATUS   FUNCIDX_STATUS
--------------------------- -------- --------------
FUNCTION-BASED NORMAL       VALID    ENABLED
==>索引狀態VALID    ENABLED

select x,y from test where f(x)=5;

X Y
--- ---
5 5
==>結果有誤,應返回6,6!!!

alter index idx_test_x rebuild;

select x,y from test where f(x)=5;

X Y
---- ---
6 6

3,drop & create方式修改函式

drop function f;

select index_type, status, funcidx_status
from user_indexes
where index_name = 'IDX_TEST_X';
INDEX_TYPE                  STATUS   FUNCIDX_STATUS
--------------------------- -------- --------------
FUNCTION-BASED NORMAL       VALID    DISABLED
==>索引狀態為 VALID    DISABLED

create function f(x in int) return int deterministic
as
begin
return x-2;
end;
/

select index_type, status, funcidx_status
from user_indexes
where index_name = 'IDX_TEST_X';
INDEX_TYPE                  STATUS   FUNCIDX_STATUS
--------------------------- -------- --------------
FUNCTION-BASED NORMAL       VALID    DISABLED
==>索引狀態為 VALID    DISABLED

select x,y from test where f(x)=6;
ORA-30554: function-based index BOCNET.IDX_TEST_X is disabled

--重建索引
alter index idx_test_x rebuild;

select index_type, status, funcidx_status
from user_indexes
where index_name = 'IDX_TEST_X';

INDEX_TYPE                  STATUS   FUNCIDX_STATUS
--------------------------- -------- --------------
FUNCTION-BASED NORMAL       VALID    ENABLED
==>索引狀態為 VALID    ENABLED

select x,y from test where f(x)=6;

X Y
---- ----
8 8

基於函式的索引狀態變化

相關文章