The ODC SQL and PL/SQL forum came up with an example a couple of days ago that highlighted an annoying limitation in the optimizer’s handling of table functions. The requirement was for a piece of SQL that would generate “installments” information from a table of contract agreements and insert into another table any installments that were not yet recorded there.
The mechanism to turn a single row of contract data into a set of installments was a (optionally pipelined) table function that involved some business logic that (presumably) dealt with the timing and size of the installments. The final SQL to create the data that needed to be inserted was reported as follows (though it had clearly been somewhat modified):
select
y.*,
trunc(sysdate,'mm'),
user
from
table_a a
join
table(
my_function(
a.loan_acct_nbr,
a.start_dt,
a.maturity_dt,
a.num_of_terms
)
) y
on
a.loan_acct_nbr = y.loan_acct_nbr
where
a.yr_mnth_cd = to_char(add_months(sysdate,-1),'yyyymm') -- last month
and a.loan_typ = 'ABC'
and not exists (
select 1
from
final_load_table l
where
l.loan_acct_nbr = y.loan_acct_nbr
and l.yr_mnth_cd = y.yr_mnth_cd
)
;
In this query, table_a is the set of contracts, final_load_table is the set of installments, and my_function() is the pipelined function returning a table of installments derived from the start date, maturity date, and number of installments for a contract. The query needs a “not exists” subquery to eliminate any installments that are already known to the database. Once this query is operating efficiently it could be used either to drive a PL/SQL loop or (generally the better strategy) to do a simple “insert as select”.
We were told that the function would return around 60 rows per contract; that the starting size of the final_load_table would be about 60M rows, and the size of the result set from the initial join would be about 60M or 70M rows (which suggests about 1M rows acquired from table_a).
The owner of this problem seemed to be saying that the query had returned no data after 4 or 5 hours – which suggests (a) the optimizer chose a bad plan and/or (b) the PL/SQL function is working very inefficiently and/or (c) the join had generated a vast amount of data but the effect of the subquery was to discard all of it .
Clearly we needed to see an execution plan (preferably from SQL Monitor) and be given have some idea of how much of the 60M/70M rows predicted for the join would be discarded because it already existed.
The owner did send us an execution plan – which included a very obvious threat and suggested a couple of optimizer errors – but supplied it as a picture rather than a flat text.
You’ll notice, of course, that the tables and columns have changed from the original statement. More significantly, though, there are serious problems with the numbers – the estimated row count for the basic join is only 5,326 rather than 50 Million which, at first sight, is probably why the optimizer has decided that a filter subquery (operation 1) to do an index-only probe (operation 5) is a good way of handling the subquery. Perhaps if the estimates had been a little more accurate (e.g. through the addition of a couple of column groups or, if necessary, by opt_estimate() or cardinality() hints) the subquery would have been unnested and turned into a hash anti-join.
I suggested a quick test of a suitable cardinality() hint – but ran up a little model to check that I’d got the hint right – and found that I had but it wasn’t going to help. So I decided to write up the model (and a possible solution for the owner of the problem) in this blog note.
Here’s the code to create the set of objects I’m going to work with. The naming follows the naming in the original statement of the problem suggested by the owner:
rem
rem Script: table_function_plan.sql
rem Author: Jonathan Lewis
rem Dated: Jan 2020
rem Purpose:
rem
rem Last tested
rem 19.3.0.0
rem 12.2.0.1
rem
create table table_a (
loan_acct_nbr number,
yr_mnth_cd varchar2(6),
start_dt date,
maturity_dt date ,
number_of_terms number,
constraint ta_pk primary key(loan_acct_nbr, yr_mnth_cd)
)
;
execute dbms_random.seed(0)
insert /*+ append */
into table_a
with generator as (
select rownum id
from dual
connect by
level <= 4000 -- > comment to avoid wordpress issue
)
select
trunc(dbms_random.value(1e9, 2e9)),
to_char(sysdate-(365-mod(rownum,365)),'yyyymm'),
sysdate-(365-mod(rownum,365)),
sysdate+(1500+mod(rownum,365)),
60
from
generator v1,
generator v2
where
rownum <= 1e4 -- > comment to avoid WordPress format issue
;
create table final_load_table_l(
loan_acct_nbr number,
yr_mnth_cd varchar2(6),
v1 varchar2(10),
padding varchar2(200),
constraint lt_pk primary key(loan_acct_nbr, yr_mnth_cd)
)
;
insert /*+ append */ into final_load_table_l
with generator as (
select rownum id
from dual
connect by
level <= 4000 -- > comment to avoid wordpress issue
)
select
trunc(dbms_random.value(1e9, 2e9)),
to_char(sysdate-(365-mod(rownum,365)),'yyyymm'),
lpad(rownum,10),
lpad('x',200,'x')
from
generator v1,
generator v2
where
rownum <= 1e4 -- > comment to avoid WordPress format issue
;
begin
dbms_stats.gather_table_stats(
ownname => null,
tabname => 'table_a',
method_opt => 'for all columns size 1'
);
dbms_stats.gather_table_stats(
ownname => null,
tabname => 'final_load_table_l',
method_opt => 'for all columns size 1'
);
end;
/
create type my_row_type as object (
loan_acct_nbr number,
yr_mnth_cd varchar2(6),
v1 varchar2(10),
padding varchar2(200)
);
/
create type my_table_type as table of my_row_type;
/
create or replace function my_function (
i_loan_acct_nbr in number,
i_yr_mnth_cd in varchar2,
i_start_dt in date,
i_maturity_dt in date,
i_number_of_terms in number
) return my_table_type pipelined
as
begin
for i in 1..i_number_of_terms loop
pipe row (
my_row_type(
i_loan_acct_nbr,
to_char(i_start_dt+32*i,'yyyymm'),
i,
lpad('x',200,'x')
)
);
end loop;
return;
end;
/
I was planning to create some large tables – hence the option to generate 16M rows from my generator CTEs – but I discovered the critical problem almost as soon as I had some data and code in place, so I didn’t need to go large.
I’ve had to create an object type and table type in order to create a pipelined function that returns the table type by piping rows of the object type. The data I’ve created, and the way the function generates data probably doesn’t bear much resemblance to the real system of course, but I don’t think it needs to be very close to make the crucial point.
Here’s the text of the select statement the OP wants to run, with the execution plan I got from my data set after running the query and pulling the plan from memory:
alter session set statistics_level = all;
select
/*+ find this 1 */
y.*
from
table_a a,
table(my_function(
a.loan_acct_nbr,
a.yr_mnth_cd,
a.start_dt,
a.maturity_dt,
a.number_of_terms
)) y
where
a.yr_mnth_cd = '202001'
and not exists (
select
/*+ unnest */
null
from final_load_table_l l
where l.loan_acct_nbr = y.loan_acct_nbr
and l.yr_mnth_cd = y.yr_mnth_cd
)
;
--------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows | Cost (%CPU)| A-Rows | A-Time | Buffers |
--------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | 5059K(100)| 14580 |00:00:00.15 | 16330 |
|* 1 | FILTER | | 1 | | | 14580 |00:00:00.15 | 16330 |
| 2 | NESTED LOOPS | | 1 | 6283K| 10421 (8)| 14580 |00:00:00.11 | 335 |
|* 3 | TABLE ACCESS FULL | TABLE_A | 1 | 769 | 10 (10)| 243 |00:00:00.01 | 297 |
| 4 | COLLECTION ITERATOR PICKLER FETCH| MY_FUNCTION | 243 | 8168 | 14 (8)| 14580 |00:00:00.10 | 38 |
|* 5 | INDEX UNIQUE SCAN | LT_PK | 14580 | 1 | 1 (0)| 0 |00:00:00.02 | 15995 |
--------------------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter( IS NULL)
3 - filter("A"."YR_MNTH_CD"='202001')
5 - access("L"."LOAN_ACCT_NBR"=:B1 AND "L"."YR_MNTH_CD"=:B2)
I’ve put in a hint to tell the optimizer to unnest the subquery – and it didn’t. Oracle does not ignore hints (unless they’re illegal, or out of context, or the optimizer never got to them, or you’ve found a bug) so why did Oracle appear to ignore this hint? There’s a really nice feature in 19.3 execution plans – you can request a hint report for a plan, and here’s the hint report for this query (ignore the bits about “find this” being an error):
Hint Report (identified by operation id / Query Block Name / Object Alias):
Total hints for statement: 3 (U - Unused (1), E - Syntax error (2))
---------------------------------------------------------------------------
0 - SEL$1
E - find
E - this
5 - SEL$3
U - unnest / Invalid correlated predicates
I put in an /*+ unnest */ hint to unnest the subquery, and I’ve been told that the predicates are not valid. The only thing about them that could be invalid is that they come from a pipelined function that has returned an object type. The pipelined function does not behave exactly like a table. But this gives me a clue about forcing the unnest to happen – hide the pipelined function inside a non-mergeable view.
select
/*+ find this 2 */
v.*
from (
select /*+ no_merge */
y.*
from
table_a a,
table(my_function(
a.loan_acct_nbr,
a.yr_mnth_cd,
a.start_dt,
a.maturity_dt,
a.number_of_terms
)) y
where
a.yr_mnth_cd = '202001'
) v
where not exists (
select
/*+ unnest */
null
from final_load_table_l l
where l.loan_acct_nbr = v.loan_acct_nbr
and l.yr_mnth_cd = v.yr_mnth_cd
)
/
------------------------------------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows | Cost (%CPU)| A-Rows | A-Time | Buffers | OMem | 1Mem | Used-Mem |
------------------------------------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | 10628 (100)| 14580 |00:00:00.12 | 387 | | | |
|* 1 | HASH JOIN RIGHT ANTI | | 1 | 6283K| 10628 (10)| 14580 |00:00:00.12 | 387 | 1878K| 1878K| 2156K (0)|
| 2 | INDEX FAST FULL SCAN | LT_PK | 1 | 10000 | 6 (17)| 10000 |00:00:00.01 | 91 | | | |
| 3 | VIEW | | 1 | 6283K| 10371 (8)| 14580 |00:00:00.11 | 296 | | | |
| 4 | NESTED LOOPS | | 1 | 6283K| 10371 (8)| 14580 |00:00:00.10 | 296 | | | |
|* 5 | TABLE ACCESS FULL | TABLE_A | 1 | 769 | 10 (10)| 243 |00:00:00.01 | 296 | | | |
| 6 | COLLECTION ITERATOR PICKLER FETCH| MY_FUNCTION | 243 | 8168 | 13 (0)| 14580 |00:00:00.10 | 0 | | | |
------------------------------------------------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - access("L"."LOAN_ACCT_NBR"="V"."LOAN_ACCT_NBR" AND "L"."YR_MNTH_CD"="V"."YR_MNTH_CD")
5 - filter("A"."YR_MNTH_CD"='202001')
I’ve wrapped the basic join between table_a and the table function in an inline view called (unimaginatively) v, and I’ve added a /*+ no_merge */ hint to that inline view. So the main query becomes a select from a non-mergeable view with a “not exists” subquery applied to a couple of view columns. And Oracle thinks that that’s okay – and my unnest hint encouraged the optimizer to use a hash anti-join.
So here’s an option for the owner of the problem – but with a few outstanding questions: will a rewrite of their query in this form do the same, will the performance of the select be good enough, and will the subsequent “insert as select” keep the same driving plan.
There’s one significant detail to worry about: the build table in this hash (anti-)join is going to be constructed from 50M (load_acct_bfr, yr_mnth_cd) pairs – which means the workarea requirement is likely to be about 1.2GB for an optimial (i.e. in-memory) hash join; otherwise the join may spill to disc and do a lot of I/O – probably as a one-pass hash join.
(Did you notice,by the way, that the word storage appeared at operation 3 in the original plan? That suggests a nice big Exadata box; however, there’s no storage predicate in the Predicate Information section for that operation and you would have thought that lease_type = ‘US_SSP’ would be pushed to storage, so maybe this is a ZFS Pillar backing a less exotic server.)
Conclusion
Some (if not all) types of correlated subqueries behave badly if the correlation predicates involve table functions. But you may be able to work around the issue by hiding part of the query, including the table function, inside a non-mergeable inline view before applying the subquery to the view.
Footnote
When I realised that the table function was causing a problem unnesting I remembered that I had written about a similar problem a few years ago – after searching for a little while I discovered a draft note that I had started in September 2015 but had not quite finished; so I’ll be finishing it off and publishing it some time in the next few days. (Update: now published)
