创建分区表
1.范围分区
create table student_range(
sno number(6) primary key,
sname varchar2(10),
sage int,
birthday date
)
partition by range(birthday)
(
partition p1 values less than
(to_date('1980-1-1','YYYY-MM-DD')) tablespace orcltbs1,
partition p2 values less than
(to_date('1990-1-1','YYYY-MM-DD')) tablespace orcltbs2,
partition p3 values less than(maxvalue)
tablespace orcltbs3 storage(initial 10M next 20M)
)
storage(initial 1M next 1M maxextents 10);
2.列表分区
create table student_list(
sno number(6) primary key,
sname varchar2(10),
sex char(2) check(sex in ('M','F'))
)
partition by list(sex)
(
partition student_male values('M') tablespace orcltbs1,
partition student_female values('F') tablespace orcltbs2
);
3.散列分区
create table student_hash(
sno number(6) primary key,
sname varchar2(10)
)
partition by hash(sno)
(
partition p1 tablespace orcltbs1,
partition p2 tablespace orcltbs2
);
或者按照分区的数量分区
create table stadent_hash1(
sno number(6) primary key,
sname varchar2(10)
)
partition by hash(sno)
partitions 2 store in (orcltbs1,orcltbs2);
~
4.复合分区
范围-列表复合分区
create table student_range_list(
sno number(6) primary key,
sname varchar2(10),
sex char(2) check(sex IN ('M','F')),
sage number(4),
birthday date
)
partition by range(birthday)
subpartition by list(sex)
(
partition p1 values less than(to_date('1980-1-1','YYYY-MM-DD'))
(
subpartition p1_sub1 values('M') tablespace orcltbs1,
subpartition p1_sub2 values('F') tablespace orcltbs2
),
partition p2 values less than(to_date('1990-1-1','YYYY-MM-DD'))
(
subpartition p2_sub1 values('M') tablespace orcltbs3,
subpartition p2_sub2 values('F') tablespace orcltbs4
),
partition p3 values less than(maxvlue) tablespace orcltbs5
);
范围-散列复合分区 create table student_range_hash(
sno number(10) primary key,
sname varchar2(6),
sage number(4),
birthday date
)
partition by range(birthday)
subpartition by hash(sno)
(
partition p1 values less than(to_date('1980-1-1','YYYY-MM-DD'))
(
subpartition p1_sub1 tablespace orcltbs1,
subpartition p1_sub2 tablespace orcltbs2
),
partition p2 values less than(to_date('1990-1-1','YYYY-MM-DD'))
(
subpartition p2_sub1 tablespace orcltbs3,
subpartition p2_sub2 tablespace orcltbs4
),
partition p3 values less than(maxvalue) tablespace orcltbs5
);
维护分区表
创建分区索引
查询分区表和分区索引信息