How to use OR statement in a left join without comprimising SQL performance-CodePudding

I have a connect and disconnect table. I need to find out how many people disconnected and reconnected have at least one matching phone number. Both tables have columns Telephone_Number, Primrary_Number and Alternate_phone_number.

FROM CONN_UNIVERSE CU
LEFT JOIN DISC_UNIVERSE DU
        ON (
        DU.TELEPHONE_NUMBER = CU.TELEPHONE_NUMBER
        OR DU.TELEPHONE_NUMBER = CU.PRIMARY_PHONE_NUMBER
        OR DU.TELEPHONE_NUMBER = CU.ALTERNATE_PHONE_NUMBER
      
        OR DU.PRIMARY_PHONE_NUMBER = CU.TELEPHONE_NUMBER
        OR DU.PRIMARY_PHONE_NUMBER = CU.PRIMARY_PHONE_NUMBER
        OR DU.PRIMARY_PHONE_NUMBER = CU.ALTERNATE_PHONE_NUMBER
      
        OR DU.ALTERNATE_PHONE_NUMBER = CU.TELEPHONE_NUMBER
        OR DU.ALTERNATE_PHONE_NUMBER = CU.PRIMARY_PHONE_NUMBER
        OR DU.ALTERNATE_PHONE_NUMBER = CU.ALTERNATE_PHONE_NUMBER)

I want to flag the rows where there is any match between different telephone numbers. This code keeps running and never finishes. On checking the query performance/profile it is considering this set of code as Cartesian join (54%).

How can I rewrite this code and get better performance?

CodePudding user response：

These are just many conditions where one is as good as the other. The DBMS cannot use any index, because in every condition you look at different columns. So, this is slow, and this is no surprise, and there is just one option I seem to be able to come up with:

If, and only if, there can be just one match in disc_universe (or you are fine with picking just one), then you could create six indexes, one for each column, and then you might be able to gain some speed with six outer joins and COALESCE. I don't know if it works, but it may be worth a try.

SELECT
  cu.some_column,
  COALESCE(du1.colx, du2.colx, du3.colx, du4.colx, du5.colx, du6.colx, du7.colx, du8.colx, du9.colx) AS colx,
  COALESCE(du1.coly, du2.coly, du3.coly, du4.coly, du5.coly, du6.coly, du7.coly, du8.coly, du9.coly) AS coly 
FROM conn_universe cu
LEFT JOIN disc_universe du1 ON du1.telephone_number = cu.telephone_number
LEFT JOIN disc_universe du2 ON du2.telephone_number = cu.primary_phone_number
LEFT JOIN disc_universe du3 ON du3.telephone_number = cu.alternate_phone_number
LEFT JOIN disc_universe du4 ON du4.primary_phone_number = cu.telephone_number
LEFT JOIN disc_universe du5 ON du5.primary_phone_number = cu.primary_phone_number
LEFT JOIN disc_universe du6 ON du6.primary_phone_number = cu.alternate_phone_number
LEFT JOIN disc_universe du7 ON du7.alternate_phone_number = cu.telephone_number
LEFT JOIN disc_universe du8 ON du8.alternate_phone_number = cu.primary_phone_number
LEFT JOIN disc_universe du9 ON du9.alternate_phone_number = cu.alternate_phone_number);

CodePudding user response：

Try this. Hopefully it reduces the number of joins, which improves performance. I also assume you need some columns (conn_info_columns) from conn_universe and some disc_info_columns from disc_universe as well as phone_type (TELEPHONE, PRIMARY_PHONE, ALTERNATE PHONE) from both.

with cte_conn_universe as (
select conn_info_columns, 'TELEPHONE'       as phone_type, telephone_number as phone_number from conn_universe where telephone_number is not null
union all
select conn_info_columns, 'PRIMARY_PHONE'   as phone_type, primary_phone_number as phone_number from conn_universe where primary_phone_number is not null
union all
select conn_info_columns, 'ALTERNATE_PHONE' as phone_type, alternate_phone_number as phone_number from conn_universe where alternate_phone_number is not null),
cte_disc_universe as (
select disc_info_columns, 'TELEPHONE'       as phone_type, telephone_number as phone_number from disc_universe where telephone_number is not null
union all
select disc_info_columns, 'PRIMARY_PHONE'   as phone_type, primary_phone_number as phone_number from disc_universe where primary_phone_number is not null
union all
select disc_info_columns, 'ALTERNATE_PHONE' as phone_type, alternate_phone_number as phone_number from disc_universe where alternate_phone_number is not null)
select coalesce(c.phone_number, d.phone_number) as phone_number,
       c.phone_type  as conn_phone_type,
       d.phone_type  as disc_phone_type,
       c.conn_info_columns,
       d.disc_info_columns
  from cte_conn_universe c
  full outer
  join cte_disc_universe d
    on c.phone_number = d.phone_number;