|
Archives of the TeradataForumMessage Posted: Fri, 09 Jul 1999 @ 13:49:21 GMT
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
| ||||||||||||||||||||||||||||||||||||||||||||||||
| ||||||||||||||||||||||||||||||||||||||||||||||||
Copyright 2016 - All Rights Reserved | ||||||||||||||||||||||||||||||||||||||||||||||||
Last Modified: 15 Jun 2023 | ||||||||||||||||||||||||||||||||||||||||||||||||