Home Page for the TeradataForum
 

Archives of the TeradataForum

Message Posted: Fri, 09 Jul 1999 @ 13:49:21 GMT


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


Subj:   Product and Merge Joins
 
From:   Hays McLean

What is the difference between a product join and a merge join as used in the 'explain' results? Are there other kinds of joins?

I have a (seemingly rather simple) query that churned for hours before I stopped it. I modified the query and ran it and it finished in a couple of minutes! (the 2 queries are below) When I looked at the 'explain' results on both the only significant difference was that the first one used a product join and the second used a merge join. Are the different types of joins more or less efficient than other types?

Below are the two queries, maybe there is something that I'm overlooking.

1st query (took hours to run):

     select  count(distinct system_id||acct_num)
     from    acct_rc_assignment_vw
     where   period_end_date = 990531
                     and
                     rc_num not in
     ('8682','8590','8684','8683','8614','8629','8621','8631','8638'
     ,'8637','8628','8634','8636','8617','8613','8632','8618','8616'
     ,'8626','8612','8633','8635','8623','8625','8622','8624','8736'
     ,'8734','8735','8737','8732','8738','8731','8741','8733','8739'
     ,'8784','8669','8619','8662','8671','8664','8752','8667')
                     and
                     (acct_num, system_id) not in (select acct_num, system_id
     from acct_rc_assignment_vw where period_end_date = 990430)

2nd query (took a couple of minutes to run):

     select  count(distinct system_id||acct_num)
     from    acct_rc_assignment_vw
     where   period_end_date = 990531
                     and
                     rc_num not in
     ('8682','8590','8684','8683','8614','8629','8621','8631','8638'
     ,'8637','8628','8634','8636','8617','8613','8632','8618','8616'
     ,'8626','8612','8633','8635','8623','8625','8622','8624','8736'
     ,'8734','8735','8737','8732','8738','8731','8741','8733','8739'
     ,'8784','8669','8619','8662','8671','8664','8752','8667')
                     and
                     (acct_num, system_id) not in (select acct_num, system_id
     from acct_rc_assignment_vw where period_end_date = 990430
                     and
                     rc_num not in
     ('8682','8590','8684','8683','8614','8629','8621','8631','8638'
     ,'8637','8628','8634','8636','8617','8613','8632','8618','8616'
     ,'8626','8612','8633','8635','8623','8625','8622','8624','8736'
     ,'8734','8735','8737','8732','8738','8731','8741','8733','8739'
     ,'8784','8669','8619','8662','8671','8664','8752','8667')
                     )

Hays McLean
First American National Bank



     
  <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: 15 Jun 2023