Ah, yes. For leading wild cards, you’d need to inverse both strings so that
the wildcard can be trailing. This allows a range filter to be used.
Thanks,
Andy
On Wed, 29 Nov 2023 at 08:44, Mark W. Farnham <mwf@xxxxxxxx> wrote:
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@xxxxxxxxx]
*Sent:* Wednesday, November 29, 2023 10:35 AM
*To:* mwf@xxxxxxxx
*Cc:* ORACLE-L; laurentiu.oprea06@xxxxxxxxx
*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@xxxxxxxx> 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
*From:* oracle-l-bounce@xxxxxxxxxxxxx [mailto:
oracle-l-bounce@xxxxxxxxxxxxx] *On Behalf Of *Laurentiu Oprea
*Sent:* Wednesday, November 29, 2023 8:08 AM
*To:* ORACLE-L (oracle-l@xxxxxxxxxxxxx)
*Subject:* optimizing join operation
Dear all,
Is there a way to optimize a join in the shape of :
table1 left outer join table2 on table2.colx = substr(table1.coly, -
length(table2.colx))
seems like on my 12.1 version DB the optimizer will only pick a NL outer
with full scan of table2
Appreciate your answers.
