Home Page for the TeradataForum
 

Archives of the TeradataForum

Message Posted: Tue, 25 Sep 2001 @ 11:17:40 GMT


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


Subj:   ROWID as UPI of "Key" Table
 
From:   Claybourne Barrineau

All,

I'm experimenting with the suggestion of using the ROWID of my Atomic Fact table as a UPI of a "transparent key" table for a Star Schema design. For some reason, the Fact table is being redistributed to all Amps before this "key" table is built. Shouldn't these ROWIDs exist on the same AMPs as the Primary Index of the Atomic Fact table? Therefore, a redistribution shouldn't be required.


Thanks,

Claybourne

explain
Insert into INFVO2PLYT.T_F_VOL_KEY_NON_NUPI
(
PKG_PS_CD
,CST_CB_CD
,CHN_ST_CD
,UNIQ_ROW_ID
)
SELECT PKG_PS_CD
, CST_CB_CD
, CHN_ST_CD
, ROWID
FROM INFVO2PLYT.T_F_VOL_DY_AC_PS_CB_ST_B4;

Explanation
--------------------------------------------------
 
  1)First, we lock a distinct INFVO2PLYT."pseudo table" for write on a RowHash to prevent global deadlock for INFVO2PLYT.T_F_VOL_KEY_NON_NUPI.  
  2)Next, we lock a distinct INFVO2PLYT."pseudo table" for read on a RowHash to prevent global deadlock for INFVO2PLYT.T_F_VOL_DY_AC_PS_CB_ST_B4.  
  3)We lock INFVO2PLYT.T_F_VOL_KEY_NON_NUPI for write, and we lock INFVO2PLYT.T_F_VOL_DY_AC_PS_CB_ST_B4 for read.  
  4)We do an all-AMPs RETRIEVE step from INFVO2PLYT.T_F_VOL_DY_AC_PS_CB_ST_B4 by way of an all-rows scan with no residual conditions into Spool 1, which is redistributed by hash code to all AMPs. Then we do a SORT to order Spool 1 by row hash. The input table will not be cached in memory, but it is eligible for synchronized scanning. The result spool file will not be cached in memory. The size of Spool 1 is estimated with high confidence to be 8,000,715 rows. The estimated time for this step is 2 hours and 43 minutes.  
  5)We do a MERGE into INFVO2PLYT.T_F_VOL_KEY_NON_NUPI from Spool 1 (Last Use).  
  6)We spoil the parser's dictionary cache for the table.  
  7)Finally, we send out an END TRANSACTION step to all AMPs involved in processing the request.  
  ->No rows are returned to the user as the result of statement 1.  



     
  <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