Skip to Main Content
  • Questions

  • Creation of Composite sub-partitions on multiple columns using template

Breadcrumb

Question and Answer

Chris Saxon

Thanks for the question, suman.

Asked: September 08, 2017 - 3:16 pm UTC

Last updated: June 13, 2024 - 12:59 pm UTC

Version: 11g

Viewed 10K+ times! This question is

You Asked

Hi,

Can we create Composite sub-partitions on multiple columns using template

Example

CREATE TABLE sgs.part_test
(
order_date_time SYSTIMESTAMP,
modulo_store_id NUMBER,
recurring_flag CHAR (1)
)
PARTITION BY RANGE (order_date_time)
INTERVAL ( NUMTOYMINTERVAL (1, 'MONTH') )
SUBPARTITION BY LIST (modulo_store_id, recurring_flag)
SUBPARTITION TEMPLATE (
SUBPARTITION recur_0y VALUES ((0,'Y'),(0,'y')),
SUBPARTITION recur_0n VALUES ((0,'N'),(0,'n')),
SUBPARTITION recur_1y VALUES ((1,'Y'),(1,'y')),
SUBPARTITION recur_1n VALUES ((1,'N'),(1,'n')),
SUBPARTITION recur_2y VALUES ((2,'Y'),(2,'y')),
SUBPARTITION recur_2n VALUES ((2,'N'),(2,'n')),
SUBPARTITION recur_3y VALUES ((3,'Y'),(3,'y')),
SUBPARTITION recur_3n VALUES ((3,'N'),(3,'n')),
SUBPARTITION recur_def VALUES (DEFAULT)
(
PARTITION pstart VALUES LESS THAN (TO_DATE ('2017-08-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN'))
NOLOGGING
NOCOMPRESS
TABLESPACE sgs_trx_data)
ENABLE ROW MOVEMENT
TABLESPACE sgs_trx_data
NOLOGGING
NOCOMPRESS
NOCACHE
PARALLEL
MONITORING;

But this part is giving error. Can it be possible?

and Connor said...

Yes...its just a syntax error

SQL> CREATE TABLE part_test
  2  (
  3  order_date_time TIMESTAMP,
  4  modulo_store_id NUMBER,
  5  recurring_flag CHAR (1)
  6  )
  7  PARTITION BY RANGE (order_date_time)
  8  INTERVAL ( NUMTOYMINTERVAL (1, 'MONTH') )
  9  SUBPARTITION BY LIST (modulo_store_id, recurring_flag)
 10  SUBPARTITION TEMPLATE (
 11  SUBPARTITION recur_0y VALUES ((0,'Y'),(0,'y')),
 12  SUBPARTITION recur_0n VALUES ((0,'N'),(0,'n')),
 13  SUBPARTITION recur_1y VALUES ((1,'Y'),(1,'y')),
 14  SUBPARTITION recur_1n VALUES ((1,'N'),(1,'n')),
 15  SUBPARTITION recur_2y VALUES ((2,'Y'),(2,'y')),
 16  SUBPARTITION recur_2n VALUES ((2,'N'),(2,'n')),
 17  SUBPARTITION recur_3y VALUES ((3,'Y'),(3,'y')),
 18  SUBPARTITION recur_3n VALUES ((3,'N'),(3,'n')),
 19  SUBPARTITION recur_def VALUES (DEFAULT)
 20  )             <<<<========== the bracket you were missing
 21  (
 22  PARTITION pstart VALUES LESS THAN (TO_DATE ('2017-08-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN'))
 23  );

Table created.


Rating

  (5 ratings)

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

Comments

Couple of things

Rajeshwaran, Jeyabal, September 12, 2017 - 10:28 am UTC

1) This question came up from Oracle 11g database, so it should be executed from Oracle 11g and not on 12c database.

2) SYSTIMESTAMP is not a datatype, instead it should be either timestamp or date.

