Teradata Interview Questions
Teradata performance tuning and optimization
What is an optimization and performance tuning and how does it really work in practical projects. Can I get any example to better understand.
There are 2 tables, table a with 10 million records, table b has 100 million records, now we are joining both tables, when we seen explain plan the plan showing td will took the table a and it will redistributes itnow the question is: by that plan is the optimizer is correct job or not ? Justify ans2....
Merge join strategies are 1. Big and small table joins - duplicate small tables 2. Column used in join is PI for one table - redistribute the other table in spool. 3. Column used in join is non PI f...
There are 2 tables, table A with 10 million records, table B has 100 million records, now we are joining both tables, when we seen Explain Plan the plan showing TD will took the table A and it will re...
Vertical & horizontal partitioning, join & hash indexes and ppi?
What are the differences between the followings?- vertical & horizontal partitioningvs- join & hash indexes vs- ppi
Horizontal partitioning is used in large tables where no of rows are very high. Rows are broken and placed in partitions. Loading becomes faster and query performance would be good. Vertical partitio...
PPI does not support any join index and hash index
What are the enhanced features in teradata v2r5 and v2r6?
V2R6 FEATURES
1. Queue Tables are added.
2. Triggered Stored Procedure added.
3. Stored Procedure Grammar Merge added.
4. Multi statements request submitted (supported in stored
procedures by using
BEGIN REQUEST
------
END RESQUEST.
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.
How many error tables are there in fload and what are their significance/use?Can we see the data of error tables?How many error tables are their in mload and what is there use?When mload job fails, can we access mload tables? If yes then how?
Read Best Answer
Editorial / Best Answer
Answered by: SHUBH25
Member Since Feb-2008 | Answered On : Feb 15th, 2008
Fload uses 2 error tables
Error table 1: where format of data is not correct.
Error table 2: violations of UPI
Mload also uses 2 error tables (ET and UV), 1 work table and 1 log table
1. ET TABLE - Data error
MultiLoad uses the ET table, also called the Acquisition Phase error table, to store data errors found during the acquisition phase of a MultiLoad import task.
2. UV TABLE - UPI violations
MultiLoad uses the UV table, also called the Application Phase error table, to store data errors found during the application phase of a MultiLoad import or delete task
3. WORK TABLE - WT
Mload loads the selected records in the work table
4. LOG TABLE
A log table maintains record of all checkpoints related to the load job, it is essential/madatory to specify a log table in mload job. This table will be useful in case you have a job abort or restart due to any reason.
Shubhangi
How many error tables are there in fload and what are their significance/use? FLOAD needs 2 error tables for its execution. 1 Error table for data issue and another error table for UPI violation Can...
There are 2 types of ERR tables created in both Mload & Fload they are ERR1:- Created due to BAD data/Constraint Violation & this table Contains this type of data ERR2: Created due to duplicate data...
What is basic teradata query language?
Basic Teradata Query Language or Batch Teradata Query Language is one of the effective utility in teradata. As using BTEQ we can write SQL statements in Interactive as well as Batch modes. using Bteq you can also Import Export data. Although for Importing and Exporting you have other utilities.
hello readers
it is just like sql assistance, but giving better performance then sql assistance, bteq work in from host (cli) where as sql runs from odbc so it having lot more opses (gateway) so gives some what less then bteq.
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...
There is so much difference between global temporary tables and volatile tables. Try to understand the difference and make comfort the global temporary table the name only defines that temporary so a...
In addition to the Shilpa's answer,
If you are using volatile table,
you can not put the default values on column level ( while creating table )
FSLDM - Financial Services Logical Data Model (Teradata FS-LDM 7.0) its developed by Teradata for the financial sectors (Specifically for Bank). it can be customized based on the user requirement.
What is the meaning of skewness in teradata? When do we use nullif function in teradata?
When we choose wrong primary index the data unevenly distributed across all the amps means some amps have more records and some amps had less records this is called skewness.
Percentage of skewness is called skew factor.30% skew factor is acceptable.
Skewness is the statistical term, which refers to the row distribution on AMPs. If the data is highly skewed, it means some AMPs are having more rows and some very less i.e. data is not properly/evenl...
How many version is there in teradata? Please give me detail information for version ? Version name and particular date ?
The following are Teradata Versions as I known.
1. Teradata v2r5
2. Teradata v2r6
3. Teradata 12
4. Teradata 13
5. Teradata 13.10
6. Teradata 14
Now Teradata 14 is the latest one, Teradata 14.10 is upcoming release
Which two statements are true about a foreign key?
Each foreign key must exist as a primary key.Foreign keys can change values over time.
The utilities are in teradata as follows
Bteq for loadind/Unloading the data
Fload for load the data only one table at a time it must be empty
mload for load the data which is empty or populated table up to 5 tables at a time
fexport to export the data
tpump to load the data upto 64 tables
Both statements are TRUE
How many codd's rules are satisfied by teradata database?
11 1/2 or 12 codd's rules support
12 rules only
What is explain and how does it work?
Read Best Answer
Editorial / Best Answer
Answered by: Madhulathamadduri
View all questions by Madhulathamadduri View all answers by Madhulathamadduri
Member Since Jan-2010 | Answered On : Apr 15th, 2011
The EXPLAIN modifier preceding an SQL statement generates an
English translation of the optimizer’s plan. It is fully parsed and
optimized, but not executed.
EXPLAIN returns:
Text showing how a statement will be processed (a plan).
An estimate of how many rows will be involved.
A relative cost of the request (in units of time).
This information is useful for:
• Predicting row counts.
• Predicting performance.
• Testing queries before production.
• Analyzing various approaches to a problem.
EXPLAIN may also be accessed using Teradata Visual Explain.
1. English version on optimizer plan2. to identify the objects used and kind of locks applied on those objects3. to identify the number of amp operation eg. single ot group amp4. to identify the data ...
Explain statement generally gives the step by step analysis of the queries being executed in the database. It is the plan prepared by PE dispatched to the amps. It could also identify intermediate sp...
Multiset table and duplication
Why fastload does not support multiset table and duplication?
Fastload will support the MULTISET table but it will ignore the all row duplicates because of the restartability mechanism of fastload.
Fastload utility does not support duplicate rows. 1.Since MS table supports dup rows, fastload cannot load MS table.2.Restart logic is one of the reason. Fastload is one of the utility which load...
What is the purpose of before journal and after journal?
Before Journal keeps the images of the table before making the change to the table. Very useful, if the changes need to be reverted incase of a fault or rollback.
We use both before journal and after journal in Permenent journal to do selective or full recovery of data..........consider Today Monday**Yesterday--------->Before ImageTuesday------------>Afte...
Clique is mainly used to prevent node failures.Group of nodes which share common disk drives is called CLIQUE.Group of cylindrical or physical disks arranged in array fashion is called VDISKS.After AM...
In case of one Disk failure the mirrored disk would provide the fallback protection In case of AMP failure the second AMP (in the fallback Clique) can provide the protection In case of Node failure th...
How will you avoid skewness?
Data or Amp skew occurs in teradata due to uneven distribution of data accross all the amps. Often, this leads to spool space error too. To avoid skewness, try to select a Primary Index which has as m...
What is a common data source for the central enterprise data warehouse?
Operational data stores
ODS (Operational Data sources) or Datamarts
What are two examples of an oltp environment?
ша transactions take a matter of seconds or less.ша many transactions involve a small amount of data.
Best example is ATM Transactions...
OLTP is typified by a small number of rows (or records) or a few of many possible tables being accessed in a matter of seconds or less. Very little I/O processing is required to complete the transacti...
What is the difference between except and minus operator in teradata SQL?
EXCEPT is ANSI Compliant and MINUS is Teradata specific. Function wise both are same.
User Panel
Categories
Interview Coaching
Ads
Connect
Ads
Interview Question
Latest Questions
Ads
Interview & Career Tips
Get invaluable Interview and Career Tips delivered directly to your inbox. Get your news alert set up today, Once you confirm your Email subscription, you will be able to download Job Inteview Questions Ebook . Please contact me if you there is any issue with the download.
NH
Performance tuning:
1. Look at the plan generated by PE.
2. Check for the recommendations of stats to be collected for the columns.
3. Try to avoid product joins and unnecessary joins.
4. Try to avoid casting in join conditions
5. Use secondary index appropriately.
6. Use join index if necessary.
Optimization of queries improves performance of TD and helps the PE to generate the most efficient execution plan. To Optimize a query, choose the Primary Index to avoid skrew, Collect statistics on t...