1 :: How teradata makes sure that there are no duplicate rows being inserted when its a SET table?

Teradata will redirect the new inserted row as per its PI to the target AMP (on the basis of its row hash value), and if it find same row hash value in that

AMP (hash synonyms) then it start comparing the whole row, and find out if duplicate.
If its a duplicate it silently skips it without throwing any error.

2 :: What is the difference between Global temporary tables and Volatile temporary tables?

Global Temporary tables (GTT) -
1. When they are created, its definition goes into Data Dictionary.
2. When materialized data goes in temp space.
3. thats why, data is active upto the session ends, and definition will remain there upto its not dropped using Drop table statement.
If dropped from some other session then its should be Drop table all;
4. you can collect stats on GTT.

Volatile Temporary tables (VTT) -
1. Table Definition is stored in System cache
2. Data is stored in spool space.
3. thats why, data and table definition both are active only upto session ends.
4. No collect stats for VTT.

3 :: What is an optimization and performance tuning and how does it really work in practical projects?

Performance tuning and optimization of a query involves collecting statistics on join columns, avoiding cross product join, selection of appropriate primary

index (to avoid skewness in storage) and using secondary index.

Avoiding NUSI is advisable.

4 :: What is explain and how does it work?

The EXPLAIN facility is a teradata extension that provides you with an "ENGLISH" translation of the steps choosen by the optimizer to execute an SQL

statement.It may be used oin any valid teradata database with a preface called "EXPLAIN".

The following is an example:-

EXPLAIN select last_name,first_name FROM employees;

The EXPLAIN parses the SQL statement but does not execute it.

This provides the designer with an "execution stratergy".

The execution stratergy provides what an optimizer does but not why it choses them.

The EXPLAIN facility is used to analyze all joins and complex queries.

5 :: What are the enhanced features in Teradata V2R5 and V2R6?

V2R6 included the feature of replica in it.in which copy of data base are available on another system.meam V2R6 provide the additional data protaction as

comprison to V2R5 while if data from one system has been vanishes.


After creating tables dynamically in the Teardata, where is the GRANT table option usually done ? When tables are newly created, what is the default role and