Discussions
Categories
- 196.8K All Categories
- 2.2K Data
- 235 Big Data Appliance
- 1.9K Data Science
- 449.9K Databases
- 221.6K General Database Discussions
- 3.8K Java and JavaScript in the Database
- 31 Multilingual Engine
- 549 MySQL Community Space
- 478 NoSQL Database
- 7.9K Oracle Database Express Edition (XE)
- 3K ORDS, SODA & JSON in the Database
- 532 SQLcl
- 4K SQL Developer Data Modeler
- 186.9K SQL & PL/SQL
- 21.3K SQL Developer
- 295.4K Development
- 17 Developer Projects
- 138 Programming Languages
- 292.1K Development Tools
- 104 DevOps
- 3.1K QA/Testing
- 645.9K Java
- 28 Java Learning Subscription
- 37K Database Connectivity
- 154 Java Community Process
- 105 Java 25
- 22.1K Java APIs
- 138.1K Java Development Tools
- 165.3K Java EE (Java Enterprise Edition)
- 17 Java Essentials
- 158 Java 8 Questions
- 85.9K Java Programming
- 79 Java Puzzle Ball
- 65.1K New To Java
- 1.7K Training / Learning / Certification
- 13.8K Java HotSpot Virtual Machine
- 94.2K Java SE
- 13.8K Java Security
- 203 Java User Groups
- 24 JavaScript - Nashorn
- Programs
- 401 LiveLabs
- 37 Workshops
- 10.2K Software
- 6.7K Berkeley DB Family
- 3.5K JHeadstart
- 5.6K Other Languages
- 2.3K Chinese
- 171 Deutsche Oracle Community
- 1.1K Español
- 1.9K Japanese
- 230 Portuguese
Partitioning on column of DATE datatype

