DROP partition range

Database
Enthusiast

DROP partition range

Good morning,

 

We are modifying all date-constrained PPI tables to have a specific end date range.  All of the tables currently have an end range that is too far in the future, but those end dates vary.

 

Is it possible to drop partitions by specifying GREATER THAN a value or must it be a defined BETWEEN range?

9 REPLIES
rjg
Supporter

Re: DROP partition range

What date is condidered to far in the future? 

 

You could calculate high date for your tables using this for '1' day partition.

Modify same for other increments.

 

SyntaxEditor Code Snippet

sel LowDate  + highval
from(sel max(partition) from Part_Table)D(Highval)

 

Enthusiast

Re: DROP partition range

Some of the tables were created with an end date of 3035-12-31... some with 2050 .... some with 2049..and there may be others.  All of these are with 1 day intervals.  So way more empty partitions than is considered a best practice.

 

 

Enthusiast

Re: DROP partition range


rjg wrote:

What date is condidered to far in the future? 

 

You could calculate high date for your tables using this for '1' day partition.

Modify same for other increments.

 

SyntaxEditor Code Snippet

sel LowDate  + highval
from(sel max(partition) from Part_Table)D(Highval)

 


What column am I using for LowDate in this query?  The partitioning column?

Enthusiast

Re: DROP partition range


rjg wrote:

What date is condidered to far in the future? 

 

You could calculate high date for your tables using this for '1' day partition.

Modify same for other increments.

 

SyntaxEditor Code Snippet

sel LowDate  + highval
from(sel max(partition) from Part_Table)D(Highval)

 


So I ran this query using one of my tables.

The value returned to me was 2021-07-10.

The max value of the partitioning column is 2016-04-05.

I'm not sure what this is giving me.

rjg
Supporter

Re: DROP partition range

The lowval would be the start date of your existing partitions, If that also varies this may not help.

 

If partitiion stats are kept current, the extra partitions should not matter. What best practice? 

 

 

Enthusiast

Re: DROP partition range


rjg wrote:

The lowval would be the start date of your existing partitions, If that also varies this may not help.

 

If partitiion stats are kept current, the extra partitions should not matter. What best practice? 

 

 


Best Practices for Adding and Dropping Partitioning Ranges from a Partitioning Expression
The following guidelines are recommended best practices for maintaining the partitioning ranges of a partitioned table.
Have a well‑defined, preferably automated, process for scheduling and issuing ALTER TABLE requests to add and drop partitioning ranges.
Define enough future ranges (a future range is a range over date values that have not yet occurred at the time the partition is added) to minimize the frequency of ALTER TABLE requests for adding and dropping partitions.
If you perform this task too infrequently, you might forget to make the necessary range drops and additions, and the process might fail because it does not occur frequently enough.
Consider the following guidelines.
It is generally better to perform this task monthly rather than yearly.
You should keep the number of “future” ranges at any one time to less than 10 percent of the total number of defined partitions.
Enthusiast

Re: DROP partition range

But back to my initial question regarding dropping partitions....

 

is BETWEEN the only way to specify partitions or is there a GREATER THAN option?

 

I haven't found it in the documentation, so I'm guessing there isn't.  But thought I would ask in case I overlooked something.

rjg
Supporter

Re: DROP partition range

Your're right, my ideas was crap.

It only returns results for populated partitions. 

rjg
Supporter

Re: DROP partition range

Maybe this will help if you know your low partition range value

 

SyntaxEditor Code Snippet

sel Date'Min_Range_Date' + P - 1  from 
(sel DefinedCombinedPartitions from dbc.partitioningconstraintsv where databasename = 'Dbname' and tablename = 'tbname')D(P)