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.
Post a Comment