Oracle 分区表

什么是分区表？

通俗的讲：分区表就是将一张表中的数据分别放进不同的表空间（物理文件）里。这样在以后的查询中就会避免扫描整张表，只是从当前的分区中查询到所需要的数据。这样做会大大提高数据查询的速度。

什么时候使用分区表?

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，我们直接查询会发现里面以及有数据了，这些数据是通过哈希算法来分散的

-- 注意：哈希分区不能直接进行删除

注意

任何分区都是在建表之初建立的，不能后期添加。