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.
