Skip to Main Content
• Questions

# Breadcrumb

## Question and Answer

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?
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.