Best approach to handle partition in truncate load table

Database
Enthusiast

Best approach to handle partition in truncate load table

Dear Expert,

 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 REPLY
Senior Apprentice

Re: Best approach to handle partition in truncate load table

Short answers:

#1: Yes, No

#2: 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.

Dieter