基础表（二）分区表

一、分区表
1.什么是分区表
当表中的数据不断增多，在拥有索引优化的前提下依然不能很好的提升查询效率，就引入了分区表
顾名思义，分区表也是一张表，分区表的原理是将大表、大索引在物理上进行分开存储在不同的表空间
但在逻辑上，依然只有一张表，在物理上由多个分区组成这张表

2.分区表的优势
(1)改善表的查询性能：在对表进行分区后，用户可以指定分区进行查询，减少单次查询时间
(2)表更容易管理：对于多个分区的数据加载和删除会更加容易
(3)便于备份和恢复：可以独立的备份和恢复每个分区
(4)提高数据安全性：将数据存放在不同的分区，减少了该表中数据同时被损坏的可能性

3.分区表的条件
(1)表数量超过2G
(2)表中包含原始数据，新的数据会被分到其他分区

4.分区表分类

(1)范围分区        (2)列表分区       (3)散列分区        (4)组合分区
 

二.范围分区

CREATE TABLE TABLE_NAME
(
COLUMN_NAME DATA_TYPE,
...
COLUMN_NAME DATA_TYPE
)
PARTITION BY RANGE(COLUMN_NAME)
( 
PARTITION 分区名 VALUES LESS THAN(值),
...
PARTITION 分区名 VALUES LESS THAN(MAXVALUE)
)TABLESPACE 空间名;

--示例：为学生表创建分区表()
--什么时候适合创建分区表：数据是分段的数据、考虑是否需要使用分区表、例如以年份为界限

--创建表
CREATE TABLE STU_RANGE
(
STU_ID NUMBER(4),
STU_NAME VARCHAR2(10),
STU_DATE DATE
)
PARTITION BY RANGE(STU_DATE)
(
PARTITION P_20229 VALUES LESS THAN (TO_DATE('2022-9-9','YYYY-MM-DD')),
PARTITION P_202210 VALUES LESS THAN (TO_DATE('2022-10-9','YYYY-MM-DD')),
PARTITION P_MAX VALUES LESS THAN (MAXVALUE)
);

--查询表
SELECT * FROM STU_RANGE;

--插入数据
--怎么用PLSQL向表中插入数据：DML语句可以直接执行吗？
--SELECT * FROM EMP;
DECLARE

BEGIN
  FOR I IN 1..5
    LOOP
  INSERT INTO STU_RANGE VALUES(I,CONCAT('SMITH',I),ADD_MONTHS(SYSDATE,-I));
   END LOOP;
END;

一.查找数据
SELECT * FROM STU_RANGE PARTITION (P_202210);
SELECT * FROM STU_RANGE PARTITION (P_20229);
SELECT * FROM STU_RANGE PARTITION (P_202211);
SELECT * FROM STU_RANGE PARTITION (P_MAX);
--可以看到按照分区的 LESS THAN 指的是小于 即小于2022-10-9的数据都放入该分区
--同理将小于2022-9-9的数据放入该P_20229中
1.当数据插入时,符合分区条件的将被插入,但是最后不符合按照日期分类后的数据会被插入到P_MAX中
--分区创建时就有要求：
1.首先是新增加的分区界限必须大于上一个分区的界限
2.其次是分区界限,第一个分区界限不能高于后者,否则会报错：分区界限过高

二.删除数据
DELETE FROM STU_RANGE PARTITION (P_202210) WHERE STU_ID = 6;

三.新增数据
INSERT INTO STU_RANGE PARTITION (P_202210) VALUES(6,'SMITH6',TO_DATE('2022-10-8','YYYY-MM-DD'))
INSERT INTO STU_RANGE PARTITION (P_202210) VALUES(6,'SMITH6',TO_DATE('2022-9-8','YYYY-MM-DD'))--ERROR
这里进行插入时,数据受到了限制,ORACLE报错：插入的分区关键字超过指定分区
经过测试,P202210本应该可以插入2022-10-9以前的所有根据日期划分的数据,即(-∞,2022-10-9)
但是因为有分区P20229,所以它的分区被限制为[2022-9-9,2022-10-9)
只有关键字(当时被选择为决定分区条件的字段)所处日期在该区间内才可以正常插入,否则报错

四.更新数据
UPDATE STU_RANGE PARTITION(P_202210) SET STU_NAME ='SMITH6'  WHERE STU_ID = 1; 


--其他语法CURD
1.指定分区查询数据
SELECT * FROM TABLE_NAME PARTITION (P_NAME);
2.指定分区删除数据
DELETE FROM TABLE_NAME PARTITION (P_NAME) WHERE COLUMN_NAME=VALUE;
3.指定分区添加数据
INSERT INTO TABLE_NAME PARTITION (P_NAME) VALUES();
4.指定分区更新数据
UPDATE TABLE_NAME PARTITION(P_NAME) SET COLUMN_NAME=VALUE WHERE COLUMN_NAME=VALUE;


--其他语法DDL
1.添加分区
ALTER TABLE TABLE_NAME ADD PARTITION P_NAME VALUES LESS THAN (VALUES);
2.删除指定分区
ALTER TABLE TABLE_NAME DROP PARTITION P_NAME;
3.查询分区信息
SELECT * FROM USER_TAB_PARTITIONS WHERE TABLE_NAME='TABLE_NAME';
4.截断分区
ALTER TABLE TABLE_NAME TRUNCATE PARTITION P_NAME;

