I have a table which is loaded daily and loading mechnism is Truncate-Load. The table has EMPLOYEE_ID as PI and Range partition on transaction date. The user queries on this table are like,
" I want to see all the employess who joined between jan'13 to Feb'13"
"I want to see all the employess who got promoted on Dec'2012"
Now my questions are:
1. Does partition on NON PI coulmn works? Does this partition on NON PI column impact the data distribution?
2. As it is "Truncate-load", so how can I maintain it? Is "drop partition" a good option?
Please let me know.
Thanks in advance,
#1: Yes, No
The partitioning on dates fits your example queries, they will benefit from the PPI, but when the PI is accessed it will be slower than before: it's still an single-AMP access, but each populated partition has to be probed. When th PI is heavily used you might consider an additional USI/NUSI on EMPLOYEE_ID.
The distribution is stil based on the PI, just the internal order changes from ROWID to partition/ROWID.
TRUNCATE doesn't exist in Teradata, but a DELETE without WHERE condition is quite similar (so-called FastPath Delete).
Just be shure to collect stats after the load.