Example: 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™

The following set of base tables, join indexes, queries, and EXPLAIN reports demonstrate how the referential integrity relationships among the underlying base tables in a join index definition influence whether the Optimizer selects the index for queries that reference fewer base tables than are referenced by the join index:

    CREATE SET TABLE t1, NO FALLBACK, NO BEFORE JOURNAL, 
                         NO AFTER JOURNAL (
      x1 INTEGER NOT NULL,
      a1 INTEGER NOT NULL,
      b1 INTEGER NOT NULL,
      c1 INTEGER NOT NULL,
      d1 INTEGER NOT NULL,
      e1 INTEGER NOT NULL,
      f1 INTEGER NOT NULL,
      g1 INTEGER NOT NULL,
      h1 INTEGER NOT NULL,
      i1 INTEGER NOT NULL,
      j1 INTEGER NOT NULL,
      k1 INTEGER NOT NULL,
    CONSTRAINT ri1 FOREIGN KEY (a1, b1, c1) REFERENCES t2 (a2, b2, c2),
    CONSTRAINT ri2 FOREIGN KEY (d1) REFERENCES t3(d3),
    CONSTRAINT ri3 FOREIGN KEY (e1, f1) REFERENCES t4 (e4, f4),
    CONSTRAINT ri4 FOREIGN KEY (g1, h1, i1, j1) 
      REFERENCES t5(g5, h5, i5, j5),
    CONSTRAINT ri5 FOREIGN KEY (k1) REFERENCES t6(k6));
    
    CREATE SET TABLE t2, NO FALLBACK, NO BEFORE JOURNAL, 
                         NO AFTER JOURNAL (
      a2 INTEGER NOT NULL,
      b2 INTEGER NOT NULL,
      c2 INTEGER NOT NULL,
      x2 INTEGER)
    UNIQUE PRIMARY INDEX(a2, b2, c2);
    
    CREATE SET TABLE t3, NO FALLBACK, NO BEFORE JOURNAL, 
                         NO AFTER JOURNAL (
      d3 INTEGER NOT NULL,
      x3 INTEGER)
    UNIQUE PRIMARY INDEX(d3);
    
    CREATE SET TABLE t4, NO FALLBACK, NO BEFORE JOURNAL, 
                         NO AFTER JOURNAL (
      e4 INTEGER NOT NULL,
      f4 INTEGER NOT NULL,
      x4 INTEGER)
    UNIQUE PRIMARY INDEX(e4, f4);
    
    CREATE SET TABLE t5, NO FALLBACK, NO BEFORE JOURNAL, 
                         NO AFTER JOURNAL (
      g5 INTEGER NOT NULL,
      h5 INTEGER NOT NULL,
      i5 INTEGER NOT NULL,
      j5 INTEGER NOT NULL,
      x5 INTEGER)
    UNIQUE PRIMARY INDEX(g5, h5, i5, j5);
    
    CREATE SET TABLE t6, NO FALLBACK, NO BEFORE JOURNAL, 
                         NO AFTER JOURNAL (
      k6 INTEGER NOT NULL,
      x6 INTEGER)
    UNIQUE PRIMARY INDEX(k6);

The following join index definition left outer joins t1 to t3 on d1=d3 and then left outer joins that result to t6 on k1=k6:

    CREATE JOIN INDEX ji_out AS
     SELECT d1, d3, k1, k6, x1
     FROM t1 LEFT OUTER JOIN t3 ON d1=d3 
     LEFT OUTER JOIN t6 ON k1=k6;

Following is an example of a query where the Optimizer can use the join index, ji_out, because the outer joins in the join index are inner joined to the query tables on unique columns. The columns d1 and k1 are declared as foreign keys in t1. Columns d3 , the primary key for t3, and k6 the primary key for t6, are declared as the unique primary index for those tables. An EXPLAIN of the SELECT statement includes a SUM step to aggregate from ji_out.

    SELECT d1, SUM(x1)
    FROM t1, t3
    WHERE d1=d3
    GROUP by 1;

The following simple join index definition specifies inner joins on tables t1, t3 and t6:

    CREATE JOIN INDEX ji_in AS
    SELECT d1, d3, k1, k6, x1
    FROM t1, t3, t6
    WHERE d1=d3 
    AND k1=k6;

