儲存過程中user_tables的owner是definer還是invoker

redhouser發表於2013-09-27
基於自定義函式的索引在使用時,需要注意因修改函式導致的索引狀態變化。
需要關注兩點:
*** 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部落格 ” ,連結:http://blog.itpub.net/18922393/viewspace-773502/,如需轉載,請註明出處,否則將追究法律責任。

相關文章