深度优先

这个家伙好懒，除了文章什么都没留下

0%

【Oracle】表分区

发表于 分类于
本文字数： 6.8k 阅读时长 ≈ 6 分钟

数据库

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
--创建表空间（数据库）
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）范围分区

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

1
2
3
4
5
6
7
8
9
10
11
12
13
14
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)    
);

–查询分区数据 

1
select * from stuinfo1 partition(p3);

–2）列表分区

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
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）散列分区

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
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）复合分区

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

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
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、什么是数据库中的锁
–为了防止两个用户同时操作一条数据，而引起的数据不一致