15.00 - An Example of How the Optimizer Takes Advantage of Referential Constraints - Teradata Database

Teradata Database SQL Data Definition Language Detailed Topics

prodname
Teradata Database
vrm_release
15.00
category
Programming Reference
featnum
B035-1184-015K

An Example of How the Optimizer Takes Advantage of Referential Constraints

To understand how the Optimizer can produce more high-performing query plans when a Referential Constraint is defined, consider the following table definitions. The only difference between the second and third tables is that tb2 defines a Referential Constraint on column b referencing column a in tb1, while tb3 does not define that constraint.

     CREATE TABLE tb1 (
       a INTEGER NOT NULL PRIMARY KEY,
       b INTEGER,
       c INTEGER);
 
     CREATE TABLE tb2 (
       a INTEGER NOT NULL,
       b INTEGER,
       c INTEGER,
     CONSTRAINT ref1
     FOREIGN KEY (b) REFERENCES WITH NO CHECK OPTION tb1(a));
 
     CREATE TABLE tb3 (
       a INTEGER NOT NULL,
       b INTEGER,
       c INTEGER);

The following EXPLAIN report shows the plan for a query on tables tb1 and tb3 when no Referential Constraints have been defined. In particular, notice the join in Step 5.

     EXPLAIN 
     SELECT tb1.a, tb3.a, MAX(tb3.c) 
     FROM tb1, tb3
     GROUP BY tb1.a, tb3.a
     WHERE tb1.a = tb3.b
     ORDER BY 1;
    
     *** Help information returned. 33 rows.
     *** Total elapsed time was 1 second.
    Explanation
    ---------------------------------------------------------------------------
      1) First, we lock a distinct TESTCOST."pseudo table" for read on a
         RowHash to prevent global deadlock for TESTCOST.tb3.
      2) Next, we lock a distinct TESTCOST."pseudo table" for read on a
         RowHash to prevent global deadlock for TESTCOST.tb1.
      3) We lock TESTCOST.tb3 for read, and we lock TESTCOST.tb1 for read.
      4) We do an all-AMPs RETRIEVE step from TESTCOST.tb3 by way of an
         all-rows scan with a condition of ("NOT (TESTCOST.tb3.b IS NULL)")
         into Spool 4 (all_amps), which is redistributed by hash code to
         all AMPs.  Then we do a SORT to order SORT to order Spool 4 by row
         hash.  The size of Spool 4 is estimated with no confidence to be 3
         rows.  The estimated time for this step is 0.03 seconds.
      5) We do an all-AMPs JOIN step from TESTCOST.tb1 by way of a RowHash
         match scan with no residual conditions, which is joined to Spool 4
         (Last Use).  TESTCOST.tb1 and Spool 4 are joined using a merge
         join, with a join condition of ("TESTCOST.tb1.a = b").  The result
         goes into Spool 3 (all_amps), which is built locally on the AMPs.
         The size of Spool 3 is estimated with no confidence to be 3 rows.
         The estimated time for this step is 0.20 seconds.
      6) We do a SUM step to aggregate from Spool 3 (Last Use) by way of an
         all-rows scan, and the grouping identifier in field 1.  Aggregate
         Intermediate Results are computed locally, then placed in Spool 5.
         The size of Spool 5 is estimated with low confidence to be 3 rows.
         The estimated time for this step is 0.17 seconds.
      7) We do an all-AMPs RETRIEVE step from Spool 5 (Last Use) by way of
         an all-rows scan into Spool 1 (group_amps), which is built locally
         on the AMPs.  Then we do a SORT to order SORT to order Spool 1 by
         the sort key in spool field1.  The size of Spool 1 is estimated
         with low confidence to be 3 rows.  The estimated time for this
         step is 0.17 seconds.
      8) Finally, we send out an END TRANSACTION step to all AMPs involved
         in processing the request.

The following EXPLAIN shows the query plan for the same query, but this time on tables tb1 and tb2, with a Referential Constraint defined between column b of table tb2 and column a of table tb1. Notice that the Optimizer has recognized that the join in step 5 of the previous query plan is unnecessary and can be eliminated. The same query plan would be produced if tb1.a and tb2.b had an explicitly declared standard Referential Integrity constraint.

     EXPLAIN 
     SELECT tb1.a, tb2.a, MAX(tb2.c) 
     FROM tb1, tb2
     GROUP BY tb1.a, tb2.a
     WHERE tb1.a = tb2.b
     ORDER BY 1;
    
     *** Help information returned. 19 rows.
     *** Total elapsed time was 1 second.
 Explanation
    ---------------------------------------------------------------------------
      1) First, we lock a distinct TESTCOST."pseudo table" for read on a
         RowHash to prevent global deadlock for TESTCOST.tb2.
      2) Next, we lock TESTCOST.tb2 for read.
      3) We do a SUM step to aggregate from TESTCOST.tb2 by way of an
         all-rows scan with a condition of ("NOT (TESTCOST.tb2.b IS NULL)"),
         and the grouping identifier in field 1.  Aggregate Intermediate
         Results are computed locally, then placed in Spool 3.  The size of
         Spool 3 is estimated with no confidence to be 3 rows.  The
         estimated time for this step is 0.15 seconds.
      4) We do an all-AMPs RETRIEVE step from Spool 3 (Last Use) by way of
         an all-rows scan into Spool 1 (group_amps), which is built locally
         on the AMPs.  Then we do a SORT to order SORT to order Spool 1 by
         the sort key in spool field1.  The size of Spool 1 is estimated
         with no confidence to be 3 rows.  The estimated time for this step
         is 0.17 seconds.
      5) 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.