一:表分区练习
表结构:
bookInfo:编号,书名,作者,单价,库存量
1.创建以上结构的图书信息表,并按单价的范围分区,小于100一区,小于200一区,小于300一区,并指定每区的表空间,插入数据,并查看每个分区中的数据信息
create table bookInfo(
bookInfoID number primary key,
bookInfoName varchar2(32),
bookInfoAuthor varchar2(32),
bookInfoPrice number,
bookInfoNumber number
)
partition by range(bookInfoPrice)(
partition p1 values less than(100),
partition p2 values less than(200),
partition p3 values less than(300)
)
/
2.在现有的分区基础上增加一个分区
alter table bookInfo add partition p4 values less than(400);
3.将第二个分区删除
alter table bookInfo drop partition p2;
4.将第三个分区中的数据删除
alter table bookInfo truncate partition p3;
5.将第一个和第四个分区合并
//再写之前先把 p3区删除
//两个不想邻的区不能合并
//删除p3区: alter table bookInfo drop partition p3;
alter table bookInfo merge partitions p1, p4 into partition p5;
6.将上一题中合并的分区拆分成两个分区
alter table bookInfo split partition p5 at(100) into (partition p1,partition p2);
7.创建以上结构的图书信息表,并按作者进行列表分区,分成三区,插入数据,并查看每个分区中的数据信息
create table bookInfo2(
bookInfoID number primary key,
bookInfoName varchar2(32),
bookInfoAuthor varchar2(32),
bookInfoPrice number,
bookInfoNumber number
)
partition by list(bookInfoAuthor)(
partition p1 values('吴承恩','施耐庵'),
partition p2 values('罗贯中','曹雪芹'),
partition p3 values(default)
)
/
insert into bookInfo2 values(1,'西游记','吴承恩',28,35);
insert into bookInfo2 values(2,'水浒传','施耐庵',120,32);
insert into bookInfo2 values(3,'三国演义','罗贯中',280,55);
insert into bookInfo2 values(4,'红楼梦','曹雪芹',270,75);
select * from bookInfo2 partition(p1);
select * from bookInfo2 partition(p2);
select * from bookInfo2 partition(p3);
8.创建以上结构的图书信息表,并按书名进行散列分区,分成三区(用两种方式完成:指定分区名和不指定分区名),插入数据,并查看每个分区中的数据信息
create table bookInfo3(
bookInfoID number primary key,
bookInfoName varchar2(32),
bookInfoAuthor varchar2(32),
bookInfoPrice number,
bookInfoNumber number
)
partition by hash(bookInfoNumber)
partitions 4;
insert into bookInfo3 values(1,'西游记','吴承恩',28,35);
insert into bookInfo3 values(2,'水浒传','施耐庵',120,32);
insert into bookInfo3 values(3,'三国演义','罗贯中',280,55);
insert into bookInfo3 values(4,'红楼梦','曹雪芹',270,75);
select * from bookInfo3;
select * from bookInfo3 partition;
9.创建以上结构的图书信息表,并进行复合分区,先按范围分区,再按散列分区,分成三区,插入数据,并查看每个分区中的数据信息
create table bookInfo4(
bookInfoID number primary key,
bookInfoName varchar2(32),
bookInfoAuthor varchar2(32),
bookInfoPrice number,
bookInfoNumber number
)
partition by range(bookInfoPrice)
subpartition by hash(bookInfoNumber)(
partition p1 values less than(100),
partition p2 values less than(200),
partition p3 values less than(maxvalue)
)
/
insert into bookInfo4 values(1,'西游记','吴承恩',28,35);
insert into bookInfo4 values(2,'水浒传','施耐庵',120,32);
insert into bookInfo4 values(3,'三国演义','罗贯中',280,55);
insert into bookInfo4 values(4,'红楼梦','曹雪芹',270,75);
select * from bookInfo4 partition(p1);
select * from bookInfo4 partition(p2);
select * from bookInfo4 partition(p3);