How do you interpret the confidence levels and join methods in Teradata explain plans?

1 Confidence levels

Confidence levels are indicators of how accurate Teradata's statistics and estimates are for the tables and columns involved in your query. They range from low to high, and reflect how well Teradata knows the data distribution, cardinality, and uniqueness of the values. The higher the confidence level, the more likely Teradata will choose an optimal join plan and allocate the appropriate amount of spool space and CPU time. The lower the confidence level, the more likely Teradata will overestimate or underestimate the number of rows and use suboptimal join methods or hash redistribution. You can check the confidence levels in the explain plan by looking for phrases like "high confidence", "low confidence", or "no confidence". You can improve the confidence levels by collecting statistics on the relevant tables and columns, especially those used in join conditions, filters, and aggregations.

2 Join methods

Join methods are the techniques that Teradata uses to combine rows from two or more tables based on a common condition. There are four main join methods in Teradata: nested join, product join, merge join, and hash join. Each join method has its own advantages and disadvantages, depending on the size, distribution, and confidence level of the tables involved. You can identify the join method in the explain plan by looking for phrases like "nested join", "product join", "merge join", or "hash join". You can also see if Teradata uses any additional steps like sorting, duplication, or redistribution to prepare the tables for the join. You can influence the join method by choosing appropriate join conditions, indexes, and partitioning schemes, as well as adjusting the optimizer parameters and settings.

3 Nested join

A nested join is a join method that compares each row from one table (the inner table) with all the rows from another table (the outer table) until it finds a match or exhausts the search. A nested join is efficient when the inner table is small and has a unique index or primary key on the join column, and the outer table is large and has a non-unique index or no index on the join column. A nested join is inefficient when both tables are large or have no indexes on the join columns, as it requires a lot of comparisons and I/O operations. You can recognize a nested join in the explain plan by looking for phrases like "for each row in", "retrieve row from", and "if exists".

4 Product join

A product join is a join method that combines every row from one table with every row from another table, regardless of the join condition. A product join is usually the last resort when no other join method is possible or feasible, as it generates a large amount of data and consumes a lot of CPU and spool space. A product join is sometimes necessary when the join condition involves expressions, functions, or constants that prevent Teradata from using indexes or partitions. You can identify a product join in the explain plan by looking for phrases like "all-AMPs join" and "cartesian product".

5 Merge join

A merge join is a join method that sorts both tables by the join columns and then merges them by comparing the values in order. A merge join is effective when both tables are large and have matching or compatible indexes or partitions on the join columns, as it avoids redistribution and duplication of data. A merge join is ineffective when either table is unsorted or has a different order or format of the join columns, as it requires extra steps to sort or convert the data. You can detect a merge join in the explain plan by looking for phrases like "merge into", "sort to order", and "end of merge".

6 Hash join

A hash join is a join method that applies a hash function to the join columns of both tables and then matches the rows with the same hash value. A hash join is efficient when both tables are large and have no indexes or partitions on the join columns, as it reduces the data to a smaller set of hash values and avoids sorting. A hash join is inefficient when either table is skewed or has a low confidence level on the join columns, as it may cause uneven distribution or overflow of data. You can spot a hash join in the explain plan by looking for phrases like "hash join", "hash by", and "hash value".

7 Here’s what else to consider

This is a space to share examples, stories, or insights that don’t fit into any of the previous sections. What else would you like to add?