RE: optimizing join operation
Date: Wed, 29 Nov 2023 11:43:24 -0500
Message-ID: <45a001da22e3$2f273470$8d759d50$_at_rsiz.com>
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)
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.
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.
I’m not sure whether the CBO will pick up those opportunities, but it might.
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
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-lReceived on Wed Nov 29 2023 - 17:43:24 CET