Why do Hash joins usually perform better than Merge Joins?
- 1 Answers
- 7167 Views
- I also Faced
- E-Mail Answers
Answer / narayana
In MERGE join rows to be join must be present in same AMP.. If the rows to be joined are not on the same AMP, Teradata will either redistribute the data or duplicate the data in spool to make that happen based on row hash of the columns involved in the joins WHERE Clause.Hash join takes place if one or both of the tables on each can fit completely inside the AMP's memory.AMP chooses to hold small tables in its memory for joins happening on ROW hash.
Usually optimizer will first identify a smaller table, and then sort it by the join column row hash sequence. If the smaller table is really small and can fit in the memory, the performance will be best. Otherwise, the sorted smaller table will be duplicated to all the AMPs. Then the larger table is processed one row at a time by doing a binary search against the smaller table for matched record.
Where as in MERGE join Columns to be join is Non INDEXED column. teradata will redistribute the table rows into SPOOL memory and sort them by hash code.So that matching data lies on same amp, so the join can happen on redistributed data
| Is This Answer Correct ? | 6 Yes | 0 No |
In BTEQ, how are the session-mode parameters being set?
I want to write one query How to get Matched records and unmatched records in table?
How is MLOAD Client System restarted after execution?
what is identity column in TD?
What are default access rights in teradata? What explicit right can be given to a user?
If I wanted to run a TPump job only once per day - basically working on a file that is produced once per day - how would you set up the parameters for that sort of job ?
How to find duplicates in a table?
What is differnce between Error code and Error Level in Bteq
how many modules are there in telecome domain?how to explain the architecture?
What are normalization, first normal form, second normal form and third normal form?
What is the difference between global temporary tables and volatile temporary tables?
Hi Frnds this que is recently asked at IBM there are two rows like aa6588fhfhf,gru282vbvv. Question is howcan we retrive the first number from two rows. Can any one help thanks in advance
Teradata 
