分区表的使用与维护

分区表

why要使用分区

应用使用时间长后，数据增大，性能下降；

逻辑上是完整的表，表中的数据在物理上存放在多个表空间上；

提升数据查询速度

优点：

改善查询性能；

增强可用性

维护方便；

均衡I/O

分区策略

策略：大而化小，分而治之

类型：水平分区

查询无需指定，会自动判断在那个分区；

水平分区

范围分区（range）、列表分区（list）、哈希分区（hash）、间隔分区（范围分区的一种）

范围分区(range)

分区列是日期时间类型

关键字：range

操作：

0.给权限

grant create tablespace to test；

1.创建表空间

create tablespace tbs1 datafile 'TBS101.DBF' size 32;
create tablespace tbs2 datafile 'TBS201.DBF' size 32;
create tablespace tbs3 datafile 'TBS301.DBF' size 32;

2.创建表

create table t_f1( #整体表名称
id int;
name varchar(50);
)storage (on main) #存在主空间
PARTITION BY RANGE (id)
(
    PARTITION p1 VALUES LESS THAN (1000) TABLESPACE tbs1,
    PARTITION p2 VALUES LESS THAN (2000) TABLESPACE tbs2,
    PARTITION p3 VALUES LESS THAN (3000) TABLESPACE tbs3
)

3.查询分区表的数据

select TABLE_NAME,TABLESPACE_NAME,PARTITIONED from SYS.USER_TABLES
where PARTITION='yes'

4.查看分区表的分区信息

select TABLE_NAME,PARTITION_NAME,TABLESPACE_NAME,HIGH)VALUE from SYS.USER_TAB_PARTITIONS WHERE TABLE_NAME='t_f1'

5.测试现有分区是否正常使用

#测试是否成功
#插入数据
BEGIN
FOR i in 1. 2800 loop
insert into t_f1 values(i,"AAA"||i);
end loop;
commit;
end;

#在总表查询
select * from t_f1;

#在分区查询
select max(id) from t_f1 partition (p1);
#或者
select * from t_f1_p1

6.添加maxvalue的值

alter table t_f1 add partition pm values less than (maxvalue)
#专门存放超过最大值的数据

堆表的分区

主表的表空间和子表的要在一起；

列表分区(list)

适合字符串类型的

1.新建列表分区

create table t_sales(
sale_id int,
sale_name varchar(30),
sale_date datetime,
sale_city varchar(50)
)
partition by list  (sale_city)
(
    partition p1 values('北京','天津','石家庄'),
    partition p2 values('上海','南京'),
    partition p3 values('武汉','长沙'),
    partition p4 values('广州','深圳')
)

2.插数据

insert into t_sales values(1,'AAA','2008-01-09','石家庄');
insert into t_sales values(2,'BBB','2002-05-09','上海');
insert into t_sales values(3,'CCC','2007-11-09','长沙');
insert into t_sales values(4,'DDD','2008-01-09','广州');
insert into t_sales values(5,'ABB','2008-01-09','石家庄');
insert into t_sales values(6,'GGG','2008-01-09','石家庄');

3.插入范围外的数据

insert into t_sales values(7,'GGG','2008-01-09','重庆');
#会报错

4.优化为正确的

alter table t_sales add partition pm values(DEFAULT);

hash分区

数据随机存储，非常快，读取数据较慢；

各个分区数据均匀存放；

若hash分区不指定分区名，默认通过hash分区数量自己创建；

1.指定分区名

create table t_hash1(
	id int,
    name varchar(50)
)
partition by hash (id)(
	partition p1,
    partition p2,
    partition p3
)

测试

begin
from i in 1..300 loop
insert into t_hash1 values(i,'AAA'||i);
end loop;
commit;
end;

#查看
select count(*) from t_hash1;
select count(*) from t_hash1_p1;
select count(*) from t_hash1_p2;
select count(*) from t_hash1_p3;

2.不指定分区

create table t_hash2(id int,name varchar(50))
partition by hash (id) partitions 4;
#分为4组，但是不做指定命名

间隔分区

范围分区的扩展。

时间间隔分区：根据时间自动创建分区；

支持年月 (numtoyminterval)、

年月日时分秒(numtodsinterval)；

0.查询

