Xref

*******
Types of Tables: 5
 1. set table - Doesn't allow duplicates
 2. multiset table - Allow duplicates
 3. derived table
 4. global temporary table(temporary table)
 5. volatile table
GTT vs VTT
Global Temporary tables (GTT):-
 -    Table Definition is stored  into Data Dictionary.
 -    Data is stored in temp space.
 -    GTT data is active upto the session ends, but table definition will remain there in Data dictionaly untill is is dropped  using Droptable statement.
 -    secondry Index can be created on Global Temporary table.
 -    Stats can be collected on GTT.
 -    CHECK or BETWEEN constraints, COMPRESS column and DEFAULT and TITLE clause are supported by Global Temporary table.
 -    In a single session 2000 Global temporary table can be materialized.

Volatile Temporary tables (VTT) :-
     -    Table Definition is stored in System cache.
 -    Data is stored in spool space.
 -    VTT data and table definition both are active only upto session ends.
 -    secondry Index can not be created on VTT
 -    stats cannot be collected on VTT.
 -    CHECK or BETWEEN constraints, COMPRESS column and DEFAULT and TITLE clause are not supported by VTT.
 -    In a single session 1000 Volatile temporary table can be materialized.
 -    VTT does not support default value for a column while creating a table.
.
*******
Types of Spaces: 3
 1. Perm space
 -This is disk space used for storing user data rows in any tables located on the database.
 -Both Users & databases can be given perm space.
 -This Space is not pre-allocated , it is used up when the  data rows are stored on disk.
 2.Spool Space
 -It is a  temporary workspace which is used for processing Rows for given SQL statements.
 -Spool space is assigned only to users . -
 -Once the SQL processing is complete the spool is freed and given to some other query.
 -Unused Perm space is automatically available for Spool . 
 3. TEMP space
 -It is allocated to any databases/users where Global temporary tables are created and data is stored in them.
 -Unused perm space is available for TEMP space
******
Teradata Extensions: 3
 HELP - Display the strucute of the table
 SHOW - How the table is created
 Explain - English version of optimizer plan
******
Default Parameters of the Table:
 Type  - SET - Doesn't allow the duplicates
 Data protection mechanism - NO FALLBACK/NO JOURAL
 Index - NUPI - Does allow the duplicate index
******
PK vs PI
 PRIMARY KEY      PRIMARY INDEX
1 It cannot be NULL     It can be NULL
2 It is not mandatory in Teradata   It is mandatory in Teradata
3 It does not help in data distribution   It helps in data distribution [ Better storage & fast reterival ]
4 It has to be unique    It can be unique or non-unique
5 It is logical implementation    It is physical implementation
6 It cannot be changed    It can be changed.
7 1 per table [not manditory]   1 per table
8 It can be alter    Can't be alter, should be drop the table & recreate it
9 N cols     12-16 cols
- Using Primary Index Teradata distributes the data in it's AMP's.
- Primary Key is an logical concept used by all the RDBMS, and it helps to uniquely identify each record in a Table.
- The three most important roles the primary index does is the following –*
   a. Data Distribution
   b. Fastest way to retrieve Data
   c. Incredibly important for Joins
         In short primary index provides the fastest physical path to retrieving data.
PI:
 If not specified explicitly when the table is created, Teradata uses the first column of the table definition as the primary index by default.

Term   Primary Key    Primary Index
Definition  A relational concept used to determine relationships
  among entities and to define referential constraints  Used to store rows on disk
Requirement  Not required, unless referential integrity checks
  are to be performed      Required
Defining   Define by CREATE TABLE statement    Defined by CREATE TABLE statement
Uniqueness  Unique       Unique or non-unique
Function   Identifies a row uniquely     Distributes rows
Values can be changed?  No      Yes
Can be null?   No      Yes
Related to access path?  No      Yes
Create at any time?  Yes     No [Drop & crate the table ]
*******
 There are 5 different indices present in Teradata
 1. Primary Index
       a.Unique primary index
       b. non Unique primary index
 2. Secondary Index
       a. Unique Secondary index
       b. non Unique Secondary index
 3. Partitioned Primary Index
       a. Case partition (ex. age, salary...)
       b. range partition ( ex. date)
 4. Join index
       a. Single table join index
       b. multiple table join index
       c. Sparse Join index ( constraint applied on join index in where clause)
 5. Hash index
