Teradata Joining Strategies

Teradata Join Strategies are utilized by the optimizer to choose the least cost plan and better performance. The strategy will be chosen based on the available information to the Optimizer like Table size, PI information, Stats Information. 

Teradata join strategies are following:

·       Merge (Exclusion)

·       Nested

·       Row Hash

·       Product (including Cartesian Product joins)

Merge Join Strategies

There are four different merge join strategies based on the redistribution. Below details have all these information.

Merge Join Strategy 1

The 1st merge join will utilize the Primary Index on both tables in the join Condition.

The key here is that both of the Primary Index columns of each table are used in the WHERE or ON clause in the join type.

The inner join above focuses on returning all rows when there is a match between the two tables. The ON clause is extremely important because this join establishes the join (equality) condition. Think of this as if you were repairing a deck and you required a certain kind of wood in order to make sure you had a match. You would go down to the local home improvement store to locate the right pieces of wood to repair the deck. A match would definitely not be cedar if your deck required pressured pine.

To investigate further, each matching row is joined where Emp = Emp which is stated from the ON Clause in the JOIN. If we analyze further, EMP is the Primary Index for both tables. This first merge join type is extremely efficient because both columns in the ON clause are the Primary Indexes of their respective tables. When this occurs, NO data has to be moved into spool and the joins can be performed in what is called AMP LOCAL. Teradata can perform this join with rapid speed. Remember that the less data that has to be moved to complete a join the better the performance will be achieved. The end result of this join is that the rows from each table are physically joined together to form one row.

Merge Join Strategy 2

The previous join strategy is the best join scenario because it focuses on utilizing the Primary Index Column in both tables for the equality (ON) condition. The next best scenario is when a join is performed on a Primary Index column of one table to a non-primary indexed column of another table.

In this example, two tables are being joined based on the DEPT column. In the department table, the Primary Index column is DEPT. As we know, this is a good match based on the equality (ON) condition. However, the employee table has EMP as the Primary Index column. Which table do you think will move based on this join?

Regardless of the equality condition, the primary objective is to bring the rows together from each table on the same AMPs. There are several options that the Teradata Optimizer could choose in order to complete this task. The first option is to duplicate the smaller table on all AMPs. The second option could be to leave the department table that has an equality condition match on the Primary Index Column stationary on the AMP. The next step would be to move the rows from the Employee table into spool. This would be accomplished by hashing (locating) the columns in the employee table, and then moving these rows into spool to the appropriate AMPs where the department table rows reside. What has occurred here is that the stationary table is already sorted by the hash code and the spool table will be re-sorted by hash code in spool to the appropriate matching AMPs.

Clearly the second option is an excellent choice for the Teradata Optimizer because it reduces the amount of resources necessary to complete the join and improve performance.

Merge Join Strategy 3

The third scenario is where neither table is being joined on the Primary Index of either table. In this case Teradata will redistribute both tables into spool and sort them by hash code. When we want to redistribute and sort by hash code we merely hash the non-primary index columns and move them to the AMPs spool where they are sorted by hash. Once this is accomplished, then the appropriate rows are together in spool on all the AMPs. It is now join time!

In the previous example, the columns in the join equality are MgrEmp = MgrNo. The Primary Index of the department table is DEPT and the Primary Index for the manager table is LOC. In this case, both columns being utilized in this join equality are not part of the Primary Index columns. So what strategy will Teradata take to resolve this join operation?

Basically rows from both tables will need to be rehashed and redistributed into SPOOL.

The reason is because neither columns selected in the ON Clause are the Primary Index of the respective tables. Therefore, both tables are redistributed based on the ON clause columns.

The next step in this process is to redistributed the rows and locate them to the matching AMPs. When this is completed, the rows from both tables will be located in two different spools. Lastly, the rows in each spool will be joined together to bring back the matching rows. This type of join strategy is extremely inefficient. It consumes a ton of resources and time to manage and assemble this type of join.

Merge Join Strategy 4

The fourth merge join strategy is called the big table - small table join. If one of the tables being joined is small, then Teradata may choose a strategy that will duplicate the smaller table across all the AMPs. The key about this strategy is that regardless if the table is part of the Primary Index Column or not Teradata could still choose to duplicate the table across all the AMPs.

