RSS Feed for This PostCurrent Article

Oracle – Understanding Your Database Update Statement

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.


Trackback URL


Sorry, comments for this entry are closed at this time.