Skip to Main Content
  • Questions
  • Why is a PL/SQL function being called 8 times per record in a simple update statement

Breadcrumb

Question and Answer

Connor McDonald

Thanks for the question, Blaine.

Asked: June 20, 2016 - 5:04 pm UTC

Last updated: June 21, 2016 - 1:52 am UTC

Version: 12.1

Viewed 1000+ times

You Asked

I'm using the Oracle Cloud Database.

I have a table with a column - location SDO_GEOMETRY.

I have a function that generates a random value to populate the column, generate_random_location().

When I run the below update statement, the function is called 8 times for each row updated.

update dd_members_t
   set location = generate_random_location(v_island)
 where member_id = 3;


To troubleshoot, I removed all of the code from the function except a single dbms_output.putline('Start'), set the in parameter to default null and set it to return null. It is still called 8 times.

update dd_members_t
   set location = generate_random_location()
 where member_id = 3;


I set the function to deterministic, which, of course, only gets called once, but that defeats the purpose of returning a random value.

I have solved the problem by using a bulk collect (the function only gets called once per row in the select statement)
  select member_id,
             generate_random_location(v_dino_island)
  bulk collect into l_member_ids
  from dd_members
  where.....


and then

     forall indx in 1 .. l_member_ids.count
       update dd_members_t
          set location = l_member_ids (indx).new_location
        where member_id = l_member_ids (indx).member_id;


But that still leaves the question of why is the function being called 8 times per record in a simple update statement, and is there a way to stop that?

and we said...

It would seem to pertain to the complexity of the object type.

For example

SQL> drop table t purge;

Table dropped.

SQL> drop type complex_obj;

Type dropped.

SQL>
SQL> create or replace
  2  type complex_obj as object ( a int, b int, c int, d int, e int );
  3  /

Type created.

SQL>
SQL> create table t (
  2    member_id int,
  3    location complex_obj );

Table created.

SQL>
SQL> insert into t values (1,null);

1 row created.

SQL>
SQL> exec dbms_application_info.set_client_info('0');

PL/SQL procedure successfully completed.

SQL>
SQL> create or replace
  2  function generate_random_location return complex_obj is
  3  begin
 4    dbms_application_info.set_client_info(nvl(userenv('client_info'),0)+1 );
  5    return null;
  6  end;
  7  /

Function created.

SQL>
SQL> select userenv('client_info') from dual;

USERENV('CLIENT_INFO')
----------------------------------------------------------------
0

1 row selected.

SQL>
SQL> update t
  2  set location = generate_random_location
  3  where member_id = 1;

1 row updated.

SQL>
SQL> select userenv('client_info') executions from dual;

EXECUTIONS
----------------------------------------------------------------
6

1 row selected.

SQL>
SQL>


And now we repeat with 10 elements

SQL> drop table t purge;

Table dropped.

SQL> drop type complex_obj;

Type dropped.

SQL>
SQL> create or replace
  2  type complex_obj as object ( a int, b int, c int, d int, e int ,
  3                               a1 int, b1 int, c1 int, d1 int, e1 int );
  4  /

Type created.

SQL>
SQL> create table t (
  2    member_id int,
  3    location complex_obj );

Table created.

SQL>
SQL> insert into t values (1,null);

1 row created.

SQL>
SQL> exec dbms_application_info.set_client_info('0');

PL/SQL procedure successfully completed.

SQL>
SQL> create or replace
  2  function generate_random_location return complex_obj is
  3  begin
  4    dbms_application_info.set_client_info(nvl(userenv('client_info'),0)+1 );
  5    return null;
  6  end;
  7  /

Function created.

SQL>
SQL> select userenv('client_info') from dual;

USERENV('CLIENT_INFO')
----------------------------------------------------------------
0

1 row selected.

SQL>
SQL> update t
  2  set location = generate_random_location
  3  where member_id = 1;

1 row updated.

SQL>
SQL> select userenv('client_info') executions from dual;

EXECUTIONS
----------------------------------------------------------------
11

1 row selected.

SQL>
SQL>


My hypothesis is that the statement is under the covers processed something *similar* to:

update t
set 
  location.a = generate_random_location.a,
  location.b = generate_random_location.b,
  location.c = generate_random_location.c,
  location.d = generate_random_location.d,
  location.e = generate_random_location.e,
  ...
where member_id = 1;


A simple workaround is to use a scalar select

SQL> drop table t purge;

Table dropped.

SQL> drop type complex_obj;

Type dropped.

SQL>
SQL> create or replace
  2  type complex_obj as object ( a int, b int, c int, d int, e int ,
  3                               a1 int, b1 int, c1 int, d1 int, e1 int );
  4  /

Type created.

SQL>
SQL> create table t (
  2    member_id int,
  3    location complex_obj );

Table created.

SQL>
SQL> insert into t values (1,null);

1 row created.

SQL>
SQL> exec dbms_application_info.set_client_info('0');

PL/SQL procedure successfully completed.

SQL>
SQL> create or replace
  2  function generate_random_location return complex_obj is
  3  begin
 4    dbms_application_info.set_client_info(nvl(userenv('client_info'),0)+1 );
  5    return null;
  6  end;
  7  /

Function created.

SQL>
SQL> select userenv('client_info') from dual;

USERENV('CLIENT_INFO')
----------------------------------------------------------------
0

1 row selected.

SQL>
SQL> update t
  2  set location = ( select generate_random_location from dual )
  3  where member_id = 1;

1 row updated.

SQL>
SQL> select userenv('client_info') executions from dual;

EXECUTIONS
----------------------------------------------------------------
1

1 row selected.

SQL>
SQL>

Rating

  (1 rating)

Is this answer out of date? If it is, please let us know via a Comment

Comments

Humm ?

Ghassan, June 21, 2016 - 4:44 am UTC

Why making thing complex?
How many time function is evaluated in :
Update t set t.c = upper (t.c )
Or
Update t set t.c = my_fct (t.c2 )

Fortunately this is related to rowcount estimated.

Except of course the deterministic fct.

Unless if you call once the fct eg .

Begin
V := fct (..);
...
Update t set t.c = v;
...
End;
/

Ps. See also documentation on psql level opt and inline sub-programs.


More to Explore

DBMS_APPLICATION_INFO

More on PL/SQL routine DBMS_APPLICATION_INFO here