Find longest match

[farhy]

Hi,

I have a table A (=30 Mill Record) with a String,
that I want to compare with a table B (200 Records).

I want to find the longest String from B that matches with the
String from A.

My Solution:
Select Case When Substr(A.StringA,1,3) = (Select B.Substr from B
Where Substr(A.StringA,1,3)=B.Substr
)
When Substr(A.StringA,1,2) = (Select B.Substr from B
Where Substr(A.StringA,1,2)=B.Substr
)
When Substr(A.StringA,1,1) = (Select B.Substr from B
Where Substr(A.StringA,1,1)=B.Substr
)
Else '0'
END
From A

Is there a solution which is more performant, because this takes very, very long :-(

I use oracle, with no Index on this tables.

so long
farhy

 

[dnoeth]

Hi farhy,
sounds like matching phone numbers to destination tags.

It's hard to find a performant query for that problem.
Following query (yet there are more efficient solutions) uses the shortest possible SQL:

SELECT
  c.called_no
  ,p.Destination_Tag
FROM
 (
  SELECT
    c.called_no
    ,p.Destination_Tag
    ,RANK() OVER (PARTITION BY c.called_no 
                  ORDER BY p.pattern DESC) rnk
  FROM
    CDRs c
      LEFT JOIN Prefixes p
        ON c.called_no like p.pattern || '%'
 ) dt
WHERE rnk = 1

I don't know if it's efficient on Oracle, but it would be on Teradata

Dieter

 

[farhy]

Hi Dieter,

thank you very much, I tried this it takes about 50 mins.

But I found a better solutions, with takes 10 mins on Oracle 9i, the secret was a hash join.

Create table tmp1
as
SELECT /*-+ PARALLEL(a,8) USE_HASH(a p) */
a.ROWID AS myrow
, a.called_no
, p.Destination_Tag
FROM CDRs a
,Prefixes p
WHERE SUBSTR(a.called_no,1,1) = p.Destination_Tag
OR SUBSTR(a.called_no,1,2) = p.Destination_Tag
OR SUBSTR(a.called_no,1,3) = p.Destination_Tag
OR SUBSTR(a.called_no,1,4) = p.Destination_Tag
OR SUBSTR(a.called_no,1,5) = p.Destination_Tag
OR SUBSTR(a.called_no,1,6) = p.Destination_Tag
OR SUBSTR(a.called_no,1,7) = p.Destination_Tag
OR SUBSTR(a.called_no,1,8) = p.Destination_Tag

Then I took the longest "Destination_Tag" via the analytical function rank.

Thank you very much Dieter for you input.

best regards
farhy