In this inner join above, the two tables involved in the join are the Employee table and the Department table. The DEPT column is the join equality that is making the match between the two tables. The DEPT column is the Primary Index Column in the Department table. The Employee table has the EMP column as the Primary Index. The final analysis of this join is that the Department table is small and makes a good candidate for this type of join strategy.

In order to join these two tables together, the first step is to get the rows together on the same AMP. In this case, since the Department table is small, Teradata will choose to duplicate the entire Department table on each AMP into spool. Once this is completed, then the next step is for the AMPs to join the base Employee rows with the Department rows.

`

Instead of redistributing the larger Employee table, which is not part of the Primary Index Column in the equality (ON) condition, Teradata will choose a more efficient strategy. This strategy would be to duplicate the smaller table across all the AMPs (Big Table -Small Table Join). This merge join strategy will consume minimal resources, and allow for Teradata to excel.

Nested Join

A nested join strategy is probably the most precise join available. This join is designed to utilize a unique index type (Either Unique Primary Index or Unique Secondary Index) from one of the tables in the join statement in order to retrieves a single row. It then matches that row to one or more rows on the other table being used in the join.

From the example above, the nested join has the join equality (ON) condition based on the DEPT column. The dept column is the Primary Index Column on the department table. In addition, the dept column is the Secondary Index Column in the employee table. Based on this information above, which rows will move?

Keep in mind that the nested join prides itself on being able to move a single row into spool and then matching that row with another table that contains several matches. How is this done? Analysis of this join statement indicates a new clause has been added to this join statement. This is known as the WHERE option. When utilized, the WHERE option allows for a single row to be retrieved from a table. In addition, a nested join will always use a unique index to isolate that single record and then join that record to another table. The other table may use an index or it may not. However, the best practice is to always use columns that have indexes when doing joins. Teradata has superior knowledge on indexed columns and can utilize this information to choose an aggressive strategy to complete a join. Utilization of indexes in join statements will improve performance and utilize less resources as the below diagram illustrates.

Since there is only one row in the department table that has a match for department =10, which is based on the AND option in the join statement, the Teradata Optimizer will choose a path to move the department table columns into spool and duplicate them across all the AMP’s.

Once this is completed, then the matches will proceed with that single record (10 and SALES) to the second table, which did not move from the base AMP. Nested Joins are great in an OLTP Environment because of the usage of both Unique and Non-Unique Indexes. In addition, a nested join can reduce the resources necessary to complete the join. Finally, nested joins, which are similar to all the join strategies discussed, must have an equality condition in the ON Clause (d.dept = e.dept).

Hash Join

The Hash Join is part of the Merge Join Family. Remember that the key to a Merge Join is based on an equality condition such as E.Dept = D.Dept in the ON clause of the join statement. A Hash Join can only take place if one or both of the tables on each AMP can fit completely inside the AMP’s memory.

Hash Join Strategy

In this example, the Hash Join has a join equality (ON) condition based on the EMP and MGREMP Columns. The key point here is the columns do not necessarily have to the same name when doing a join operation. The columns names can be different but the row information has to be similar in order for the match to work. Both the EMP and MGREMP columns have the same type of information so therefore a join based on these column names will be successful. In addition, EMP column is the Primary Index column on the employee table. However, the MGREMP column is not an index column in the department table. Based on this information above, which rows will move?

Remember that the key to determining a Hash Join is if the SMALLER TABLE can be held completely in each AMP’s MEMORY.

The Hash Join process is where the smaller table is sorted by row hash and duplicated on every AMP. The key here is that the smaller table is required to be held completely in each AMP’s memory. Teradata will use the join column of the larger table in order to search for a match. The row hash join is extremely efficient because it eliminates the sorting, redistribution, and or copying of the larger table into spool. In addition, the rows that are duplicated into the AMP’s memory yield increased performance because the rows never go into spool. Rows that go into spool always have to involve disk activity. AMP memory does not involve disk interaction, which automatically increases performance. Hash Joins and Nested Joins are both Great in an OLTP Environment for these reasons.

Exclusion Join

All of the joins that we have reviewed up to this point were based on finding matching rows based on a join equality condition. The returned rows from these types of joins compared rows from both tables in a join and then returned rows that matched. In addition, these joins were inclusive. When working with exclusion joins the thought process has to be reversed. Exclusion Joins have one primary function. They exclude rows during a join. The best example here is when I was out with my best friend and his two sons (Blake and Zach). They were telling a story that sounded believable about their dog Freddie who allegedly chased a neighbor’s cat down the street. When they completed the story, I asked them if this was true…they said in unison “NOT”! Well the NOT statement works exactly the same in Teradata. When you put a NOT in front of a statement it will give you the opposite answer.

SELECT EMP, DEPT, NAME 
FROM 
EMPLOYEETABLE
WHERE DEPT=10 and 
EMP NOT IN (SELECT MGREMP from DEPTTABLE WHERE MGREMP IS NOT NULL )

As you can see in the above example and as has been discussed above this type of join utilizes the NOT IN statement. Exclusion joins are used for finding rows that don’t have a matching row in the other table. Queries with the NOT IN operator are the types of queries that always result in exclusion joins. In this case, this query will find all the employees who belong to department 10 who are NOT managers.

These joins will always involve a Full Table Scan because Teradata will need to compare every record to eliminate rows that will need to be excluded. With this being stated, this type of join can be resource intensive if the two tables in this comparison are large.

In addition, the biggest problem with the Exclusion Joins is when the NOT IN statement is used. The reason for this is that NULLs are considered unknowns so the data returned in the answer will be NULLs. There are two ways to correct this:

·       Define NOT IN columns as NOT NULL on the CREATE TABLE.

·       Add the “ AND WHERE Column IS NOT NULL” to the end of the JOIN as seen in the above example.

Product Joins

What Makes Product Joins Different

Product Joins compare every row of one table to every row of another table. They are called product joins because they are a product of the number of rows in table one multiplied by the number of rows in table two. For example, if one table had five rows and the other table had five rows then the Product Join would compare 5 x 5 or 25 rows with a potential of 25 rows coming back.

SELECT E.EMP,D.DEPT 
FROM EMPLOYEETABLE E,DEPTTABLE D
WHERE
EMP LIKE '_b%'

About 99% of the time, product joins are major mistakes. The recommendation is to avoid these types of queries whenever possible. The reason is because all rows in both tables will be compared. Remember, Teradata tables have the potential to contain millions of rows. If a user accidentally writes a product join against two tables that have 1 million rows each. The result set would return One Trillion Rows (1000000000000)! Needless to say this is a mistake you want to make. So how do you avoid writing a product join?

To avoid a product join, check your syntax to ensure that the join is based on an EQUALITY condition. In the join syntax example above, the equality statement reads “WHERE EMP Like ‘_b%’”. Because this clause is not based on a common domain condition between the two tables (i.e., e.dept = d.dept), the result is a product join. Another cause of a product join is when aliases are not used after being established. Finally check your join syntax to ensure the WHERE clause is not missing.

Cartesian Product Join

Just as discussed with Product Joins above, a Cartesian Product Join is usually something you want to avoid. If we decided to run this query in Michigan this would be call the big “Mistake on the Lake”. A Cartesian Product Join will join every row in one table to every row in another table. The only thing that decides the number of rows will be the total number of rows from both tables. If one table had 5 rows and another had 10 rows then you will always get 50 rows returned. Imagine this situation. if we have a table with 10 million rows and another with 25 million rows and a Cartesian product join is written (by accident) against these two tables. What will be the result? Well, based on the example above, you will get back about 25 Trillion Rows (250000000000000)! This is definitely NOT the correct answer this user would want. This is why spool space limitations are utilized.


SELECT E.EMP,D.DEPT 
FROM EMPLOYEETABLE E,DEPTTABLE D;

About 99% of the time, a Cartesian Product Join is a major problem. The recommendation is avoid these types of queries whenever possible. The reason is because all rows in both tables will be joined. So how do you avoid writing a Cartesian Product Join?

To avoid a Cartesian Product Join, check your syntax to ensure that the join is based on an EQUALITY condition. In the join syntax example above, the WHERE clause is missing. Because this clause is missing, a common domain condition between the two tables (i.e., e.dept = d.dept) does not exist, the result is a product join. Another cause of a product join is when aliases are not used after being established.

Teradata Interview Questions

Hi Everyone, 

This post aims at giving the important/repeated questions that have been asked in the interview for Teradata positions. Instead of just giving answers, i have explained most of the concepts in a detail manner for the better understanding and makes you to answer if the question is based out of your answer. Please post  if you need any more details on the concepts or Teradata related questions if you need an answer. I will keep updating this post and will include any sections if you are interested. ALL THE VERY BEST and Happy Reading.

TERADATA INTERVIEW QUESTIONS AND CONCEPTS

What is Teradata & how does it differ from other conventional Systems?

Teradata is a relational database that can store billions of rows and petabytes (1 petabyte=1000 terabytes) of data. The architecture of the system makes it possible and provides the flexibility to access & process the data in a faster way.

It differs from other conventional database systems from its architecture to the processing speed. Easy scalability and its fault tolerance keep the demand high for this system.

What are the main components of Teradata System?

Teradata has 3 main components which do wonder to the world of data management & Storage.

It has

1. PE (Parsing Engine) : Acts as a gate keeper to the Teradata Systems and manages all sessions, interprets the SQL statements for any errors, manages the access rights for the user, defines a least expensive optimizer plan for the query to execute and sends the request to AMP via Bynet.

2. Message Passing Layer (Bynet)  : Carries messages between the AMPs and PEs, provides Point-to-Point and Broadcast communications, Merging answer sets back to the PE and Making Teradata parallelism possible

3. AMP (Access Module Processor) : AMP is the heart of Teradata which does most of the operations for data storage and retrieval. It also takes care of  finding the rows requested, Lock management of the tables and rows, Sorting rows, Aggregating columns, Join processing etc.

What are Primary Index and Primary Key in Teradata?

Unlike other database systems, Teradata distributes the data based on PI (Primary Index). PI is defined at the time of table creation and database automatically takes the first column as the PI if the PI is not mentioned explicitly.

Since the data distribution is based on PI, it is wise to choose a PI that evenly distributes the data among the AMP. 

For example, if Table A has two columns like below and we have 5 AMPs in the System.

ID            Gender

1              Male

2              Male

3              Male

4              Male

5              Female

If we choose ID as PI, since the values are distinct all 5 rows are distributed evenly across all 5 AMPs. But if GENDER has been chosen as PI , we have only 2 distinct values and data will be stored in only 2 AMPS leaving other 3 AMPS empty and idle.

Note: Same Value of PI will be stored in the same AMP.

Primary Key is a concept that uniquely identifies a particular row of a table.

What are the types of PI (Primary Index) in Teradata?

There are two types of Primary Index. Unique Primary Index ( UPI) and Non Unique Primary Index (NUPI). By default, NUPI is created when the table is created. Unique keyword has to be explicitly given when UPI has to be created.

UPI will slower the performance sometimes as for each and every row , uniqueness of the column value has to be checked and it is an additional overhead to the system but the distribution of data will be even.

Care should be taken while choosing a NUPI so that the distribution of data is almost even . UPI/NUPI decision should be taken based on the data and its usage.

How to Choose Primary Index(PI) in Teradata?

Choosing a Primary Index is based on Data Distribution and Join frequency of the Column. If a Column is used for joining most of the tables then it is wise to choose the column as PI candidate.

For example, We have an Employee table with EMPID and DEPTID and this table needs to be joined to the Department Table based on DEPTID.

It is not a wise decision to choose DEPTID as the PI of the employee table. Reason being, employee table will have thousands of employees whereas number of departments in a company will be less than 100. So choosing EMPID will have better performance in terms of distribution.

How the data is distributed among AMPs based on PI in Teradata?

•       Assume a row is to be inserted into a Teradata table

•       The Primary Index Value for the Row is put into the Hash Algorithm

•       The output is a 32-bit Row Hash

•       The Row Hash points to a bucket in the Hash Map.The first 16 bits of the Row Hash of is used to locate a bucket in the Hash Map

•       The bucket points to a specific AMP

•       The row along with the Row Hash are delivered to that AMP

When the AMP receives a row it will place the row into the proper table, and the AMP checks if it has any other rows in the table with the same row hash. If this is the first row with this particular row hash the AMP will assign a 32-bit uniqueness value of 1. If this is the second row hash with that particular row hash, the AMP will assign a uniqueness value of 2. The 32-bit row hash and the 32-bit uniqueness value make up the 64-bit Row ID. The Row ID is how tables are sorted on an AMP.

This uniqueness value is useful in case of NUPI's to distinguish each BUPI value.

Both UPI and NUPI is always a One AMP operation as the same values will be stores in same AMP.

How Teradata retrieves a row?

For example, a user runs a query looking for information on Employee ID 100. The PE sees that the Primary Index Value EMP is used in the SQL WHERE clause.

Because this is a Primary Index access operation, the PE knows this is a one AMP operation. The PE hashes 100 and the Row Hash points to a bucket in the Hash Map that represents AMP X. AMP X  is sent a message to get the Row Hash and make sure it’s EMP 100.

What are Secondary Indexes (SI) , types of SI and disadvantages of Secondary Indexes in Teradata?

Secondary Indexes provide another path to access data. Teradata allows up to 32 secondary indexes per table. Keep in mind; row distribution of records does not occur when secondary indexes are defined. The value of secondary indexes is that they reside in a subtable and are stored on all AMPs, which is very different from how the primary indexes (part of base table) are stored. Keep in mind that Secondary Indexes (when defined) do take up additional space.

Secondary Indexes are frequently used in a WHERE clause. The Secondary Index can be changed or dropped at any time. However, because of the overhead for index maintenance, it is recommended that index values should not be frequently changed.

There are two different types of Secondary Indexes, Unique Secondary Index (USI), and Non-Unique Secondary Index (NUSI). Unique Secondary Indexes are extremely efficient. A USI is considered a two-AMP operation. One AMP is utilized to access the USI subtable row (in the Secondary Index subtable) that references the actual data row, which resides on the second AMP.

A Non-Unique Secondary Index is an All-AMP operation and will usually require a spool file. Although a NUSI is an All-AMP operation, it is faster than a full table scan.

Secondary indexes can be useful for:

•       Satisfying complex conditions

•       Processing aggregates

•       Value comparisons

•       Matching character combinations

•       Joining tables

How are the data distributed in Secondary Index Subtables in Teradata?

When a user creates a Secondary Index, Teradata automatically creates a Secondary Index Subtable. The subtable will contain the:

•       Secondary Index Value

•       Secondary Index Row ID

•       Primary Index Row ID

When a user writes an SQL query that has an SI in the WHERE clause, the Parsing Engine will Hash the Secondary Index Value. The output is the Row Hash, which points to a bucket in the Hash Map.

That bucket contains an AMP number and the Parsing Engine then knows which AMP contains the Secondary Index Subtable pertaining to the requested USI information. 

The PE will direct the chosen AMP to look-up the Row Hash in the Subtable. The AMP will check to see if the Row Hash exists in the Subtable and double check the subtable row with the actual secondary index value. Then, the AMP will pass the Primary Index Row ID back up the BYNET network. This request is directed to the AMP with the base table row, which is then easily retrieved.

What are the types of JOINs available in Teradata?

Types of JOINs are  :  Inner Join, Outer Join (Left, Right, Full), Self Join, Cross Join and Cartesian Joins.

The key things to know about Teradata and Joins

•       Each AMP holds a portion of a table.

•       Teradata uses the Primary Index to distribute the rows among the AMPs.

•       Each AMP keeps their tables separated from other tables like someone might keep clothes in a dresser drawer.

•       Each AMP sorts their tables by Row ID.

•       For a JOIN to take place the two rows being joined must find a way to get to the same AMP.

•       If the rows to be joined are not on the same AMP, Teradata will either redistribute the data or duplicate the data in spool to make that happen.

What are the types of Join Strategies available in Teradata?

Join Strategies are used by the optimizer to choose the best plan to join tables based on the given join condition.

•       Merge (Exclusion)

•       Nested

•       Row Hash

•       Product (including Cartesian Product joins)

There are different types of merge join strategies available. But in general , while joining two tables the data will be redistributed or duplicated across all AMPs to make sure joining rows are in the same AMPs.

If the two tables are joined based on PI, no redistribution/duplication will happen as the rows will be in the same AMP and performance will be better.  If one table PI is used and Other table PI not used, redistribution/duplication of the table will happen based on the table size.In these cases Secondary Indexes will be helpful.

Explain types of re-distribution of data happening for joining of columns from two tables in Teradata?

Case 1 - P.I = P.I joins

Case 2 - P.I = non Index joins

Case 3 - non Index = non Index joins

Case1 - there is no redistribution of data over amp's. Since amp local joins happen as data are present in same AMP and need not be re-distributed. These types of joins on unique primary index are very fast.

Case2 - data from second table will be re-distributed on all amps since joins are happening on PI vs. non Index column. Ideal scenario is when small table is redistributed to be joined with large table records on same amp

case3 - data from both the tables are redistributed on all AMPs. This is one of the longest processing queries; Care should be taken to see that stats are collected on these columns

What is Partitioned Primary Index (PPI) in Teradata?

Partitioned primary index is physically splitting the table into a series of subtables, one for every partitioning value. When a single row is accessed, it looks first at the partitioning value to determine the subtable, then at the primary index to calculate the rowhash for the row(s).

For example, we have PPI on a MONTH Column, the rows of particular months are all sorted with in the same partition and whenever data is accessed for particular month, it will retrive the data in a faster way.

It helps to avoid full table scans.

What are the advantages and disadvantages of PPI in Teradata?

Advantages:

·      Range queries don’t have to utilize a Full Table Scan.

·      Deletions of entire partitions are lightning fast.

·      PPI provides an excellent solution instead of using Secondary Indexes

·      Tables that hold yearly information don’t have to be split into 12 smaller tables to avoid Full Table Scans (FTS). This can make modeling and querying easier.

·      Fastload and Multiload work with PPI tables, but not with all Secondary Indexes.

Disadvantages:

·      A two-byte Partition number is added to the ROW-ID and it is now called a ROW KEY. The two-bytes per row will add more Perm Space to a table.

·      Joins to Non-Partitioned Tables can take longer and become more complicated for Teradata to perform.

·      Basic select queries utilizing the Primary Index can take longer if the Partition number is not also mentioned in the WHERE clause of the query.

·      You can’t have a Unique Primary Index (UPI) if the Partition Number is not at least part of the Primary Index. You must therefore create a Unique Secondary Index to maintain uniqueness.

Volatile and Global Temporary Tables in Teradata?

Volatile tables are temporary tables that are materialized in spool and are unknown to the Data Dictionary.

A volatile table may be utilized multiple times and in more than one SQL statement throughout the life of a session. This feature allows for additional queries to utilize the same rows in the temporary table without requiring the rows to be rebuilt.

Volatile tables are local to session and the tables are dropped once the session is disconnected.

ON COMMIT PRESERVE ROWS option should be mentioned at the time of table creation. It means that at the end of a transaction, the rows in the volatile table will not be deleted. The information in the table remains for the entire session. Users can ask questions to the volatile table until they log off. Then the table and data go away.

Global Temporary Tables are similar to volatile tables in that they are local to a user’s session. However, when the table is created, the definition is stored in the Data Dictionary. In addition, these tables are materialized in a permanent area known as Temporary Space. Because of these reasons, global tables can survive a system restart and the table definition will not discarded at the end of the session. However, when a system restarts, the rows inside the Global Temporary Table will be removed. Lastly, Global tables require no spool space. They use Temp Space.

Statistics can be collected in both of the tables in TD13 Version. Previously Collecting Stats on Volatile tables are not allowed.

SubQuery and Correlated Subquery in teradata?

Sub queries and Correlated Sub queries are two important concepts in Teradata and used most of the times.

The basic concept behind a subquery is that it retrieves a list of values that are used for comparison against one or more columns in the main query. Here the subquery is executed first and based on the result set, the main query will be executed.

For example,

Select empname,deptname from employee where empid IN ( select empid from salarytable where salary>10000).

In the above query, empid will be choosen first based on the salary in the subquery and main query will be executed based on the result subset.

Correlated Subquery is an excellent technique to use when there is a need to determine which rows to SELECT based on one or more values from another table.It combines subquery processing and Join processing into a single request.

It first reads a row from the main query and then goes into the subquery to find the rows that match the specified column value.Then it goes for the next row from the main query. This process continues untill all the qualifying rows from MAIN query.

For example,

select empname,deptno, salary

from employeetable as emp

where

salary=(select max(salary) from employeetable as emt where emt.deptno=emp.deptno)

Above query returns the highest paid employee from each department. This is also one of the scenario based questions in teradata.

How to calculate the tablesize , database size  and free space left in a database in teradata?

DBC.TABLESIZE and DBC.DISKSPACE are the systems tables used to find the space occupied.

Below Query gives the table size of each tables in the database and it will be useful to find the big tables in case of any space recovery.

SELECT         DATABASENAME,

TABLENAME,

SUM(CURRENTPERM/(1024*1024*1024)) AS "TABLE SIZE"

FROM

DBC.TABLESIZE

WHERE

DATABASENAME = <'DATABASE_NAME'> AND TABLENAME = <'TABLE_NAME'>

GROUP BY 1,2;

Below query gives the total space and free space available in a database.

SELECT

        DATABASENAME DATABASE_NAME,

        SUM(MAXPERM)/(1024*1024*1024) TOTAL_PERM_SPACE,

        SUM(CURRENTPERM)/(1024*1024*1024) CURRENT_PERM_SPACE,

        TOTAL_PERM_SPACE-CURRENT_PERM_SPACE as FREE_SPACE

 FROM

        DBC.DISKSPACE

 WHERE

        DATABASENAME = <'DATABASE_NAME'>

group by 1;

What are the Performance improvement techniques available in Teradata?

First of all use EXPLAIN plan to see how the query is performing. Keywords like Product joins, low confidence are measures of poor performance.

Make Sure, STATS are collected on the columns used in WHERE Clause and JOIN columns. If STATS are collected , explain plan will show HIGH CONFIDENCE This tells the optimizer about the number of rows in that table which will help the optimizer to choose the redistribution/duplication of smaller tables.

Check the joining columns & WHERE Clause whether PI, SI or PPI are used.

Check whether proper alias names are used in the joining conditions.

Split the queries into smaller subsets in case of poor performance.

 What does” Pseudo Table” Locks mean in EXPLAIN Plan in Teradata?

It is a false lock which is applied on the table to prevent  two users from getting conflicting locks with all-AMP requests.

PE will determine an particular AMP to manage all AMP LOCK requests for given table and Put Pseudo lock on the table.

Can you compress a column which is already present in table using ALTER in Teradata?

No,   We cannot use ALTER command to compress the existing columns in the table.

A new table structure has to be created which includes the Compression values and data should be inserted into Compress column table.

Please note - ALTER can be used only to add new  columns with compression values to table.

How to create a table with an existing structure of another table with or without data and also with stats defined in Teradata?

CREATE TABLE new_TABLE AS old_TABLE WITH DATA

CREATE TABLE new_TABLE AS old_TABLE WITH NO DATA

CREATE TABLE new_TABLE AS old_TABLE WITH DATA AND STATS

How to find the duplicate rows in the table in Teradata?

Group by those fields and add a count greater than 1 condition for those columns

For example –

SELECT name, COUNT (*) FROM TABLE EMPLOYEE GROUP BY name HAVING COUNT (*)>1;

Also  DISTINCT will be useful. If both DISTINCT and COUNT(*) returns same number then there are no duplicates.

Which is more efficient GROUP BY or DISTINCT to find duplicates in Teradata?

With more duplicates GROUP BY is more efficient while if we have fewer duplicates the DISTINCT is efficient.

What is the difference between TIMESTAMP (0) and TIMESTAMP (6) in teradata?

Both has the Date and Time Values. The major difference is that TIMESTAMP (6) has microsecond too.

What is spool space and when running a job if it reached the maximum spool space how you solve the problem in Teradata?

Spool space is the space which is required by the query for processing or to hold the rows in the answer set. Spool space reaches maximum when the query is not properly optimized. We must use appropriate condition in WHERE clause and JOIN on correct columns to optimize the query. Also make sure unncessary volatile tables are dropped as it occupies spool space.       

         

Why does varchar occupy 2 extra bytes?

The two bytes are for the number of bytes for the binary length of the field.

It stores the exact no of characters stored in varchar

What is the difference between User and database in Teradata?

- User is a database with password but database cannot have password

- Both can contain Tables , views and macros

- Both users and databases may or may not hold privileges

- Only users can login, establish a session with Teradata database and they can submit requests

What are the types of HASH functions used in teradata?

These are the types of HASH, HASHROW, HASHAMP and HASHBAKAMP. Their SQL functions are-

HASHROW (column(s))

HASHBUCKET (hashrow)

HASHAMP (hashbucket)

HASHBAKAMP (hashbucket)

To find the data distribution of a table based on PI, below query will be helpful. This query will give the number of records in each AMP for that particular table.

SELECT HASHAMP(HASHBUCKET(HASHROW(PI_COLUMN))),COUNT(*) FROM TABLENBAME GROUP BY 1.

TERADATA UTILITIES:

Explain Fast Load in Teradata?

Loads large amount of data from external file into an empty table at high speed.Only load one empty table with 1 fast load job.Tables defined with Referential integrity, secondary indexes, join indexes, hash indexes or triggers cannot be loaded with FastLoad.Duplicate rows cannot be loaded into a multiset table with FastLoad

FastLoad has two phases – Data Acquisition and Application. It requires separate error table for each phase. Use the BEGIN LOADING statement to specify (and create) the two error tables for the operation. 

We can specify the error table in the same database as the data table or different database.

We must remove the error tables before we re-run the same load job or it will terminate in an error condition.

Explain Multi Load in Teradata?

Used for loading, updating or deleting data to and from populated tables, typically with batch inputs from a host file.Cannot process tables defined with USI’s, Referential Integrity, Join Indexes, Hash Indexes, or Triggers.No data retrieval capability.Import tasks require use of Primary Index

The Multiload supports five target tables per script. Tables may contain pre-existing data. Ability to do INSERTs UPDATEs, DELETEs and UPSERTs.

Use RELEASE MLOAD tablename; statement if MLOAD doesn’t successfully completed and there is no desire to restart the MLOAD. This will return the target table to general availability. Error tables should be dropped manually, as RELASE MLOAD will not drop them automatically.

Explain TPUMP (Teradata Parallel Data Pump) Utility in Teradata?

•TPUMP allows near real time updates from Transactional Systems into the Data Warehouse.It can perform Insert, Update and Delete operations or a combination from the same source.

•It can be used as an alternative to MLOAD for low volume batch maintenance of large databases.

•TPUMP allows target tables to have Secondary Indexes, Join Indexes, Hash Indexes, Referential Integrity, Populated or Empty Table, Multiset or Set Table or Triggers defined on the Tables.

•TPUMP can have many sessions as it doesn’t have session limit.

•TPUMP uses row hash locks thus allowing concurrent updates on the same table

How do you transfer large amount of data in Teradata?

Transferring of large amount of data can be done by using the various Teradata Utilities i.e. BTEQ, FASTLOAD, MULTILOAD, TPUMP and FASTEXPORT.

•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.

How to make sure BTEQ utility is not erroring out while dropping a table when the table doesnt exist in Teradata?

Setting the error level to 0 will achieve this.

ERRORLEVEL (3807) SEVERITY 0;

DROP TABLE TABLENAME;

ERRORLEVEL (3807) SEVERITY 8;

Difference between MultiLoad and TPump in Teradata?

Tpump provides an alternative to MultiLoad for low volume batch maintenance of large databases under control of a Teradata system.

Tpump updates information in real time, acquiring every bit of a data from the client system with low processor utilization.

It does this through a continuous feed of data into the data warehouse, rather than the traditional batch updates.

Continuous updates results in more accurate, timely data. Tpump uses row hash locks than table level locks. This allows you to run queries while Tpump is running.

Different phases of MultiLoad in Teradata?

• Preliminary phase

• DML phase

• Acquisition phase

• Application phase

• End phase

Which is faster – MultiLoad delete or Delete command in Teradata?

MultiLoad delete is faster than normal Delete command, since the deletion happens in data blocks of 64Kbytes, whereas delete command deletes data row by row.

Transient journal maintains entries only for Delete command since Teradata utilities doesn’t support Transient journal loading

For smaller table deletes, simple DELETE command is enough. Multiload delete is useful when the delete has to be performed on a large table in teradata.

How to Skip or Get first and Last Record from Flat File through MultiLoad in Teradata?

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

Why Fload doesn’t support multiset table in Teradata?

Fload does not support Multiset table because of restart capability.

Say, the fastload job fails. Till the fastload failed, some number of rows was sent to the AMP's.

Now if you restart FLOAD,  it would start loading record from the last checkpoint and some of the consecutive rows are sent for the second time. These will be caught as duplicate rows are found after sorting of data.

This restart logic is the reason that Fastload will not load duplicate rows into a MULTISET table. It assumes they are duplicates because of this logic. 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

How to skip the header row in the fastload script

RECORD 2; /* this skips first record in the source file */

DEFINE ...