Convert MINUS query to OUTER JOIN


Convert MINUS query to OUTER JOIN


I have a requirement to avoid exclusion join for a query using MINUS set operator. Query is something like this:

sel t1.col1,t1.col2 from t1


sel t2.col1,t2.col2 from t2;

In this case, optimizer chooses to redistribute both tables on all columns of projection list to perform an exclusion join in the next step.

This redistribution is taking longer when table have huge data.

The idea is to avoid redistribution for at least the bigger table. 

Is there any way we can convert above MINUS query to OUTER JOIN query ?

I'm sure we can avoid at least one redistribution step if we use outer join.

NOTE: Both tables may contain NULL data in their respective columns.


Re: Convert MINUS query to OUTER JOIN

SELECT t1.col1, t1.col2 FROM t1
   LEFT OUTER (select t.col1, t.col2, 1 isRecordPresent from t2 t ) tt2
     ON (
       (t1.col1=tt2.col1 or (t1.col1 is null and tt2.col1 is null))
       (t1.col2=tt2.col2 or (t1.col2 is null and tt2.col2 is null))
WHERE tt2.isRecordPresent is null

Could this query help you?





Re: Convert MINUS query to OUTER JOIN

Hey insac,

Thank you very much for the help! this is working as expected :)

Senior Apprentice

Re: Convert MINUS query to OUTER JOIN

You can also try a NOT EXISTS:

select * from tab1 as t1
where not exists
( select * from tab2 as t2
where (t1.col1 = t2.col1 or (t1.col1 is null and t2.col1 is null)
and (t1.col2 = t2.col2 or (t1.col2 is null and t2.col2 is null)
and ...

If both table got the same PI this should be a direct Merge Join.


Re: Convert MINUS query to OUTER JOIN

Thanks Dieter, both Tables are having different PIs.

I checked the performance of both queries and see that outer join query taking less CPU with around 2B rows.

Other than that, I added distinct in sel part to avoid duplicate rows.

Now both MINUS and OUTER JOIN query giving same results.


Re: Convert MINUS query to OUTER JOIN

Create multiset volatile table VT1_1 
(Inx Varchar(20)) on commit preserve rows;

Create multiset volatile table VT2_2
(Inx Varchar(20))  on commit preserve rows;

Insert into VT1_1
Sel Id||col1||col2 from <Tbl1>;

Insert into VT2_2
Sel Id||col1||col2 from <Tbl2>;

Sel VT1_1.* from VT1_1 Left outer join
VT2_2 on VT1_1.Inx=VT2_2.Inx
Where VT2_2.Inx is null ;

You can create 2 Volatile tables, insert the concatenated values for all the 3 cols from each table & then do a LOJ.