Archives of the TeradataForum
Message Posted: Wed, 07 Jun 2006 @ 16:45:47 GMT
<-- 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)
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?
|Copyright 2016 - All Rights Reserved|
|Last Modified: 28 Jun 2020|