“Call me!” Many many times!

Some readers might recall that classic Blondie track “Call me”.  Of course, some readers might be wishing that I wouldn’t harp on about great songs from the 80’s. But bear with me, there is a (very tenuous) link to this post. If you haven’t heard the song, you can jump to the chorus right here.  Go on, I’ll wait until you get back. Smile

This golden oldie is relevant when it comes to dealing with object types in PL/SQL, and in particular, when you are using them in a SQL-related context.  To set the scene, I’ll start with a trivial example – creating a simple object, utilizing that object in a function, and then using a simple SELECT-FROM-DUAL on that function to check that it works.


SQL> create or replace
  2  type three_values as object
  3   ( val_1 int,
  4     val_2 int,
  5     val_3 int
  6   );
  7  /

Type created.

SQL>
SQL> create or replace
  2  function f return three_values is
  3  begin
  4     return three_values(1,2,3);
  5  end;
  6  /

Function created.

SQL>
SQL> select f from dual;

F(VAL_1, VAL_2, VAL_3)
-----------------------------------------
THREE_VALUES(1, 2, 3)

So far so good. But as is often the case, the requirement is not to get the object as an entity in it’s own right, but to get at the scalar values within the object. Hence the wrapping of that query as an inline view to an outer one to get the individual columns:


SQL> select x.c.val_1, x.c.val_2, x.c.val_3
  2  from
  3  (select f c from dual ) x;

   C.VAL_1    C.VAL_2    C.VAL_3
---------- ---------- ----------
         1          2          3

1 row selected.

But let’s explore that a little more carefully. Observation of the SQL text might lead us to think that

  • we ran the inner query,
  • saved the result we’ve already seen into a temporary result of type THREE_VALUES,
  • then extracted the elements via the outer SELECT.

But now I’ll make a small amendment to the function as follows. I’ll introduce a package variable so we can do some tracking of executions


SQL> create or replace package pkg as
  2    x int := 0;
  3  end;
  4  /

Package created.

SQL>
SQL> create or replace
  2  function f return three_values is
  3  begin
  4     pkg.x:= pkg.x+1;
  5     return three_values(1,2,3);
  6  end;
  7  /

Function created.

As you would expect, a simple SELECT-FROM-DUAL results in the function being executed once.


SQL> select f from dual;

F(VAL_1, VAL_2, VAL_3)
-----------------------------------------------------
THREE_VALUES(1, 2, 3)

1 row selected.

SQL> set serverout on
SQL> exec dbms_output.put_line('calls='||pkg.x);
calls=1

PL/SQL procedure successfully completed.

As before, I’ll now wrap that query in another SELECT to extract the column elements. I’ll reset my package variable to start the count again from zero.


SQL> exec pkg.x := 0;

PL/SQL procedure successfully completed.

SQL> select x.c.val_1, x.c.val_2, x.c.val_3
  2  from
  3  (select f c from dual ) x;

   C.VAL_1    C.VAL_2    C.VAL_3
---------- ---------- ----------
         1          2          3

1 row selected.

SQL> set serverout on
SQL> exec dbms_output.put_line('calls='||pkg.x);
calls=3

PL/SQL procedure successfully completed.

Notice the difference. The function was called three times. If that function was doing some “heavy lifting” then those additional executions might be a cause for concern. The number of calls to a function referenced from SQL has always been indeterminate, so this is nothing new, but it still might catch you by surprise. References throughout the SQL to those function scalar values can bump the count up even more:


SQL> exec pkg.x := 0;

PL/SQL procedure successfully completed.

SQL> select x.c.val_1, x.c.val_2, x.c.val_3
  2  from
  3  (select f c from dual ) x
  4  where x.c.val_1 = 1 and x.c.val_2 = 2;

   C.VAL_1    C.VAL_2    C.VAL_3
---------- ---------- ----------
         1          2          3

1 row selected.

SQL> set serverout on
SQL> exec dbms_output.put_line('calls='||pkg.x);
calls=5

but conversely, you cannot just simply assume that every reference will result in an additional execution. For example, adding an ORDER BY clause containing references does not increment the count.


SQL> exec pkg.x := 0;

PL/SQL procedure successfully completed.

