I have a table as follows which is currently partitioned AND sub-partitioned on the same column PRD_DESC fied. The partitions are one per year and 12 monthly sub-partitions per partition
CREATE TABLE TESTTAB (
"PRD_DESC" VARCHAR2(6 BYTE)NOT NULL ENABLE,
"PRD_DESC_YMW" VARCHAR2(8 BYTE)NOT NULL ENABLE,
...
...
...
PARTITION BY RANGE ("PRD_DESC")
SUBPARTITION BY LIST ("PRD_DESC")
(PARTITION "PROJ_COSTS_DTL_HIST_2009" VALUES LESS THAN ('200999')
( SUBPARTITION "PROJ_COSTS_DTL_HIST_2009_00" VALUES ('200900') ,
SUBPARTITION "PROJ_COSTS_DTL_HIST_2009_01" VALUES ('200901') ,
...
...
...
PARTITION "PROJ_COSTS_DTL_HIST_2023" VALUES LESS THAN ('2024')
(SUBPARTITION "PROJ_COSTS_DTL_HIST_2023_01" VALUES ('202301') ,
SUBPARTITION "PROJ_COSTS_DTL_HIST_2023_02" VALUES ('202302') ,
SUBPARTITION "PROJ_COSTS_DTL_HIST_2023_03" VALUES ('202303') ,
SUBPARTITION "PROJ_COSTS_DTL_HIST_2023_04" VALUES ('202304') ,
SUBPARTITION "PROJ_COSTS_DTL_HIST_2023_05" VALUES ('202305') ,
SUBPARTITION "PROJ_COSTS_DTL_HIST_2023_06" VALUES ('202306') ,
SUBPARTITION "PROJ_COSTS_DTL_HIST_2023_07" VALUES ('202307') ,
SUBPARTITION "PROJ_COSTS_DTL_HIST_2023_08" VALUES ('202308') ,
SUBPARTITION "PROJ_COSTS_DTL_HIST_2023_09" VALUES ('202309') ,
SUBPARTITION "PROJ_COSTS_DTL_HIST_2023_10" VALUES ('202310') ,
SUBPARTITION "PROJ_COSTS_DTL_HIST_2023_11" VALUES ('202311') ,
SUBPARTITION "PROJ_COSTS_DTL_HIST_2023_12" VALUES ('202312')
The data in that field is stored in YYYYMM format. In short, the partition key is on the "YYYY" portion of the column and the sub partitions are on "MM" portion. Disclaimer: I didn't design this and recently inherited this. Every year in Dec, we add partitions and sub partitions to the table manually by splitting the highest partition and also adding sub-partitions manually. I am trying to automate this whole thing by enabling interval partitioning method. But, I am not sure what would the DDL look like if I have to accomplish the objective: partition by YYYY part and sub-partition by MM part.
I have this DDL in mind but not sure it would work:
CREATE TABLE TESTTAB (
"PRD_DESC" VARCHAR2(6 BYTE)NOT NULL ENABLE,
"LDG_PRD_DESC_YMW" VARCHAR2(8 BYTE)NOT NULL ENABLE,
...
...
...
)
tablespace USERS
partition by range (PRD_DESC) INTERVAL( NUMTOYMINTERVAL (1, 'YEAR'))
subpartition by list(PRD_DESC)
subpartition template (
subpartition SP_01 values (01),
subpartition SP_02 values (02),
subpartition SP_03 values (03),
subpartition SP_04 values (04),
subpartition SP_05 values (05),
subpartition SP_06 values (06),
subpartition SP_07 values (07),
subpartition SP_08 values (08),
subpartition SP_09 values (09),
subpartition SP_10 values (10),
subpartition SP_11 values (11),
subpartition SP_12 values (12)
) The problem I see in the above DDL is that the subpartition doesn't take in to account the YYYY part. It just mentions the "MM" part and skips the YYYY part completely. So, I am not sure how Oracle will load the appropriate data in the right partition/sub-partition.
I appreciate any pointer you can provide for me to take it forward. Just a nudge would be enough too. I was thinking of creating 2 virtual cols - one for YYYY and another for MM. Will that work? How will I populate those columns automatically at INSERT time?
Deeply appreciate your service to the Oracle community.
If the data comes in as YYYYMM strings then you're on the same track I would try - using virtual columns to split this into years and months.
How will I populate those columns automatically at INSERT time?That's the beauty of virtual columns - you don't provide their value! The database infers them from the expression.
So I'd use substr to extract the components and make the columns integers (though making the YYYY column a real date could be useful/necessary too)
For example:
create table testtab (
prd_desc varchar2(6 byte),
yyyy integer as ( substr ( prd_desc, 1, 4 ) ),
mm integer as ( substr ( prd_desc, 5 ) )
)
partition by range (yyyy) interval (1)
subpartition by list(mm)
subpartition template (
subpartition SP_01 values (01),
subpartition SP_02 values (02),
subpartition SP_03 values (03),
subpartition SP_04 values (04),
subpartition SP_05 values (05),
subpartition SP_06 values (06),
subpartition SP_07 values (07),
subpartition SP_08 values (08),
subpartition SP_09 values (09),
subpartition SP_10 values (10),
subpartition SP_11 values (11),
subpartition SP_12 values (12)
) (
partition p2023 values less than ( 2024 )
);
insert into testtab ( prd_desc )
with rws as (
select add_months ( date'2023-01-01', level * 5 ) dt
from dual connect by level <= 4
)
select to_char ( dt, 'yyyymm' ) from rws;
select partition_name, high_value from user_tab_partitions;
/*
PARTITION_NAME HIGH_VALUE
P2023 2024
SYS_P1584 2025
*/
select partition_name, subpartition_name, high_value from user_tab_subpartitions;
/*
PARTITION_NAME SUBPARTITION_NAME HIGH_VALUE
P2023 P2023_SP_01 01
P2023 P2023_SP_02 02
P2023 P2023_SP_03 03
P2023 P2023_SP_04 04
P2023 P2023_SP_05 05
P2023 P2023_SP_06 06
P2023 P2023_SP_07 07
P2023 P2023_SP_08 08
P2023 P2023_SP_09 09
P2023 P2023_SP_10 10
P2023 P2023_SP_11 11
P2023 P2023_SP_12 12
SYS_P1584 SYS_SUBP1572 01
SYS_P1584 SYS_SUBP1573 02
SYS_P1584 SYS_SUBP1574 03
SYS_P1584 SYS_SUBP1575 04
SYS_P1584 SYS_SUBP1576 05
SYS_P1584 SYS_SUBP1577 06
SYS_P1584 SYS_SUBP1578 07
SYS_P1584 SYS_SUBP1579 08
SYS_P1584 SYS_SUBP1580 09
SYS_P1584 SYS_SUBP1581 10
SYS_P1584 SYS_SUBP1582 11
SYS_P1584 SYS_SUBP1583 12
*/