Home Page for the TeradataForum
 

Archives of the TeradataForum

Message Posted: Fri, 28 May 2004 @ 21:23:01 GMT


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


Subj:   Re: Exclusion Joins
 
From:   Christie, Jon

  Another query:  


     INSERT INTO Temp
     SELECT * FROM table_X X LEFT JOIN table_Y Y
     ON X.i = Y.i AND X.j = Y.j AND X.k = Y.k
     WHERE Y.i IS NULL
     ;

Dieter, I'd run an explain on this one. You're absolutely right that this version of the query will return the same result, but it may not get a good plan. I have seen a case where the optimizer chose to do the outer join and then remove the inner join rows from the result by running the spool though a retrieve step. In almost all cases, this would be a slower plan than using an exclusion join. At some versions, the optimizer may be smart enough to use an exclusion join, but if it doesn't, you've got plan that will not perform well.



     
  <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: 23 Jun 2019