********
*Primary Index and the ROW HASH* :
The primary index is the key to determine where the ROW of the table will reside on which AMP. When a new row arrive for insert in Teradata the following steps occur –
 1. Teradata *Parsing Engine* (PE) examines the primary index of the row.
 2. Teradata takes the primary index of the rows and run it through  *HASHING ALGORITHM*.
 3. The output of the Hashing Algorithm is the *32 bit Row – Hash value*.
e.g – New row coming with following attributes -
 *Hash the PI value* -> 55 / HASHING ALGORITHM = *11110000111100001111000011110000* (Row – Hash)
            This value in Binary format is called as the Row – Hash Value. And it will always be unique for the given PI. Means it’s not possible to get two Row – Hash Value for the same EMP_ID (55).
This 32 bit Row – Hash value determines in which AMP the row will reside and it always attached along with the ROW to make it a UNIQUE identification for that ROW.
Now in Teradata we have *HASH MAP* which contains the different bucket called as *Hash Map Buckets.* These buckets contain only the different AMP number which is attached with the Teradata system
*Uniqueness value*
 When the AMP receives the row it will place the row into the proper table, and the AMP itself check that is there any other row with the same Row- Hash Value. If this is the first Row with the particular Row-Hash value then it will assign a *uniqueness value of 1* along with the row. But if this is the second row with the same Row-Hash value for the same table then it will assign a *uniqueness value of 2*.
Similarly it assign a value of 3 if it finds third occurrence of the same Row – Hash, in this fashion it keeps on adding uniqueness value for the duplicates rows. Together with Row – Hash and the uniqueness value the Teradata make as *64 bit ROW – ID* to uniquely identify each row in the given AMP.
Two types of Primary Indexes are given below  –
*Unique Primary Index (UPI)*
 A unique primary index means that the value for the selected column must be unique.  In the example below the *EMP_ID* is the UPI.
 A unique primary index (UPI) will always spread the rows of the table evenly amongst the AMPs.
*Non Unique Primary Index (NUPI)*
 A Non unique primary index means that the value for the selected column can be non unique.  In the example below the *LAST_NAME* is the NUPI.
 A Non Unique Primary Index (NUPI) will almost never spread the rows of the table evenly amongst the AMPs.
 Unique primary index and Non-unique primary index are associated with SET and MULTISET tables respectively.
For a SET table, Unique primary index is always defined. The reason behind is in order to avoid the overhead of duplicate check. If UPI is not defined for SET table, then the SET table itself will scan
the entire records to find out the duplicates whereas UPI proactively checks duplicate entry. Since less columns are defined under UPI, it finds duplicate records faster than SET. NUPI otherwise will be used for MULTISET tables. Since Multiset table does not look for duplicate entries, NUPI will be just used to index fields.
Difference in brief:
 UPI     NUPI
Defined for SET table   Defined for MULTISET table
Duplicate check is handled by UPI  No duplicate check is handled
UPI reduces overhead for SET for duplicates NUPI will be used for indexing purpose only.
*****
Combination  :        SET vth NUPI          SET vth UPI       MSET vth NUPI          MSET vth UPI
Duplicate Index Value : Y  N  Y  N 
Duplicate Row  : N  N  Y  N
Allow the NULLs  : Y  Y  Y  Y
Update the PI value  : Y  Y  Y  Y
 ** Hence,we can say that P.I is alwys overrides the properties of SET & MSET
*******
a) I have not defined PRIMARY INDEX or PRIMARY KEY on table what will happen now: In this case, Teradata will check if any column is defined as UNIQUE, then it will make that column as UNIQUE PRIMARY INDEX else first column will be created as PRIMARY INDEX.
b) I have not defined PRIMARY INDEX however a column is defined as PRIMARY KEY: In this case, Teradata will make the PRIMARY KEY column as UNIQUE PRIMARY INDEX of the table.
c) I have defined both PRIMARY KEY and PRIMARY INDEX on different column: In this case, Teradata will make PRIMARY KEY column as UNIQUE SECONDARY INDEX i.e. UNIQUE INDEX on the table.
So one must understand the importance of PRIMARY INDEX in Teradata. Generally, PRIMARY KEY concept is taken care by UNIQUE PRIMARY INDEX in Teradata environment.
******
SI:
There may be some situations when  the query may not be using Primary Index column for data retrieval. In such cases, data retrieval will be very slow. In such situations we can create Secondary Index on such columns which are not part of PRIMARY Index however are used very often in JOIN conditions or other conditions for data retrieval. Like PRIMARY INDEX, we have two types of SECONDARY Index too:
Unique Secondary Index:
 CREATE UNIQUE INDEX [COLUMN_NAME] ON TABLENAME;
