Jump to content

Kusrbo

Members
  • Content count

    1
  • Joined

  • Last visited

Community Reputation

0 Neutral

About Kusrbo

  • Rank
    Newbie
  1. Hi, Hope you can guide me here. I have a fuzzy matching requirement .. for ex; Table1 - T1Col1, T1col2, T1col3, T1col4, T1col5. Table 2 - T2Col1, T2col2, T2col3, T2col4, T2col5. so my requirement is - T1 - Its not necessary all fields i.e T1col1, T1col2, T1col3, T1col4, T1col5 are not nulls but there are scenarios where T1Col2 is populated and T1Col3, T1Col4 and 5 is null. Best case scenario here is all fields are not nulls and the worst case is except T1Col1 rest of the fields are nulls. - I came up with a fuzzy logic matching so that if atleast one field is matching then 'where' clause should pass through. select count(*) from T1, T2 where Nvl(T1COl1, nvl(T2Col1, 'x')) = nvl(T2Col1, 'x') and Nvl(T1COl2, nvl(T2Col2, 'x') ) = nvl(T2Col2, 'x') and Nvl(T1COl3, nvl(T2Col3, 'x')) = nvl(T2Col3, 'x') and Nvl(T1COl4, nvl(T2Col4, 'x')) = nvl(T2Col4 'x') and and substr(T1COl5, 1,1) = T2Col5 ; Record count in T1 and T2 are 243000 and 55000 respectively When I run the above statement, it takes 1426.809 seconds and gave me 11349 records. Looks like it is performance poor. Is that because of usage of substr or usage of too many nvl's in where clause? Can you help me here how can I improve my query performance or is there a better way of doing that matching? Thanks,
×