Am I doing this correctly?
Hi,
I like to use partitions to help with the deletion of data over time. Note that I'm a nembie to this.
Can you tell me if I’m going about this correctly?
ALERT TABLE tblCustomer
PARTITION BY RANGE ( YEAR (DateStamp) )
SUBPARTITION BY HASH ( MONTH (DateStamp) ) (
PARTITION y2010 VALUES LESS THAN (2010) (
SUBPARTITION y2010mJan,
SUBPARTITION y2010mFeb,
SUBPARTITION y2010mMar,
SUBPARTITION y2010mApr,
SUBPARTITION y2010mMay,
SUBPARTITION y2010mJune,
SUBPARTITION y2010mJuly,
SUBPARTITION y2010mAug,
SUBPARTITION y2010mSep,
SUBPARTITION y2010mOct,
SUBPARTITION y2010mNov,
SUBPARTITION y2010mDec
),
PARTITION y2011 VALUES LESS THAN (2011) (
SUBPARTITION y2011mJan,
SUBPARTITION y2011mFeb,
SUBPARTITION y2011mMar,
SUBPARTITION y2011mApr,
SUBPARTITION y2011mMay,
SUBPARTITION y2011mJune,
SUBPARTITION y2011mJuly,
SUBPARTITION y2011mAug,
SUBPARTITION y2011mSep,
SUBPARTITION y2011mOct,
SUBPARTITION y2011mNov,
SUBPARTITION y2011mDec
),
PARTITION y2012 VALUES LESS THAN (2012) (
SUBPARTITION y2012mJan,
SUBPARTITION y2012mFeb,
SUBPARTITION y2012mMar,
SUBPARTITION y2012mApr,
SUBPARTITION y2012mMay,
SUBPARTITION y2012mJune,
SUBPARTITION y2012mJuly,
SUBPARTITION y2012mAug,
SUBPARTITION y2012mSep,
SUBPARTITION y2012mOct,
SUBPARTITION y2012mNov,
SUBPARTITION y2012mDec
),
PARTITION y2013 VALUES LESS THAN (2013) (
SUBPARTITION y2013mJan,
SUBPARTITION y2013mFeb,
SUBPARTITION y2013mMar,
SUBPARTITION y2013mApr,
SUBPARTITION y2013mMay,
SUBPARTITION y2013mJune,
SUBPARTITION y2013mJuly,
SUBPARTITION y2013mAug,
SUBPARTITION y2013mSep,
SUBPARTITION y2013mOct,
SUBPARTITION y2013mNov,
SUBPARTITION y2013mDec
),
PARTITION y2014 VALUES LESS THAN (2014) (
SUBPARTITION y2014mJan,
SUBPARTITION y2014mFeb,
SUBPARTITION y2014mMar,
SUBPARTITION y2014mApr,
SUBPARTITION y2014mMay,
SUBPARTITION y2014mJune,
SUBPARTITION y2014mJuly,
SUBPARTITION y2014mAug,
SUBPARTITION y2014mSep,
SUBPARTITION y2014mOct,
SUBPARTITION y2014mNov,
SUBPARTITION y2014mDec
),
PARTITION yMax VALUES LESS THAN MAXVALUE
Also I’m assuming that there is no way to automatically create a new partition when we go into 2015? So I would have to add a new partition each year?
ALERT TABLE tblCustomer
ADD PARTITION y2015 VALUES LESS THAN (2015) (
SUBPARTITION y2015mJan,
SUBPARTITION y2015mFeb,
SUBPARTITION y2015mMar,
SUBPARTITION y2015mApr,
SUBPARTITION y2015mMay,
SUBPARTITION y2015mJune,
SUBPARTITION y2015mJuly,
SUBPARTITION y2015mAug,
SUBPARTITION y2015mSep,
SUBPARTITION y2015mOct,
SUBPARTITION y2015mNov,
SUBPARTITION y2015mDec
)
How do I check that the data is contained within the correct partition?
What is the best way to remove the data in a sub partition for example I would want to drop the sub partition y2010mJan in January 2014, drop y2010mFeb in February 2014 and in January 1st 2015 I would want to drop the partition y2010.
Jim