Oracle数据笔记-【2】表分区

-------------------------数据库----------------------

--创建表空间（数据库）
create tablespace Test
datafile 'c:\Test.dbf'
size 10m
autoextend on

--创建用户
create user mike
identified by mike123
default tablespace Test

--给用户授权
grant connect to mike;
grant resource to mike

--切换用户6
--创建学生信息表
create table stuinfo
(
 id number(3) primary key,
 name varchar2(20) not null,
 sex varchar2(2) check(sex='男'or sex='女') not null,
 age number(2) check(age>0),
 address varchar2(20) default('湖北武汉'),
 birthday date
);

insert into stuinfo2 values(1,'eric','男',18,default,to_date('1995-10-11','yyyy-mm-dd'));
insert into stuinfo2 values(2,'lily','男',28,default,to_date('1985-10-11','yyyy-mm-dd'));
insert into stuinfo2 values(3,'chunchun','女',58,default,to_date('1965-10-11','yyyy-mm-dd'));
insert into stuinfo2 values(4,'eric888','男',null,default,to_date('1995-10-11','yyyy-mm-dd'));
insert into stuinfo2 values(5,'葬爱K','女',16,default,to_date('2000-9-11','yyyy-mm-dd'));
insert into stuinfo2 values(6,'葬爱Q','男',null,default,to_date('1999-8-11','yyyy-mm-dd'));
insert into stuinfo2 values(7,'葬爱S','男',null,default,to_date('1993-1-11','yyyy-mm-dd'));
select * from stuinfo

-------------------------分区表----------------------

--1、什么是分区表？

--对于超大规模的数据库进行分区数据存储，达到优化数据库的目的

--2、分区表的分类

--1）范围分区

--适用：明确的可以划分范围的，如：月份、编号

create table stuinfo2
(
       id number(3) primary key,
       name varchar2(20) not null,
       sex varchar2(2) check(sex='男'or sex='女') not null,
       age number(2) check(age>0),
       address varchar2(20) default('湖北武汉'),
       birthday date
)
partition by range(id)
(
       partition p1 values less than(3),
       partition p2 values less than(5)    
);

--查询分区数据

select * from stuinfo1 partition(p3);

--2）列表分区

create table stuinfo3
(
      id number(3) primary key,
       name varchar2(20) not null,
       sex varchar2(2) check(sex='男'or sex='女') not null,
       age number(2) check(age>0),
       address varchar2(20) default('武汉'),
       birthday date  
)
partition by list(address)
(
      partition p1 values('北京','上海','深圳'),
      partition p2 values('武汉','郑州','长沙'),
      partition p3 values(default)
);
insert into stuinfo3 values(1,'eric','男',18,default,to_date('1995-10-11','yyyy-mm-dd'));
insert into stuinfo3 values(2,'lily','男',28,default,to_date('1985-10-11','yyyy-mm-dd'));
insert into stuinfo3 values(3,'chunchun','女',58,'北京',to_date('1965-10-11','yyyy-mm-dd'));
insert into stuinfo3 values(4,'eric888','男',null,'深圳',to_date('1995-10-11','yyyy-mm-dd'));
insert into stuinfo3 values(5,'葬爱K','女',16,'西藏',to_date('2000-9-11','yyyy-mm-dd'));
insert into stuinfo3 values(6,'葬爱Q','男',null,'台湾',to_date('1999-8-11','yyyy-mm-dd'));
insert into stuinfo3 values(7,'葬爱S','男',null,'长沙',to_date('1993-1-11','yyyy-mm-dd'));
insert into stuinfo3 values(8,'葬爱A','男',null,'合肥',to_date('1993-1-11','yyyy-mm-dd'));
select * from stuinfo3 partition(p3)

--3）散列分区

create table stuinfo5
(
      id number(3) primary key,
       name varchar2(20) not null,
       sex varchar2(2) check(sex='男'or sex='女') not null,
       age number(2) check(age>0),
       address varchar2(20) default('武汉'),
       birthday date  
)
partition by hash(id) --指定散列分却
partitions 4  --分区数量

insert into stuinfo5 values(1,'eric','男',18,default,to_date('1995-10-11','yyyy-mm-dd'));
insert into stuinfo5 values(2,'lily','男',28,default,to_date('1985-10-11','yyyy-mm-dd'));
insert into stuinfo5 values(3,'chunchun','女',58,'北京',to_date('1965-10-11','yyyy-mm-dd'));
insert into stuinfo5 values(4,'eric888','男',null,'深圳',to_date('1995-10-11','yyyy-mm-dd'));
insert into stuinfo5 values(5,'葬爱K','女',16,'西藏',to_date('2000-9-11','yyyy-mm-dd'));
insert into stuinfo5 values(6,'葬爱Q','男',null,'台湾',to_date('1999-8-11','yyyy-mm-dd'));
insert into stuinfo5 values(7,'葬爱S','男',null,'长沙',to_date('1993-1-11','yyyy-mm-dd'));
insert into stuinfo5 values(8,'葬爱A','男',null,'合肥',to_date('1993-1-11','yyyy-mm-dd'));

select * from stuinfo5 partition(sys_p24)
select * from stuinfo5 partition(sys_p25)
select * from stuinfo5 partition(sys_p26)
select * from stuinfo5 partition(sys_p27)

--查询分区信息
select * from user_tab_partitions

--4）复合分区

--复合 = 范围+列表 / 范围+散列

create table stuinfo6
(
       id number(3) primary key,
       name varchar2(20) not null,
       sex varchar2(2) check(sex='男'or sex='女') not null,
       age number(2) check(age>0),
       address varchar2(20) default('武汉'),
       birthday date
)
partition by range(id)
subpartition by list(address)
(
             partition p1 values less than(4)
             (
                  subpartition p11 values('北京'),
                  subpartition p12 values('上海')
             ),
             partition p2 values less than(maxvalue)
             (
                  subpartition p21 values('武汉'),
                  subpartition p22 values('郑州')     
             )
)

insert into stuinfo6 values(1,'eric','男',18,'上海',to_date('1995-10-11','yyyy-mm-dd'));
insert into stuinfo6 values(2,'lily','男',28,'上海',to_date('1985-10-11','yyyy-mm-dd'));
insert into stuinfo6 values(3,'chunchun','女',58,'北京',to_date('1965-10-11','yyyy-mm-dd'));
insert into stuinfo6 values(4,'eric888','男',null,'深圳',to_date('1995-10-11','yyyy-mm-dd'));
insert into stuinfo2 values(5,'葬爱K','女',16,'西藏',to_date('2000-9-11','yyyy-mm-dd'));
insert into stuinfo2 values(6,'葬爱Q','男',null,'台湾',to_date('1999-8-11','yyyy-mm-dd'));
insert into stuinfo6 values(7,'葬爱S','男',null,'长沙',to_date('1993-1-11','yyyy-mm-dd'));
insert into stuinfo6 values(8,'葬爱A','男',null,'合肥',to_date('1993-1-11','yyyy-mm-dd'));

select * from stuinfo6 partition (p11)

-----分区表维护

--查询分区

select * from stuinfo2 partition (p2)

--增加分区

alter table stuinfo2

add partition p666

values less than(6)

select * from stuinfo2 partition (p666) 

--删除分区

alter table stuinfo2 drop partition p666

----------------------锁

--1、什么是数据库中的锁

--为了防止两个用户同时操作一条数据，而引起的数据不一致