Restrictions on Partial Covering by Join Indexes - Teradata VantageCloud Lake

If statistics indicate cost-effectiveness, 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, this join 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 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, the join counts against the 64 tables per query block restriction on joins.

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;

Because index ji_substr_f1 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;