Dear Oracle Team,
I have a table which has 2 important columns one for the year and another with date. Since the volume of the table is very high, we are planning to partition the table based on the year column which is a number(4) type and sub-partition by month using the Date field.
I understood from Oracle Docs as well as after browsing this space for more time that we can't create automatic partitions at sub-partition level.
I saw some of solutions provided but could not find a solution matching for my case.
Could you please help me out?
My table structure is given below
CREATE TABLE t_partition (
p_id NUMBER,
p_year NUMBER(4),-- eg 2018
p_signed_day DATE,
p_signed_user VARCHAR2(10)
)
Here I need the main partition based on p_year to be automatic for each year and then create 12 sub partitions(one for each month) per each year using the p_signed_day field.
I thought through using a sub partition template, but couldn't get it going to take the month information from the Date field while setting sub-partition clause.
How can I achieve this?
Thanks
K R
Probably the easiest is to:
- Define a virtual column extracting the month from the date
- Subpartition on this
- Create a subpartition template listing out the month values
Then when new top-level partitions are added, you'll get a subpartition/month for them:
create table t (
ins_date date,
ins_year int as ( extract ( year from ins_date ) ),
ins_month int as ( extract ( month from ins_date ) )
) partition by range ( ins_year )
interval ( 1 )
subpartition by list ( ins_month )
subpartition template (
subpartition sp_jan values ( 1 ),
subpartition sp_feb values ( 2 ), --etc.
subpartition sp_others values ( default )
) (
partition p2019 values less than ( 2020 )
);
insert into t ( ins_date ) values ( date'2020-01-01' );
select * from t;
INS_DATE INS_YEAR INS_MONTH
01-JAN-2020 00:00:00 2020 1
select partition_name, subpartition_name
from user_tab_subpartitions
where table_name = 'T';
PARTITION_NAME SUBPARTITION_NAME
P2019 P2019_SP_JAN
P2019 P2019_SP_FEB
P2019 P2019_SP_OTHERS
SYS_P8460 SYS_SUBP8458
SYS_P8460 SYS_SUBP8459
SYS_P8460 SYS_SUBP8457 Though I have to ask: is p_year derived from p_signed_day? If so, you'd be better off making this a virtual column too (as in my example).