1. 表分区概念
分区表是将大表的数据分成称为分区的许多小的子集。倘若硬盘丢失了分区表，数据就无法按顺序读取和写入，导致无法操作。
2. 表分区分类
(1)范围分区
create table table_name(
id number(11) primary key,
name varchar2(255),
account varchar2(255)
)
partition by range(column id, ...)
(
partition partition_name values less than ('10000') tablespace tablespace_name ,
partition partition_name values less than (maxvalue) tablespace tablespace_name
)
(2)散列分区
- 形势一
create table table_name(
id number(11) primary key,
name varchar2(255),
account varchar2(255)
)
partition by hash(column id, ...)
(
partition partition_name tablespace tablespace_name ,
partition partition_name tablespace tablespace_name
)
- 形式二
create table table_name(
id number(11) primary key,
name varchar2(255),
account varchar2(255)
)
partition by hash (column id, ...) partitions2
store in (tablespace_name , tablespace_name );
(3)列表分区
create table table_name(
id number(11) primary key,
name varchar2(255),
account varchar2(255),
sex varchar2(1)
)
partition by list(column sex, ...)
(
partition partition_name values('0') tablespace tablespace_name,
partition partition_name values('1') tablespace tablespace_name
)
(4)复合分区
- 范围 + 散列
create table table_name(
id number(11) primary key,
name varchar2(255),
account varchar2(255),
sex varchar2(1)
)
partition by range(id) subpartition by hash(sex)
(
partition partition_name values less than (10000) tablespace tablespace_name
(
subpartition partition_name tablespace tablespace_name,
subpartition partition_name tablespace tablespace_name
),
partition p6 values less than (maxvalue) tablespace tablespace_name
(
subpartition partition_name tablespace tablespace_name ,
subpartition partition_name tablespace tablespace_name
)
)
- 范围 + 列表
类似 “ 范围 + 散列 ”
3. 'ora-14037' 问题： 是因为分区临界值问题