I had to do the same thing and solved slightly differently. Basically as far as I understand by reading the docs MySQL subpartitioning does not support partition types besides HASH and KEY.
In MySQL 5.6, it is possible to subpartition tables that are partitioned by RANGE or LIST. Subpartitions may use either HASH or KEY partitioning. This is also known as composite partitioning.
This means that we can't determine in what subpartition a record will end up. It's up to MySQL. So I don't think it's wise giving such names to your subpartitions (i.e. january) because no way you're going to know if stuff created on that month will end up there. This because you can't subpartition by MONTH(nav_date)
but only by HASH(MONTH(nav_date))
or KEY(MONTH(nav_date))
.
So to solve the problem I decided to create a new month
column in my table and then I added an index to it. Then I subpartioned by KEY(month)
without caring about the subpartition names. This way in MySQL 5.6 I could select the main partition from the FROM clause and the subpartition by specifying the month in the WHERE clause.
Follows the full example:
CREATE TABLE `my_example` (
`id` int(10) unsigned NOT NULL AUTO_INCREMENT,
`value` double DEFAULT NULL,
`is_deleted` tinyint(1) NOT NULL DEFAULT '0',
`timestamp` datetime NOT NULL,
`last_modified` datetime NOT NULL,
`month` tinyint(1) NOT NULL,
PRIMARY KEY (`id`,`timestamp`, `month`),
KEY `in_is_deleted` (`is_deleted`),
KEY `in_last_modified` (`last_modified`),
KEY `in_timestamp` (`timestamp`),
KEY `in_month` (`month`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci
PARTITION BY RANGE (YEAR(`timestamp`))
SUBPARTITION BY KEY (`month`)
SUBPARTITIONS 12 (
PARTITION p2011 VALUES LESS THAN (2012),
PARTITION p2012 VALUES LESS THAN (2013),
PARTITION p2013 VALUES LESS THAN (2014),
PARTITION p2014 VALUES LESS THAN (2015),
PARTITION p2015 VALUES LESS THAN (2016),
PARTITION p2016 VALUES LESS THAN (2017),
PARTITION p2017 VALUES LESS THAN (2018),
PARTITION pmax VALUES LESS THAN MAXVALUE
);
EXPLAIN PARTITIONS SELECT * FROM my_example PARTITION (p2015); -- will go through all twelve subpartitions
EXPLAIN PARTITIONS SELECT * FROM my_example PARTITION (p2015) WHERE month = 9; -- will look only into one subpartition
Rebuild the index to write out all pages freshly in the optimal way.
If you want to absolutely minimize space usage, specify ONLINE = OFF
. Online operations add a small amount of per-row space overhead.
MAXDOP = 1
can be good to reduce fragmentation. Not sure if that can help with space usage, though. I can't think of anything that a DOP of 1 might save.
If the target file of the rebuild already contains fragmented freespace the newly built index/partition can turn out to be fragmented immediately. Consider building into an empty target filegroup that is pre-sized to approximately the right size.
Best Answer
This online compression is extra cost option. And moreover usually online compression has worse compression ratio, then offline one. It's because the 1st one work on row level while the latter one works on block level. So if you really want to spare some space in the database and your old data are mostly read-only you should use:
And then rebuild(not recreated) all indexes manually, preferably with reasonable parallel option.
PS: you should also read something about various compression options Oracle offers. Unfortunately it is not simple from both technical and also licensing perspective.
Briefly, Oracle supports these compression options:
BASIC, offline, block level. Usually the bigger the tablespace's block size the better compression ratio. This one is used only when moving tables/partitions, or when using direct path load (/* +APPEND */ hint)
OTLP, online, row level, being part of advanced compression extra cost option.