Re: optimizing join operation

Appreciate all your answers. I`ll try to see if maybe I can find something in the business rules or in data format to avoid the join in this shape, if I have any interesting development I`ll let you know.

Thank you.

În joi, 30 nov. 2023 la 00:11, Jonathan Lewis <jlewisoracle_at_gmail.com> a scris:

> There's not really a lot you can do to optimise this query as you have to
> compare every row in t2 with each row in t1 because there's no logical way
> for Oracle to check "does the t1 value end with the t2 value" without
> fetching the t2 value. (The only filter that could short-circuit the
> testing is by comparing the lengths through an index range scan before
> checking the values.
>
> This might work:
>
> create index t2_i1 on t2(length(colx), reverse(colx));
>
> select /*+ leading(table1 table2) use_nl(table2) index(table2) */
> table1.id, table2.id, table1.coly, table2.colx
> from
> table1
> left outer join
> table2
> on
> length(table2.colx) <= length(table1.coly)
> and reverse(table1.coly) like reverse(table2.colx) || '%'
> order by
> table1.id, table2.id
> /
>
> select * from table(dbms_xplan.display_cursor(format=>'allstats last'));
>
>
> -------------------------------------------------------------------------------------------------------------------------------------
> | Id | Operation | Name | Starts | E-Rows |
> A-Rows | A-Time | Buffers | OMem | 1Mem | Used-Mem |
>
> -------------------------------------------------------------------------------------------------------------------------------------
> | 0 | SELECT STATEMENT | | 1 |
> | 32 |00:00:00.01 | 27 | | | |
> | 1 | SORT ORDER BY | | 1 | 20
> | 32 |00:00:00.01 | 27 | 4096 | 4096 | 4096 (0)|
> | 2 | NESTED LOOPS OUTER | | 1 | 20
> | 32 |00:00:00.01 | 27 | | | |
> | 3 | TABLE ACCESS FULL | TABLE1 | 1 | 20
> | 22 |00:00:00.01 | 23 | | | |
> | 4 | TABLE ACCESS BY INDEX ROWID BATCHED| TABLE2 | 22 | 1
> | 25 |00:00:00.01 | 4 | | | |
> |* 5 | INDEX RANGE SCAN | T2_I1 | 22 | 1
> | 25 |00:00:00.01 | 3 | | | |
>
> -------------------------------------------------------------------------------------------------------------------------------------
>
> Predicate Information (identified by operation id):
> ---------------------------------------------------
>
> 5 - access("TABLE2"."SYS_NC00003$"<=LENGTH("TABLE1"."COLY"))
> filter(REVERSE("TABLE1"."COLY") LIKE "TABLE2"."SYS_NC00004$"||'%')
>
>
> Whether this improves the performance or not depends very much on what the
> data looks like - but I think it will give the same result as the original.
>
> Regards
> Jonathan Lewis
>
>
> On Wed, 29 Nov 2023 at 13:09, Laurentiu Oprea <laurentiu.oprea06_at_gmail.com>
> wrote:
>
>> 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