Re: optimizing join operation

One thought that came to mind when I saw the request was that t2 might be a "legacy style" (i.e. badly designed) table that had a single column holding two (or more) pieces of information. If this is the case and you can find out how coly is created (e.g. some data crunch like client_countty || ~ || client_code supplied by an external system) then you might be able to define a virtual column that contains only the tail end that you need. Once you've got that you can index the virtual column and join on equality.

Regards
Jonathan Lewis

On Thu, 30 Nov 2023 at 08:17, Laurentiu Oprea <laurentiu.oprea06_at_gmail.com> wrote:

> 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