Home Page for the TeradataForum
 

Archives of the TeradataForum

Message Posted: Wed, 26 May 2004 @ 15:39:42 GMT


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


Subj:   Re: Exclusion Joins
 
From:   Michael Larkins

I am not sure why you think a correlated subquery will be so slow, unless you are assuming this because of the slowness in other databases.

You might try the following, even via an EXPLAIN compared to your NOT IN that uses concatenation:

     INSERT INTO Temp
         SELECT * FROM table_X X
            WHERE NOT EXISTS(SELECT * Y.k FROM table_Y Y
                                where X.i=Y.i and X.j=Y.j );

You might also experiment with making both y.i and y.j secondary indices (if not already a PI) in an effort to speed it up even more. Because then, Teradata does not need to access the larger base data rows from Table_Y. Instead it can use the small index rows (more of them in a single block - fewer blocks to read - less IO=faster).


Hope this helps,

Michael Larkins
Certified Teradata Master



     
  <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