Re: optimizing join operation
Date: Wed, 29 Nov 2023 22:10:32 +0000
Message-ID: <CAGtsp8my3-aGRcmff9T+SFmGpfuDwiEfMYazufXDnSvdY2x_rg_at_mail.gmail.com>
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
table1
table2
select * from table(dbms_xplan.display_cursor(format=>'allstats last'));
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
On Wed, 29 Nov 2023 at 13:09, Laurentiu Oprea <laurentiu.oprea06_at_gmail.com>
wrote:
> Dear all,
from
left outer join
on
length(table2.colx) <= length(table1.coly)
and reverse(table1.coly) like reverse(table2.colx) || '%'
order by
table1.id, table2.id
/
| 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 | | | |
-------------------------------------------------------------------------------------------------------------------------------------
Jonathan Lewis
>
> 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 - 23:10:32 CET