DWHPro Insights

Teradata Join Index: Types, Examples and EXPLAIN

By Roland Wenzlofsky ·

Teradata Join Index: Types, Examples and EXPLAIN

A Teradata join index is a persistent structure the optimizer can use to answer queries against its base tables. It can store selected columns from one table, a prejoined result, or an aggregate. Applications continue to query the base tables; EXPLAIN tells you whether the optimizer chooses the join index.

Updated and lab-tested 24 October 2026 on Teradata Database 20.00.28.81 with BTEQ 20.00.00.11, BTET transaction mode. The examples verify creation, optimizer selection and a maintenance check. They do not establish a production speedup or maintenance-cost percentage.

Join-index types and when to use them

  • Single-table join index: provides another physical representation of one table, such as a different primary index or a narrower set of columns.
  • Multi-table join index: stores a join result that eligible queries may reuse instead of repeating the join.
  • Aggregate join index: stores a supported aggregation that eligible queries may reuse or further aggregate.
  • Sparse join index: includes a WHERE condition so it stores a subset of the base data. This property can be combined with other join-index designs.

A useful candidate serves repeated, expensive work well enough to justify its permanent space and maintenance overhead. It is not automatically preferable to a secondary index, a temporary table or a query rewrite. Compare it with your real read and write workload.

Create the sample tables and data

Use a disposable lab database with the object names unused, sufficient permanent and spool space, and permission to create tables, join indexes and statistics. The full example loads 20,000 employees and 100 departments from the system calendar. Each department has 200 employees. Submit statements separately.

CREATE MULTISET TABLE dwhpro_ji_employee
(
    employee_id INTEGER NOT NULL,
    department_no INTEGER NOT NULL,
    salary DECIMAL(10,2) NOT NULL,
    employee_note CHAR(200)
) UNIQUE PRIMARY INDEX (employee_id);

CREATE SET TABLE dwhpro_ji_department
(
    department_no INTEGER NOT NULL,
    department_name VARCHAR(30) NOT NULL
) UNIQUE PRIMARY INDEX (department_no);

INSERT INTO dwhpro_ji_employee
SELECT n, n MOD 100, CAST(1000 + (n MOD 100) AS DECIMAL(10,2)), 'lab row'
FROM
(
    SELECT ROW_NUMBER() OVER (ORDER BY calendar_date) AS n
    FROM SYS_CALENDAR.CALENDAR
    QUALIFY n <= 20000
) AS src;

INSERT INTO dwhpro_ji_department
SELECT n, 'Department ' || TRIM(CAST(n AS VARCHAR(3)))
FROM
(
    SELECT ROW_NUMBER() OVER (ORDER BY calendar_date) - 1 AS n
    FROM SYS_CALENDAR.CALENDAR
    QUALIFY n < 100
) AS src;

COLLECT STATISTICS COLUMN (employee_id) ON dwhpro_ji_employee;
COLLECT STATISTICS COLUMN (department_no) ON dwhpro_ji_employee;
COLLECT STATISTICS COLUMN (department_no) ON dwhpro_ji_department;

The employee table is distributed by employee_id. Our target query filters on department_no and selects only three columns. Before creating the join index, this query used an all-AMP scan of the employee table in the lab:

Teradata Query Performance Tuning: The DWHPro Guide book cover

Get the complete Teradata tuning book, free

Teradata Query Performance Tuning: The DWHPro Guide
By Roland Wenzlofsky & Artemios Vogiatzis

206 pages on statistics and demographics, access paths and indexes, query execution plans, and joins. Get the full PDF and practical Teradata updates from DWHPro.

Confirm your email to receive your download link. Privacy policy. Unsubscribe in any newsletter.

About the free book
-- BEFORE: filter is not on the base table's primary index.
EXPLAIN SELECT employee_id, department_no, salary
FROM dwhpro_ji_employee WHERE department_no = 7;

Single-table join index: a tested alternative access path

CREATE JOIN INDEX dwhpro_ji_by_department AS
SELECT employee_id, department_no, salary
FROM dwhpro_ji_employee
PRIMARY INDEX (department_no);

COLLECT STATISTICS COLUMN (department_no) ON dwhpro_ji_by_department;

Explain the same base-table query again:

