什么是分区表?
通俗的讲:分区表就是将一张表中的数据分别放进不同的表空间(物理文件)里。这样在以后的查询中就会避免扫描整张表,只是从当前的分区中查询到所需要的数据。这样做会大大提高数据查询的速度。
什么时候使用分区表?
1、表的大小超过2GB。
2、表中包含历史数据,新的数据被增加都新的分区中。
分区表有哪些优缺点?
优点:
1.改善查询性能:对分区对象的查询可以仅搜索自己关心的分区,提高检索速度。
2.增强可用性:如果表的某个分区出现故障,表在其他分区的数据仍然可用;
3.维护方便:如果表的某个分区出现故障,需要修复数据,只修复该分区即可;
4.均衡I/O:可 以把不同的分区映射到磁盘以平衡I/O,改善整个系统性能。
缺点:
已经存在的表没有方法可以直接转化为分区表。
分区表的分类:
1、范围分区表
2、列表分区表
3、哈希分区表
4、组合分区表
范围分区表(RANGE)
-- 每一个分区都必须有一个 values less than 子句,他指定了改分区的上限值。
-- 每一个分区也都有下限值(除了第一个),这个值就是前一个分区的上限值。
-- 一般情况下会以时间段来做范围分区
-- 创建语句
CREATE TABLE 表名
(
列名 数据类型 [约束],
列名 数据类型 [约束],
....
)
PARTITION BY RANGE(列名:根据哪个列做的分区)
(
PARTITION 分区名1 VALUES LESS THAN (值1),
PARTITION 分区名2 VALUES LESS THAN (值2),
PARTITION 分区名3 VALUES LESS THAN (值3),
....
[PARTITION 分区名N VALUES LESS THAN (MAXVALUE)]
-- maxvalue 代表一个不确定的值,它高于任何其他分区的值。
);
-- 举例说明:
CREATE TABLE RANGE_DEMO1
(
ID NUMBER, -- 编号
NAME VARCHAR2(20), -- 姓名
BIRTHDAY DATE -- 生日
)
PARTITION BY RANGE(BIRTHDAY)
(
PARTITION P_1999 VALUES LESS THAN (DATE'1999-12-31'),
PARTITION P_2000 VALUES LESS THAN (DATE'2000-12-31'),
PARTITION P_2001 VALUES LESS THAN (DATE'2001-12-31'),
PARTITION P_MAX VALUES LESS THAN (MAXVALUE)
-- maxvalue 代表一个不确定的值,它高于任何其他分区的值。
);
-- 向 RANGE_DEMO1 中添加数据
INSERT INTO RANGE_DEMO1 VALUES(1001,'张三',DATE'1999-02-15');
INSERT INTO RANGE_DEMO1 VALUES(1002,'李四',DATE'1999-08-25');
INSERT INTO RANGE_DEMO1 VALUES(1003,'王五',DATE'1999-05-11');
INSERT INTO RANGE_DEMO1 VALUES(1004,'赵六',DATE'1999-10-11');
INSERT INTO RANGE_DEMO1 VALUES(1005,'刘德华',DATE'2000-02-15');
INSERT INTO RANGE_DEMO1 VALUES(1006,'郭富城',DATE'2000-08-25');
INSERT INTO RANGE_DEMO1 VALUES(1007,'张学友',DATE'2000-05-11');
INSERT INTO RANGE_DEMO1 VALUES(1008,'黎明',DATE'2000-10-11');
INSERT INTO RANGE_DEMO1 VALUES(1009,'迪丽热巴',DATE'2001-02-15');
INSERT INTO RANGE_DEMO1 VALUES(1010,'古力娜扎',DATE'2001-08-25');
INSERT INTO RANGE_DEMO1 VALUES(1011,'马尔扎哈',DATE'2001-05-11');
INSERT INTO RANGE_DEMO1 VALUES(1012,'德玛西亚',DATE'2001-10-11');
INSERT INTO RANGE_DEMO1 VALUES(1013,'迪迦奥特曼',DATE'2002-05-11');
INSERT INTO RANGE_DEMO1 VALUES(1014,'赛罗奥特曼',DATE'2002-10-11');
-- 以往我们查询1999年生日的数据时
SELECT * FROM RANGE_DEMO1 WHERE TO_CHAR(BIRTHDAY,'YYYY') = 1999;
-- 现在有了分区表,我们可以这样玩 ,这样就避免了全盘扫描,大大提高了查询效率
SELECT * FROM RANGE_DEMO1 PARTITION (P_1999);
-- 查询分区表语法:
SELECT * FROM 表名 PARTITION (分区名);
-- 新增分区:首先表一定是分区表才能新增
-- 新增分区高于最后一个分区界限
ALTER TABLE 表名 ADD PARTITION 分区名 VALUES LESS THAN (值或日期);
alter table RANGE_DEMO1 add partition P_2002 values less than (DATE'2002-12-31');
-- 删除分区
alter table 表名 drop partition 分区名;
alter table RANGE_DEMO1 drop partition P_MAX;
列表分区表 (LIST)
-- 列表分区的特点是某列的值只是有限的,基于这样的特点我们可以采用列表分区。比如 性别列、地区列等等。
-- 创建语句:
CREATE TABLE 表名
(
列名 数据类型 [约束],
列名 数据类型 [约束],
....
)
PARTITION BY LIST(字段)
(
PARTITION 分区名1 VALUES (值),
PARTITION 分区名2 VALUES (值),
PARTITION 分区名3 VALUES (值),
.....
PARTITION 分区名n VALUES (DEFAULT) -- 表示该分区存储未在其他分区定义的数据行。
-- 如果没有这个分区,那么我的添加数据必须符合上面的分区值,否则报错。
-- 但一旦创建了这个 DEFAULT 分区,不能再次添加分区,添加前必须删除 DEFAULT 分区
);
-- 举例说明:
-- 创建语句:
CREATE TABLE LIST_DEMO2
(
ID NUMBER, -- 编号
NAME VARCHAR2(20), -- 姓名
ADDRESS VARCHAR2(100) -- 所在省份
)
PARTITION BY LIST(ADDRESS)
(
PARTITION P_BeiJing VALUES ('北京'),
PARTITION P_ShanXi VALUES ('山西'),
PARTITION P_ShanDong VALUES ('山东'),
PARTITION P_QiTa VALUES (DEFAULT)
);
-- 向 LIST_DEMO1 添加数据
INSERT INTO LIST_DEMO2 VALUES(1001,'张三','北京');
INSERT INTO LIST_DEMO2 VALUES(1002,'李四','北京');
INSERT INTO LIST_DEMO2 VALUES(1003,'王五','北京');
INSERT INTO LIST_DEMO2 VALUES(1004,'赵六','北京');
INSERT INTO LIST_DEMO2 VALUES(1005,'刘德华','山西');
INSERT INTO LIST_DEMO2 VALUES(1006,'郭富城','山西');
INSERT INTO LIST_DEMO2 VALUES(1007,'张学友','山西');
INSERT INTO LIST_DEMO2 VALUES(1008,'黎明','山西');
INSERT INTO LIST_DEMO2 VALUES(1009,'迪丽热巴','山东');
INSERT INTO LIST_DEMO2 VALUES(1010,'古力娜扎','山东');
INSERT INTO LIST_DEMO2 VALUES(1011,'马尔扎哈','山东');
INSERT INTO LIST_DEMO2 VALUES(1012,'德玛西亚','山东');
INSERT INTO LIST_DEMO2 VALUES(1013,'迪迦奥特曼','江苏');
INSERT INTO LIST_DEMO2 VALUES(1014,'赛罗奥特曼','江苏');
-- 之前查询北京的人,进行全盘扫描,每一条数据都要做个比较,判断是否符合where条件
SELECT * FROM LIST_DEMO2 WHERE ADDRESS = '北京';
-- 现在利用分区查询 直接查询分区,查询速度大大提升
SELECT * FROM LIST_DEMO2 PARTITION (P_BeiJing);\
--新增分区:
alter table 表名 add partition 分区名 values (值);
ALTER TABLE LIST_DEMO2 ADD PARTITION P_JiangSu VALUES('江苏');
-- 注意:当有 DEFAULT 分区时,无法添加分区
--删除分区
alter table 表名 drop partition 分区名 ;
ALTER TABLE LIST_DEMO2 DROP PARTITION P_QiTa;
哈希分区表
/*
这类分区是在列值上使用散列算法,以确定将行放入哪个分区中。当列的值没有合适的条件时,建议使用散列分区。
散列分区为通过指定分区编号来均匀分布数据的一种分区类型,因为通过在I/O设备上进行散列分区,使得这些分区大小一致。
*/
-- 创建语法
CREATE TABLE 表名
(
列名 数据类型 [约束],
列名 数据类型 [约束],
....
)
PARTITION BY HASH (字段)
(
PARTITION 分区名1,
PARTITION 分区名2,
PARTITION 分区名3,
......
);
-- 举例说明:
-- 创建表
CREATE TABLE HASH_DEMO1
(
ID NUMBER, -- 编号
NAME VARCHAR2(20), -- 姓名
BIRTHDAY DATE -- 生日
)
PARTITION BY HASH (ID)
(
PARTITION P1,
PARTITION P2,
PARTITION P3
);
-- 将 RANGE_DEMO1 表中的数据添加进去
INSERT INTO HASH_DEMO1 SELECT * FROM RANGE_DEMO1;
-- 查询分区
SELECT * FROM HASH_DEMO1 PARTITION (P1);
SELECT * FROM HASH_DEMO1 PARTITION (P2);
SELECT * FROM HASH_DEMO1 PARTITION (P3);
--新增分区:
alter table 表名 add partition 分区名;
ALTER TABLE HASH_DEMO1 ADD PARTITION P4;
SELECT * FROM HASH_DEMO1 PARTITION (P4);
-- 添加后的P4,我们直接查询会发现里面以及有数据了,这些数据是通过哈希算法来分散的
-- 注意:哈希分区不能直接进行删除
注意
任何分区都是在建表之初建立的,不能后期添加。