I’ve written earlier about how to calculate a query’s scalar subquery cache size, and I’ve had an interesting thought since the last blog entry. The last entry was all about scalar subqueries that appear in a query’s select clause, but the same mechanism exists for an unnested subquery that uses a FILTER operation.
My thought was this–If I have a query that uses 1 table within a FILTER operation, I’d have 1024 buckets (same as what we had with the scalar subquery cache size) but…occasionally, a FILTER operation will call multiple tables. My thought is–if I have more than one table in my scalar subquery…do I still only get 1024 buckets total? Or would I get 1024 buckets for each subquery? In other words…do I get 1024 buckets for each FILTER step in the plan? Or each table that the FILTER step invokes?
I decided to figure it out with a similar setup from what I had last time:
create table test_subq_call
(
id varchar2(100) constraint pk_test_subq_call primary key
, numb_funct_calls number default '0' not null
);
create or replace function test_subq_cache (p_n in number)
return number
authid definer
is
pragma autonomous_transaction;
begin
update test_subq_call
set numb_funct_calls = numb_funct_calls + 1
where id = test_subq_cache.p_n;
if sql%rowcount != 1 then
raise_application_error(-20001, 'Updated zero rows :(');
end if;
commit;
return null;
end test_subq_cache;
/
create or replace function test_subq_cache (p_n in number, p_l in varchar2)
return number
authid definer
is
pragma autonomous_transaction;
begin
update test_subq_call
set numb_funct_calls = numb_funct_calls + 1
where id = test_subq_cache.p_n || test_subq_cache.p_l;
if sql%rowcount != 1 then
raise_application_error(-20001, 'Updated zero rows :(');
end if;
commit;
return null;
end test_subq_cache;
/
create or replace function generate_rows
return t_number
pipelined
authid definer
is
pragma autonomous_transaction;
CONST_MAX_NUMBER_OF_TESTS constant pls_integer := 2000000;
begin
for v_rownum in 1 .. CONST_MAX_NUMBER_OF_TESTS + 1 loop
insert into test_subq_call(id) values (v_rownum || 'a');
insert into test_subq_call(id) values (v_rownum || 'b');
commit;
pipe row(v_rownum);
if v_rownum > 1 then
pipe row (v_rownum - 1);
end if;
end loop;
end generate_rows;
/
create table zero1
as
select 0 zero from dual;
create table zero2
as
select 0 zero from dual;This gives us the needed setup to create the following query:/
select count(a.column_value)
from table(generate_rows()) a
where exists (select *
from zero1 b
where b.zero = test_subq_cache(a.column_value, 'a'))
or
exists (select *
from zero2 c
where c.zero = test_subq_cache(a.column_value, 'b'))
;Running this query allows for Oracle to generate 2 rows in our TEST_SUBQ_CALL table. Each record is anticipated to be called twice…unless subquery caching is used, and we only end up calling it once.
After running the above query, I ran this query to count up the number of anticipated calls, in comparison with the actual number of calls:
select total_anticipated_func_calls - actual_func_calls number_of_caching_hash_buckets
from (select count(*) *2 total_anticipated_func_calls
, sum(numb_funct_calls) actual_func_calls
from test_subq_call
where id not like '2000001%');The result was 2048 buckets (1024 * 2). Unsurprisingly, when I re-rigged the same test for 3 separate tables, Oracle allocated 3072 buckets (1024 * 3).
So the answer is, for modern versions of Oracle, I imagine you get however many tables are in your filter clause * 1024 for you subquery caching.
Free Oracle SQL Tuning Guide
Checkout my FREE guide, 7 SQL Tuning Secrets You Can Use Immediately, Even If You’ve Never Tuned a Query In Your Life!
Get it here: tuningsql.com/secrets
