JOIN STRATEGY

209.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)

Merge (Exclusion)

Requirements:

• The rows to be joined have to be located on a common AMP
• Both spools have to be sorted by the ROWID calculated over the join column(s)

Process:

The ROWHASH of each qualifying row in the left spool is used to look up matching rows with identical ROWHASH in the right spool (by means of a binary search as both spools are sorted by ROWID)

Possible Join Preparations required:

• Re-Distribution of one or both spools by ROWHASH or
• Duplication of the smaller spool to all AMPs
• Sorting of one or both spools by the ROWID

while joining two tables the data will be redistributed or duplicated across all AMPs to make sure joining rows are in the same AMPs.

Relocation of rows to the common AMP can be done by redistribution of the rows by the join column(s) ROWHASH or by copying the smaller table as a whole to all AMPs.

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.

The common AMP of rows from two spools being joined is defined by the join columns.

Case 1 – P.I = P.I joins

• The Primary Indexes (or any other suitable index) of both tables equals the join columns:
• 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
• No join preparation is needed as the rows to be joined are already on the common AMP

Case 2 – P.I = non Index joins

• Only the Primary Index (or any other suitable index)  of one table matches the join columns: The rows of the second table have to be relocated to the common AMP
• 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 -Data in small table is duplicated to Every AMP where it is joined locally with large table
• • 1- duplicate all rows of one table onto every AMP (The duplication of all rows is done when the non-PI column is on a small table),
  • 2- redistribute the rows of one table by hashing the non-PI join column and sending them to the AMP containing the matching PI row,

Case 3 – non Index = non Index joins 

• Neither the Primary Index of the first table (or any other suitable index) nor the Primary Index (or any other suitable index)  of the second table matches the join columns:
• 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.
• redistribute both tables by hashed join column value

Nested Join

Example:

Select EMP.Ename , DEP.Deptno, EMP.salary

from

EMPLOYEE EMP ,

DEPARTMENT DEP

Where EMP.Enum = DEP.Enum

and EMp.Enum= 2345;— this results in nested join

 Hash join

• Hash Join is also based on equality condition (=).

•  We can say Hash Join to be close relative of Merge based on its functionality.

In case of merge join, joining would happen in same amp.

•   In Hash Join, one or both tables which are on same amp are fit completely inside the AMP’s Memory   .
• Amp chooses to hold small tables in its memory for joins happening on ROW hash.

• The Sprinter, but only if executed in FSG Cache

Requirements:

• The rows to be joined have to be located on a common AMP
• The smaller spool is sorted by the ROWHASH calculated over the join column(s) and kept in the FSG cache
• The bigger spool stays unsorted

Process:

• The bigger spool is full table scanned row by row
• Each ROWID from the bigger spools is searched in the smaller spool (by means of a binary search)

Possible Join Preparations required:

• Re-Distribution of the smaller spool by ROWHASH or
• Duplication of the smaller spool to all AMPs
• Sorting of the smaller spools

Advantages of Hash joins are

1. They are faster than Merge joins since the large table doesn’t need to be sorted.
2. Since the join happening b/w table in AMP memory and table in unsorted spool, it happens so quickly.

 Exclusion Join

These type of joins are suggested by optimizer when following are used in the queries

• – NOT IN
• – EXCEPT
• – MINUS
• – SET subtraction operations

Select

EMP.Ename , DEP.Deptno, EMP.salary

from

EMPLOYEE EMP

WHERE

EMP.Enum

NOTIN

( Select Enum from

DEPARTMENT DEP

where Enum isNOTNULL );

Please make sure to add an additional WHERE filter “with <column> IS NOT NULL” since usage of NULL in a NOT IN <column> list will return no results.

Exclusion join for following NOT In query has 3 scenarios

Case 1: matched data in “NOT IN” sub Query will disqualify that row

Case 2: Non-matched data in “NOT IN” sub Query will qualify that row

Case 3: Any Unknown result in “NOT IN” will disqualify that row – (‘NULL’ is a typical example of this scenario).

Product join

• to find a match between two tables with a join condition which is not based on equality (>, <, <>), or join conditions are ORed together.
• The reason why we call it “Product” join is that, the number of comparisons required is the “product” of the number of rows of both tables. For example, table t1 has 10 rows, and table t2 has 25 rows, then it would require 10×25=250 comparisons to find the matching rows.
• When the WHERE clause is missing, it will cause a special product join, called Cartesian Join or Cross Join, 

Requirements:

• The rows to be joined have to be located on the AMP
• No spool needs to be sorted!

Process:

• A full table scan is done on the smaller spool and
• Each qualifying row of spool 1 is compared against each row of spool 2

Possible Join Preparations required:

• Re-Distribution of one or both spools by ROWHASH or
• Duplication of the smaller spool

Sliding Window Merge Join

The Teradata Traditional Merge Join

Requirements:

• The rows to be joined have to be located on a common AMP
• Both spools have to be sorted by the ROWID calculated over the join column(s)

Possible Join Preparations required:

• Re-Distribution of one or both spools by ROWHASH or
• Duplication of the smaller spool to all AMPs
• Sorting of one or both spools by the ROWID

