RE: optimizing join operation
Date: Wed, 29 Nov 2023 10:22:04 -0500
Message-ID: <454301da22d7$cfa60d10$6ef22730$_at_rsiz.com>
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_at_freelists.org [mailto:oracle-l-bounce_at_freelists.org] On Behalf Of Laurentiu Oprea
Sent: Wednesday, November 29, 2023 8:08 AM
To: ORACLE-L (oracle-l_at_freelists.org)
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.
--
http://www.freelists.org/webpage/oracle-l
Received on Wed Nov 29 2023 - 16:22:04 CET