All Forums General
Purushotham 87 posts Joined 04/14
15 Sep 2014
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
 

Purushotham.M
Purushotham 87 posts Joined 04/14
15 Sep 2014

Hi,
 
From the above table,  I want to create a Partition on ID column.
In partition definition what should i specify in Each interval ?
 

Purushotham.M

Raja_KT 1246 posts Joined 07/09
15 Sep 2014

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/Alter_Function-Syntax.020.106.html
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 

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.

Purushotham 87 posts Joined 04/14
15 Sep 2014

Hi Raja,
If a table is not empty,can we drop a particular Partition.
 
Thanks for your reply..
Regards,
Purushotham

Purushotham.M

Raja_KT 1246 posts Joined 07/09
15 Sep 2014

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/Alter_Function-Details.008.088.html
You could have tried it. Real life time experience is way galaxy far better than anything :).
Cheers,

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.

Purushotham 87 posts Joined 04/14
16 Sep 2014

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.

Purushotham.M

Raja_KT 1246 posts Joined 07/09
16 Sep 2014

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

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.

You must sign in to leave a comment.