In the query below, the Optimizer can use the join index, ji_in. An EXPLAIN of the SELECT statement includes a SUM step to aggregate from ji_in.

    SELECT d1, SUM(x1)
    FROM t1, t3
    WHERE d1=d3
    GROUP BY 1;

This aggregate join index definition specifies inner joins on tables t1, t2, and t4:

    CREATE JOIN INDEX ji_in_aggr AS
    SELECT a1, e1, SUM(x1) AS total
    FROM t1, t2, t4
    WHERE a1=a2 
    AND   e1=e4 
    AND   a1>1
    GROUP BY 1, 2;

The Optimizer can use join index, ji_in_aggr, in its plan for the following query because it has the same join term as the query. An EXPLAIN of the SELECT statement includes a RETRIEVE step from ji_in_aggr with a condition of ("(ji_in_aggr.a1 > 2)      AND (ji_in_aggr.a1 >= 3)").

    SELECT a1, e1, SUM(x1) AS total
    FROM t1, t2, t4
    WHERE a1=a2 
    AND   e1=e4 
    AND   a1>2
    GROUP BY 1, 2;

The Optimizer does not use the join index, ji_in_aggr, for the query below because the conditions b1=b2 and f1=f4 are not covered by the join index. The Optimizer specifies a full-table scan to retrieve the specified rows. An EXPLAIN of the SELECT statement includes a RETRIEVE step from t1 by way of an all-rows scan with a condition of ("t1.a1 > 2"), a JOIN step from t2 using a product join, with a join condition of ("b1 = t2.b2"), and then a JOIN step from t4.

    SELECT a1, e1, SUM(x1) AS total
    FROM t1, t2, t4
    WHERE b1=b2 
    AND   f1=f4 
    AND   a1>2
    GROUP BY 1, 2;

The aggregate join index, ji_in_aggr, below specifies inner joins on tables t1, t3, and t6 using conditions that exploit a foreign key-primary key relationship between table t1 and tables t3 and t6, respectively:

    CREATE JOIN INDEX ji_in_aggr AS
    SELECT d1, k1, SUM(x1) AS total
    FROM t1, t3, t6
    WHERE d1=d3 
    AND   k1=k6
    GROUP BY 1, 2;

The Optimizer includes the join index, ji_in_aggr, in its access plan for the query below even though ji_in_aggr is defined with an inner joined table, t6, that the query does not reference. This is acceptable to the Optimizer because of the foreign key-primary key relationship between t1 and the extra table, t6, on columns k1 and k6, which have a foreign key-primary key relationship and are explicitly defined as a foreign key and as the unique primary index for their respective tables. An EXPLAIN of the SELECT statement includes a SUM step to aggregate from ji_in_aggr.

    SELECT d1, SUM(x1)
    FROM t1, t3
    WHERE d1=d3
    GROUP BY 1;

The following join index definition left outer joins table t1 with, in succession, tables t2, t3, t4, t5, and t6 on a series of equality conditions made on foreign key-primary key relationships among the underlying base tables:

    CREATE JOIN INDEX ji_out AS
    SELECT a1, b1, c1, c2, d1, d3, e1, e4, f1, g1, h1, i1, j1, j5, k1,
           k6, x1 
    FROM t1 
    LEFT OUTER JOIN t2 ON  a1=a2 
                       AND b1=b2 
                       AND c1=c2
    LEFT OUTER JOIN t3 ON  d1=d3 
    LEFT OUTER JOIN t4 ON  e1=e4 
                       AND f1=f4 
    LEFT OUTER JOIN t5 ON  g1=g5 
                       AND h1=h5 
                       AND i1=i5 
                       AND j1=j5 
    LEFT OUTER JOIN t6 ON  k1=k6;

Even though the query below references fewer tables than are defined in the join index, ji_out , the Optimizer includes the join index in its access plan because all the extra outer joins are defined on unique columns and the extra tables are the inner tables in the outer joins. An EXPLAIN of the SELECT statement indicates that the Optimizer plan includes a SUM step to aggregate from ji_out.

    SELECT a1, b1, c1, SUM(x1)
    FROM t1, t2
    WHERE a1=a2 
    AND   b1=b2 
    AND   c1=c2
    GROUP BY 1, 2, 3;

