Home Page for the TeradataForum
 

Archives of the TeradataForum

Message Posted: Tue, 26 May 2004 @ 01:43:33 GMT


     
  <Prev Next>  
<<First
<Prev
Next> Last>>  


Subj:   Exclusion Joins
 
From:   Anomy Anom

<-- Anonymously Posted: Tuesday, May 25, 2004 11:19 -->

Hi,

I am trying to reproduce an exclusion join in a better fashion so that I can avoid overheads of redistributions and extra joins.

I need to achieve all the rows from table X which do not exist in table Y and store them in Temp table.

Both tables have heavyweight indexes (X - 5 columns in UPI and Y - 7 columns in UPI)

The query does not involve all the UPI columns from either table and it might involve columns which are not part of UPI and are Nullable fields-

     INSERT INTO Temp SELECT * FROM table_X X WHERE X.i || X.j || X.k NOT IN
     (SELECT Y.i || Y.j || Y.k FROM table_Y Y);

Another way could be using correlated query, but it will also be slow if both table have 5m rows.

Isn't it possible to join them using Inner Join so that they produce exclusion join by adding some condition.


Regards.



     
  <Prev Next>  
<<First
<Prev
Next> Last>>  
 
 
 
 
 
 
 
 
  
  Top Home Privacy Feedback  
 
 
Copyright for the TeradataForum (TDATA-L), Manta BlueSky    
Copyright 2016 - All Rights Reserved    
Last Modified: 27 Dec 2016