一 分区技术概述
分区技术(Partitioning)可以将大表、大索引分解为更小、易于管理的块,这些块称为分区(Partition),通过分区技术可以有效的解决大表、大索引带来的问题,对分区表执行的SQL查询或DML语句与普通数据表的语句一样。但是定义了分区后,DML语句可以访问、操作一个单独的分区,而不是整个表或索引,这样通过分区技术就能简化对大数据库对象的管理工作。
使用分区技术,有以下优势:
- 提高数据的可用性;
- 将大段分解为小段,从而减轻管理的负担;
- 改善某些查询的性能(分区修剪);
- 可以把数据的修改分布到多个分区上,从而减少I/O竞争;
二 分区表的分类
1 范围分区
1)范围分区介绍
创建范围分区的关键字是Range,创建该分区后,其中的数据可以根据分区键指定的范围进行分布,当数据在范围内均匀分布时,性能最好。
当表结构采用范围分区时,首先要考虑分区的列应该符合范围分区的方法;其次要考虑列的数据值的取值范围;最后考虑列的边界问题。
2)语法结构
![](https://i-blog.csdnimg.cn/blog_migrate/6104b873ba7c54bf411cfb7e60d757c8.png)
3)示例
--创建范围分区表
create table T_RANGE
(
ID NUMBER,
NAME VARCHAR2(100),
CDATE DATE default sysdate,
CREATOR VARCHAR2(20) default 'system'
)
partition by range (CDATE)
(
partition P1 values less than (TO_DATE('2018-05-21', 'YYYY-MM-DD')),
partition P2 values less than (TO_DATE('2018-05-22', 'YYYY-MM-DD')),
partition P3 values less than (TO_DATE('2018-05-23', 'YYYY-MM-DD')),
partition P4 values less than (TO_DATE('2018-05-24', 'YYYY-MM-DD')),
partition P5 values less than (maxvalue)
);
--创建序列
create sequence seq_range;
--初始化范围分区表数据
insert into t_range values(seq_range.nextval,'Jack',to_date('2018-05-20', 'YYYY-MM-DD'),'system');
insert into t_range values(seq_range.nextval,'Peter',to_date('2018-05-21', 'YYYY-MM-DD'),'system');
insert into t_range values(seq_range.nextval,'Geo',to_date('2018-05-22', 'YYYY-MM-DD'),'system');
insert into t_range values(seq_range.nextval,'Lucy',to_date('2018-05-23', 'YYYY-MM-DD'),'system');
insert into t_range values(seq_range.nextval,'Smith',to_date('2018-05-24', 'YYYY-MM-DD'),'system');
insert into t_range values(seq_range.nextval,'James',to_date('2018-05-25', 'YYYY-MM-DD'),'system');
insert into t_range values(seq_range.nextval,'Tom',to_date('2018-05-26', 'YYYY-MM-DD'),'system');
commit;
--查看表数据
select * FROM t_range;
select * FROM t_range partition(p1);
--查看分区信息
SELECT * FROM user_part_tables t WHERE t.table_name = 'T_RANGE';
SELECT * FROM User_Tab_Partitions t WHERE t.table_name='T_RANGE';
SELECT * FROM user_segments t WHERE t.segment_name = 'T_RANGE';
2 散列分区
1)散列分区介绍
对于散列分区表,Oracle会使用一个散列函数对每一条插入数据的分区列值计算出其散列值,以此确定数据应当放在N个分区中的哪个分区中。Oracle建议N是2的一个幂(2、4、8、16等),这样表中的数据才能最好的分布在所有的分区上。散列分区可用于大强度更新、具有高度争用的环境,使用散列分区后,更新不在集中于一个热点段中,而是分散到16个分区上,每个分区都能进行数据的修改。
2)语法结构
![](https://i-blog.csdnimg.cn/blog_migrate/dd23765e27e6472ef0aaebb798b30662.png)
3)示例
--创建哈希分区表
create table T_HASH
(
id NUMBER,
name VARCHAR2(100),
cdate DATE default SYSDATE,
creator VARCHAR2(100) default 'system'
)
partition by hash (ID)
(
partition P1
tablespace USERS,
partition P2
tablespace TEST
);
--创建序列
CREATE SEQUENCE seq_hash;
--初始化散列表
BEGIN
FOR j IN 1..10 LOOP
INSERT INTO t_hash(ID,NAME) VALUES(seq_hash.nextval,'HASH'||j);
END LOOP;
COMMIT;
END;
--查看表数据
SELECT * FROM t_hash;
SELECT * FROM t_hash PARTITION(p1);
--查看分区信息
SELECT * FROM User_Part_Tables t WHERE t.table_name='T_HASH';
SELECT * FROM User_Tab_Partitions t WHERE t.table_name='T_HASH';
SELECT * FROM User_Segments t WHERE t.segment_name='T_HASH';
4)注意事项
- 使用哈希分区,无法控制一行最终放在哪个分区中,Oracle会应用散列函数,并依据散列的结果来确定行会放在哪里;
- 改变散列分区的个数,数据会在所有分区中重新分布,向一个散列分区表增加或删除一个分区时,将导致所有数据都会重写,因为现在每一行都可能属于一个不同的分区;
- 分区数应该是2的幂。如果分区数是2的幂,那么分区将会均匀分布,如果不是,那么分区将会不均匀分布。
3 列表分区
1)列表分区介绍
列表分区为每个分区指定了一个离散值列表,每行数据根据其分区键值所属列表归到相应的分区中。
2)语法结构
![](https://i-blog.csdnimg.cn/blog_migrate/48fac4b7b7c43a5c2ce78de9e7898819.png)