Author Topic: Subpartitions - composite with list  (Read 3614 times)

ranadeepb

  • Newbie
  • *
  • Posts: 1
    • View Profile
Subpartitions - composite with list
« on: November 30, 2009, 01:28:41 PM »
Hi,

Has anyone tried to create composite partition scheme as list-???. I tried all 3: list-hash, list-range and list-list and was not able to make any work. Code is from this site -

CREATE TABLE composite_list_hash (
cust_id   NUMBER(10),
cust_name   VARCHAR2(25),
cust_state  VARCHAR2(2),
time_id   DATE)
PARTITION BY LIST(cust_state)
SUBPARTITION BY HASH (cust_id)
SUBPARTITION TEMPLATE(
SUBPARTITION sp1 TABLESPACE CSG_D,
SUBPARTITION sp2 TABLESPACE CSG_D,
SUBPARTITION sp3 TABLESPACE CSG_D,
SUBPARTITION sp4 TABLESPACE CSG_D) (
PARTITION west VALUES ('OR'),
PARTITION east VALUES ('NY'),
PARTITION cent VALUES ('IL'));

CREATE TABLE composite_list_rng (
cust_id   NUMBER(10),
cust_name   VARCHAR2(25),
cust_state  VARCHAR2(2),
time_id   DATE)
PARTITION BY LIST(cust_state)
SUBPARTITION BY RANGE (cust_id)
SUBPARTITION TEMPLATE(
SUBPARTITION original VALUES LESS THAN (1001) TABLESPACE part1,
SUBPARTITION acquired VALUES LESS THAN (8001) TABLESPACE part2,
SUBPARTITION recent VALUES LESS THAN (MAXVALUE) TABLESPACE part3) (
PARTITION west VALUES ('OR', 'WA'),
PARTITION east VALUES ('NY', 'CT'),
PARTITION cent VALUES ('IL', 'MN'));

CREATE TABLE composite_list_list (
cust_id   NUMBER(10),
cust_name   VARCHAR2(25),
cust_state  VARCHAR2(2),
time_id   DATE)
PARTITION BY LIST(cust_state)
SUBPARTITION BY LIST (cust_id)
SUBPARTITION TEMPLATE(
SUBPARTITION beg VALUES (1,3,5) TABLESPACE part1,
SUBPARTITION mid VALUES (2,4,6) TABLESPACE part2,
SUBPARTITION end VALUES (7,8,9,0) TABLESPACE part3) (
PARTITION west VALUES ('OR', 'WA'),
PARTITION east VALUES ('NY', 'CT'),
PARTITION cent VALUES ('IL', 'MN'));

The syntax looks ok to me.

Any help will be much appreciated.

Thanks
Ranadeep