Delete Partition Vs Drop Partition?

Data Modeling

Delete Partition Vs Drop Partition?

I have not done much work with partitions in Teradata, I just wanted to know the diffrence between Deleting a partition and sroping a partition? 

and what are limitations with these commands? 

Khurram
3 REPLIES

Re: Delete Partition Vs Drop Partition?

Hi Khurram,

AFAIK:

For both row and column partitioning,

In case the Partitioned  table is created with no range clause, if you drop the range, then data will be moved to no range partition.So here you can delete the data beforehand.

In case the Partitioned  table is created having no "no range" or "unknown" clauses, you can add WITH DELETE clause.

PS:I dont k now why I cannot paste both the queries, row and partitioned.

Cheers,

Raja

Re: Delete Partition Vs Drop Partition?

Raja,

Thank you very much for the reponse, I am able to understand it to some extent. 

It would be great if you can paste the queries.

Khurram

Re: Delete Partition Vs Drop Partition?

Hi Khurram,

here:

say a row or column partitioning table use alter table1:

MODIFY drop range BETWEEN DATE 'your first date' AND DATE 'your last date' EACH INTERVAL '1' MONTH ADD RANGE BETWEEN DATE 'your fist date' AND DATE 'your last date' EACH INTERVAL '1' MONTH WITH DELETE;

cheers,

Raja