DROP OLDEST PARTITION AND ADD NEW PARTITION

Database

DROP OLDEST PARTITION AND ADD NEW PARTITION

Hi,

I am trying to drop the oldest partition from my table and add a new partition .

I am using a CAL_KEY(integer data type) field for partitioning my table .I have to DROP the oldest month partition from my table.

so I cast this field as below PARTITION BY RANGE_N(CAST((CAL_KEY ) AS DATE FORMAT 'YYYYMMDD') BETWEEN 20100601 AND 20100630 EACH INTERVAL '1' MONTH ,
20100701 AND 20100731 EACH INTERVAL '1' MONTH ,
20100801 AND 20100831 EACH INTERVAL '1' MONTH )

I am using sel min(partition) from tablename to get the oldest partition from the table.it works fine.

but the problem is with the data type of CAL_KEY.In table it is defined as integer so I am getting the following error:3732

kindly help me

thanks,
vani

Tags (1)
8 REPLIES
Senior Apprentice

Re: DROP OLDEST PARTITION AND ADD NEW PARTITION

Hi vani,
could you post the source code you acztually tried?

MIN(PARTITION) returns the lowest actually used *logical* partition number, which is usually 1.

Your partitioning creates partitions in the year 3910 :-)

Dieter

Re: DROP OLDEST PARTITION AND ADD NEW PARTITION

Hi Dieter,

sorry.pls find the code

SELECT MAX(cal_key),min(cal_key),min(v1.PARTITION)
FROM
table name v1 ,
(SELECT MIN(PARTITION) AS PARTNO FROM table name )v2
WHERE v1.PARTITION= v2.PARTNO
this is giving me the minimum partition number and min and max of cal_key .but while i am using the partition number in the below query

ALTER TABLE table name MODIFY PRIMARY INDEX ( field1,field2... ) DROP RANGE WHERE PARTITION =197 WITH DELETE;

I am getting error
Senior Apprentice

Re: DROP OLDEST PARTITION AND ADD NEW PARTITION

Ypou query for the oldest partition with data in it, but not the oldest defined partition.
This is always partition 1:

DROP RANGE WHERE PARTITION =1

But caution, the previous "partition 2" will be "partition 1" after the alter table, so don't repeat that "drop range" :-)

Dieter

Re: DROP OLDEST PARTITION AND ADD NEW PARTITION

Hi Dieter,

actually my requirement is I have to retain only 13 months and dropping off any older data.my table is contains 13 months partition i have to drop the oldest month data and need to create a new partition at the begining of new month
Enthusiast

Re: DROP OLDEST PARTITION AND ADD NEW PARTITION

Hi Vani,

Is the below query giving you the minimum partition as 197 [ My intention is it giving value greater than 1]. Are you using the below query to find the minimum partition.

Why am I asking is generally even if you delete the oldest means the first partition in the table the next partition will become the first partition.

======================================
SELECT MAX(cal_key),min(cal_key),min(v1.PARTITION)
FROM
table name v1 ,
(SELECT MIN(PARTITION) AS PARTNO FROM table name )v2
WHERE v1.PARTITION= v2.PARTNO
this is giving me the minimum partition number and min and max of cal_key .but while i am using the partition number in the below query

ALTER TABLE table name MODIFY PRIMARY INDEX ( field1,field2... ) DROP RANGE WHERE PARTITION =197 WITH DELETE;
======================================
Senior Apprentice

Re: DROP OLDEST PARTITION AND ADD NEW PARTITION

Only ranges at the "ends" of your partitioning schema can be added/dropped, but you try to drop a partition inbetween.
What's the actual source code of your PARTITION BY?

Why don't you simply delete the oldest month using a SQL DELETE?
Instead of creating a new partition every month you can also extend the partitioning until 2030.
Both should not cause any problems, as long as your partition stats are current.

If you still insist to run your ALTER TABLE you could use:
DROP RANGE WHERE PARTITION <=197

Dieter
Teradata Employee

Re: DROP OLDEST PARTITION AND ADD NEW PARTITION

constraintText in dbc.indexConstraints with constraintType = ‘Q’ (PPI)

SEL CAST ((TRIM( SUBSTRING(constrainttext FROM (INDEX(constrainttext,'''')+1)  FOR 10))) AS DATE)

FROM dbc.indexconstraints

WHERE databasename = database

AND tablename = table

AND constrainttype = 'Q'

gives you the start date of the partition with or withoud data.

Re: DROP OLDEST PARTITION AND ADD NEW PARTITION

Have also similar requirement . Vanitha .  Could you please share  to me what have you done .

Already I did purge the OLD data   but got stuck up in "Drop the Partion" and  Add new partion.