Single String vs Nested Partitions
When creating partitions by date it is almost always more effective to partition by a single string of ‘YYYY-MM-DD’ rather than use a multi-depth partition with The Year, Months, and Days all as their own. The advantage to using the single string approach allows for more SQL operators to be utilized such as LIKE, IN, and BETWEEN which cannot be as easily used if one utilizes nested partitions. Example: Dates to Select: 2015-01-01, 2015-02-03,2016-01-01 Tables Table A, Partitioned by DateStamp String as YYYY-MM-DD Table B, Partitioned by YEAR INT, Month INT, Day INT Queries on Table A All Dates SELECT * FROM TableA WHERE DateStamp IN (‘2015-01-01’, ‘2015-02-03’, ‘2016-01-01’) Only 2015 SELECT * FROM TableA WHERE DateStamp LIKE ‘2015-%’ Only 2015 and February SELECT * FROM TableA WHERE DateStamp LIKE ‘2015-02-%’ All Days that start/end with a 5 SELECT * FROM TableA WHERE DateStamp LIKE ‘%-%-%5’ All Days Between 2015-01-01 and 2015-03-01 SELECT * FROM TableA WHERE DateStamp BETWEEN ‘2015-01-01’ AND ‘2015-03-01’ Queries on Table B All Dates SELECT * FROM TableB WHERE (YEAR=2015 AND MONTH=01 AND DAY=01) OR (YEAR=2015 AND MONTH=02 AND DAY=03) OR (YEAR=2016 AND MONTH=01 AND DAY=01) Only 2015 SELECT * FROM TableB WHERE YEAR=2015 Only 2015 and February SELECT * FROM TableB WHERE YEAR=2015 and MONTH=02 All Days that start/end with a 5 SELECT * FROM TableB WHERE DAY LIKE ‘%5’ All Days Between 2015-01-01 and 2015-03-01 SELECT * FROM TableA WHERE YEAR=2015 and ((MONTH=01 OR MONTH=02) OR (MONTH =03 and DAY =01))
... View more
