IOT支持分区,包括范围分区,散列分区和列表分区,但不支持组合分区,以下是测试脚本:
范围分区:
CREATE TABLE iot_range_example
( range_key_column date ,
object_id number,
data varchar2(20),
constraint iot_range_pk primary key (range_key_column, object_id)
)
organization index
PARTITION BY RANGE (range_key_column)
( PARTITION part_1 VALUES LESS THAN
(to_date('01/01/2005','dd/mm/yyyy')),
PARTITION part_2 VALUES LESS THAN
(to_date('01/01/2006','dd/mm/yyyy')),
PARTITION part_3 VALUES LESS THAN
(MAXVALUE)
)
/
drop table iot_range_example;
散列分区:
CREATE TABLE iot_hash_example
( hash_key_column date,
object_id number,
data varchar2(20),
constraint iot_hash_pk primary key (hash_key_column, object_id)
)
organization index
PARTITION BY HASH (hash_key_column)
( partition part_1,
partition part_2
)
/
drop table iot_hash_example;
列表分区:
create table iot_list_example
( state_cd varchar2(2),
data varchar2(20),
constraint iot_list_pk primary key (state_cd)
)
organization index
partition by list(state_cd)
( partition part_1 values ( 'ME', 'NH', 'VT', 'MA' ),
partition part_2 values ( 'CT', 'RI', 'NY' )
)
/
drop table iot_list_example;
尝试一下组合分区:
CREATE TABLE composite_example
( range_key_column date,
hash_key_column int,
data varchar2(20),
constraint iot_comp_pk primary key (range_key_column, hash_key_column)
)
organization index
PARTITION BY RANGE (range_key_column)
subpartition by hash(hash_key_column) subpartitions 2
(
PARTITION part_1
VALUES LESS THAN(to_date('01/01/2005','dd/mm/yyyy'))
(subpartition part_1_sub_1,
subpartition part_1_sub_2
),
PARTITION part_2
VALUES LESS THAN(to_date('01/01/2006','dd/mm/yyyy'))
(subpartition part_2_sub_1,
subpartition part_2_sub_2
)
)
/
会提示以下错误:
ORA-25198: 仅支持对索引表进行范围, 列表和散列分区。
参考文献:
《Oracle Database Concepts 10g Release 2》
《Oracle 9i&10g编程艺术》