15.00 - Examples of Exceptions to the General Rules for Extra Tables in the Join Index Definition - Teradata Database

Teradata Database Design

prodname
Teradata Database
vrm_release
15.00
category
User Guide
featnum
B035-1094-015K

Examples of Exceptions to the General Rules for Extra Tables in the Join Index Definition

“Example 1” on page 590 and “Example 2” on page 591 illustrate cases that are exceptions to the general coverage rules for extra tables in a join index definition (see “Restriction on Coverage by Join Indexes When a Join Index Definition References More Tables Than a Query” on page 577).

Example  

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, as you can see by looking at the bold text in the EXPLAIN report:

     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; 
 
     EXPLAIN SELECT x7, y7, x8, y8 
             FROM t7, t8 
             WHERE y7=x8 
             AND   x7>1; 
 
    *** Help information returned. 14 rows. 
    *** Total elapsed time was 1 second.
   
Explanation
---------------------------------------------------------------------------
  1) First, we lock a distinct HONG_JI."pseudo table" for
     read on a RowHash to prevent global deadlock for HONG_JI.ji.
  2) Next, we lock HONG_JI.ji for read.
  3) We do an all-AMPs RETRIEVE step from HONG_JI.ji by way
     of an all-rows scan with a condition of (
     "HONG_JI.ji.x1 > 1") into Spool 1 (group_amps), which
     is built locally on the AMPs.  The size of Spool 1 is estimated
     with no confidence to be 3 rows.  The estimated time for this step
     is 0.06 seconds.
  4) Finally, we send out an END TRANSACTION step to all AMPs involved
     in processing the request.
  -> The contents of Spool 1 are sent back to the user as the result of
     statement 1.  The total estimated time is 0.06 seconds.

Example  

As with “Example 1” on page 590, 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 does select join index ji to cover the query, as the bold text in the EXPLAIN report for the example query demonstrates:

     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;
 
     EXPLAIN SELECT x7, y7, x10, y10 
             FROM t7, t10 
             WHERE y7=x10 
             AND   x7>1; 
 
    *** Help information returned. 14 rows. 
    *** Total elapsed time was 1 second.
   
   Explanation
   ---------------------------------------------------------------------------
     1) First, we lock a distinct HONG_JI."pseudo table" for
        read on a RowHash to prevent global deadlock for HONG_JI.ji.
     2) Next, we lock HONG_JI.ji for read.
     3) We do an all-AMPs RETRIEVE step from HONG_JI.ji by way
        of an all-rows scan with a condition of ("HONG_JI.ji.x1 > 1") 
        into Spool 1 (group_amps), which is built locally on the AMPs.  
        The size of Spool 1 is estimated with no confidence to be 3 rows.  
        The estimated time for this step is 0.06 seconds.
     4) Finally, we send out an END TRANSACTION step to all AMPs involved
        in processing the request.
     -> The contents of Spool 1 are sent back to the user as the result of
        statement 1.  The total estimated time is 0.06 seconds.