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. 
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.
