Dropping a partition...

Data Modeling
Enthusiast

Dropping a partition...

Hi All -

How would I drop a partition if i need to calculate against the current date?

So far I have 

ALTER TABLE XBITbls.FctBlgAcctSvcPrc_PARTITION

    MODIFY

      DROP RANGE WHERE XBITbls.FctBlgAcctSvcPrc_PARTITION.PartitionDt = CURRENT_DATE - 2

It errors out...

CREATE TABLE_1

...................

..... IsCurrFl BYTEINT,

      SnapDttm TIMESTAMP(0),

      PartitionDt DATE FORMAT 'yyyy-mm-dd')

PRIMARY INDEX ( DimAcctSk ,SvcCdDimBlgPrdSk ,PckgCdDimBlgPrdSk ,

MSOSvcCdDimBlgPrdSk ,SvcDimBlgOffrDiscCdSk ,SpclDimBlgOffrDiscCdSk ,

CustDimBlgOffrDiscCdSk ,BlgPrdSvcCdSeq )

PARTITION BY RANGE_N(PartitionDt  BETWEEN DATE '2014-12-02' AND DATE '2030-12-31' EACH INTERVAL '1' DAY ,

 NO RANGE, UNKNOWN);

Thanks,

Dan

4 REPLIES
Senior Apprentice

Re: Dropping a partition...

Hi Dan,

if you always want to drop the first partition (the one two days ago) you could simply use

WHERE PARTITION = 1;

every day. But never run it two times on a singe day :-)

Otherwise I would simply

DELETE FROM XBITbls.FctBlgAcctSvcPrc_PARTITION
WHERE PartitionDt = CURRENT_DATE - 2;

without dropping the partition. 

Empty partitions don't have any overhead.

Enthusiast

Re: Dropping a partition...

Thanks, Dieter!!

Enthusiast

Re: Dropping a partition...

Follow up question, Dieter...

If I wanted to go the route of dropping the partition, instead of doing the delete, could i compare the PARTITION level against a date? Similar to 

WHERE PARTITION = 1 AND PartitionDt = CURRENT_DATE -1;

Would this work?

Dan

Senior Apprentice

Re: Dropping a partition...

Hi Dan,

no it wouldn't. Simply try it.