Teradata Join Index

Roland Wenzlofsky

November 19, 2019

minutes reading time

What is the Teradata Join Index?

The Teradata Join index stores pre-joined tables, the result of aggregations, or simply the content of one table in a different structure. Join indexes are comparable to materialized views.

Although the name indicates that join indexes are an index structure, they have to be considered an added layer of pre-joined, pre-aggregated, or permanent tables with their own Primary Index, Secondary Indexes, and row-level partitioning.

The join index is comparable to a secondary index because it provides the Optimizer with additional access paths to the data.

Teradata stores join indexes as separate physical database objects, unlike secondary indexes stored as internal subtables.

The user can decide to store a join index in any database.

Because join indexes are stored like base tables, their maintenance costs are visible in table DBC.DBQLOGTBL (Teradata does not store information about the maintenance costs of other index types).

The use of a Join Index by the Optimizer is not subject to the user’s decision.

Only the Teradata Optimizer decides when it makes sense to use the join index from a cost perspective and when it is cheaper to use the underlying table structures.

Secondary indexes typically offer exactly one additional access path to the base table (hash lookup, in the case of a NUSI, also full index scan).

Join indexes can be modeled in various ways and serve as the basis for secondary indexes or partitioning.

When to Use a Join Index?

  • Join together large tables with a significant number of rows
  • Join large tables selecting only a few columns
  • Queries with complex expressions in their predicate
  • Denormalization of PDM to increase performance
  • Create alternative row-level partitioning
  • Create an alternative Primary Index as an additional access path
  • Movement of time-consuming joins and aggregations into the batch window

A join index provides different access paths to data and is used for performance tuning.

Higher performance usually is achieved by designing a join index with a different primary index or row-level partitioning.

However, even if the primary index or the partitions of the join index cannot be used for querying, the Optimizer will use the join index with a full index scan; if the index is considerably smaller than the base table, fewer data blocks have to be read.

The Primary Index of a Join Index

A unique Primary Index can be defined if the index is not compressed (this is not related to multivalue compression, but the compression of repeating groups in the index) and only on a single table join index.

This will give all the advantages of a UPI on a base table, such as ensuring that no duplicates can be inserted or created with an update statement.

Usually, the Primary Index is used for data distribution, and the rows are sorted by rowhash on the AMPs. But the Join Index also allows choosing another sort order by using the “ORDER BY VALUE” clause. Value order gives better performance for range queries:

    SELECT (a, b), (c, d, e)
    FROM TheTable
    LEFT JOIN Customer ON a= c
    ORDER BY a

Value order is only allowed for 4 Byte numeric values. Historically, this is the maximum permitted value for row hashes stored in each data block’s array of data pointers (DATE can be used as it’s internally stored as INTEGER).

Join Indexes with Built-In Functions

If the functions DATE, CURRENT_DATE, or CURRENT_TIMESTAMP are used in the Expression partition of a join index, a one-time evaluation occurs when the index is created and not dynamically when the index is used. This also applies to using these functions in a WHERE condition of the join index.

However, “ALTER TABLE TO CURRENT” can be used on the index to update the value in the Expression partition to the current value.

Why you should use OUTER JOINS

Since the rows that cannot be matched are lost with an INNER JOIN, it is better to define Join Indexes with OUTER JOIN. This allows more queries to be covered.

Join Index and Partitioning

  • A Join Index can be Row or Column Partitioned
  • Partitioning adds access path
  • A Join Index can be partitioned on top of the non-partitioned table
  • A non-partitioned Join Index on top of a partitioned base table is not recommended for performance reasons
  • A Row compressed Join Index can’t be row-level partitioned
  • The Partitioning Expression of a join index can be numeric or of a character data type
  • DATE, CURRENT_DATE, or CURRENT_TIMESTAMP functions can be used in the partition expression of a join index

Join Index Coverage

If the join index does not contain all selected columns (“not covering”), it can access these missing columns using the ROWID of the base table. The dummy column “ROWID” usually must be included (exceptions see below) in the SELECT list.

The cost saving is that the relevant rowids are first extracted into a spool file using the join index. In a subsequent step, the base table rows belonging to the rowids are queried.

If the join index contains all SELECT list columns (“covering”), no access to the base table is required.

There is no coverage possible for SELECT * FROM table_name queries:

