Performance issues in queries when "OR" is used inside WHERE

Question

This query executes in 700 ms:

SELECT pcl.COL1,
       pcl.COL2,
       pcl.COL3,
       pcl.COL4,
       (SELECT cc.COL5
          FROM T3 cc
          JOIN T4 cvp
            ON cvp.COL10 = cc.COL10
           AND cvp.COL2 = pcl.COL2) AS COL10,
       pp.COL11
FROM T1 ba
JOIN T2 pcl
  ON pcl.COL1 = ba.COL1
 AND (pcl.COL2 = ba.COL2 OR ba.COL2 = 0)
JOIN T5 pscl
  ON pscl.COL2 = pcl.COL2
 AND pscl.COL4 <> 3
 AND (pscl.COL6 = ba.COL6 OR ba.COL6 = 'ALL')
LEFT JOIN T6 bba
  ON bba.COL7 = ba.COL7
LEFT JOIN T7 bsa
  ON bsa.COL7 = ba.COL7
LEFT JOIN T8 pp
  ON pp.COL1 = pcl.COL1
WHERE (ba.COL8 = 617617 OR bsa.COL8 = 617617 OR bba.COL9 = 617617)

The issue is:

(ba.COL8 = 617617 OR bsa.COL8 = 617617 OR bba.COL9 = 617617)

COL8 and COL9 have index. if I keep any 1 of the 3 conditions then it takes 30 ms to execute. If I keep 2 of them then it takes 300 ms to execute. But I need all 3 of them. How can I improve/rewrite my SELECT?

Answer 1

You are not using the bba or bsa tables except as filters so you could change from using JOINs to using EXISTS:

SELECT pcl.COL1,
       pcl.COL2,
       pcl.COL3,
       pcl.COL4,
       (SELECT cc.COL5
          FROM T3 cc
          JOIN T4 cvp
            ON cvp.COL10 = cc.COL10
           AND cvp.COL2 = pcl.COL2) AS COL10,
       pp.COL11
FROM   T1 ba
       JOIN T2 pcl
       ON    pcl.COL1 = ba.COL1
         AND (pcl.COL2 = ba.COL2 OR ba.COL2 = 0)
       JOIN T5 pscl
       ON     pscl.COL2 = pcl.COL2
          AND pscl.COL4 <> 3
          AND (pscl.COL6 = ba.COL6 OR ba.COL6 = 'ALL')
       LEFT JOIN T8 pp
       ON pp.COL1 = pcl.COL1
WHERE  ba.COL8 = 617617
OR     EXISTS(SELECT 1 FROM T6 bba WHERE bba.COL7 = ba.COL7 AND bba.COL9 = 617617)
OR     EXISTS(SELECT 1 FROM T7 bsa WHERE bsa.COL7 = ba.COL7 AND bsa.COL8 = 617617)