Archives of the TeradataForum

Message Posted: Wed, 07 Jun 2006 @ 16:45:47 GMT

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

<-- Anonymously Posted: Wednesday, June 07, 2006 11:30 -->

I have a Brand O database conversion problem where 250 row values were mashed into 250 columns and the table has grown to 13,000,000 rows. Most of the rows in the 250 columns are null. The old table looks like this:

Existing Table (Ugh!)

     Customer_Number     Integer Not Null PK,
     Col001              Decimal(9,2),
     Col002              Decimal(9,2),
     Col003              Decimal(9,2),
     Col004              Decimal(9,2),
     Col005              Decimal(9,2),
     Col006              Decimal(9,2),
     Col007              Decimal(9,2),
     .
     Col250              Decimal(9,2)

New Table

     Customer_Number     Integer,
     Type_ID             SmallInt,
     Sales_Amount        Decimal(9,2)

We're trying to come up with a set based solution to make this conversion happen as fast as possible. Options we're looking at are:

     250 Unions Inserted into New_Table - the issue here is 250 x 13 MM =
     3.25 billion rows in the set of Unions
                 Insert into New_Table
     Select Customer_Number, 1, Col001 From Old_Table Where Col001 is not
     null
     Union
     Select Customer_Number, 2, Col002 From Old_Table Where Col002 is not
     null
                 Union
                 .
     Select Customer_Number, 250, Col250 From Old_Table Where Col250 is not
     null

Stored Procedure using Cursor (much code omitted for clarity) - the issue here is anemic cursor performance.

     Selects * from Old_Table
     If Col001 is not null
       Insert into New_Table
       (Customer_Number, 1, Col001)
     End If

     If Col002 is not null
       Insert into New_Table
       (Customer_Number, 1, Col001)
     End If

.    .
     If Col250 is not null
       Insert into New_Table
       (Customer_Number, 2, Col250)
     End If

Do 250 FastExports with 250 FastLoads to a staging table each iteration does an insert to New_Table and clears staging for next. The drawback here is authoring a flood of scripts.

Virtually every Brand O database conversion to Teradata has this issue (I know of one Brand O DW that had 5,000 columns in one table), does anyone have a better solution?

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