Two different algorithms can be used for the Merge Join:

1. The Fast Path Algorithm

The comparision takes place alternating from both sides starting wit the left table. The algorithm tries to join rows with matching rowhash. If there is no match, the pointer is positioned on the row with the next highest rowhash value and the comparison continues until all rows have been compared.

This method is used if left and right table are full table scanned:

2. The Slow Path Algorithm

This algorithm reads each row from the left table and tries to match it against rows with the same rowhash from the right table.

This method is used if the left table is accessed via an index:

Traditional Merge Joins have a big advantage over other join types which don’t require both tables to be sorted by rowhash of the join columns:

Each data block of both tables is accessed exactly once (the algorithm basically slides down on each of the rowhash sorted tables) and it is therefore less sensitive to the size of available FSG cache.

The Teradata Sliding Window Merge Join

The sliding window merge join can be considered as an advancement of the traditional merge join. After introducing the feature of row partitioning, it was required to find an algorithm which allows to join a row partitioned table (PPI table) with

• Another PPI table having different partition characteristics
• A Non-partitioned table

The optimizer has the possibility to change a PPI table into a NPPI table and vice versa. This step can be followed by a traditional merge join. Nevertheless, in order to avoid this join preparation step, a sliding window merge join can be executed without the requirement of the restructuring of tables.

As opposed to non-partitioned tables (NPPI tables), which are only sorted by rowhash, PPI tables are sorted on each AMP by two levels:

1. Each row is placed into its assigned row partition.
2. Within each row partition the rows are sorted by rowhash (the same way the rows of a NPPI table are stored).

The Sliding Window Merge Join was designed to be able to directly join

• A NPPI table with a PPI table or
• Two PPI tables with different partitioning

Directly means without changing a PPI table into a NPPI table or the other way around.

In order to understand the sliding window join process, one has to remember how PPI table rows are stored:

 Data rows are sorted by rowhash within the data blocks, but several row partitions can hold the same rowhash value! 

The easiest way of merge joining a NPPI table against a PPI table seems by join the NPPI table against each PPI table partition. This would be a reasonable solution because the NPPI table, and each partition of the PPI table, are sorted by rowhash allowing to execute a binary search within the data blocks of each partition.

Nevertheless, for performance reasons, Teradata implements a slightly different, but faster, algorithm:

The AMP reads the first data block from the NPPI table and one data block per partition from the PPI table. The rows are joined (binary search) and the algorithm moves down both tables by reading data block after data block from both tables.

The data block of the NPPI tables stays in FSG cache as long as data blocks from any partition of the PPI tables can be matched. If no more rows can be matched, the next data block of the NPPI tables is moved into the FSG cache and above described process is repeated, until the last data blocks of each table and partition is reached.

This process requires each data block of each table to be touched  exactly once (similar to a traditional merge join).

This process could theoretically result in a similar join performance, as we can reach with a traditional merge join, but there is one restriction degrading performance: The available FSG cache memory.

If the FSG cache is not big enough to hold at least one data block from each PPI table partition, the optimizer has to split the join process into so-called windows.

Assume for example, that the PPI tables consists of 4 partitions but there is only space in FSG cache for the first data block of  2 partitions. In this case the process would define 2 windows, each one consisting of 2 partitions:

1. Join the NPPI table against the first 2 partitions
2. Join  the NPPI table against the remaining 2 partitions

As a result, the NPPI table has to be read twice and the join becomes more costly. The more windows are needed, the more costly the join becomes. If the NPPI table is small enough, caching effects could decrease the negative performance impact of having several windows.

The sliding window merge join has a similar performance pattern like a traditional merge join, if most of the partitions can be eliminated before the join takes place. The best case scenario is, when there is sufficient FSG cache available to join all partitions at once. This kind of setup is called single window merge join.

The join of two PPI tables with different partitioning is implemented similar to the join between a NPPI and a PPI table:

The left table and the right table rows are split into windows (each window containing a part of the row partitions) and each window from the left table is joined against each window from the right table.

Here is an example: If the join needs to create 5 windows from the left PPI table and 2 windows from the right PPI table, this would result in a product join of 5*2 windows:

210.What is the default join strategy in Teradata?

• There is no “default” join strategy.
• Optimizer decides the type of strategy based on the best retrieval path and other parameters to execute the query.
• Each join strategy has its own pros and cons, and it’s hard to say which one is the best, depending on different circumstances.
• The optimizer will choose the best join strategy based on data demographics, statistics and indexes if any of them are available.
• Using EXPLAIN can help find out what join strategies are to be adopted. No matter which join strategy, it is always applied between two tables. The more tables, the more join steps.
• Rows must be on the same AMP to be joined.
• So row distribution or duplication is unavoidable for some join strategies.

• types of join
  • Product Join
  • Merge Join
  • Exclusion Join
  • Hash Join
  • Nested Join

211.What are the types of JOINs available in Teradata?

Types of JOINs are  :

• Inner Join,
• Outer Join (Left, Right, Full), S
• elf 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.