Non-Unique Secondary Index:
 CREATE INDEX [COLUMN_NAME] ON TABLENAME;
Creating SECONDARY INDEX may help in performance optimization however it also comes at some cost in terms of resources. Whenever a SECONDARY index is created on table , a subtable is created on all the AMPs which hold following information:
 SECONDARY INDEX VALUE || SECONDARY INDEX ROW_ID  || PRIMARY INDEX ROW_ID
 So whenever we query using column defined as SECONDARY INDEX, all AMP’s are asked to check for their sub-table if they hold that value. If yes, then AMPs retrieve the corresponding PRIMARY INDEX row_id from their subtable. Then the AMP holding the PRIMARY INDEX row_id is asked to retrieve respective records. Hence, Data Retrieval via Secondary Index is always 2 AMP or more AMP operation. For NUSI [Non Unique Secondary Index] subtable is created in the same AMP holding the PRIMARY row_id. However for USI[Unique Secondary Index], subtables hold the information about rows of different AMPs. Secondary Index avoids FULL TABLE scan. However one should collect STATS on Secondary Index columns in order to allow Optimizer to use Secondary Index and not Full Table Scan.
Advantages of Secondary Index:
    Avoids FULL Table Scan by providing alternate data retrieval path.
    Enhances performances.
    Can be dropped and created anytime.
    A table may have multiple Secondary Index defined where as only one Primary Index is permissible.
Disadvantages of Secondary Index:
    Needs extra storage space for SUBTABLE.
    Needs extra I/O to maintain SUBTABLE.
    Collect STATS is required in order to avoid FULL TABLE SCAN.
To Drop Secondary Index use below command:
 DROP INDEX [COLUMN_NAME] ON TABLENAME;
******
PPI:
  *Advantage of Partition Primary Index –*
      o Partitioned Primary Index is one of the unique features of Teradata, which is used for distribution of rows based on different partitions so that they can be retrieved much faster than any other conventional           approach.
      o Maximum partitions allowed by Teradata – *65,535*
  * It also reduces the overhead of scanning the complete table (or FTS) thus improving performance.
What are types of PARTITION PRIMARY INDEX (PPI) in Teradata?
 Answer:
Types of PPI:
  1. Partition by CASE
  2. Partition by Range
 1. Partition by CASE
  PRIMARY INDEX (customer_number)
 PARTITION BY case1 (
              order_total < 10000 ,
              order_total < 20000 ,
              order_total < 30000,
              NO CASE     OR        UNKNOWN ) ;
 2. Partition by RANGE
  PRIMARY INDEX (customer_number)
  PARTITION BY range1 (
              Order_date BETWEEN date '2010-01-01'       AND      date '2010-12-01'
              EACH interval '1' month ,
              NO RANGE   OR  UNKNOWN);
 P.S:If  we use NO RANGE or NO CASE  - then all values not in this range will be in a single partition.
 If we specify UNKNOWN, then all null values will be placed in this partition
- Multi-Level Partitioning: You can have up to 15 levels of partitions within partitions.
 PRIMARY INDEX(ORDER_NO)
 PARTITION BY (
  RANGE_N ( ORDER_DATE BETWEEN DATE '2012-01-01' AND DATE '2012-12-31'
   EACH INTERVAL '1' DAY)
  CASE_N (ORDER_TOTAL < 5000,
    ORDER_TOTAL < 10000,
    ORDER_TOTAL < 15000,
    ORDER_TOTAL < 20000,
   NO CASE, UNKNOWN));
