Home Page for the TeradataForum
 

Archives of the TeradataForum

Message Posted: Wed, 31 Oct 2012 @ 16:49:13 GMT


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


Subj:   Re: Question on soft "RI" in Teradata
 
From:   Bernard, Vincent L

The Optimizer is aware of Soft RI. Therefore, is a "parent" entity is referenced by a "child", it is understood that every value in the "child's" joining column needs to first exist in the parent. It is not enforced by the database, so the developer is responsible for ensuring it.

Since the relationship is understood, the optimizer can draw conclusions.

For example:

     create table parenttable
     (
     col1 integer,
     col2 integer,
     col3 integer
     )unique primary index(col1)
     ;
     create table childtable
     (
     col1 integer,
     col2 integer,
     col3 integer,
     col4 integer,
     constraint fkey foreign key (col1) references WITH NO CHECK OPTION
     parenttable(col1)
     )unique primary index (col1,col2)
     ;

(ParentTable.col1 is referenced by ChildTable.col1)

Query:

     SELECT
     c.col1,
     c.col2,
     c.col3,
     c.col4
     FROM parenttable p inner join childtable c on p.col1 = c.col1 ;

The optimizer will assume that there is no need to join the tables and the explain reflects this.

     Explain SELECT
     c.col1,
     c.col2,
     c.col3,
     c.col4
     FROM parenttable p inner join childtable c on p.col1 = c.col1 ; (Explain with the
     constraint)
Explanation
--------------------------------------------------
 
  1)First, we lock a distinct TESTDB."pseudo table" for read on a RowHash to prevent global deadlock for TESTDB.c.  
  2)Next, we lock TESTDB.c for read.  
  3)We do an all-AMPs RETRIEVE step from TESTDB.c by way of an all-rows scan with a condition of ("NOT (TESTDB.c.col1 IS NULL)") into Spool 1 (all_amps), which is built locally on the AMPs. The size of Spool 1 is estimated with low confidence to be 48 rows (1,776 bytes). The estimated time for this step is 0.01 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.01 seconds.  


(Explain with constraint dropped)

Explanation
--------------------------------------------------
 
  1)First, we lock a distinct TESTDB."pseudo table" for read on a RowHash to prevent global deadlock for TESTDB.c.  
  2)Next, we lock a distinct TESTDB."pseudo table" for read on a RowHash to prevent global deadlock for TESTDB.p.  
  3)We lock TESTDB.c for read, and we lock TESTDB.p for read.  
  4)We do an all-AMPs RETRIEVE step from TESTDB.c by way of an all-rows scan with a condition of ("NOT (TESTDB.c.col1 IS NULL)") into Spool 2 (all_amps), which is redistributed by the hash code of (TESTDB.c.col1) to all AMPs. Then we do a SORT to order Spool 2 by row hash. The size of Spool 2 is estimated with low confidence to be 48 rows (1,392 bytes). The estimated time for this step is 0.01 seconds.  
  5)We do an all-AMPs JOIN step from Spool 2 (Last Use) by way of a RowHash match scan, which is joined to TESTDB.p by way of a RowHash match scan. Spool 2 and TESTDB.p are joined using a merge join, with a join condition of ("TESTDB.p.col1 = col1"). The result goes into Spool 1 (all_amps), which is built locally on the AMPs. The size of Spool 1 is estimated with index join confidence to be 48 rows (1,776 bytes). The estimated time for this step is 0.02 seconds.  
  6)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.02 seconds.  


Vincent Bernard



     
  <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: 15 Jun 2023