Interested to Buy Any Domain ? << Click Here >> for more details...
Why do Hash joins usually perform better than Merge Joins?
- 1 Answers
- 8039 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 |
If the PMON is not working then how do you identify and monitor all the processes, resources and sessions ?
Can we load a Multi set table using MLOAD?
What is the difference between SI and JI?
How to run a query in teradata?
In the Primary Index, what is the score of AMPs that are actively involved?
Explain fastload in teradata?
What is the Maximum number of volatile tables that can be created?
How does hashing happens in teradata?
Suppose i have data like 2009/12/31 555555534 ' ' Generally last 2 records are wrong format it is going to error table but i don't want load error table if any wrong format in Date column Pass "NULL" how can we do it fast load
If the query is NOT WRITTEN PROPERLY then what are the recommendations you can give to the developer ?
Why do you get spool space errors? How do trouble-shoot them?
any one answer me how they can analyzing the project using data ware housing?
Teradata 
