15.00 - Join Indexes - Teradata Database

Teradata Database SQL Fundamentals

Teradata Database
Programming Reference

Join Indexes

Join indexes are not indexes in the usual sense of the word. They are file structures designed to permit queries (join queries in the case of multitable join indexes) to be resolved by accessing the index instead of having to access and join their underlying base tables.

You can use join indexes to:

  • Define a prejoin table on frequently joined columns (with optional aggregation) without denormalizing the database.
  • Create a full or partial replication of a base table with a primary index on a foreign key column table to facilitate joins of very large tables by hashing their rows to the same AMP as the large table.
  • Define a summary table without denormalizing the database.
  • You can define a join index on one or several tables.

    Depending on how the index is defined, join indexes can also be useful for queries where the index structure contains only some of the columns referenced in the statement. This situation is referred to as a partial cover of the query.

    Unlike traditional indexes, join indexes do not implicitly store pointers to their associated base table rows. Instead, they are generally used as a quick access method that eliminates the need to access and join the base tables they represent. They substitute for rather than point to base table rows. The only exception to this is the case where an index partially covers a query. If the index is defined using either the ROWID keyword or the UPI or USI of its base table as one of its columns, then it can be used to join with the base table to cover the query.

    Defining Join Indexes

    To create a join index, use the CREATE JOIN INDEX statement.

    For example, suppose that a common task is to look up customer orders by customer number and date. You might create a join index like the following, linking the customer table, the order table, and the order detail table:

       CREATE JOIN INDEX cust_ord2
       AS SELECT cust.customerid,cust.loc,ord.ordid,item,qty,odate
       FROM cust, ord, orditm
       WHERE cust.customerid = ord.customerid
       AND ord.ordid = orditm.ordid;

    Multitable Join Indexes

    A multitable join index stores and maintains the joined rows of two or more tables and, optionally, aggregates selected columns.

    Multitable join indexes are for join queries that are performed frequently enough to justify defining a prejoin on the joined columns.

    A multitable join index is useful for queries where the index structure contains all the columns referenced by one or more joins, thereby allowing the index to cover that part of the query, making it possible to retrieve the requested data from the index rather than accessing its underlying base tables. For obvious reasons, an index with this property is often referred to as a covering index.

    Single-Table Join Indexes

    Single-table join indexes are very useful for resolving joins on large tables without having to redistribute the joined rows across the AMPs.

    Single-table join indexes facilitate joins by hashing a frequently joined subset of base table columns to the same AMP as the table rows to which they are frequently joined. This enhanced geography eliminates BYNET traffic as well as often providing a smaller sized row to be read and joined.

    Aggregate Join Indexes

    When query performance is of utmost importance, aggregate join indexes offer an extremely efficient, cost-effective method of resolving queries that frequently specify the same aggregate operations on the same column or columns. When aggregate join indexes are available, the system does not have to repeat aggregate calculations for every query.

    You can define an aggregate join index on two or more tables, or on a single table. A single-table aggregate join index includes a summary table with:

  • A subset of columns from a base table
  • Additional columns for the aggregate summaries of the base table columns
  • Sparse Join Indexes

    You can create join indexes that limit the number of rows in the index to only those that are accessed when, for example, a frequently run query references only a small, well known subset of the rows of a large base table. By using a constant expression to filter the rows included in the join index, you can create what is known as a sparse index.

    Any join index, whether simple or aggregate, multitable or single-table, can be sparse.

    To create a sparse index, use the WHERE clause in the CREATE JOIN INDEX statement.

    Effects of Join Indexes

  • Load Utilities
  • MultiLoad and FastLoad utilities cannot be used to load or unload data into base tables that have a join index defined on them because join indexes are not maintained during the execution of these utilities. If an error occurs because of a join index, take these steps:

  • Ensure that any queries that use the join index are not running.
  • Drop the join index. (The system defers completion of this step until there are no more queries running that use the join index.)
  • Load the data into the base table.
  • Recreate the join index.
  • The TPump utility, which performs standard SQL row inserts and updates, can be used to load or unload data into base tables with join indexes because it properly maintains join indexes during execution. However, in some cases, performance may improve by dropping join indexes on the table prior to the load and recreating them after the load.

  • ARC (Archive/Recovery Utility)
  • Archive and recovery cannot be used on a join index itself. Archiving is permitted on a base table or database that has an associated join index defined. Before a restore of such a base table or database, you must drop the existing join index definition. Before using any such index again in the execution of queries, you must recreate the join index definition.

  • Permanent Journal Recovery
  • Using a permanent journal to recover a base table (that is, ROLLBACK or ROLLFORWARD) with an associated join index defined is permitted. The join index is not automatically rebuilt during the recovery process. Instead, it is marked as nonvalid and it must be dropped and recreated before it can be used again in the execution of queries.

    Join Indexes and Base Tables

    In most respects, a join index is similar to a base table. For example, you can do the following things to a join index:

  • Create nonunique secondary indexes on its columns.
  • Create a unique primary index on its columns, provided it is a non-compressed and nonvalue-ordered single-table join index.
  • Partition its primary index, if it is a noncompressed join index.
  • Unlike base tables, you cannot do the following things with join indexes:

  • Query or update join index rows explicitly.
  • Store and maintain arbitrary query results such as expressions.
  • Note: You can maintain aggregates or sparse indexes if you define the join index to do so.

    Related Topics


    For more information on …

    See …

    creating join indexes

    “CREATE JOIN INDEX” in SQL Data Definition Language.

    dropping join indexes

    “DROP JOIN INDEX” in SQL Data Definition Language.

    displaying the attributes of the columns defined by a join index

    “HELP JOIN INDEX” in SQL Data Definition Language.

    using join indexes to enhance the performance of your databases

  • Database Design.
  • SQL Data Definition Language.
  • database design considerations for join indexes
  • improving join index performance
  • Database Design.