Scalar Subquery Caching

rajesh@10GR2> create table t as select object_id,object_name,owner from all_objects;

Table created.

Elapsed: 00:00:01.50
rajesh@10GR2> exec dbms_stats.gather_table_stats(user,'T');

PL/SQL procedure successfully completed.

Elapsed: 00:00:00.31
rajesh@10GR2>
rajesh@10GR2> create or replace function f(x in varchar2)
  2  return number
  3  is
  4  begin
  5     dbms_application_info.set_client_info(userenv('client_info')+1);
  6     return (length(x));
  7  end f;
  8  /

Function created.

Elapsed: 00:00:00.18
rajesh@10GR2> select count(object_name),count(distinct object_name)
  2  from t;

COUNT(OBJECT_NAME) COUNT(DISTINCTOBJECT_NAME)
------------------ --------------------------
             56228                      31794

Elapsed: 00:00:00.09
rajesh@10GR2>
rajesh@10GR2> exec dbms_application_info.set_client_info(0);

PL/SQL procedure successfully completed.

Elapsed: 00:00:00.00
rajesh@10GR2>
rajesh@10GR2> set autotrace traceonly statistics;
rajesh@10GR2> select object_name,f(object_name) as obj_length from t;

56228 rows selected.

Elapsed: 00:00:02.46

Statistics
----------------------------------------------------------
         17  recursive calls
          0  db block gets
        706  consistent gets
          0  physical reads
          0  redo size
    1337669  bytes sent via SQL*Net to client
       4510  bytes received via SQL*Net from client
        376  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
      56228  rows processed

rajesh@10GR2> set autotrace off;
rajesh@10GR2>
rajesh@10GR2> select userenv('client_info') from dual;

USERENV('CLIENT_INFO')
----------------------------------------------------------------
56228

Elapsed: 00:00:00.01

As you can see the function called ONCE - PER - ROW over and over again.

rajesh@10GR2> exec dbms_application_info.set_client_info(0);

PL/SQL procedure successfully completed.

Elapsed: 00:00:00.01
rajesh@10GR2>
rajesh@10GR2> set autotrace traceonly statistics;
rajesh@10GR2> select object_name, (select f(object_name) from dual) as obj_length from T;

56228 rows selected.

Elapsed: 00:00:02.09

Statistics
----------------------------------------------------------
          1  recursive calls
          0  db block gets
        681  consistent gets
          0  physical reads
          0  redo size
    1337669  bytes sent via SQL*Net to client
       4510  bytes received via SQL*Net from client
        376  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
      56228  rows processed

rajesh@10GR2> set autotrace off;
rajesh@10GR2>
rajesh@10GR2> select userenv('client_info') from dual;

USERENV('CLIENT_INFO')
----------------------------------------------------------------
40462

Now its called 40462 times - the effect of this scalar sub query caching is now apparent.

Similar demonstration using Inline views.

rajesh@10GR2> exec dbms_application_info.set_client_info(0);

PL/SQL procedure successfully completed.

Elapsed: 00:00:00.01
rajesh@10GR2>
rajesh@10GR2> set autotrace traceonly statistics;
rajesh@10GR2> select object_name,f(object_name) as obj_length from ( select object_name from t order by object_name);

56228 rows selected.

Elapsed: 00:00:02.34

Statistics
----------------------------------------------------------
          1  recursive calls
          0  db block gets
        308  consistent gets
          0  physical reads
          0  redo size
    1137252  bytes sent via SQL*Net to client
       4510  bytes received via SQL*Net from client
        376  SQL*Net roundtrips to/from client
          1  sorts (memory)
          0  sorts (disk)
      56228  rows processed

rajesh@10GR2> set autotrace off;
rajesh@10GR2>
rajesh@10GR2> select userenv('client_info') from dual;

USERENV('CLIENT_INFO')
----------------------------------------------------------------
56228

Elapsed: 00:00:00.01
rajesh@10GR2> exec dbms_application_info.set_client_info(0);

PL/SQL procedure successfully completed.

Elapsed: 00:00:00.01
rajesh@10GR2>
rajesh@10GR2> set autotrace traceonly statistics;
rajesh@10GR2> select object_name, (select f(object_name) from dual) as obj_length from ( select object_name from t order by object_name);

56228 rows selected.

Elapsed: 00:00:02.14

Statistics
----------------------------------------------------------
          1  recursive calls
          0  db block gets
        308  consistent gets
          0  physical reads
          0  redo size
    1137252  bytes sent via SQL*Net to client
       4510  bytes received via SQL*Net from client
        376  SQL*Net roundtrips to/from client
          1  sorts (memory)
          0  sorts (disk)
      56228  rows processed

rajesh@10GR2> set autotrace off;
rajesh@10GR2> select userenv('client_info') from dual;

USERENV('CLIENT_INFO')
----------------------------------------------------------------
40462

Elapsed: 00:00:00.03
rajesh@10GR2>