slot to see if the answer exists. In this case,
it will not, so Oracle Database must run the
scalar subquery with the input of 10 to get
the answer. If that answer (count) is 42, the
hash table may look something like this:
Select count(*) from emp where emp.deptno = :deptno
:deptno Count(*)
…. …
10 42
…. ….
You’ll have saved the DEPTNO value of
10 and the answer (count) of 42 in some
slot—probably not the first or last slot, but
whatever slot the hash value 10 is assigned
to. Now suppose the second row you get
back from the PROJEC TS table includes a
DEPTNO value of 20. Oracle Database will
again look in the hash table after assigning
the value 20, and it will discover “no result
in the cache yet.” So it will run the scalar
subquery, get the result, and put it into the
hash table cache. Now the cache may look
like this:
Code Listing 1: PL/SQL function f
SQL> create or replace function f( x in varchar2 )
return number
2 as
3 begin
4 dbms_application_info.set_client_info
(userenv('client_info')+ 1 );
5 return length(x);
6 end;
7 /
Function created.
Code Listing 2: Demonstrating once-per-row repetition in function f
SQL> begin
2 :cpu := dbms_utility.get_cpu_time;
3 dbms_application_info.set_client_info(0);
4 end;
5 /
PL/SQL procedure successfully completed.
SQL> select owner, f(owner) from stage;
…
72841 rows selected.
SQL> select
2 dbms_utility.get_cpu_time-:cpu cpu_hsecs,
3 userenv('client_info')
4 from dual;
Select count(*) from emp where emp.deptno = :deptno
:deptno Count(*)
……
10 42
…. ….
20 55
……
Now suppose the query returns a third
row and it again includes a DEPTNO value
of 10. This time, Oracle Database will see
DEPTNO = 10, find that it already has that
value in the hash table cache, and will
simply return 42 from the cache instead
of executing the scalar subquery. In fact, it
will never have to run that scalar subquery
for the DEPTNO values of 10 or 20 again for
that query—it will already have the answer.
What happens if the number of unique
DEPTNO values exceeds the size of the
hash table? What if there are more than
255 values? Or, more generally, if more
than one DEPTNO value is assigned to the
same slot in the hash table, what happens
in a hash collision?
The answer is the same for all these
questions and is rather simple: Oracle
CPU_HSECS USERENV('CLIENT_INFO')
—————————————— ————————————————————————————————
118 72841
Database will not be able to cache the
second or nth value to that slot in the
hash table. For example, what if the third
row returned by the query contains the
DEPTNO = 30 value? Further, suppose that
DEPTNO = 30 is to be assigned to exactly
the same hash table slot as DEPTNO = 10.
The database won’t be able to effectively
cache DEPTNO = 30 in this case—the value
will never make it into the hash table. It
will, however, be “partially cached.” Oracle
Database still has the hash table with all
the previous executions, but it also keeps
the last scalar subquery result it had “next
to” the hash table. That is, if the fourth row
also includes a DEPTNO = 30 value, Oracle
Database will discover that the result is not
in the hash table but is “next to” the hash
table, because the last time it ran the scalar
subquery, it was run with an input of 30. On
the other hand, if the fourth row includes
a DEPTNO = 40 value, Oracle Database will
run the scalar subquery with the DEPTNO
= 40 value (because it hasn’t seen that
value yet during this query execution) and
overwrite the DEPTNO = 30 result. The next
time Oracle Database sees DEPTNO = 30
in the result set, it’ll have to run that scalar
subquery again.