3. RANGE Based Character PPI
 PRIMARY INDEX(EMP_NO)
 PARTITION BY RANGE_N
  (LAST NAME BETWEEN ( 'A ','B ','C ','D ','E ','F ','G ','H ',
   'I ','J ','K ','L ','M ','N ','O ','P ','Q ','R ','S ','T ',
   'U ','V ','W ','X ','Y ','Z ' AND 'ZZ',UNKNOWN));
******
--Displays ROW HASH
select hashrow ( Col_name ) from Table_Name;
******
Referential Integrity-RI
 Referential integrity is a relational database concept in which multiple tables share a relationship based on the data stored in the tables, and that relationship must remain consistent.
 A feature which ensures data integrity and consistency between primary and foreign key columns.
 Eg: Col_1 is defined as PK in T1 & it's defined as FK in T2
  If Col_1 is updated in T1 then automatically updates the value in T2.==> cascading update
  If Col_1 is removed in T1 then automatically removed in T2.==> cascading delete
  We can't add row in T2,unless added in T1
******
* BTEQ (Basic Teradata Query) supports all 4 DMLs: SELECT, INSERT, UPDATE and DELETE. BTEQ also support IMPORT/EXPORT protocols.
 * Fastload, MultiLoad and Tpump transfer the data from Host to Teradata.
 * FastExport is used to export data from Teradata to the Host.
*******
If you want to load, empty table then you use the fastload, so it will very usefull than the mutiload..because fastload performs the loading of the data in 2phase..and it noneed 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 endloading command, Amp will hashes the record and send it to the appropriate AMPS .
Multiload:
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 opreation will done in the tables
Phase 5: In this table locks will be released and work tables will be dropped.
 * Preliminary Phase – Basic Setup
 * DML Phase – Get DML steps down on AMPs
 * Acquisition Phase – Send the input data to the AMPs and sort it
 * Application Phase – Apply the input data to the appropriate Target Tables
 * End Phase – Basic Cleanup
***
MultiLoad is a very powerful utility; it has following limitations:
 * MultiLoad Utility doesn’t support SELECT statement.
 * Concatenation of multiple input data files is not allowed.
 * MultiLoad doesn’t support Arithmatic Functions i.e. ABS, LOG etc. 
 * MultiLoad doesn’t support Exponentiation and Aggregator Operators i.e. AVG, SUM etc. in Mload Script.
 * MultiLoad doesn’t support USIs (Unique Secondary Indexes), Refrential Integrity, Join Indexes, Hash Indexes and Triggers.
 * Import task require use of PI (Primary Index).
*****
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.
*****
Differences between BTEQ and Sql assistant (query man)?
BTEQ
•Basic Teradata Query utility
•SQL front-end
•Report writing and formatting features
•Interactive and batch queries
•The default number of sessions, upon login, is 1.
Teradata Query Manager / Queryman / TeradataSQL Assistant
•SQL front-end for ODBC compliant databases
•Historical record of queries including:
-         Timings
-         Status
-         Row counts
•Random sampling feature
•Limit amount of data returned
•Import/Export between database and PC
•Export to EXCEL or ACCESS
*****
What are Differences between Teradata and ANSI Session modes in Teradata?
 Answer:
 TERADATA MODE
 -------------
 1. Comparison is not Case sensitive
 2. Create table are default to SET tables
 3. Each transaction is committed implicitly
 4. Supports all Teradata commands
 5. It follows BTET (Begin and End Transaction) Mode

 ANSI MODE
 -------------
 1. Comparison is CASE sensitive
 2. Create table are default to MULTISET tables
 3. Each transaction has to be committed explicitly
 4. Does not support all Teradata commands
 5. It does not follow BTET Mode
***
How many types of Skew exist?
 Answer
 If you utilized unequally TD resources (CPU,AMP,IO,Disk and etc) this is called skew exists. Major are 3 types of skews (CPU skew, AMP/Data skew, IO Skew).
 -Data skew?
 When data is not distributed equally on all the AMPs.
 -Cpu skew?
 Who is taking/consuming more CPU called cpu skew.
 -IO skew?
 Who perform more IO Operation? Resulting in IO Skew
