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