select sysdate.sysdate+numtoyminterval(1,'YEAR');
#查询今天日期后一年的时间
select sysdate.sysdate+numtoyminterval(1,'mouth');
#查询今天日期后一个月的时间

1.创建

create table t_interval_year(
	id number,
    name varchar(50),
    birthday date
)
partition by range(birthday)
interval (numtoyminterval(1,'YEAR'))(
	partition p2014 values less than (to_date('2015-01-01')),
    partition p2015 values less than (to_date('2016-01-01')),
     
)

2.测试

insert into t_interval_year values(1,'AAA','2014-03-07');
insert into t_interval_year values(2,'AAA','2015-05-07');
#若不存在分区则会自动创建分区
insert into t_interval_year values(3,'AAC','2018-05-07');

按日创建间隔分区

1创建

create table t_interval_day(
	id number,
    name varchar(50),
    birthday datetime
    #注意date类型不支持时分秒，所以这里要换成datetime
)
partition by range(birthday)
interval (numtodsinterval(1,'DAY'))(
	partition p0 values less than (to_date('2015-01-01'))
     
)

2.测试

insert into t_interval_day values(1,'AAA','2014-03-07');
insert into t_interval_day values(2,'AAA','2015-05-07');
#若不存在分区则会自动创建分区
insert into t_interval_day values(3,'AAC','2018-05-07');

组合分区

Range-list
Range-hash
Range-range
List-list
List-range
List-hash
Hash-hash

List-range

销售地点-销售时间分组

create table t_sales2(
sale_id int,
sale_name varchar(30),
sale_date datetime,
sale_city varchar(50)
)
partition by list  (sale_city)
subpartition by range (saledate)
subpartition template(
    subpartition p11 values less than ('2021-01-01'),
    subpartition p12 values less than ('2022-01-01'),
    subpartition p13 values less than ('2023-01-01')
)
(
    partition p1 values('北京','天津','石家庄'),
    partition p2 values('上海','南京'),
    partition p3 values('武汉','长沙'),
    partition p4 values('广州','深圳'),
    partition pm values default
)

分区的维护

1.删除分区

alter table t_sales2 drop partition_pm;
#删除pm分区，连同分区上面的数据；

2.增加分区alter

alter table t_sales add partition pm values(DEFAULT);

3.合并分区

alter table test t_f1 merge partitions p1,p2 into partition p1_p2; 
#数据也会一起合并

4.拆分分区

alter table t_f1 split partition p1_2 at (1000) into (partition p1,partition p2);

5.交换分区

create table t1 (id int,name varchar(30));
insert into t1 values(1,'1000AA');
insert into t1 values(4000,'4000AA');
insert into t1 values(5000,'5000AA');
insert into t1 values(7000,'7000AA');
alter table t_f1 EXCHANGE PARTITION p2 WITH TABLE t1;
#将t_f1的p2分区与t1表进行交换

要注意

索引组织表和堆表不能进行交换

允许行移动：才能使更新后的数据进入正确的分区

alter table t4 enable row movement

6.普通表变为分区表

如何把非分区表转换为分区表？
（1）把非分区表的结构、数据导出来，dexp/create table 别名 as select
* from 表名;
（2）删除或者重命名非分区表
（3）建一个结构一样的分区表
（4）将数据导入到分区表中，dimp insert into .... select * from ....;

例子

0.创建新表
create table t4(id int, name varchar(30));
1.录入数据：
BEGIN
for i in 1..1000 LOOP
insert into t4 values(i,'AAA'||i);
end loop;
commit;
end;

2.导出数据
dexp test/Dameng123 file=/home/dmdba/t5.dmp tables=t4
#到bin目录下，test/dameng 是用户名和密码注意修改

3.改名对非分区表重命名：
alter table t4 rename to t4_bak;

4.创建分区表：
create table T4 (id int,name VARCHAR(30))
PARTITION BY RANGE (id)
(
PARTITION p1 VALUES LESS THAN (200), PARTITION p2 VALUES LESS THAN (400), PARTITION p3 VALUES LESS THAN (600), PARTITION p4 VALUES LESS THAN (MAXVALUE)
)

5.导入数据：
dimp test/Dameng123 file=/home/dmdba/t5.dmp ignore=y

分区表