Archives of the TeradataForum
Message Posted: Fri, 24 Mar 2006 @ 15:20:34 GMT
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?
|Copyright 2016 - All Rights Reserved|
|Last Modified: 28 Jun 2020|