The following join index definition specifies all inner joins on tables t1, t2, t3, t4, t5 and t6 and specifies equality conditions on all the foreign key-primary key relationships among those tables:

    CREATE JOIN INDEX ji_in AS
    SELECT a1, b1, c1, c2, d1, d3, e1, e4, f1, g1, g5, h1, i1, j1, k1,
           k6, x1 
    FROM t1, t2, t3, t4, t5, t6
    WHERE a1=a2 
    AND   b1=b2 
    AND   c1=c2 
    AND   d1=d3 
    AND   e1=e4 
    AND   f1=f4 
    AND   g1=g5 
    AND   h1=h5 
    AND   i1=i5 
    AND   j1=j5 
    AND   k1=k6;

Even though the join index, ji_in, references six tables with inner joins, the Optimizer includes the join index for the query below, which only references two of the six tables, because all of the conditions in the join index definition are based on foreign key-primary key relationships among the underlying base tables. An EXPLAIN of the SELECT statement indicates that the Optimizer plan includes a SUM step to aggregate from ji_in.

    SELECT a1, b1, c1, SUM(x1)
    FROM t1, t2
    WHERE a1=a2 
    AND   b1=b2 
    AND   c1=c2
    GROUP BY 1, 2, 3;

The following example shows how the Optimizer uses a join index with an extra inner join when the connections among the tables in its definition are appropriately defined.

Suppose you have the following table definitions:

    CREATE SET TABLE fact (
     f_d1 INTEGER NOT NULL,
     f_d2 INTEGER NOT NULL, 
    FOREIGN KEY (f_d1) REFERENCES WITH NO CHECK OPTION dim1 (d1),
    FOREIGN KEY (f_d2) REFERENCES WITH NO CHECK OPTION dim2 (d2))
    UNIQUE PRIMARY INDEX (f_d1,f_d2);
    
    CREATE SET TABLE dim1 (
     a1 INTEGER NOT NULL,
     d1 INTEGER NOT NULL, 
    FOREIGN KEY (a1) REFERENCES WITH NO CHECK OPTION dim1_1 (d11))
    UNIQUE PRIMARY INDEX (d1);
    
    CREATE SET TABLE dim2 (
     d1 INTEGER NOT NULL,
     d2 INTEGER NOT NULL)
    UNIQUE PRIMARY INDEX (d2);
    
    CREATE SET TABLE dim1_1 (
     d11 INTEGER NOT NULL,
     d22 INTEGER NOT NULL)
    UNIQUE PRIMARY INDEX (d11);

In the join index defined below, the fact table is joined with dimension tables dim1 and dim2 by foreign key-primary key joins on fact.f_d1=dim1.d1 and fact.f_d2=dim2.d2. Dimension table dim1 is also joined with its dimension subtable dim1_1 by a foreign key-primary key join on dim1.a1=dim1_1.d11. A query on the fact and dim2 tables uses the join index, ji_all, because of its use of foreign key-primary key relationships among the tables:

    CREATE JOIN INDEX ji_all AS 
    SELECT (COUNT(*)(FLOAT)) AS countstar, dim1.d1, dim1_1.d11, dim2.d2,
           (SUM(fact.f_d1)(FLOAT)) AS sum_f1
    FROM fact, dim1, dim2, dim1_1 
    WHERE ((fact.f_d1 = dim1.d1) 
    AND    (fact.f_d2 = dim2.d2)) 
    AND    (dim1.a1 = dim1_1.d11)
    GROUP BY dim1.d1, dim1_1.d11, dim2.d2 
    PRIMARY INDEX (d1);

The results of the aggregate operations COUNT and SUM are typed as FLOAT. See CREATE JOIN INDEX in Teradata Vantage™ - SQL Data Definition Language Detailed Topics , B035-1184 .

The Optimizer includes the join index, ji_all, in the query below. Even though table fact is the parent table of both tables dim1 and dim2, those tables are joined with fact on foreign key columns in the join index definition. Similarly, dim1 is joined to dim1_1 in the join index definition on a foreign key column. An EXPLAIN of the SELECT statement includes SUM step to aggregate from ji_all.

    SELECT d2, SUM(f_d1) 
    FROM fact, dim2 
    WHERE f_d2=d2 
    GROUP BY 1;