....
CREATE TABLE sgs.part_test 
( 
 order_date_time SYSTIMESTAMP,  <<======= 
 modulo_store_id NUMBER, 
 recurring_flag CHAR (1) 
....


With Oracle 11g database, list (sub)partition cannot be on multiple columns.

demo@ORA11G> CREATE TABLE part_test
  2  (
  3   order_date_time timestamp,
  4   modulo_store_id NUMBER,
  5   recurring_flag CHAR (1)
  6  )
  7  PARTITION BY RANGE (order_date_time)
  8   INTERVAL ( NUMTOYMINTERVAL (1, 'MONTH') )
  9   SUBPARTITION BY LIST (modulo_store_id, recurring_flag)
 10   SUBPARTITION TEMPLATE (
 11      SUBPARTITION recur_0y VALUES ((0,'Y'),(0,'y')),
 12      SUBPARTITION recur_0n VALUES ((0,'N'),(0,'n')),
 13      SUBPARTITION recur_1y VALUES ((1,'Y'),(1,'y')),
 14      SUBPARTITION recur_1n VALUES ((1,'N'),(1,'n')),
 15      SUBPARTITION recur_2y VALUES ((2,'Y'),(2,'y')),
 16      SUBPARTITION recur_2n VALUES ((2,'N'),(2,'n')),
 17      SUBPARTITION recur_3y VALUES ((3,'Y'),(3,'y')),
 18      SUBPARTITION recur_3n VALUES ((3,'N'),(3,'n')),
 19      SUBPARTITION recur_def VALUES (DEFAULT) )
 20   (
 21   PARTITION pstart VALUES LESS THAN (TO_DATE ('2017-08-01','yyyy-mm-dd')) );
 SUBPARTITION TEMPLATE (
 *
ERROR at line 10:
ORA-14304: List partitioning method expects a single partitioning column


demo@ORA11G>
demo@ORA11G>  create table t (
  2     empno int,
  3     deptno int,
  4     sal int)
  5   partition by list(deptno,sal)
  6   ( partition p1 values(10,1000) ) ;
 ( partition p1 values(10,1000) )
 *
ERROR at line 6:
ORA-14304: List partitioning method expects a single partitioning column


demo@ORA11G>


Starting with 12.2.0.1 we have a new feature called "Multi-column List Partitioning" - that lift this up.

http://docs.oracle.com/database/122/VLDBG/release-changes.htm#GUID-387B86B7-DBE7-440D-9BCA-E5469E7AE88B

when executed from Oracle 12c(12.2) - this error disappears.

demo@ORA12C> CREATE TABLE part_test
  2  (
  3   order_date_time timestamp,
  4   modulo_store_id NUMBER,
  5   recurring_flag CHAR (1)
  6  )
  7  PARTITION BY RANGE (order_date_time)
  8   INTERVAL ( NUMTOYMINTERVAL (1, 'MONTH') )
  9   SUBPARTITION BY LIST (modulo_store_id, recurring_flag)
 10   SUBPARTITION TEMPLATE (
 11      SUBPARTITION recur_0y VALUES ((0,'Y'),(0,'y')),
 12      SUBPARTITION recur_0n VALUES ((0,'N'),(0,'n')),
 13      SUBPARTITION recur_1y VALUES ((1,'Y'),(1,'y')),
 14      SUBPARTITION recur_1n VALUES ((1,'N'),(1,'n')),
 15      SUBPARTITION recur_2y VALUES ((2,'Y'),(2,'y')),
 16      SUBPARTITION recur_2n VALUES ((2,'N'),(2,'n')),
 17      SUBPARTITION recur_3y VALUES ((3,'Y'),(3,'y')),
 18      SUBPARTITION recur_3n VALUES ((3,'N'),(3,'n')),
 19      SUBPARTITION recur_def VALUES (DEFAULT) )
 20   (
 21   PARTITION pstart VALUES LESS THAN (TO_DATE ('2017-08-01','yyyy-mm-dd')) );

Table created.

demo@ORA12C>  create table t (
  2     empno int,
  3     deptno int,
  4     sal int)
  5   partition by list(deptno,sal)
  6   ( partition p1 values(10,1000) ) ;

Table created.

demo@ORA12C>

Code sample not working

A reader, June 11, 2024 - 11:08 am UTC

I just tried to execute your example in an Oracle 23ai and got the error "Missing left parenthesis". Is the subpartitioning on multiple columns not possible anymore?
Chris Saxon
June 11, 2024 - 1:13 pm UTC

Which example? This is still supported in Oracle Database 23ai; Connor's example still works:

select banner from v$version;
/*
BANNER
-------------------------------------------------------------------------------
Oracle Database 23ai Free Release 23.0.0.0.0 - Develop, Learn, and Run for Free
*/
CREATE TABLE part_test
(
order_date_time TIMESTAMP,
modulo_store_id NUMBER,
recurring_flag CHAR (1)
)
PARTITION BY RANGE (order_date_time)
INTERVAL ( NUMTOYMINTERVAL (1, 'MONTH') )
SUBPARTITION BY LIST (modulo_store_id, recurring_flag)
SUBPARTITION TEMPLATE (
SUBPARTITION recur_0y VALUES ((0,'Y'),(0,'y')),
SUBPARTITION recur_0n VALUES ((0,'N'),(0,'n')),
SUBPARTITION recur_1y VALUES ((1,'Y'),(1,'y')),
SUBPARTITION recur_1n VALUES ((1,'N'),(1,'n')),
SUBPARTITION recur_2y VALUES ((2,'Y'),(2,'y')),
SUBPARTITION recur_2n VALUES ((2,'N'),(2,'n')),
SUBPARTITION recur_3y VALUES ((3,'Y'),(3,'y')),
SUBPARTITION recur_3n VALUES ((3,'N'),(3,'n')),
SUBPARTITION recur_def VALUES (DEFAULT)
)
(
PARTITION pstart VALUES LESS THAN (TO_DATE ('2017-08-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN'))
);
--Table PART_TEST created.

select partition_name, subpartition_count 
from   user_tab_partitions
where  table_name = 'PART_TEST';
/*
PARTITION_ SUBPARTITION_COUNT
---------- ------------------
PSTART                      9
*/

Nevermind...

A reader, June 11, 2024 - 3:50 pm UTC

I just checked it again and my IDE was the Problem...

While we're here

A reader, June 11, 2024 - 4:03 pm UTC

But while I got you here, just to make sure: Is it still impossible to make the subpartitions automatic in this case? My use-case includes many possible combinations of two values and I would then have to manually create new subpartitions for these, correct?
Chris Saxon
June 12, 2024 - 9:56 am UTC

Correct, automatic list subpartitioning is unsupported as of Oracle Database 23ai.

You can create a VALUES ( DEFAULT ) partition as in the example above. This provides a safety net if you miss any of the subpartition key values. You can split them out into new subpartitions later if necessary.

Tank you

A reader, June 13, 2024 - 7:44 am UTC

Thanks for the quick response! Very unfortunate but oh well. ;)
Chris Saxon
June 13, 2024 - 12:59 pm UTC

You're welcome
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

Design

New to good database design? Check out Chris Saxon's full fundamentals class.