-- AFTER: the SQL still names the base table.
EXPLAIN SELECT employee_id, department_no, salary
FROM dwhpro_ji_employee WHERE department_no = 7;

Observed result: the plan named DWHPRO_JI_BY_DEPARTMENT and used a single-AMP RETRIEVE through its primary index. It estimated 200 rows with high confidence. The earlier plan scanned the base table across all AMPs and also estimated 200 rows. This change demonstrates an alternative access path; it does not measure elapsed-time improvement.

The SQL did not query the join index directly. The optimizer selected it because it could satisfy this query and was costed favorably in this setup. Another table size, predicate, selected column or statistics state can produce a different choice.

Verify automatic maintenance

While the single-table join index exists, the full script inserts one employee into department 7, checks the result, deletes that employee and checks again:

SELECT COUNT(*) AS department_rows, SUM(salary) AS department_salary
FROM dwhpro_ji_employee WHERE department_no = 7;

-- Test automatic maintenance on the existing single-table join index.
INSERT INTO dwhpro_ji_employee VALUES (20001, 7, 1007.00, 'maintenance check');
SELECT COUNT(*) AS department_rows, SUM(salary) AS department_salary
FROM dwhpro_ji_employee WHERE department_no = 7;
DELETE FROM dwhpro_ji_employee WHERE employee_id = 20001;
SELECT COUNT(*) AS department_rows, SUM(salary) AS department_salary
FROM dwhpro_ji_employee WHERE department_no = 7;

The observed count and salary sum were 200 / 201400.00 before the insert, 201 / 202407.00 afterward, and 200 / 201400.00 after the delete. This verifies the query results while the index is maintained. It does not quantify write overhead. The script then drops this join index before testing the next design.

Multi-table join index: store a reusable join result

CREATE JOIN INDEX dwhpro_ji_employee_department AS
SELECT e.employee_id, e.department_no, e.salary, d.department_name
FROM dwhpro_ji_employee AS e
INNER JOIN dwhpro_ji_department AS d
ON e.department_no = d.department_no
PRIMARY INDEX (department_no);

COLLECT STATISTICS COLUMN (department_no) ON dwhpro_ji_employee_department;
EXPLAIN SELECT e.employee_id, e.department_no, e.salary, d.department_name
FROM dwhpro_ji_employee AS e
INNER JOIN dwhpro_ji_department AS d
ON e.department_no = d.department_no
WHERE e.department_no = 7;

For the matching employee/department query in the downloadable script, EXPLAIN selected DWHPRO_JI_EMPLOYEE_DEPARTMENT through single-AMP primary-index access and estimated 200 rows. The script drops this index before testing the aggregate design, so the examples do not compete with one another.

An inner-join index stores matching rows. An outer-join design can retain unmatched rows and support a different set of query rewrites, but it must meet the documented restrictions. Choose join semantics for the required coverage; “always use an outer join” is not a complete design rule. Teradata’s worked join-index examples explain this distinction.

Aggregate join index: precompute department totals

CREATE JOIN INDEX dwhpro_ji_department_totals AS
SELECT department_no, COUNT(*) AS employee_count, SUM(salary) AS total_salary
FROM dwhpro_ji_employee
GROUP BY department_no
PRIMARY INDEX (department_no);

COLLECT STATISTICS COLUMN (department_no) ON dwhpro_ji_department_totals;
EXPLAIN SELECT department_no, COUNT(*) AS employee_count, SUM(salary) AS total_salary
FROM dwhpro_ji_employee GROUP BY department_no;

The matching GROUP BY query selected DWHPRO_JI_DEPARTMENT_TOTALS and scanned the 100 department-level rows in the lab. The base employee table contained 20,000 rows. The row-count reduction explains the opportunity, but measuring the full benefit requires the query frequency, write workload, storage and maintenance costs.

Covering and non-covering join indexes

A covering join index supplies the information needed for a query without retrieving missing values from the base table. A non-covering index can sometimes support a plan that looks up the remaining values. Eligibility depends on the available row identifiers or keys, the query and the supported optimizer transformations.

SELECT * does not universally prevent coverage. It requests every column, which can make a narrow index insufficient. In our single-table example, employee_note was deliberately excluded from the index. Explicit column lists make both the requirement and a coverage mismatch easier to see.

