Home Page for the TeradataForum
 

Archives of the TeradataForum

Message Posted: Sun, 03 Jul 2005 @ 14:26:46 GMT


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


Subj:   Creating PPI table
 
From:   Anomy Anom

<-- Anonymously Posted: Sunday, July 03, 2005 07:16 -->

hi

I have very big table with about 800 million rows.

I want to migrate the table to PPI .

I built a new table with partitions.

when I am trying to move only one day ( or month) - the teradata - is using spool space for this operation.

why is that?

the definition of the table :

     CREATE SET TABLE rtdata.J0033_FCT_PURCHASE ,NO FALLBACK ,
          NO BEFORE JOURNAL,
          NO AFTER JOURNAL
          (
           Event_Id DECIMAL(18,0) NOT NULL,
           Model_Agreement_Nbr DECIMAL(18,0),
           Model_Product_Id SMALLINT NOT NULL,
           Card_Agreement_Nbr DECIMAL(18,0) NOT NULL,
           Card_Product_Id SMALLINT,
           Event_Purchase_Date DATE FORMAT 'dd/mm/yyyy',
           Event_Value_Date DATE FORMAT 'dd/mm/yyyy' NOT NULL,
           Number_Of_Payments SMALLINT,
           Event_activity_Code CHAR(2) CHARACTER SET LATIN NOT CASESPECIFIC NOT NULL,
           Party_Id DECIMAL(18,0),
           Merchant_Zip_Area_Id INTEGER,
           Mall_Code SMALLINT,
           SIC_Source_Code SMALLINT,
           Merchant_Branch_Host_Id INTEGER,
           Card_Branch_Host_Id INTEGER,
           Card_Holder_Id DECIMAL(18,0),
           Holder_Zip_Area_Id INTEGER,
           Card_Owner_Id DECIMAL(18,0),
           Club_Host_Nbr INTEGER,
           Sub_Club_Nbr INTEGER COMPRESS 0 ,
           Discount_Club_Id DECIMAL(18,0),
           Purchase_Amount DECIMAL(13,2),
           Amount_Category SMALLINT,
           Discount_Amount DECIMAL(11,2) COMPRESS 0.00 ,
           ICF_Amount DECIMAL(11,2) COMPRESS 0.00 ,
           Commision_Amount DECIMAL(11,2),
           Gross_Amount_For_ICF DECIMAL(13,2) COMPRESS 0.00 ,
           Event_Ind_Code CHAR(1) CHARACTER SET LATIN NOT CASESPECIFIC NOT NULL,
           Purchase_Ind BYTEINT,
           Model_Value_Date DATE FORMAT 'dd/mm/yyyy' NOT NULL,
           Card_Value_Date DATE FORMAT 'dd/mm/yyyy' NOT NULL)
     UNIQUE PRIMARY INDEX ( Event_Id )
     INDEX ( Card_Agreement_Nbr )
     INDEX ( Model_Agreement_Nbr )
     INDEX ( Event_Value_Date )
     INDEX ( Party_Id );

the definition of the new table is :

     CREATE SET TABLE rtdata.J0033_FCT_PURCHASE_HIST ,NO FALLBACK ,
          NO BEFORE JOURNAL,
          NO AFTER JOURNAL
          (
           Event_Id DECIMAL(18,0) NOT NULL,
           Model_Agreement_Nbr DECIMAL(18,0),
           Model_Product_Id SMALLINT NOT NULL,
           Card_Agreement_Nbr DECIMAL(18,0) NOT NULL,
           Card_Product_Id SMALLINT,
           Event_Purchase_Date DATE FORMAT 'dd/mm/yyyy',
           Event_Value_Date DATE FORMAT 'dd/mm/yyyy' NOT NULL,
           Number_Of_Payments SMALLINT,
           Event_activity_Code CHAR(2) CHARACTER SET LATIN NOT CASESPECIFIC NOT NULL,
           Party_Id DECIMAL(18,0),
           Merchant_Zip_Area_Id INTEGER,
           Mall_Code SMALLINT,
           SIC_Source_Code SMALLINT,
           Merchant_Branch_Host_Id INTEGER,
           Card_Branch_Host_Id INTEGER,
           Card_Holder_Id DECIMAL(18,0),
           Holder_Zip_Area_Id INTEGER,
           Card_Owner_Id DECIMAL(18,0),
           Club_Host_Nbr INTEGER,
           Sub_Club_Nbr INTEGER COMPRESS 0 ,
           Discount_Club_Id DECIMAL(18,0),
           Purchase_Amount DECIMAL(13,2),
           Amount_Category SMALLINT,
           Discount_Amount DECIMAL(11,2) COMPRESS 0.00 ,
           ICF_Amount DECIMAL(11,2) COMPRESS 0.00 ,
           Commision_Amount DECIMAL(11,2),
           Gross_Amount_For_ICF DECIMAL(13,2) COMPRESS 0.00 ,
           Event_Ind_Code CHAR(1) CHARACTER SET LATIN NOT CASESPECIFIC NOT NULL,
           Purchase_Ind BYTEINT,
           Model_Value_Date DATE FORMAT 'dd/mm/yyyy' NOT NULL,
           Card_Value_Date DATE FORMAT 'dd/mm/yyyy' NOT NULL)
     PRIMARY INDEX ( Event_Id )
     PARTITION BY RANGE_N(Event_Value_Date  BETWEEN
     DATE '2000-01-01' AND DATE '2010-01-01' EACH INTERVAL '1' MONTH );

I use the following statement to load :

     insert into rtdata.J0033_FCT_PURCHASE_hist
     select * from rtdata.J0033_FCT_PURCHASE
     where Event_Value_Date  ='01/01/2004'

this is the explain:

Explanation
--------------------------------------------------
 
  1)First, lock rtdata."pseudo table" for write on a row hash.  
  2)Next, we lock rtdata."pseudo table" for read on a row hash.  
  3)We lock rtdata.J0033_FCT_PURCHASE_hist for write and we lock rtdata.J0033_FCT_PURCHASE for read.  
  4)We do an All-AMPs RETRIEVE step from rtdata.J0033_FCT_PURCHASE by way of a traversal of index # 12 without accessing the base table into Spool 32331, which is built locally on the AMPs.  
  5)We do a MERGE into table J0033_FCT_PURCHASE_hist from Spool 32331.  
  6)We send out an END TRANSACTION step to all AMPs involved in processing the request.  


the question is why the teradata system use the spool and why this step taking so much time(40 minutes)?

when I run the statement -

     select count(*) from rtdata.J0033_FCT_PURCHASE
       where Event_Value_Date  ='01/01/2004'

I get the answer immediately.

the number of rows that where Event_Value_Date ='01/01/2004' = 700,000



     
  <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: 27 Dec 2016