Hi All,
I am working on a data warehousing project for business intelligence application. We have several aggregate tables in our database and we are planning to create partitions on date_id column which is of `DATE` data type. All the aggregate tables are designed to hold two years worth of data. On average each table consists of 10-15 million records.
I mainly thought of two methods for creating partitions on date_id columns:
Method 1 : PARTITION BY RANGE(date_id)
As partition by range only supports columns of integer type. I would convert the date_id(DATE) into integer value using TO_DAYS function for creating partitioning key and threshold values as shown below.
Table structure before partitioning:
CREATE TABLE `fact_agg_combo_report_day` (
`BrandID` varchar(4) DEFAULT NULL,
`RegionID` varchar(4) DEFAULT NULL,
`RegionName` varchar(25) DEFAULT NULL,
`DistrictID` varchar(4) DEFAULT NULL,
`DistrictName` varchar(25) DEFAULT NULL,
`StoreID` varchar(4) DEFAULT NULL,
`StoreDescription` varchar(50) DEFAULT NULL,
`MerchComboID` int(11) DEFAULT NULL,
`MerchComboDesc` varchar(255) DEFAULT NULL,
`ChannelCode` char(1) DEFAULT NULL,
`DATE_ID` date DEFAULT NULL,
`DATE_ID_YESTERDAY` date DEFAULT NULL,
`DayNameYesterday` varchar(9) DEFAULT NULL,
`DATE_ID_LY` date DEFAULT NULL,
`DAY_OF_WEEK` int(11) DEFAULT NULL,
`DayName` varchar(9) DEFAULT NULL,
`ComboType` varchar(5) DEFAULT NULL,
`SalesAmount` decimal(10,4) DEFAULT NULL,
`SalesAmountLocal` decimal(10,4) DEFAULT NULL,
`ItemQuantity` bigint(11) DEFAULT NULL,
`SalesAmountLY` decimal(10,4) DEFAULT NULL,
`SalesAmountLocalLY` decimal(10,4) DEFAULT NULL,
`ItemQuantityLY` bigint(11) DEFAULT NULL,
`PercentToLY` decimal(19,8) DEFAULT NULL,
`PlanSalesAmount` decimal(12,4) DEFAULT NULL,
`PlanSalesUnits` bigint(11) DEFAULT NULL,
`PercentToPlan` decimal(21,8) DEFAULT NULL,
KEY `ix1_fact_agg_combo_report_day` (`BrandID`,`StoreID`,`DATE_ID`,`MerchComboID`),
KEY `ix_fact_agg_combo_report_day` (`DATE_ID`)
)
Table structure after partitioning will look like :
CREATE TABLE `fact_agg_combo_report_day_saj` (
`BrandID` varchar(4) DEFAULT NULL,
`RegionID` varchar(4) DEFAULT NULL,
`RegionName` varchar(25) DEFAULT NULL,
`DistrictID` varchar(4) DEFAULT NULL,
`DistrictName` varchar(25) DEFAULT NULL,
`StoreID` varchar(4) DEFAULT NULL,
`StoreDescription` varchar(50) DEFAULT NULL,
`MerchComboID` int(11) DEFAULT NULL,
`MerchComboDesc` varchar(255) DEFAULT NULL,
`ChannelCode` char(1) DEFAULT NULL,
`DATE_ID` date DEFAULT NULL,
`DATE_ID_YESTERDAY` date DEFAULT NULL,
`DayNameYesterday` varchar(9) DEFAULT NULL,
`DATE_ID_LY` date DEFAULT NULL,
`DAY_OF_WEEK` int(11) DEFAULT NULL,
`DayName` varchar(9) DEFAULT NULL,
`ComboType` varchar(5) DEFAULT NULL,
`SalesAmount` decimal(10,4) DEFAULT NULL,
`SalesAmountLocal` decimal(10,4) DEFAULT NULL,
`ItemQuantity` bigint(11) DEFAULT NULL,
`SalesAmountLY` decimal(10,4) DEFAULT NULL,
`SalesAmountLocalLY` decimal(10,4) DEFAULT NULL,
`ItemQuantityLY` bigint(11) DEFAULT NULL,
`PercentToLY` decimal(19,8) DEFAULT NULL,
`PlanSalesAmount` decimal(12,4) DEFAULT NULL,
`PlanSalesUnits` bigint(11) DEFAULT NULL,
`PercentToPlan` decimal(21,8) DEFAULT NULL,
KEY `ix1_fact_agg_combo_report_day` (`BrandID`,`StoreID`,`DATE_ID`,`MerchComboID`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1
/*!50100 PARTITION BY RANGE (TO_DAYS(date_id))
(PARTITION pmin VALUES LESS THAN (0) ENGINE = InnoDB,
PARTITION p201312 VALUES LESS THAN (735599) ENGINE = InnoDB,
PARTITION p201401 VALUES LESS THAN (735630) ENGINE = InnoDB,
PARTITION p201402 VALUES LESS THAN (735658) ENGINE = InnoDB,
PARTITION p201403 VALUES LESS THAN (735689) ENGINE = InnoDB,
PARTITION p201404 VALUES LESS THAN (735719) ENGINE = InnoDB,
PARTITION p201405 VALUES LESS THAN (735750) ENGINE = InnoDB,
PARTITION p201406 VALUES LESS THAN (735780) ENGINE = InnoDB,
PARTITION p201407 VALUES LESS THAN (735811) ENGINE = InnoDB,
PARTITION p201408 VALUES LESS THAN (735842) ENGINE = InnoDB,
PARTITION p201409 VALUES LESS THAN (735872) ENGINE = InnoDB,
PARTITION p201410 VALUES LESS THAN (735903) ENGINE = InnoDB,
PARTITION p201411 VALUES LESS THAN (735933) ENGINE = InnoDB,
PARTITION p201412 VALUES LESS THAN (735964) ENGINE = InnoDB,
PARTITION p201501 VALUES LESS THAN (735995) ENGINE = InnoDB,
PARTITION p201502 VALUES LESS THAN (736023) ENGINE = InnoDB,
PARTITION p201503 VALUES LESS THAN (736054) ENGINE = InnoDB,
PARTITION p201504 VALUES LESS THAN (736084) ENGINE = InnoDB,
PARTITION p201505 VALUES LESS THAN (736115) ENGINE = InnoDB,
PARTITION p201506 VALUES LESS THAN (736145) ENGINE = InnoDB,
PARTITION p201507 VALUES LESS THAN (736176) ENGINE = InnoDB,
PARTITION p201508 VALUES LESS THAN (736207) ENGINE = InnoDB,
PARTITION p201509 VALUES LESS THAN (736237) ENGINE = InnoDB,
PARTITION p201510 VALUES LESS THAN (736268) ENGINE = InnoDB,
PARTITION p201511 VALUES LESS THAN (736298) ENGINE = InnoDB,
PARTITION p201512 VALUES LESS THAN (736329) ENGINE = InnoDB,
PARTITION p201601 VALUES LESS THAN (736360) ENGINE = InnoDB,
PARTITION pmax VALUES LESS THAN MAXVALUE ENGINE = InnoDB)
Method2: PARTITION BY RANGE COLUMNS
As this partitioning type supports DATE type along with integers I can use this method directly specifying the column name, value as the partitioning key and threshold for the partition respectively. The issue with this method is to deciding the threshold for min partition. I have selected the min date as `0000-01-01` as we never going to have any records with that date or closer to that date.
Table Structure after partitioning:
CREATE TABLE `fact_agg_combo_report_day` (
`BrandID` varchar(4) DEFAULT NULL,
`RegionID` varchar(4) DEFAULT NULL,
`RegionName` varchar(25) DEFAULT NULL,
`DistrictID` varchar(4) DEFAULT NULL,
`DistrictName` varchar(25) DEFAULT NULL,
`StoreID` varchar(4) DEFAULT NULL,
`StoreDescription` varchar(50) DEFAULT NULL,
`MerchComboID` int(11) DEFAULT NULL,
`MerchComboDesc` varchar(255) DEFAULT NULL,
`ChannelCode` char(1) DEFAULT NULL,
`DATE_ID` date DEFAULT NULL,
`DATE_ID_YESTERDAY` date DEFAULT NULL,
`DayNameYesterday` varchar(9) DEFAULT NULL,
`DATE_ID_LY` date DEFAULT NULL,
`DAY_OF_WEEK` int(11) DEFAULT NULL,
`DayName` varchar(9) DEFAULT NULL,
`ComboType` varchar(5) DEFAULT NULL,
`SalesAmount` decimal(10,4) DEFAULT NULL,
`SalesAmountLocal` decimal(10,4) DEFAULT NULL,
`ItemQuantity` bigint(11) DEFAULT NULL,
`SalesAmountLY` decimal(10,4) DEFAULT NULL,
`SalesAmountLocalLY` decimal(10,4) DEFAULT NULL,
`ItemQuantityLY` bigint(11) DEFAULT NULL,
`PercentToLY` decimal(19,8) DEFAULT NULL,
`PlanSalesAmount` decimal(12,4) DEFAULT NULL,
`PlanSalesUnits` bigint(11) DEFAULT NULL,
`PercentToPlan` decimal(21,8) DEFAULT NULL,
KEY `ix1_fact_agg_combo_report_day` (`BrandID`,`StoreID`,`DATE_ID`,`MerchComboID`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1
/*!50500 PARTITION BY RANGE COLUMNS(date_id)
(PARTITION pmin VALUES LESS THAN ('0000-01-01') ENGINE = InnoDB,
PARTITION p201403 VALUES LESS THAN ('2014-04-01') ENGINE = InnoDB,
PARTITION p201404 VALUES LESS THAN ('2014-05-01') ENGINE = InnoDB,
PARTITION p201405 VALUES LESS THAN ('2014-06-01') ENGINE = InnoDB,
PARTITION p201406 VALUES LESS THAN ('2014-07-01') ENGINE = InnoDB,
PARTITION p201407 VALUES LESS THAN ('2014-08-01') ENGINE = InnoDB,
PARTITION p201408 VALUES LESS THAN ('2014-09-01') ENGINE = InnoDB,
PARTITION p201409 VALUES LESS THAN ('2014-10-01') ENGINE = InnoDB,
PARTITION p201410 VALUES LESS THAN ('2014-11-01') ENGINE = InnoDB,
PARTITION p201411 VALUES LESS THAN ('2014-12-01') ENGINE = InnoDB,
PARTITION p201412 VALUES LESS THAN ('2015-01-01') ENGINE = InnoDB,
PARTITION p201501 VALUES LESS THAN ('2015-02-01') ENGINE = InnoDB,
PARTITION p201502 VALUES LESS THAN ('2015-03-01') ENGINE = InnoDB,
PARTITION p201503 VALUES LESS THAN ('2015-04-01') ENGINE = InnoDB,
PARTITION p201504 VALUES LESS THAN ('2015-05-01') ENGINE = InnoDB,
PARTITION p201505 VALUES LESS THAN ('2015-06-01') ENGINE = InnoDB,
PARTITION p201506 VALUES LESS THAN ('2015-07-01') ENGINE = InnoDB,
PARTITION p201507 VALUES LESS THAN ('2015-08-01') ENGINE = InnoDB,
PARTITION p201508 VALUES LESS THAN ('2015-09-01') ENGINE = InnoDB,
PARTITION p201509 VALUES LESS THAN ('2015-10-01') ENGINE = InnoDB,
PARTITION p201510 VALUES LESS THAN ('2015-11-01') ENGINE = InnoDB,
PARTITION p201511 VALUES LESS THAN ('2015-12-01') ENGINE = InnoDB,
PARTITION p201512 VALUES LESS THAN ('2016-01-01') ENGINE = InnoDB,
PARTITION p201601 VALUES LESS THAN ('2016-02-01') ENGINE = InnoDB,
PARTITION p201602 VALUES LESS THAN ('2016-03-01') ENGINE = InnoDB,
PARTITION p201603 VALUES LESS THAN ('2016-04-01') ENGINE = InnoDB,
PARTITION p201604 VALUES LESS THAN ('2016-05-01') ENGINE = InnoDB,
PARTITION pmax VALUES LESS THAN (MAXVALUE) ENGINE = InnoDB)
Please let me know which method would be appropriate to use. Potential issues that may occur by using either of these methods if any. Would there be any performance issues with partitioning?
Answers
-
It should not make a difference. I would personally go with the second option and do away with the pmin partition if you can make sure you can scrub you data for dates below the threshold.
Dave Stokes
MySQL Community Manager