Archives of the TeradataForum

Message Posted: Fri, 24 Mar 2006 @ 15:20:34 GMT

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

Hi,

We are currently running on V2R5.1 and are looking at implementing PPI on out largest Transaction Table. Our Transaction table has over 2 Billion rows on it which we multiload to nightly and we don't delete any rows from it.

We have the table set up as a NUPI on Acc_No & Trans_Dte and NUSI on Acc_No and a NUSI Trans_Dte.

We are looking at putting PPI on the Trans_Date partitioned by day starting at the earliest date going up to say 31 Dec 2006.

See DDL below

     Create set table Treansaction_Detail
     (Acc_No    Char(16)
     ,Trans_Dte  Date
     ,Trans_Amt    Decimal(18,2)
     ,Trans_Narrative  Char(30)
     ,etc
     ,etc...)
     Primary index (Acc_No, Trans_Dte)
     Index (Acc_no) , Index (Trans_Dte)
     Partition by range_n (Trans_Dte between 980101 and 1061231 each interval
     '1' day);

How do people maintain the range of the PPI on a on going basis?

1) Are one off scripts written to extend the range of the PPI?

2) Do people drop and re-create the table with the new range and insert all the old data into the table?

3) Do you run batch scripts to automatically update the range of the PPI of a table one a month/quarter/year?

I would imaging that it's probably a good idea to collect stats on the PPI index again after maintaining the PPI.

What are your experiences?

Thanks,

Fachtna O'Donovan
Data Warehouse Software Engineer
AIB Bank
Ireland

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