Avoid the practice of writing SQL queries without specifying a column list or join index coverage is not given!

When will the Optimizer use a non-covering Join Index?

  • One of the join index definition columns is the dummy column “ROWID”. If this is the case, the base table ROWID stays with each index row, and a lookup of the base table row can be done as described above.
  • The UPI columns of the base table are available in the SELECT list of the Join Index. Teradata can hash this column combination to identify the AMP containing a row. Again, the rowids are used to access the base table. A message with the rowid is sent to the AMP to receive the required row.
  • The column set of a NUPI of the base table is contained in the select list of the join index (condition 1), and one of the two additional conditions is met:

    (2a) The Join Index contains the dummy column “ROWID” or
    (2b) There is a USI on the base table available, which matches the column set of the NUPI.

If condition (2a) is met, the base table rows can be accessed via the column “ROWID” by rehashing the NUPI columns and accessing the base table rows via the rowids extracted into a spool file.

Condition (2b) is similar to the UPI case described above. The AMPs owning the requested rows are identified by rehashing the USI columns. Everything else is the same as in condition (2a)

Coverage does not guarantee the use of a Join Index. The cost of using it must be less than accessing the base table using another index or a full table scan.

What are the Limitations of a Join Index?

  • The Usage of FULL OUTER JOIN is not allowed.
  • LEFT or RIGHT JOINS: on the inner side, at least one non-nullable column must be selected
  • OUTER JOIN is preferable for Join Index usage likelihood but not allowed for Aggregate Join Index
  • HAVING and QUALIFY not allowed
  • No Set Operations are allowed (UNION, INTERSECT, MINUS)
  • No Subqueries are allowed
  • No inequality conditions in ON clauses of join index definitions. Are allowed. Exception: If they are ANDed to at least one equality join condition
  • Only <, <=, >=, > as comparison operators allowed
  • TOP n and SAMPLE not allowed
  • The join index is marked as invalid when restoring a base table or database.
  • A maximum of 64 columns per table per Join Index is allowed
  • A maximum of 32 Indexes can be defined on a table, including join indexes
  • UPI only allowed for single table join index
  • Value-ordered NUSI and Partition Expression on the same Join Index columns are not allowed.

Multi-Table Join Index

Multi-Table Join Indexes allow you to move resource-intensive joins from the online to the batch window.

Shifting the workload does not reduce the total workload but moves it to a point in time that benefits the system’s overall performance.

The syntax for creating a join index is not much different from a CREATE TABLE AS statement:

SELECT t01.PK, t01.COL_A,t02.COL_B
t01.PK = t02.PK

The statement above shows several essential characteristics of a join index:

Join indexes are an excellent alternative to temporary tables. However, unlike a temporary table, the use of the join index is not guaranteed. Whether the Optimizer uses an existing join index depends on the available statistics.

A join index should only be created if the Optimizer can reuse it in different scenarios. The additional space required must be taken into account accordingly. A temporary table is probably better if join index’s usage cannot be achieved consistently.

Suppose we want to overcome any possible deviation between indexed and queried columns. In that case, it is feasible to store the pseudo-column ROWID in the join index, which allows the usage of this index even if not all needed columns are available in the query.

Teradata will get the missing columns from the base table (as it has the ROWID available, this is the same as primary index access).

Limitations of the Multi-Table Join Index

The bulk load utilities can only be used if no join index is defined.

However, a multi-table join index is usually used for pre-joining tables to avoid expensive online joins, such as creating ad-hoc reports. Joins are one of the most costly tasks. Often large amounts of data have to be redistributed.

The significant advantage of the Join Index is that Teradata entirely does the maintenance, and optimized methods are used to, e.g., keep an Aggregate Join Index up to date without re-aggregating the whole index table with every minor change.

However, since Fastload and Multiload are not possible, there is a contradiction here:

If the join index has to be removed before loading and then created again, the advantages mentioned above (for example, optimized aggregation) are not available.

Restrictions for outer-joined tables in the join index are another problem:

SELECT t01.PK, t01.COL_A,t02.COL_B
t01.PK = t02.PK

Each outer join  in a multi-table join index has to meet the following restrictions:

  • All columns from the left table have to be selected
  • At least one column from the right table must be defined as NOT NULL.

Compressed Join Index

