Hello Friends,
We are trying to create a monthly range partition table , partitioned on
column PART_DATE. This will hold Orders and part_date is nothing but
invoice date. Some Team mates are asking to use the "PART_DATE" column as
data type "INTEGER" with "YYYYMM" format and also define partitions as
below. Want to know experts' views on this. If the data type of the
partition key matters here or not? Or if there is any downside of each
approach in future?
-- With date data type, It will look like as below
CREATE TABLE TAB1( COLUMN1 VARCHAR(36) NOT NULL , PART_DATE DATE NOT
NULL ) PARTITION BY RANGE (PART_DATE);
CREATE TABLE TAB1_202309 PARTITION OF TAB1 FOR VALUES FROM ('2023-09-01')
TO ('2023-10-01');
CREATE TABLE TAB1_202310 PARTITION OF TAB1 FOR VALUES FROM ('2023-10-01')
TO ('2023-11-01');
CREATE TABLE TAB1_202311 PARTITION OF TAB1 FOR VALUES FROM ('2023-11-01')
TO ('2023-12-01');
ALTER TABLE TAB1 ADD CONSTRAINT PK_TAB1 PRIMARY KEY ( COLUMN1 ,
PART_DATE );
VS
-- With integer data type, It will look like as below
CREATE TABLE TAB1( COLUMN1 VARCHAR(36) NOT NULL , PART_DATE_YM_NM
Integer NOT NULL ) PARTITION BY RANGE (PART_DATE_YM_NM);
CREATE TABLE TAB1_202309 PARTITION OF TAB1 FOR VALUES FROM ('202309') TO
('202310');
CREATE TABLE TAB1_202310 PARTITION OF TAB1 FOR VALUES FROM ('202310') TO
('202311');
CREATE TABLE TAB1_202311 PARTITION OF TAB1 FOR VALUES FROM ('202311') TO
('202312');
ALTER TABLE TAB1 ADD CONSTRAINT PK_TAB1 PRIMARY KEY ( COLUMN1 ,
PART_DATE_YM_NM );
