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 );
