Oracle – Understanding Your Database Update Statement
By admin on Dec 25, 2007 in Oracle
Run the following SQLs to create the table and index
create table mytable
( col1 number(12) primary key,
col2 varchar2(100),
col3 varchar2(100),
col4 varchar2(100)
);
create or replace function myfunc(
col2 in varchar2
) return number deterministic
as
begin
dbms_output.put_line('Testing function');
return 1;
end;
/
create index myidx on mytable(myfunc(col2));
insert into mytable values(1,'test','test','test');
The function based index myfunc is created on mytable using col2. A record with primary key col1=1 is inserted.
Now, let’s see what happened.
Run the followings
set serveroutput on; update mytable set col2 = col2 where col1=1;
You will see
PL/SQL executed.
The function is not called because Oracle detected that the value of col2 is not modified.
Run the followings
set serveroutput on; update mytable set col2 = 'testing' where col1=1;
You will see
Testing function Testing function PL/SQL executed.
The function is called 2 times, once to find the original location in the index, and once to calculate the new location.
Sorry, comments for this entry are closed at this time.