Skip to Main Content

Breadcrumb

May 4th

Question and Answer

Chris Saxon

Thanks for the question.

Asked: August 14, 2019 - 1:21 pm UTC

Last updated: August 14, 2019 - 1:21 pm UTC

Version:

Viewed 10K+ times! This question is

You Asked

Hi,

I have a requirement where I need to do interval subpartition over a table. but there is an oracle limitation on interval subpartition. how can I achieve this? below is the example.

Create table CARS (auto_make varchar(30),
Auto_model varchar(30),
Purchase_date date)
PARTITION BY LIST (auto_make)
SUBPARTITION BY RANGE (Purchase_Date) interval (NUMTOYMINTERVAL(1,'month'))
SUBPARTITION TEMPLATE
(subpartition sp1 values less than
TO_DATE('15-03-2015','dd-mm-yyyy'))
(partition p_vw values ('JETTA'));

Error report -
SQL Error: ORA-14179: An unsupported partitioning method was specified in this context.
14179. 00000 - "An unsupported partitioning method was specified in this context."
*Cause: An attempt was made to specify an unsupported partitioning method in this context.
*Action: Remove the invalid option.

Thanks in advance.

and Chris said...

Sorry, as the error indicates, interval subpartitioning isn't implemented yet!

Can you flip the partitions around, so you have interval partitions and list subpartitions?

create table cars (auto_make  varchar(30),
                  auto_model varchar(30),
                  purchase_date date)
partition by range (purchase_date)
interval (numtoyminterval(1,'month'))
subpartition by list (auto_make)
subpartition template (
  subpartition p_vw values ('JETTA')
) (
  partition sp1 values less than
    ( to_date('15-03-2015','dd-mm-yyyy') )
);


Is this answer out of date? If it is, please let us know via a Comment

More to Explore

VLDB

If you are new to partitioning, check out Connor McDonald's introduction series here.

VLDB

Documentation set on VLDB and Partitioning.