I think you missed the minus sign on the substr function. Easy to not see.
so … t1.coly like ‘%’||t2.colx, but I haven’t seen a plan improvement from a leading wildcard.
a legitimate filter would be and … length(t1.coly) < length(t2.colx)
From: Andy Sayer [mailto:andysayer_at_gmail.com]
Sent: Wednesday, November 29, 2023 10:35 AM
To: mwf_at_rsiz.com
Cc: ORACLE-L; laurentiu.oprea06_at_gmail.com
Subject: Re: optimizing join operation
I would try
table1 left outer join table2 on table2.colx = substr(table1.coly, - length(table2.colx))
And table1.coly like table2.colx||'%'
You might need a bit of hinting, and/or an expansion to make it an inner join + a not exists.
The key is to not mix tables on either side of your join condition. Equality is always preferred but here a like should be able to transform into a range filter,
Thanks,
Andy
On Wed, 29 Nov 2023 at 07:23, Mark W. Farnham <mwf_at_rsiz.com> wrote:
So you have an equality requirement that can never be true if t1.coly is null.
Whether or not a union all of t1 where t1.coly is null with nulls supplied for the column values of t2
and the same statement using the inline view of t1 where t1.coly is not null is faster is a question of data.
Likewise if t2.colx is null, that is never going to match, either, so you could pre-trim the returns from t2 when colx is null in the outer join. I don’t know whether the optimizer handed that is not null restriction and a single column index on t2.colx [or even the functional index on the length(t2.colx)] would optimize the statement in the obvious way.
I’d fool around with things like that, making quite certain the union all could never include a row from t1 twice or leave out a row from t1, since you need exactly all the rows from t1 once each.
Adding a functional index on length(t2.colx) presents the optimizer with an opportunity without changing the text of the query at all.
Adding just a plain index on t2.colx as a single column might also give a better plan without changing the text of the query.
I’m not sure whether the CBO will pick up those opportunities, but it might.
mwf
