Jump to content
Kusrbo

Fuzzy Logic Matching in where clause - Poor Performance

Recommended Posts

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,

Share this post


Link to post
Share on other sites

Hi Kusr o, and welcome to the forum!

First, gather the SQL execution plan for the statement, so that you can see how Oracle is accessing the data:

http://www.dba-oracle.com/t_sql_execution_plan_tips.htm

I suspect that you will see a full table scan.

Next, you want to try a matching function-based index:

http://www.dba-oracle.com/t_function_based_indexes.htm

Good luck!

Share this post


Link to post
Share on other sites

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now

×