Multi-Table Join Indexes can be compressed by putting brackets around a group of columns. Compression reduces occupied disk space as each compressed values group is only stored once (not in each row).

Frequent combinations of column values are, therefore, good candidates.

SELECT t01.PK,(t01.COL_A,t02.COL_B)
t01.PK = t02.PK

Single Table Join Index 

The name is somehow misleading as no join is involved. Single-Table join indexes are created from exactly one base table.

Their primary purpose is to have the same table available with a different primary index, partitioning, or a smaller table (the join index table) with fewer columns to be spooled.

Let’s assume we have the following table below:


By creating the below join index, we now have physically stored the same table twice, with two different primary indexes:

SELECT t01.PK, t01.COL_A

The join index with a different primary index gives the Teradata Optimizer an additional data access path.

Aggregate Join Index

Aggregate join indexes are pre-aggregations, automatically maintained in an optimized way by Teradata (only the required rows are re-aggregated). An Aggregate Join Index can be based on one or multiple tables.

The aggregate join index allows defining a summary table without violating the normalization of the data model.

Below is an example of a single-table aggregate join index:


Aggregate join indexes are limited in the aggregation functions, which can be applied: SUM, COUNT, MIN, or MAX.

An aggregate join index is only used if the following conditions are fulfilled:

  • The grouping clause must include all columns in the grouping clause of the query.
  • WHERE clause columns belonging to tables not defined in the aggregate join index must be part of the join index select list.
  • For an aggregate join index with a PPI, an aggregated column cannot be a partitioning column.

The Teradata Optimizer will consider rewriting a query to use an existing Aggregate Join Index.

It will consider the usage of an existing Aggregate Join Index in the following scenarios:

  • For the  coverage of queries containing a subquery (derived table spool)
  • For internally created distinct and grouping steps
  • To partly cover outer join queries
  • For partial/early grouping steps

Sparse Join Index

Adding a WHERE condition to the join index turns a given index into a sparse join index.

Only those rows are stored in the join index that fulfills the WHERE condition. This saves space. However, a sparse index only makes sense if the queries use the WHERE condition. If not, the join index cannot be used.

A sparse join index consumes less permanent space as only the rows matching the WHERE conditions are stored.

A further advantage is given because the maintenance of the index is optimized:

If a row is to be inserted, it is first checked to see whether it meets the WHERE condition. Only if the row is to be inserted does it meet the WHERE condition criteria. Teradata also checks whether it satisfies the partition expression.

Join Index Summary

Knowledge and preparation are needed to allow the Optimizer to use a join index.

A join index makes sense when queries are stable. In systems with many ad-hoc queries, finding a set of helpful join indexes that the Optimizer also uses is usually challenging.

Be warned when experimenting with join indexes in a production environment, as they may have unexpected side effects. I will give you one real-life example:

A daily batch load had a SQL step deleting a huge 6+ billion records table:

DELETE FROM the_huge_table;

It’s usually a big deal. The cylinder index marks the data blocks belonging to the table as free, and that’s it.

I overlooked this DELETE step in the load process and added a multi-table join index to the table.

The result was that the subsequent batch load took 20 hours to maintain the join index triggered by the DELETE.

I had to cancel the DELETE, and so did the subsequent ROLLBACK. This is always critical because the table is then in an inconsistent state. But that didn’t matter in this case because the table should be deleted entirely.

We can use the query below helps to find all join indexes available in a Teradata System:

SELECT * FROM dbc.Indices WHERE indextype='J';

See also:
The Secondary Index in Teradata – The NUSI compendium

  • Avatar
    Siyanda B says:

    Failed [5464 : HY000] Error in Join Index DDL, Only satisfiable single-table conditions and equality join conditions are allowed in the ON clause. What causes this error, I am struggling to resolve the because I do not understand the problem. When I execute the query (without CREATE JOIN INDEX statement), the query executes with no issues but when I encapsulate it in a JI it refuses.

  • Avatar
    Paramananda says:

    if we have a single table join index(st_jn_idx) on dept_no for emp table.
    How to join the dept table and the join single table join index on emp table.

    Can we write,
    select emp.*
    from dept as d join st_jn_idx as e
    on d.dept_no=e.dept_no;

    Kindly suggest.


  • {"email":"Email address invalid","url":"Website address invalid","required":"Required field missing"}

    You might also like