I am trying to build subpartitioned table with CTAS from non-partitioned table but I got errors. How can I do that.Here is similiar example for my data ID IS PRIMARY KEY:

  TASK
ID START_DATE
1  22/09/21
2  21/09/21
3  20/09/21

And Here is my code :

CREATE TABLE EMPLOYEE AS 
(SELECT * FROM TASK)
PARTITION BY RANGE ("START_DATE")
SUBPARTITION BY LIST("ID")
PARTITION P1 VALUES LESS THAN TO_DATE('20210920','YYYYYMMDD') (
SUBPARTITION S1 VALUES (1),
SUBPARTITION S2 VALUES (2),
SUBPARTITION S3 VALUES (3)
),
PARTITION P2 VALUES LESS THAN TO_DATE('20210921','YYYYYMMDD') (
SUBPARTITION S4 VALUES (1),
SUBPARTITION S5 VALUES (2),
SUBPARTITION S6 VALUES (3)
),
PARTITION P3 VALUES LESS THAN TO_DATE('20210922','YYYYYMMDD') (
SUBPARTITION S7 VALUES (1),
SUBPARTITION S8 VALUES (2),
SUBPARTITION S9 VALUES (3)
),
PARTITION P4 VALUES LESS THAN MAXVALUE (
SUBPARTITION S10 VALUES (1),
SUBPARTITION S11 VALUES (2),
SUBPARTITION S12 VALUES (3)
)

How can I do that it is important for me I am getting this error and I think I will get another errors when I correct it:

00933. 00000 -  "SQL command not properly ended"

CodePudding user response：

An example

SQL> create table task ( id number , start_date date ) ;

Table created.

SQL> CREATE TABLE t2
  2  PARTITION BY RANGE (START_DATE)
  3  SUBPARTITION BY LIST (ID)
  4  (
  5  PARTITION P1 VALUES LESS THAN (TO_DATE('2021-07-20','YYYY-MM-DD'))
  6  (
  7  SUBPARTITION S1 VALUES (1),
  8  SUBPARTITION S2 VALUES (2),
  9  SUBPARTITION S3 VALUES (3)
 10  ),
 11  PARTITION P2 VALUES LESS THAN (TO_DATE('2021-08-20','YYYY-MM-DD'))
 12  (
 13  SUBPARTITION S4 VALUES (1),
 14  SUBPARTITION S5 VALUES (2),
 15  SUBPARTITION S6 VALUES (3)
 16  )
 17  )
 18* as select * from task
SQL> /

Table created.

Considerations

• Use the expression as select * from xxx at the end of the construction.
• Keep in mind the parenthesis for each partition and subpartition construction.
• Although I did not put it on my example, try to use a subpartition template.

CodePudding user response：

I would recommend an INTERVAL partition and subpartition template. Would be this one:

CREATE TABLE EMPLOYEE (
  ...
)
PARTITION BY RANGE (START_DATE) INTERVAL (INTERVAL '1' DAY)
SUBPARTITION BY LIST (ID)
SUBPARTITION TEMPLATE (
    SUBPARTITION S1 VALUES (1),
    SUBPARTITION S2 VALUES (2),
    SUBPARTITION S3 VALUES (3)
)
( PARTITION P_INITIAL VALUES LESS THAN (TIMESTAMP '2021-09-01 00:00:00') SEGMENT CREATION DEFERRED );