15.00 - Restrictions on Partial Covering by Join Indexes - Teradata Database

Teradata Database Design

Product
Teradata Database
Release Number
15.00
Content Type
User Guide
Publication ID
B035-1094-015K
Language
English (United States)

Restrictions on Partial Covering by Join Indexes

The Optimizer can use a join index that partially covers a query in the following cases:

  • One of the columns in the index definition is the keyword ROWID.
  • You can only specify ROWID in the outermost SELECT of the CREATE JOIN INDEX statement. See “CREATE JOIN INDEX” in SQL Data Definition Language Detailed Topics.

  • The column set defining the UPI of the underlying base table is also carried in the definition.
  • The column set defining the NUPI of the underlying base table plus either of the following is also carried in the definition:
  • One of the columns in the definition of that index is the keyword ROWID.
  • You can only specify ROWID in the outermost SELECT of a CREATE JOIN INDEX request.

  • The column set defining a USI on the underlying base table.
  • The ROWID option is the preferable choice.

  • Partial covering is not supported for the inner table of an outer join.
  • Partial covering is not supported for queries that contain a TOP n or TOP m PERCENT clause.
  • If statistics indicate that it would be cost-effective, the Optimizer can specify that the partially covering single‑table join index be joined to one of its underlying base tables using either the ROWID or the UPI or USI to join to the column data not defined for the index itself.

    Even though you do not explicitly specify this join when you write your query, it counts against the 128 table restriction on joins.

    For example, suppose you define the tables t1, t2, and t3 and the join indexes j1 and j2 as follows:

         CREATE TABLE t1 (
           a1 INTEGER, 
           b1 INTEGER, 
           c1 INTEGER);
     
         CREATE TABLE t2 (
           a2 INTEGER, 
           b2 INTEGER, 
           c2 INTEGER);
     
         CREATE TABLE t3 (
           a3 INTEGER, 
           b3 INTEGER, 
           c3 INTEGER);
     
         CREATE JOIN INDEX j1 AS
          SELECT b1, b2, t1.ROWID AS t1rowid 
          FROM t1,t2 
          WHERE a1=a2;
     
         CREATE JOIN INDEX j2 AS
          SELECT b1, b2, t1.ROWID t1rowid, t2.rowid t2rowid 
          FROM t1,t2 
          WHERE a1=b2;

    Join index j1 partially covers the following queries:

         SELECT a1, b1, c1, b2
         FROM t1,t2 
         WHERE t1.a1=t2.b2 
         AND   t1.b1=10;
     
         SELECT a1, b1, c1, b2, b3, c3
         FROM t1,t2,t3
         WHERE t1.a1=t2.b2 
         AND   t1.b1=t3.a3 
         AND   t3.b3 > 0;

    The same join index does not partially cover the following queries:

         SELECT * 
         FROM t1,t2 
         WHERE t1.a1=t2.b2 
         AND   t1.b1=10;
     
         SELECT * 
         FROM t1,t2,t3 
         WHERE t1.a1=t2.b2 
         AND   t1.b1=t3.a3 
         AND   t3.b3 > 0;

    Join index j2, on the other hand, partially covers all of these queries.

    Even though you do not explicitly specify the join back to the base table when you write your query, it counts against the 64 tables per query block restriction on joins.

    Be aware that a join index defined with an expression in its select list provides less coverage than a join index that is defined using base columns.

    For example, the Optimizer can use join index ji_f1 to rewrite a query that specifies any character function on f1.

         CREATE JOIN INDEX ji_f1 AS 
          SELECT b1, f1 
          FROM t1 
          WHERE a1 > 0;

    At the same time, because it is defined with a SUBSTR expression in its select list, the Optimizer can only use join index ji_substr_f1 to rewrite a query that specifies the same SUBSTR function on f1.

         CREATE JOIN INDEX ji_substr_f1 AS 
          SELECT b1, SUBSTR(f1,1,10) AS s 
          FROM t1 
          WHERE a1 > 0;