Tera Data Interview Questions and Answers
Question - 1 : - what is explain in teradata?
Answer - 1 : - Explain is a fn using which you can find the execution procedure of any query in sql assistant. To use this fn type Explain before any query and run it or press F6 after writing a query. It also gives the estimated time, join confidence and memory needed to execute that query. It's advisable to use explain before executing any complex query.
Question - 2 : - Teradata performance tuning and optimization?
Answer - 2 : - 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.
Question - 3 : - What is the difference between Global temporary tables and Volatile temporary tables?
Answer - 3 : - 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.
Question - 4 : - How teradata makes sure that there are no duplicate rows being inserted when its a SET table?
Answer - 4 : - 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.
Question - 5 : - What are the enhanced features in Teradata V2R5 and V2R6?
Answer - 5 : - 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.
Question - 6 : - 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 what the default privileges which get assigned ?
Answer - 6 : - The GRANT option for any particular table depends on the privilages of the user. If it is an admin user you can grant privilages at any point of time.
The deafult roles associated with the newly created tables depend on he schema in which they are created.
Question - 7 : - What is cliques? What is Vdisk and how it will communicate with physical data storage at the time of data retrieval through AMP ?
Answer - 7 : - Cliques are group of nodes. Vdisk is group of disks virtually connected together and amp is assinged one set of Vdisk to work with. An amp will be responsible for one VDISK.
Question - 8 : - what is basic teradata query language?
Answer - 8 : - The Basic Teradata Query Language is (BTEQ)
Question - 9 : - What are the types of tables in Teradata?
Answer - 9 : - These are the five kind of tables.
1 SET
2 MULTI SET
3 GLOBAL TEMPORARY TABLES
4 TEMPORARY TABLES
5 VOLATILE TEMPORARY TABLES
Question - 10 : - How do you define Teradata? Give some of the primary characteristics of the same.
Answer - 10 : -
Teradata is basically an RDMS which is used to drive the Datamart, Datawarehouse, OLAP, OLTP, as well as DSS Appliances of the company. Some of the primary characteristics of Teradata are given below.
- Is capable of running on Single-nodes, as well as multi-nodes.
- Parallelism is built into the system.
- Very much compatible with the standards of ANSI.
- Tends to act in the same way as a server.
- It is an Open System that basically executes for UNIX MR-RAS, Suse Linux ETC, WIN2K, etc.