SQL> select x.c.val_1, x.c.val_2, x.c.val_3
  2  from
  3  (select f c from dual ) x
  4  where x.c.val_1 = 1 and x.c.val_1+10 = 11
  5  order by x.c.val_1, x.c.val_2, x.c.val_3;

   C.VAL_1    C.VAL_2    C.VAL_3
---------- ---------- ----------
         1          2          3

1 row selected.

SQL> set serverout on
SQL> exec dbms_output.put_line('calls='||pkg.x);
calls=5

PL/SQL procedure successfully completed.

A 10053 trace on any of these queries reveals the reason why we can expect to see multiple executions. When I parse my initial query


select x.c.val_1, x.c.val_2, x.c.val_3
from
(select f c from dual ) x

then scrolling through the 10053 trace, I’ll end up with this:


Final query after transformations:******* UNPARSED QUERY IS *******
SELECT SYS_OP_ATG("SCOTT"."F"(),1,2,2) "C.VAL_1",SYS_OP_ATG("SCOTT"."F"(),2,3,2) "C.VAL_2",SYS_OP_ATG("SCOTT"."F"(),3,4,2) "C.VAL_3" 
FROM "SYS"."DUAL" "DUAL"

You can see that the query has been transformed to have explicit calls of the function for each column we requested.

Can the multiple calls be avoided? Yes. There’s a couple of simple options to explore here. First of these, is that you can use a scalar subquery to take advantage of some query level caching that the database will do to avoid repeated executions of query sub-components.  Here is the previous examples repeated but with a scalar subselect to access the function.


SQL> exec pkg.x := 0;

PL/SQL procedure successfully completed.

SQL> select x.c.val_1, x.c.val_2, x.c.val_3
  2  from
  3  (select ( select f from dual ) c
  4   from dual ) x;

   C.VAL_1    C.VAL_2    C.VAL_3
---------- ---------- ----------
         1          2          3

1 row selected.

SQL>
SQL> exec dbms_output.put_line('calls='||pkg.x);
calls=1

PL/SQL procedure successfully completed.

SQL>
SQL>
SQL>
SQL> exec pkg.x := 0;

PL/SQL procedure successfully completed.

SQL> select x.c.val_1, x.c.val_2, x.c.val_3
  2  from
  3  (select ( select f from dual ) c
  4   from dual ) x
  5  where x.c.val_1 = 1 and x.c.val_2 = 2;

   C.VAL_1    C.VAL_2    C.VAL_3
---------- ---------- ----------
         1          2          3

1 row selected.

SQL>
SQL> exec dbms_output.put_line('calls='||pkg.x);
calls=1

PL/SQL procedure successfully completed.

Alternately, since we are returning an object as a table, then we can reflect this by adding an additional nested table object type to handle this within the function:


SQL> create or replace
  2  type three_val_list as
  3   table of three_values
  4  /

Type created.

SQL>
SQL> create or replace
  2  function f return three_val_list is
  3  begin
  4     pkg.x:= pkg.x+1;
  5     return three_val_list(three_values(1,2,3));
  6  end;
  7  /

Function created.

SQL> exec pkg.x := 0;

PL/SQL procedure successfully completed.

SQL> select * from f();

     VAL_1      VAL_2      VAL_3
---------- ---------- ----------
         1          2          3

1 row selected.

SQL> exec dbms_output.put_line('calls='||pkg.x);
calls=1

PL/SQL procedure successfully completed.

Before you get carried away and start screaming “Performance Bug!” from the rooftops, the moral of the story here is probably to first ask yourself: Is there a valid reason for referencing my OBJECT data types via SQL? Very often I see the instantiation of any variable (complex or scalar) being done with a SELECT-FROM-DUAL. Don’t do it – it is almost never needed.

But, if you have truly embraced the Object-Relational features and are genuinely gathering object types from SQL statements, then consider some of the workarounds above to avoid excessive function calls.

Passing parameters

I had an interesting question from a previous work colleague. She had a need to perform a query on attribute pairs. For the sake of discussion, we’ll assume that pairs are a shopping centre name, and a checkout aisle in that shopping centre.

So for example, on screen, a user may say:

Show me the sales coming through the following checkout aisles:

Perth,17
Wembley,32
Floreat,11

