QUERY REWRITE

Database
Enthusiast

QUERY REWRITE

HI ALL,

I AM FACING THE BELOW PROBLEM AT MY SITE.

select  TABLE1.*

from    work_db.TABLE11 XLA

        inner join

        work_db.TABLE2

   on  (   (    TABLE1.COLUMN1 = TABLE2.COLUMN1

          and   TABLE1.COLUMN2 = TABLE2.COLUMN2)

       or   TABLE1.COLUMN3 = TABLE2.COLUMN3);

THE ABOVE QUERY IS PRODUCING A BAD PRODUCT JOIN IN THE EXPLAIN PLAN. I KNOW THAT USING UNION I CAN RESLOVE THIS ISSUE.

CAN WE RE WRITE THIS SQL WITH OUT USING UNION ?

3 REPLIES
Senior Apprentice

Re: QUERY REWRITE

DON'T SHOUT!!!

No, there's no other way to avoid the product join besides rewriting as a UNION or improving your data model :-)

Dieter

Enthusiast

Re: QUERY REWRITE

Hi,

Can you try this and check if this helps.

select  TABLE1.*

from    work_db.TABLE11 XLA

left join work_db.TABLE2 A

on TABLE1.COLUMN1 = A.COLUMN1

and   TABLE1.COLUMN2 = A.COLUMN2 

left join work_db.TABLE2 B

on TABLE1.COLUMN3 = B.COLUMN3

where A.COLUMN1 is not null or B.COLUMN3 is not null 

Thanks.

Enthusiast

Re: QUERY REWRITE

Thanks Dnoeth and Kartik :-)

I tried every thing, but still i am getting product join. So we are planning to change the data model.