Example: Exceptions to Join Index Coverage - Advanced SQL Engine - Teradata Database

SQL Data Definition Language Syntax and Examples

Product
Advanced SQL Engine
Teradata Database
Release Number
17.05
Published
January 2021
Language
English (United States)
Last Update
2021-01-22
dita:mapPath
ncd1596241368722.ditamap
dita:ditavalPath
hoy1596145193032.ditaval
dita:id
B035-1144
lifecycle
previous
Product Category
Teradata Vantage™

Below are examples of exceptions to the general coverage rules for extra tables in a join index definition. See CREATE JOIN INDEX in Teradata Vantage™ - SQL Data Definition Language Detailed Topics , B035-1184 .

In the following example, table t9 is the parent table of tables, t7 and t8. Generally, this relationship disqualifies a join index from covering any query with fewer tables than are referenced in the definition for that index. However, because t7 and t8 are joined on the FK columns (y7 = x8) in the join index definition, the Optimizer uses the index, ji, to cover the query:

    CREATE SET TABLE t7(
     x7 INTEGER NOT NULL, 
     y7 INTEGER NOT NULL, 
     z7 INTEGER NOT NULL, 
    CONSTRAINT r7 FOREIGN KEY (y7) REFERENCES WITH NO CHECK OPTION 
               t9 (y9))
    PRIMARY INDEX (x7);
    CREATE SET TABLE t8(
     x8 INTEGER NOT NULL, 
     y8 INTEGER NOT NULL, 
     z8 INTEGER NOT NULL, 
    CONSTRAINT r8 FOREIGN KEY (x8) REFERENCES WITH NO CHECK OPTION 
               t9 (x9));
    CREATE SET TABLE t9(
     x9 INTEGER NOT NULL UNIQUE, 
     y9 INTEGER NOT NULL, 
     z9 INTEGER NOT NULL) 
    UNIQUE PRIMARY INDEX(y9);
    CREATE JOIN INDEX ji AS 
     SELECT x7, y7, x8, y8, x9, y9 
     FROM t7, t8, t9 
     WHERE y7=x8 
     AND   y7=y9 
     AND   x8=x9;

An EXPLAIN of the SELECT statement includes a RETRIEVE step from ji with a condition of ("ji.x1 > 1").

    SELECT x7, y7, x8, y8 
    FROM t7, t8 
    WHERE y7=x8 
    AND   x7>1;

As with the previous example, this example demonstrates how a join index can be used to cover a query when one table in the join index definition is a parent of two others if the tables are joined on foreign key-primary key relationships. t9 is the parent table of both t7 and t10. But because t7 and t10 are joined with t9 on the same PK column, by transitive closure, t7 and t10 are joined on y7=x10. The Optimizer can use the join index ji to cover the query:

    CREATE SET TABLE t10(
     x10 INTEGER NOT NULL, 
     y10 INTEGER NOT NULL, 
     z10 INTEGER NOT NULL, 
    CONSTRAINT r10 FOREIGN KEY (x10) REFERENCES WITH NO CHECK OPTION 
               t9 (y9))
    PRIMARY INDEX x10;
    CREATE JOIN INDEX ji AS 
     SELECT x7, y7, x10, y10, x9, y9 
     FROM t7, t10, t9 
     WHERE y7=y9 
     AND   x10=y9;

An EXPLAIN of the SELECT statement includes a RETRIEVE step from ji with a condition of ("ji.x1 > 1").

    SELECT x7, y7, x10, y10 
    FROM t7, t10 
    WHERE y7=x10 
    AND   x7>1;