Thanks for the question, suman.

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

Last updated: September 11, 2017 - 1:26 am 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

(1 rating)

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>

More to Explore

Design

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

Breadcrumb

Question and Answer

You Asked

and Connor said...

Rating

Comments

Couple of things

More to Explore

Design

Creation of Composite sub-partitions on multiple columns using template

Breadcrumb

Question and Answer

You Asked

and Connor said...

Rating

Comments

Couple of things

More to Explore

Design