分区表和分区索引

创建分区表

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
);

维护分区表
创建分区索引
查询分区表和分区索引信息