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 choose by the optimizer to execute an SQL statement.It may be used in 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 strategy". The execution strategy provides what an optimizer does but not why it choses them. The EXPLAIN facility is used to analyze all joins and complex queries.
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.
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.
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.
Fload, Mload and error tables:[How many error tables are there in fload and what are their significance/use? How many error tables are their in mload and what is there use ?
- load uses two error tables
- Error table 1:
- where format of data is not correct.
- Error table 2: violations of UPI Mload also uses two error tables (ET and UV), 1 work table and 1 log table
- 1. ET TABLE - Data error
- Multi Load uses the ET table, also called the Acquisition Phase error table, to store data errors found during the acquisition phase of a Multi Load import task.
- 2. UV TABLE - UPI violations Multi Load uses the UV table, also called the Application Phase error table, to store data errors found during the application phase of a Multi Load import or delete task
- 3. WORK TABLE - WT Mload loads the selected records in the work table
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.mean V2R6 provide the additional data protection as Comparison 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 What the default privileges which get assigned ?
The GRANT option for any particular table depends on the privileges of the user. If it is an admin user you can grant privileges at any point of time.
The default roles associated with the newly created tables depend on the schema in which they are created.What is cliques? What is Vdisk and how it will communicate with physical data storage at the time of data retrieval through AMP ?
A clique is a set of Teradata nodes that share a common set of disk arrays. Cabling a subset of nodes to the same disk arrays creates a clique. Each AMP vproc must have access to an array controller, which in turn accesses the physical disks. AMP vprocs are associated with one or more ranks (or mirrored pairs) of data. The total disk space associated with an AMP is called a vdisk. A vdisk may have up to three ranks. Hence Vdisk will communicate with physical storage through array controllers.
What is basic teradata query language ?
BTEQ(Basic teradata query) It allows us to write SQL statements along with BTEQ commands. We can use BTEQ for importing, exporting and reporting purposes. The commands start with a (.) dot and can be terminated by using (;), it is not mandatory to use (;). SQL statements doesn't start with a dot, but (;) is compulsory to terminate the SQL statement. BTEQ will assume anything written without a dot as a sql statement and requires a (;) to terminate it.
How many codd's rules are satisfied by teradata database ?
There are 12 codd's rules applied to the teradata database
What is the difference between Multi load & Fast load interms of Performance ?
- If you want to load, empty table then you use the fast load, so it will useful than the multi load..because fast load performs the loading of the data in 2phase..and it no need a work table for loading the data.., so it is faster as well as it follows the below steps to load the data in the table
- Phase1-It moves all the records to all the AMP first without any hashing
- Phase2-After giving end loading command, Amp will hashes the record and send it to the appropriate AMPS .
- Multi load: It does the loading in the 5 phases
- Phase1:It will get the import file and checks the script
- Phase2:It reads the record from the base table and store in the work table
- Phase3:In this Application phase it locks the table header
- Phase4:In the DML operation will done in the tables
- Phase5: In this table locks will be released and work tables will be dropped.
Does SDLC changes when you use Teradata instead of Oracle ?
If the teradata is going to be only a data base means It won't change the System development life cycle (SDLC) If you are going to use the teradata utilities then it will change the Architecture or SDLC If your schema is going to be in 3NF then there won't be huge in change
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.
first : True second : False 1. Foreign Keys can change values over time. 2. Each Foreign Key must exist as a Primary Key.
What are two examples of an OLTP environment ?
- Transactions take a matter of seconds or less.
- Many transactions involve a small amount of data.
- Example-1: On Line Banking On Line Reservation (Transportation like Rail, Air etc.)
- Example-3: 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 transaction.
- For eg. 1. This type of transaction takes place when we take out money at an ATM. Once our card is validated, a debit transaction takes place against our current balance to reflect the amount of cash withdrawn.
- 2. This type of transaction also takes place when we deposit money into a checking account and the balance gets updated. We expect these transactions to be performed quickly. They must occur in real time.
