儲存過程中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/，如需轉載，請註明出處，否則將追究法律責任。

相關文章