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@xxxxxxxxx>
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.
