Home Page for the TeradataForum
 

Archives of the TeradataForum

Message Posted: Thu, 01 Nov 2012 @ 16:58:33 GMT


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


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

It is the same. Just to be clear, the difference however is in the maintenance of the table.

With Soft RI, the ETL is responsible for enforcing integrity, off-loading this from the database and freeing resources. In addition, there are none of the additional table restrictions that come from hard RI.

(Explain with Hard RI)

     Explain SELECT
     c.col1,
     c.col2,
     c.col3,
     c.col4
     FROM parenttable p inner join childtable c on p.col1 = c.col1 ;
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.  


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