Skip to Main Content
  • Questions

  • Can I user automatic List in subpartitions?

Breadcrumb

Question and Answer

Connor McDonald

Thanks for the question, Osama.

Asked: May 23, 2018 - 1:07 pm UTC

Last updated: February 03, 2022 - 3:15 am UTC

Version: 12.2.0.1.0 - 64bit

Viewed 10K+ times! This question is

You Asked

Dears,

I have a table that contains two columns one for year and the other for month. I need to partition this table based on year and month, where year represent the partitions and under that the month represent that sub-partitions. What I need is to make automatic list partitioning by year and then automatic sub-partitioning by month under each year so that each year partition will have 12 months sub-partitions. can this be done using the automatic list for both partitions and sub-partitions??


drop table t10 purge;
CREATE TABLE t10 (
  id           NUMBER,
  description  VARCHAR2(50),
  year number,
  month number,
  created_date DATE
  );




alter table t10 modify 
partition by list  (year) automatic
subpartition by list (month) automatic 
(partition Y_2015 values(2015))
(subpartition M_01 values (01))
online;


I got this this error msg
ERROR at line 1:
ORA-14179: An unsupported partitioning method was specified in this context.

what is the perfect solution for this situation?
Thanks in advance
Osama


and Chris said...

No. Automatic list partitioning is unsupported at the subpartition level.

But I don't see the need for this here. You know there are 12 months in a year!

So you can create a subpartition template, listing out the partitions for each month:

CREATE TABLE t10 (
  id           NUMBER,
  description  VARCHAR2(50),
  year number,
  month number,
  created_date DATE
) partition by list (year) automatic
  subpartition by list (month)  
  subpartition template (
    subpartition M_01 values (01),
    subpartition M_02 values (02),
    subpartition M_03 values (03),
    subpartition M_04 values (04),
    subpartition M_05 values (05),
    subpartition M_06 values (06),
    subpartition M_07 values (07),
    subpartition M_08 values (08),
    subpartition M_09 values (09),
    subpartition M_10 values (10),
    subpartition M_11 values (11),
    subpartition M_12 values (12) 
) (
  partition Y_2015 values(2015)
);


Then when you insert a row that will create a new top-level partition, you'll get all the months too:

insert into t10 values (1, 'test', 2016, 3, sysdate);

select partition_name, subpartition_name, high_value 
from   user_tab_subpartitions
where  table_name = 'T10';

PARTITION_NAME   SUBPARTITION_NAME   HIGH_VALUE   
Y_2015           Y_2015_M_01         01           
Y_2015           Y_2015_M_02         02           
Y_2015           Y_2015_M_03         03           
Y_2015           Y_2015_M_04         04           
Y_2015           Y_2015_M_05         05           
Y_2015           Y_2015_M_06         06           
Y_2015           Y_2015_M_07         07           
Y_2015           Y_2015_M_08         08           
Y_2015           Y_2015_M_09         09           
Y_2015           Y_2015_M_10         10           
Y_2015           Y_2015_M_11         11           
Y_2015           Y_2015_M_12         12           
SYS_P2191        SYS_SUBP2179        01           
SYS_P2191        SYS_SUBP2180        02           
SYS_P2191        SYS_SUBP2181        03           
SYS_P2191        SYS_SUBP2182        04           
SYS_P2191        SYS_SUBP2183        05           
SYS_P2191        SYS_SUBP2184        06           
SYS_P2191        SYS_SUBP2185        07           
SYS_P2191        SYS_SUBP2186        08           
SYS_P2191        SYS_SUBP2187        09           
SYS_P2191        SYS_SUBP2188        10           
SYS_P2191        SYS_SUBP2189        11           
SYS_P2191        SYS_SUBP2190        12

Rating

  (3 ratings)

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

Comments

Thanks so much for your prompt reply

Osama Ammar, May 23, 2018 - 1:54 pm UTC

The answer is more than enough.. thank you..

Another issue, is there is an automatic way of renaming the partitions and sub-partitions once they have created to have a meaningful names????

Thanks again :)
Connor McDonald
May 24, 2018 - 2:26 am UTC

No, but I've done routines in the past to parse the "HIGH_VALUE" column to come up with a meaningful renaming strategy.

Here's a video showing an example of parsing long columns


Instead of month I have country

Rejishal T, February 05, 2021 - 3:58 pm UTC

In this case since sub-partition is based o month, we know there can be only 12.
I have the scenario where I have country, and I don't know which country is going to be selected for a product by a customer. And I dont want to add all the country list as sub-partitions.

Anyway? (I saw you already mentioned No. But still asking as the post is almost 3 years old)
Chris Saxon
February 08, 2021 - 2:01 pm UTC

Automatic list subpartitioning is still unsupported.

I'm unclear what you're trying to do here though - are you subpartitioning by month or by country?

Paul, February 02, 2022 - 1:19 pm UTC

You created a YouTube video on how to create a PLSQL function to read a long and RETURN a VARCHAR2. Is that code available in a blog so I can play with it
Connor McDonald
February 03, 2022 - 3:15 am UTC

https://connor-mcdonald.com/2021/04/06/unlong-my-dictionary-those-pesky-long-columns/

https://connor-mcdonald.com/2019/07/08/high_value-and-other-long-columns-revisited/

should help, and also check the comments sections in those - there's some other methods as well
more

Connor and Chris don't just spend all day on AskTOM. You can also catch regular content via Connor's blog and Chris's blog. Or if video is more your thing, check out Connor's latest video and Chris's latest video from their Youtube channels. And of course, keep up to date with AskTOM via the official twitter account.

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.