0

To improve the performance of the following query we have to remove the OR clause and use UNION. OR clause does not allow the index to be considered that is why we need to use UNION instead. Kindly let me know if there is any other better way to improve the performance of this query and avoid using OR clause? 

        SELECT *                    
            FROM  A
            LEFT OUTER JOIN  B
                ON A.NBR_CH = B.NBR_CH
            LEFT OUTER JOIN C
                ON B.ID = C.ID                  
            WHERE A.LIS IN (:IdList)
                AND ((C.TYP_C = :Type
                AND C.R_NBR LIKE :rNbr)
                OR (A.R_NBR LIKE :rNbr))        
            WITH UR
Gordon Linoff
  • 1,242,037
  • 58
  • 646
  • 786
user6804978
  • 37
  • 1

2 Answers2

0

Would it be like this?

 SELECT *                    
            FROM  A
            LEFT OUTER JOIN  B
                ON A.NBR_CH = B.NBR_CH
            LEFT OUTER JOIN C
                ON B.ID = C.ID                  
            WHERE A.LIS IN (:IdList)
                AND ((C.TYP_C = :Type
                AND C.R_NBR LIKE :rNbr))        
            WITH UR
    UNION

 SELECT *                    
            FROM  A
            LEFT OUTER JOIN  B
                ON A.NBR_CH = B.NBR_CH
            LEFT OUTER JOIN C
                ON B.ID = C.ID                  
            WHERE A.LIS IN (:IdList)
                AND A.R_NBR LIKE :rNbr       
            WITH UR
Edgar
  • 339
  • 1
  • 2
  • 10
0

The above is correct. But I wanted to know if there is any other way to do this without using the UNION

user6804978
  • 37
  • 1