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 -->

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>>

Attachments

Library

Quick Reference