She had the beginnings of a package to solve the problem, but got stuck at when it come to referencing the two sets of attributes



SQL> create or replace package DEMO is
  2
  3    type t_numbers is table of number(5)
  4       index by pls_integer;
  5
  6    type t_strings is table of varchar(20)
  7       index by pls_integer;
  8
  9    procedure list(p_center t_strings, p_checkout t_numbers);
 10
 11  end;
 12  /

Package created.

SQL> create or replace package body DEMO is
  2
  3    procedure list(p_center t_strings, p_checkout t_numbers) is
  4    begin
  5      for i in
  6      (
  7        select *
  8        from   shopping_stats
  9  --      where  (center,checkout_num) in
 10  --           ( ???? p_center,p_checkout ????)
 11      )
 12      loop
 13        dbms_output.put_line(i.center||','||i.checkout_num||','||i.sales_total);
 14      end loop;
 15    end;
 16
 17  end;
 18  /

Package body created.


So here was a simple solution for her, which also satisfied the need of not changing the calling interface.

Step 1: We create some database types to match the required structure



SQL> create or replace type each_parameter_row as object (
  2    center      varchar2(20),
  3    checkout_num   number(5)
  4  );
  5  /

Type created.

SQL> create or replace type list_of_parameters as
  2    table of each_parameter_row
  3  /

Type created.
 

Step 2: Create a pipelined function to map the plsql array to the new database types



function parms_as_table return list_of_parameters pipelined is
begin
  for i in p_center.first .. p_center.last loop
    pipe row ( each_parameter_row(p_center(i),p_checkout(i))  );
  end loop;
  return;
end;

Step 3: Change the base code to query from the output of that function



procedure list(p_center t_strings, p_checkout t_numbers) is
begin
  for i in
  (
    select *
    from   shopping_stats
    where  (center, checkout_num) in
         ( select center, checkout_num
           from table(demo.parms_as_table)
         )
  )
  loop
    dbms_output.put_line(i.center||','||i.checkout_num||','||i.sales_total);
  end loop;
end;

And now we can put it all together…



SQL> create or replace package DEMO is
  2
  3    type t_numbers is table of number(12)
  4       index by pls_integer;
  5
  6    type t_strings is table of varchar(20)
  7       index by pls_integer;
  8
  9    function parms_as_table(p_center t_strings, p_checkout t_numbers) return list_of_parameters pipelined;
 10
 11    procedure list(p_center t_strings, p_checkout t_numbers);
 12
 13  end;
 14  /

Package created.

SQL> create or replace package body DEMO is
  2
  3    function parms_as_table(p_center t_strings, p_checkout t_numbers)
  4               return list_of_parameters pipelined is
  5    begin
  6      for i in p_center.first .. p_center.last loop
  7        pipe row ( each_parameter_row(p_center(i),p_checkout(i))  );
  8      end loop;
  9      return;
 10    end;
 11
 12    procedure list(p_center t_strings, p_checkout t_numbers) is
 13    begin
 14      for i in
 15      (
 16        select *
 17        from   shopping_stats
 18        where  (center, checkout_num) in
 19             ( select center, checkout_num
 20               from table(demo.parms_as_table(p_center, p_checkout))
 21             )
 22      )
 23      loop
 24        dbms_output.put_line(i.center||','||i.checkout_num||','||i.sales_total);
 25      end loop;
 26    end;
 27
 28  end;
 29  /

Package body created.


And of course then write a unit test 🙂




SQL> insert into shopping_stats values ('Perth',17,100);

1 row created.

SQL> insert into shopping_stats values ('Wembley',32,110);

1 row created.

SQL> insert into shopping_stats values ('Floreat',11,120);

1 row created.

SQL>
SQL>
SQL> set serverout on
SQL> declare
  2    p1 demo.t_strings;
  3    p2 demo.t_numbers;
  4  begin
  5    p1(1) := 'Perth';
  6    p2(1) := 17;
  7
  8    p1(2) := 'Wembley';
  9    p2(2) := 32;
 10
 11    demo.list(p1,p2);
 12  end;
 13  /
Wembley,32,110
Perth,17,100

PL/SQL procedure successfully completed.

SQL>
SQL> roll;
Rollback complete.