Answer Posted / 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

What is meant by a Channel Driver?

727

---

What are the components used in smp and massively parallel processing (mpp) machines?

717

---

In Teradata, what is the significance of UPSERT command?

675

---

What are the different design perspectives used in teradata?

646

---

What is meant by a Highest Cost Plan?

706

---

---

How does hashing happens in teradata?

713

---

What are the steps to create a data model?

696

---

What do you mean by teradata intelliflex?

711

---

What are the multiload utility limitations?

677

---

What can be achieved by using the teradata rdbms?

704

---

What are the various indexes in teradata? Why are they preferred?

645

---

Explain the term 'primary key' related to relational database management system?

700

---

What are the functions performed by bynet?

708

---

Why does varchar occupy 2 extra bytes?

713

---

Why managing the data is important?

665

---