HOw to drop a particular Partition in partition table

General
Enthusiast

HOw to drop a particular Partition in partition table

Hi Everyone,

Can anyone pls how to drop a Particular partition in Partition Primary index table.

ex: CREATE TABLE PURU_TAB,FALLBACK(ID INT,NAME CHAR(10),DOB DATE)PRIMARY INDEX(ID)

PARTITION BY RANGE_N(DOB BETWEEN DATE '2014-01-15' AND DATE '2014-08-30' EACH INTERVAL '1' MONTH,NO RANGE,UNKNOWN)

here i want to drop one partition in a table ....how can we drop the partition.

Regards,

Purushotham

6 REPLIES
Enthusiast

Re: HOw to drop a particular Partition in partition table

Hi,

From the above table,  I want to create a Partition on ID column.

In partition definition what should i specify in Each interval ?

Enthusiast

Re: HOw to drop a particular Partition in partition table

You can refer to this link for adding and dropping partition:

http://www.info.teradata.com/HTMLPubs/DB_TTU_14_00/index.html#page/SQL_Reference/B035_1144_111A/Alte...

Be aware of rules too 

Rules for Modifying Populated and Unpopulated Tables and Join Indexes

you can add:

RANGE_N(id BETWEEN 0

AND 100000

EACH 100),... It can be 1 or 2 or....... interval 

Enthusiast

Re: HOw to drop a particular Partition in partition table

Hi Raja,

If a table is not empty,can we drop a particular Partition.

Thanks for your reply..

Regards,

Purushotham

Enthusiast

Re: HOw to drop a particular Partition in partition table

There are cases, for that reason I  mentioned rules. I do not want to rephrase the words from the doc. The link is provided here:

http://www.info.teradata.com/HTMLPubs/DB_TTU_14_00/index.html#page/SQL_Reference/B035_1184_111A/Alte...

You could have tried it. Real life time experience is way galaxy far better than anything :).

Cheers,

Enthusiast

Re: HOw to drop a particular Partition in partition table

Hi Raja,

AS you provided the link, i tried it but  I am getting an error.

Eg : ALTER TABLE PPI MODIFY DROP RANGE BETWEEN DATE '2014-01-13' AND DATE '2014-01-31'.If i tried i am getting error

Syntax error,expected something like a 'CHECK' keyword between the 'MODIFY' keyword and the 'DROP' keyword.

Please help me out in this scenario.

Regards,

Purushotham.

Enthusiast

Re: HOw to drop a particular Partition in partition table

Check your syntax properly. Check your table  and data properly too. Understand, which data you are going to drop.