Top Teradata Interview Questions and Answers (2 of 7)
|#Top Teradata Interview Questions and Answers:|
- What is the usage of Fallback?
- How many primary keys we will apply on a table?
- How does rows store in Teradata?
- Tell me the types of Indexes?
NoPI-No primary index
UPI-Unique primary index
NUPI-Non unique primary index
USI-Unique secondary index
NUSI-Non unique secondary index
- Difference between UPI and NUPI and the situation where exactly? We will use these?
- The role of SI in teradata and explain the types?
- How do we create a join and hash indexes and explain the scenerios? When we will use exactly?
A) There are four basic types of joins that Teradata can perform depending on the characteristics of the table definition. When the join domain is the primary index (PI) column, with a unique secondary index (USI) the join is referred to as a nested join and involves, at most, three AMPs. The second type of join is a merge join, with three different forms of a merge join, based on the request.
- The newest type of join in Teradata is the Row Hash join using the pre-sorted Row Hash value instead of a sorted data value match. This is beneficial since the data row is stored based on the row hash value and not the data value. The last type is the product join.
- In Teradata, each AMP performs all join processing in parallel locally. This means that matching values in the join columns must be on the same AMP to be matched. When the rows are not distributed and stored on the same AMP, they must be temporarily moved to the same AMP, in spool. Remember, rows are distributed on the value in the PI column(s). If joins are performed on the PI of both tables, no row movement is necessary. This is because the rows with the same PI value are on the same AMP - easy, but not always practical. Most joins use a primary key, which might be the UPI and a foreign key, which is probably not the PI.
- Regardless of the join type, in a parallel environment, the movement of at least one row is normally required. This movement puts all matching rows together on the same AMP. The movement is usually required due to the user's choice of a PI. Remember, it is the PI data value that is used for hashing and row distribution to an AMP. Therefore, since the joined columns are mostly columns other than the PI, rows need to be redistributed to another AMP. The redistributed rows will be temporarily stored in spool space and used from there for the join processing.
- What is the meaning of Transient journal overhead?
Permanent Journal – All AMPs copy any changes to the database and store them in the Permanent Journal permanently. The DBA or Table Creator must request a Permanent Journal. Each day, the DBA usually does a "Check Point with Save", and that days Permanent Journal is stored permanently on tape or disk off of the Teradata system
- What is skewness in teradata?
- What is a subtable and when it requires?