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
>
>
>
>
>
> *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