Archives of the TeradataForum

Message Posted: Wed, 13 Apr 2005 @ 13:03:55 GMT

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

I have a sticky problem with table creations and want to incorporate partitioning to speed data access. Here's my situation:

     Table1:
       Columns:  Account Nbr, Posting Timestamp, Activity Date, data columns.
       Data Range:  Contains two calendar years worth of data based on
       current date.  Est. 55 million rows.

     Table 2:
       Columns:  Account Nbr, Posting Timestamp, no Activity Date, data columns.
       Data Range:  Contains two calendar years worth of data based on
       current date.  Est. 100 million rows.

Table1 and Table2 must be efficiently joinable in views based on Account Number and Posting Timestamp.

Table1 is most likely accessed based on the range of dates in the Activity Date column.

Table1 or Table2 may be joined with other database tables based on join fields of Account Number and date range of Activity Date.

I'd like to get suggestions as to the best way to set up these two tables, keeping in mind that they will be appended/updated using a Multi- load nightly, or TPUMP batch process during the day.

My first approach was to create Table1 with Primary Index on Account Number and Posting Timestamp and Partition on Extract(Month from Activity Date), but I get an invalid Update error (UTY0805 RDBMS failure, 3538: A MultiLoad UPDATE Statem.) in a Multi-load where the Account Number and Posting Timestamp are checked using the Where clause "Where Account_Number = :Account_Number and Posting_TMS = :Posting_TMS (Timestamp(6));". The incoming timestamp field is a CHAR(26) timestamp unloaded from a DB2 subsystem. If I recreate the table without the partitioning index, then the update statement receives no error, and the muli-load runs successfully. I prefer to partition based on the Activity DT column rather than the Posting Timestamp column for two reasons: First, a record may be posted in January that has an expected activity date well into the future (up to 12 months), and the typical method for using data from these tables is based on Activity Date rather than posting date in order to report on monthly, quarter to date, etc. date ranges. Thoughts or suggestions?

Thanks,

John

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