基于自定义函数的索引在使用时,需要注意因修改函数导致的索引状态变化。
需要关注两点：
*** 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

ITPub博客

存储过程中user_tables的owner是definer还是invoker

最新文章