1.
ALTER TABLE STU_RANGE ADD PARTITION P_202211 VALUES LESS THAN(TO_DATE('2022-11-9','YYYY-MM-DD'));
ALTER TABLE STU_RANGE ADD PARTITION P_MAX VALUES LESS THAN(MAXVALUE);
--添加前必须删除P_MAX分区，否则报错：分区界限必须高于最后一个分区
--即分区必须比前一个界限大
2.
ALTER TABLE STU_RANGE DROP PARTITION P_MAX;
3.
SELECT * FROM USER_TAB_PARTITIONS WHERE TABLE_NAME='STU_RANGE';
4.
ALTER TABLE STU_RANGE TRUNCATE PARTITION P_202211; 

三.列表分区

列表分区-list
--列表分区和范围分区
范围分区以日期字段为关键字
列表分区以id为关键字

CREATE TABLE TABLE_NAME
(COLUMN_NAME DATA_TYPE,
 ...
 COLUMN_NAME DATA_TYPE
)PARTITION BY LIST(COLUMN_NAME)
(
PARTITION P_NAME VALUES(VALUE1,VALUE2,...)
...
PARTITION P_NAME VALUES(DEFAULT)
);



--示例：
CREATE TABLE STU_LIST 
(STU_ID NUMBER(4),
 STU_NAME VARCHAR2(20),
 STU_DATE DATE
)PARTITION BY LIST(STU_ID)
(
PARTITION P_01 VALUES(1,2,3),
PARTITION P_02 VALUES(4,5,6),
PARTITION P_03 VALUES(DEFAULT)
);
--查看表信息
SELECT * FROM STU_LIST;

--插入数据
DECLARE

BEGIN
  FOR I IN 1..10
    LOOP
  INSERT INTO STU_LIST VALUES(I,CONCAT('SMITH',I),ADD_MONTHS(SYSDATE,-I));
    END LOOP;
END;

--按照分区查询数据
SELECT * FROM STU_LIST PARTITION (P_01);
SELECT * FROM STU_LIST PARTITION (P_02);
SELECT * FROM STU_LIST PARTITION (P_03);
1.前面我们在范围分区的时候就已经说了,按照分区表的创建要求,新创建的分区必须大于原有的最大分区
而当我们创建了P(MAXVALUE)时，将无法再创建新的分区,这里的P(DEFAULT)也是如此
故而当我们需要新的分区时,需要将这个分区删除后再创建,很麻烦,所以一般不创建该分区


--删除指定分区
ALTER TABLE STU_LIST DROP PARTITION P_03;
--添加新的分区
ALTER TABLE STU_LIST ADD PARTITION P_03 VALUES(11,12,13);
--查看已有分区
SELECT * FROM USER_TAB_PARTITIONS WHERE TABLE_NAME='STU_LIST';
--指定分区插入数据
INSERT INTO STU_LIST PARTITION(P_03) VALUES(11,'SMITH11',ADD_MONTHS(SYSDATE,-11));

四.散列分区

散列分区-HASH
散列分区是一种通过哈希算法来将数据无规则的插入向不同分区的一种分区
1.无规则不是算法无规则,而是指不能像前面几种分区根据关键字的VALUES固定好每个数据的去向
而是指在没有插入之前,算法没有计算前,插入的分区是位置
2.哈希分区无法删除某个分区,只能合并分区


--示例：
CREATE TABLE STU_HASH
( COLUMN_NAME1 DATA_TYPE,
         ...
  COLIMN_NAMEn DATA_TYPE
)PARTITION BY HASH(COLUMN_NAME)
(
  PARTITION P_NAME1,
         ...
  PARTITION P_NAMEn
)TABLESPACE USERS;




CREATE TABLE STU_HASH
(STU_ID NUMBER(3),
 STU_NAME VARCHAR2(20),
 STU_DATE DATE
)PARTITION BY HASH(STU_ID)
(PARTITION P_01,
 PARTITION P_02,
 PARTITION P_03
)TABLESPACE USERS;


--插入数据

DECLARE

BEGIN
  FOR I IN 1..10
    LOOP
      INSERT INTO STU_HASH VALUES(I,CONCAT('SMITH',I),ADD_MONTHS(SYSDATE,-I));
    END LOOP;
END;

--查看散列表
SELECT * FROM STU_HASH;

--查看指定分区数据
SELECT * FROM STU_HASH PARTITION (P_01);--1
SELECT * FROM STU_HASH PARTITION (P_02);--6
SELECT * FROM STU_HASH PARTITION (P_03);--3
--可以看到插入的数据非常随机,且不能人为指定


--向指定分区插入数据
INSERT INTO STU_HASH PARTITION (P_03) VALUES(11,'SMITH11',ADD_MONTHS(SYSDATE,-11))
--可以看到报错：插入的分区关键字超过指定分区
--即不能人为指定分区,这条数据在算法第一次计算后被插入到P_01分区后
--即使清除了再次插入也是必须插入P_01,即算法的稳定性与不可预知性

五.组合分区

组合分区 RANGE-LIST
--分区一共有三种：RANGE、LIST、HASH
--组合分区是将不同的分区进行组合形成的新的分区