Wednesday, November 10, 2010

Oracle 10G Composite RANGE-LIST partitioning

Composite RANGE-LIST partitioned table maintenace.

1) Create

create
table mytable
(
PROC varchar2(30 byte),
ACTION varchar2(100 byte),
START_TIME date,
END_TIME date,
)
tablespace USERS enable row movement
partition by range (start_time)
subpartition by list (action)
subpartition template
(
subpartition added values ('ADD') tablespace users ,
subpartition removed values ('REMOVE') tablespace users
)
(
partition nov10 values less than (to_date('01/12/10','DD/MM/YYYY')) tablespace users,
partition dec10 values less than (to_date('01/01/11','DD/MM/YYYY')) tablespace users,
partition jan11 values less than (to_date('01/02/11','DD/MM/YYYY')) tablespace users
);

create index my_idx on mytable (ACTION) local;

All partitions are stored in the same tablespace and a local index is created too.

No comments:

Post a Comment