Coverage alone does not force index selection. A cheaper base-table access path can still win. For comparisons with another platform, see join indexes versus Snowflake materialized views.

Why is the optimizer not using my join index?

  1. Check the query match. Compare selected columns, join semantics, filters and aggregation grain with the definition.
  2. Check sparse-index coverage. The optimizer must be able to prove the stored subset can satisfy the relevant query requirement.
  3. Inspect the estimates. Check statistics on participating base tables and the join index where relevant. Our explicit index statistic is part of this lab, not a blanket collection policy for every design.
  4. Compare access costs. A small table scan or existing selective access path may cost less.
  5. Check object state and release restrictions. Inspect the definition and consult the documentation for the installed version, especially after restore or structural changes.

The tested statistics guide shows how to compare estimates with actual counts. Use DBQL evidence where available to assess real workload behavior instead of treating EXPLAIN’s estimated time as a measured duration.

Restrictions, partitioning and maintenance costs

A join index consumes permanent space and is maintained when relevant base data changes. Multi-table and aggregate designs can move work out of reads and into writes. Include bulk loads, large DELETE operations, updates, recovery and maintenance windows in the evaluation.

Some index designs support partitioning, value ordering or row compression, subject to restrictions. Row compression in a join index groups repeating values and is distinct from multivalue compression. Supported expressions, aggregates and combinations vary; check the current CREATE JOIN INDEX reference before extending these examples.

Check compatibility with the specific load utility or TPT operator you use. Do not assume every loading method has the same restrictions. Dropping and rebuilding an index around a load also has a cost and changes the access paths available during that window.

A case described in the earlier version of this article involved a large DELETE against a table with more than six billion rows. Adding a multi-table join index caused the subsequent batch to spend about 20 hours on index maintenance. That historical observation is a warning to test writes, not a result from the small lab here. Recovery or rollback intervention requires a DBA-led procedure; do not treat cancelling recovery as a tuning technique.

Inspect the join index and clean up

SELECT DatabaseName, TableName
FROM DBC.TablesV
WHERE DatabaseName = DATABASE AND TableKind = 'I';
SHOW JOIN INDEX dwhpro_ji_department_totals;

The metadata query lists join-index objects in the current database when they are visible to your account. The tested cleanup removes the join index before its base tables:

DROP JOIN INDEX dwhpro_ji_department_totals;
DROP TABLE dwhpro_ji_employee;
DROP TABLE dwhpro_ji_department;

Download the complete tested example

Download the SQL, expected results and plan excerpts. Run join-index.sql from start to finish in a separate lab database. It contains the data load, all three index definitions, matching EXPLAIN requests, the maintenance check and cleanup. No credentials are included.

Start with one repeated query pattern, verify selection, and measure the combined read/write workload. Keep the join index only if the benefit justifies its operational cost. A secondary index or a query rewrite may be the simpler solution.

Need help with query performance or platform costs?

Diagnose plans, skew, spool pressure and expensive workloads. Agree a baseline and acceptance criteria before making changes.

Explore Teradata performance tuning

Teradata Query Performance Tuning: The DWHPro Guide book cover

Get the complete Teradata tuning book, free

Teradata Query Performance Tuning: The DWHPro Guide
By Roland Wenzlofsky & Artemios Vogiatzis

206 pages on statistics and demographics, access paths and indexes, query execution plans, and joins. Get the full PDF and practical Teradata updates from DWHPro.

Confirm your email to receive your download link. Privacy policy. Unsubscribe in any newsletter.

About the free book

Follow DWHPro in Google to see our articles more often in Search.

8 thoughts on “Teradata Join Index: Types, Examples and EXPLAIN”

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

    Reply
  2. 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.

    Thanks

    Reply

Leave a Comment

Teradata Query Performance Tuning: The DWHPro Guide book cover

Get the complete Teradata tuning book, free

Teradata Query Performance Tuning: The DWHPro Guide
By Roland Wenzlofsky & Artemios Vogiatzis

206 pages on statistics and demographics, access paths and indexes, query execution plans, and joins. Get the full PDF and practical Teradata updates from DWHPro.

Confirm your email to receive your download link. Privacy policy. Unsubscribe in any newsletter.

About the free book

Protected by Security by CleanTalk and CleanTalk Anti-Spam