Interested to Buy Any Domain ? << Click Here >> for more details...
Why do Hash joins usually perform better than Merge Joins?
- 1 Answers
- 7777 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 |
What is the difference between global temporary tables and volatile temporary tables?
Increasing no of amps will increase performance. Is this true?
Describe primary index in teradata?
What are the various indexes in teradata?
How can we pass Variable in Bteq with out using shell script Is it possible or not If possible let me know how can we pass varaibale
how can we analyze the locks ?
i have a table like sales....the field are Prodid Jan(jam month sales)Feb March 1 20 76 50 2 30 94 40 3 40 90 30 4 70 20 30 5 23 40 40 6 85 30 55 7 84 20 65 8 10 93 40 9 57 30 30 10 38 83 40 11 35 39 90 12 83 89 50 Now the Question is i want get the max sales of 12 products from the months.hint:for eg I WANT GET 89 for product12... Can any one help me
What are the available primary index types?
What are the frequently used data types in teradata?
What are the scenarios in which full table scans occurs?
What are the different methods ot loading a dimension table? A fact table etc?
Why do Hash joins usually perform better than Merge Joins?
Teradata 
