Home Page for the TeradataForum
 

Archives of the TeradataForum

Message Posted: Mon, 10 Apr 2006 @ 19:16:47 GMT


     
  <Prev Next>   <<First <Prev
Next>
Last>>
 


Subj:   Re: Implementing Soft RI
 
From:   Anomy Anom

<-- Anonymously Posted: Monday, April 10, 2006 15:16 -->

See SQL reference DDL for TTU8.0 page 439 under reference constraint of create table topic.

Example provided there:

Chapter 1: SQL Data Definition Language Statement Syntax
CREATE TABLE (Column Definition Clause)
SQL Reference: Data Definition Statements pages: 439 - 441

Example of How the Optimizer Takes Advantage of Referential Constraints To understand how the Optimizer can produce more performant query plans when a non-enforced 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  



     
  <Prev Next>   <<First <Prev
Next>
Last>>
 
 
 
 
 
 
 
 
 
  
  Top Home Privacy Feedback  
 
 
Copyright for the TeradataForum (TDATA-L), Manta BlueSky    
Copyright 2016 - All Rights Reserved    
Last Modified: 23 Jun 2019