All Forums Teradata Applications
Sankalp.C 45 posts Joined 08/13
16 Dec 2013
Limit on a table

My senario is like -
I have two table Tab1 and Tab2. Incase, I Tab1 can't have more than 5 milion entries, incase of any thing more old data should be passed to Tab 2.
 
How to do it in teradata?

M.Saeed Khurram 544 posts Joined 09/12
16 Dec 2013

Hi San,
Can you please elaborate the scenario? You have two tables, Tab1 can hold only 5 million rows? and what is the benchmark for more old data?
and what is the source? is there any flag to represent older data?
 

Khurram

Sankalp.C 45 posts Joined 08/13
16 Dec 2013

Yes, 5 million is the volume constraint.
Or you can think I do not want data which is more than 1 yr old in Tab1. it should be passed to tab2( rather a history table by nature.).

Raja_KT 1246 posts Joined 07/09
16 Dec 2013

Hi Sankalp,
I think you may need to have a look at temporal features(Valid time, transaction time and bitemporal tables) of Teradata and then use a script to purge data to table2. 
The have features like time dimension.
Cheers,
Raja

Raja K Thaw
My wiki: http://en.wikipedia.org/wiki/User:Kt_raj1
Street Children suffer not by their fault. We can help them if we want.

dnoeth 4628 posts Joined 11/04
16 Dec 2013

@Raja:
Temporal will not help in that case as deleting rows will not remove them from the table, but update them to closed.
 
@Sankalp: 
How do you determine which rows to be moved? If you need exactly 5,000,000 rows you might try to put that logic in a statement trigger , but i don't know if this is actually possible and performant.
If it's just one year simply add partitioning by day for the next few years and run a daily job to drop the oldest partitions using WHERE PARTITION = 1 and the INSERT INTO option.

Dieter

Raja_KT 1246 posts Joined 07/09
16 Dec 2013

Ah ha! Thanks Dieter.
But yes a statement level trigger maybe a good option, though I have not implemented in Teradata.
Thanks,
Raja

Raja K Thaw
My wiki: http://en.wikipedia.org/wiki/User:Kt_raj1
Street Children suffer not by their fault. We can help them if we want.

Sankalp.C 45 posts Joined 08/13
17 Dec 2013

thanks! raja
Thanks! dieter for your kind attention.

You must sign in to leave a comment.