Need help on query tuning

Analytics
Enthusiast

Need help on query tuning

Hello All,

 

We are working on a performance tuning request. We have a complex view and part of it as below-

 

LEFT OUTER JOIN
table1 A
on
(b.name2=a.name1 OR
b.name3=a.name1)

 

The recommendation was to change it as

 

LEFT OUTER JOIN
table1 A
on b.name2=a.name1
LEFT OUTER JOIN
table1 AA
ON b.name3=AA.name1

 

However, this is causing some data quality in calculated columns. Can you please help me in re-writing the code without any issue. This particular step is consuming lot of time and we can not ignore it. The explain plan is showing a cross join for this particualr step when we use 'OR' clause.

 

Thanks in Advance!

 

Prasanth.

3 REPLIES
Junior Contributor

Re: Need help on query tuning

The ORed join condition will result in a Product join in Explain.

If the logic is actually a kind of XOR, i.e. join using b.name3=a.name1 only if b.name2 is null , you can rewrite it using CASE logig, e.g. coalesce(b.name3, b.name2)=a.name1

Otherwise you can split in two left joins (but the you need a lot of COALESCEs) or in two Selects with UNION (not ALL).

Enthusiast

Re: Need help on query tuning

hi dnoeth,

 

Thanks for your quick reply. But, I need some more clarifiactions- 

 

1. i can not use coalesce function. There is no so such thing like either of the column is null

2. The LEFT join is a part of very big complex view, hence I can not re-write the whole query

3.

I was just trying to write the LEF JOIN like this

LEFT JOIN

(sel name1 from table1 A

where a.name1 =b.name2

union

sel name1 from table1 AA

where AA.name1 =b.name3)

 

But I donot know how to add ON condition.

 

Could you please give an exact query to replicate the same funcitonality as the existing OR clause.

 

 

Thanks,

Prasanth.

 

 

 

Highlighted
Junior Contributor

Re: Need help on query tuning

To apply the UNION you must copy the whole query and remove one of the ORed conditions in each Select.