Show Posts

This section allows you to view all posts made by this member. Note that you can only see posts made in areas you currently have access to.


Topics - ranadeepb

Pages: [1]
1
PSOUG / 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

Pages: [1]