什么是分区表
一个用户表存储的用户非常多时,查询变慢,便可以根据用户的某些特征进行分区,可以根据性别分区,查询时只查询特定的分区,效率自然也会提升
定义:
- 允许用户将一个表分成多个分区
- 用户可以执行查询,只访问表中的特定分区
- 讲不通的分区存储在不同的磁盘中,提高访问性能和安全性
- 可以独立的备份和恢复每个分区
什么时候需要分区表
问题:有一个销售记录表,存放2015年开始的销售记录,由于业务量的增大,存储数据已经达到了4GB,按照年份制度查询越来越慢,请给出解决方案:
-------- 这时就需要表的分区:
- 将销售记录表修改为分区表
- 按照销售日期进行分区
- 每一个年份为一个分区
分区策略
- 数据大于2GB
- 已有的数据和新添加的数据有明显的划分界限
如何分区 - 根据业务需求,决定创建何种分区
- 大表每个分区创建一个表空间
分区表的类型
范围分区
以表中的一列值或一组列的值的范围作为分区依据
SQL>partition by range(column_name)
(
partition part1 values less than (rang1),
partition part2 values less than (rang2),
...
[partition partN values less than(maxvalue)]
);
示例代码:
--创建表
create table stu(
id number(4) not null,
name varchar2(10),
birth date
)--同时为表创建分区,根据birth列
partition by range(birth)
(
--创建了一个小于`'2000/1/1`日期的part1分区
partition part1 values less than (to_date('2000/1/1','yyyy/mm/dd')),
--创建了一个小于`'2000/1/1`日期的part2分区
partition part2 values less than (to_date('2015/1/1','yyyy/mm/dd')),
--创建了一个小于当列的类型最大值的part3分区,可以说是默认
partition part3 values less than (maxvalue)
);
--查看刚才创建的分区
select table_name,partition_name from user_tab_partitions where table_name=upper('stu')
--向表中插入两条数据,更具日期放在不同的分区
insert into stu values(1,'张三',to_date('1999/1/1','yyyy/mm/dd'));
insert into stu values(2,'李四',to_date('2010/1/1','yyyy/mm/dd'));
insert into stu values(3,'王五',to_date('2020/1/1','yyyy/mm/dd'));
-- 查看刚才分区中的数据
select * from stu partition (part1);
select * from stu partition (part2);
select * from stu partition (part3);
间隔分区
- 实现范围分区的自动化
- 最开始的分区是永久分区,随着数据的增加自动创建新的分区
SQL>partition by range(column_name)
interval(numToymInterval(n,'interval_unit'))
(partition part1 values less than(range1),
...
)
interval
:代表“间隔”,按照后面括号中的定义间隔添加分区
numToymInterval(n,'interval_unit')
:将n装换成interval_unit所指定的值
interval_unit
:可以为YEAR,MONTH
分区表的管理
添加,删除,合并,拆分,移动,截断
--添加
--添加时,分区界限必须高于最后一个分区的界限
alter table stu add partition part4 values less than(to_date('2025/1/1','yyyy/mm/dd'));
--删除:
--注意删除表分区后,对应的表分区的数据也会删除掉
alter table stu drop partition part3;