Partition by list...subpartition by list 1

[djbjr]

Can I do this?

create table test_table (current_flag varchar2(1), status_group varchar2(20))
PARTITION BY LIST (CURRENT_FLAG)
SUBPARTITION BY LIST (STATUS_GROUP)
subpartition template
( subpartition ACTIVE values ('ACTIVE'),
subpartition DCEASED values ('DECEASED'),
subpartition LTD values('LTD')
subpartition RETIRED values('RETIRED')
subpartition SEPARATION values('SEPARATION')
subpartition TERMINATED values( DEFAULT))
(
PARTITION ACTIVE VALUES ('Y'),
PARTITION NOT_ACTIVE VALUES (DEFAULT))


I keep getting the error

ERROR at line 3:
ORA-00922: missing or invalid option


Can I not partition a table this way?

HELP ME DAAAAAVE!

 

[djbjr]

.....and its not because I forgot the columns on the subpartition. I just notice and fixed that and it still doesnt work.

 

[Dagon]

The Oracle 9i manual implies subpartitioning is only valid for range partitioning:

Composite Partitioning

Composite partitioning partitions data using the range method, and within each partition, subpartitions it using the hash or list method. Composite range-hash partitioning provides the improved manageability of range partitioning and the data placement, striping, and parallelism advantages of hash partitioning. Composite range-list partitioning provides the manageability of range partitioning and the explicit control of list partitioning for the subpartitions.

Composite partitioning supports historical operations, such as adding new range partitions, but also provides higher degrees of parallelism for DML operations and finer granularity of data placement through subpartitioning.

 

[djbjr]

Thanks I found that same info yesterday afternoon.

ho hum.