Drop or Delete Which one is better for this scenario?

Database
Enthusiast

Drop or Delete Which one is better for this scenario?

I have 2 tables one having 800 Million Records and other having 150 Million Records, both these tables are full refresh tables on a weekly basis. Both the tables are date partitioned, having USI on 2 columns each.

These 2 tables store only 5 years of data from the load date.

Load strategy Currently what we have is Delete the entire tables using Delete all and load the data.

Do you think we will have some savings if we approach with below one?

Instead of deleting, if we drop the table and re-create with fresh DDL with only 5 years of partition weekly and load data.

5 REPLIES
Enthusiast

Re: Drop or Delete Which one is better for this scenario?

any inputs on this please, Mainly i would like to know, if there is a SI on a table, when we do a Delete or Delete All, will it follow fast path delete or not.

Supporter

Re: Drop or Delete Which one is better for this scenario?

you don't give much information what changes on a weekly basis. Will old data be changed?

How long takes your current approach?

Enthusiast

Re: Drop or Delete Which one is better for this scenario?

Hi Ulrich thanks for your response, Its a full refresh, we clear all the data and re-load the 5 years of data from foundation tables. again next week we repeat the same process. 

My question here is, which process helps me in clearing the data better & faster, Delete table or Drop table. 

Teradata Employee

Re: Drop or Delete Which one is better for this scenario?

You can still get fast path delete with the SI.

USI will impact your loading, though.

Enthusiast

Re: Drop or Delete Which one is better for this scenario?

Thanks Fred