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

We're taking a break this week as Connor and Chris will both be at CloudWorld, the premier Oracle conference of 2024. If you're in Vegas, please come say Hi or pop into our sessions

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

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)

We're not taking comments currently, so please try again later if you want to add a comment.

### 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?
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?
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. ;)
June 13, 2024 - 12:59 pm UTC

You're welcome

# More to Explore

##### Design

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