*****
Fastload: Fastload support Multiset table but does not support the duplicate rows. Multiset tables are tables that allow duplicate rows. When Fastload finds the duplicate rows it discards it. Fast Load can load data into multiset table but will not load the duplicate rows.
Can we load a Multi set table using MLOAD?
 Answers:
 We can Load SET, MULTISET tables using Mload, But here when loading into MULTISET table using MLOAD duplicate rows will not be rejected, we have to take care of them before loading.But in case of Fload when we are loading into MULTISET duplicate rows are automatically rejected, FLOAD will not load duplicate rows weather table is SET or MULTISET
*****
Can I use “drop” statement in the utility “fload”?
Answers:
 YES,But you have to declare it out of the FLOAD Block it means it should not come between .begin loading,.end loading FLOAD also supports DELETE,CREATE,DROP statements which we have to declare out of FLOAD blocking the FLOAD Block we can give only INSERT
****
What do High confidence, Low confidence and No confidence mean in EXPLAIN plan?
 Answer
 Explain gives the execution strategy means what are the different steps that the query will go through.
 HIGH CONFIDENCE:  Statistics are collected.
 LOW CONFIDENCE:   Statistics are not collected.  But the where condition is having the condition on indexed column. Then estimations can be based on sampling.
 NO CONFIDENCE: Statistics are not collected and the condition is on non indexed column.
***
How to Skip or Get first and Last Record from Flat File through MultiLoad?
 Answer
 In .IMPORT command in Mload we have a option to give record no. from which processing should begin. i.e. ‘FROM m’ ‘m’ is a logical record number, as an integer, of the record in the identified data source where processing is to begin. You can mention ’m’ as 2 and processing will  start from second record.
 THRU k and FOR n are two options in the same Mload command, functions same towards the end of the processing.
 Adding to the above, if from n"start record" and for n "stop record" are not mentioned, mload considers records from   start till the end of the file.
*****
Which is faster – MultiLoad delete or Delete command?
 Answer
 MultiLoad delete is faster then normal Delete command, since the deletion happens in data blocks of 64Kbytes, where as delete command deletes data row by row.  Transient journal maintains entries only for Delete command since Teradata utilities doesn’t support Transient journal loading.
*****
How many codd's rules are satisfied by teradata database?
 Answers:
 12 codd’s rules.
*****
What is the difference between Sub-Query & Co-Related Sub-Query?
 Answers:
 When queries are written in a nested manner then it is termed as a sub-query. A Sub-Query get executed once for the parent statement
whereas Co-Related Sub-Query get executed once for each row of the parent query.
*******
What are the ways by which we can use zero to replace a null value for a given column ?
Answer -
 1. By using Teradata SQL supported command as follows
 Select Col1, ZEROIFNULL(Col2)  from Table_name;
 2. By using ANSI SQL command as follows
 a. Coalesce
  Select Col1,COALESCE(Col2,0)    from Table_name;
 b.Case operator
  Select col1,
        Case When Col2 IS NOT NULL
                      Then Col2
                 Else 0
          End
  from Table_name;
 It is always suggested to use ANSI standard while coding in Teradata , since any changes in Teradata version due to upgrade/patches installation will lead to
 - Time for regression testing
 - rework of code.
********
Can a macro be called inside a macro?
Answer:
 The main purpose of run a set of repeated sql queries. Macro supports only DML queries. Hence We cant call any-other macro or not even a procedure in a macro.
*******
Why is BTET transaction processing overhead in Teradata?
Answer:
 - BTET makes all the queries running between BT and ET as single transaction . If any of query fails then the entire set of queries will not be committed.
 - BTET also has an overhead with locking , since it holds locks on tables till the ET is occured or all the queries have executed successfully
 - DDL statements cannot be used  everywhere in BTET processing , but these statements can be given towards the end of BTET transaction.
 - Using large number of BTET caused transient Journal to grow and has tendancy for System Restarts
******
Deleting duplicate rows :
Step 1:
CREATE SET VOLATILE TABLE YOUR_VOLATILE_TABLE AS
(
SEL *
FROM YOUR_MAIN_TABLE
WITH DATA ON COMMIT PRESERVE ROWS);

Step 2:
1.DELETE FROM YOUR_MAIN_TABLE;
Step 3:
1.INSERT INTO YOUR_MAIN_TABLE SELECT * FROM YOUR_VOLATILE_TABLE;