【PL/SQL】 四种表分区用途全攻略

看完这章你会学习到以下内容：

1. 表分区的优势和缺点
2. 表分区的种类
3. 表分区的常规操作（查询，更改，添加，删除）

---

表分区
前提：表数据量比较大的时候,查询比较慢
通常都是在新建表格时候设立好。

优点：
1.提高查询效率
2.增强可用性
3.维护方便（只对某些有问题分区数据维护,不用对整张表维护）
缺点：
1.分区表需要维护（维护创建的分区）

---

表的分区（范围，列表，组合，哈希）
关键在于(按什么字段分区)：
1.PARTITION BY RANGE() = 范围
2.LIST() = 列表
3.RANGE() SUBPARTITION BY LIST() = 组合
4.HASH() = 哈希

---

1.范围分区：

--范围分区：需要定义最大值，固定搭配Less than
        按入职日期进行范围分区
        CREATE TABLE MYEMP
        (
            EMPNO  NUMBER(4)  PRIMARY KEY,
            ENAME VARCHAR2(10),
            HIREDATE DATE,
            DEPTNO NUMBER(7)
        )
        PARTITION  BY  RANGE (HIREDATE)   -- 按照那个字段分区
        (
              PARTITION  part1 VALUES  LESS  THAN (TO_DATE('1981-1-1','YYYY/MM/DD')), --①
              PARTITION  part2 VALUES  LESS  THAN (TO_DATE('1982-1-1','YYYY/MM/DD')),
              PARTITION  part3 VALUES  LESS  THAN (TO_DATE('1983-1-1','YYYY/MM/DD')), 
              PARTITION  part4 VALUES  LESS  THAN (TO_DATE('1988-1-1','YYYY/MM/DD')), 
              PARTITION  part5 VALUES  LESS  THAN (MAXVALUE)                          --默认最大
        );

2.列表分区：
该分区的特点是某列的值比较少并且不会经常变动,基于此特点我们可以采用列表分区。

--列表分区：
        CREATE TABLE MYEMP2
        (
            EMPNO  NUMBER(4)  PRIMARY KEY,
            ENAME VARCHAR2(10),
            HIREDATE DATE,
            DEPTNO NUMBER(7)
        )
        PARTITION BY LIST (DEPTNO)  --    按DEPTNO进行LIST分区
        (
              PARTITION MYEMP_DEPTNO_10  VALUES (10) ,
              PARTITION MYEMP_DEPTNO_20  VALUES (20) ,
              PARTITION MYEMP_DEPTNO_30  VALUES (30) , 
              PARTITION MYEMP_DEPTNO_40  VALUES (40) 
        );

3.Hash分区：
通过计算hash值,将相同的hash值放到相同的分区

    CREATE TABLE MYEMP3
    (
      EMPNO  NUMBER(4)  PRIMARY KEY,
      ENAME VARCHAR2(10),
      HIREDATE DATE,
      DEPTNO NUMBER(7)
    )
    PARTITION BY HASH (ENAME)
       (PARTITION part01, 
        PARTITION part02);

4.组合分区：
范围里面嵌套列表分区

    CREATE TABLE MYEMP4
    (
        EMPNO  NUMBER(4)  PRIMARY KEY,
        ENAME VARCHAR2(10),
        HIREDATE DATE,
        DEPTNO NUMBER(7,2)
    )
    PARTITION BY RANGE(HIREDATE) SUBPARTITION BY LIST(DEPTNO)
    (
       PARTITION P1 VALUES LESS THAN(TO_DATE('1981-01-01','YYYY-MM-DD'))
              (
                  SUBPARTITION P1A VALUES (10),
                  SUBPARTITION P1B VALUES (20),
                  SUBPARTITION P1C VALUES (30),
                  SUBPARTITION P1D VALUES (40)
              ),
       PARTITION P2 VALUES LESS THAN (TO_DATE('1982-01-01','YYYY-MM-DD'))
              (
                  SUBPARTITION P2A VALUES (10),
                  SUBPARTITION P2B VALUES (20),
                  SUBPARTITION P2C VALUES (30),
                  SUBPARTITION P2D VALUES (40)
              ),
       PARTITION P3 VALUES LESS THAN (TO_DATE('1983-01-01','YYYY-MM-DD'))
              (
                 SUBPARTITION P3A VALUES (10) ,
                  SUBPARTITION P3B VALUES (20),
                  SUBPARTITION P3C VALUES (30),
                  SUBPARTITION P3D VALUES (40)
              ),
       PARTITION P4 VALUES LESS THAN (TO_DATE('1988-01-01','YYYY-MM-DD'))
              (
                 SUBPARTITION  P4A VALUES (10),
                  SUBPARTITION P4B VALUES (20),
                  SUBPARTITION P4C VALUES (30),
                  SUBPARTITION P4D VALUES (40)
              ),
       PARTITION P5 VALUES LESS  THAN (MAXVALUE)
              (
                  SUBPARTITION P5A VALUES (10),
                  SUBPARTITION P5B VALUES (20),
                  SUBPARTITION P5C VALUES (30),
                  SUBPARTITION P5D VALUES (40)
              )                
    );

---

分区表相关操作
1.查看分区数据（列表分区为例）

    SELECT * FROM MYEMP2 PARTITION(MYEMP_DEPTNO_40);

2.添加分区（原有的分区表没有给定默认分区的前提）

    ALTER TABLE MYEMP2 ADD PARTITION MYEMP_DEPTNO_50 VALUES (50);

3.删除分区

--注意：如果删除的分区是表中唯一的分区,那么此分区将不能被删除,要想删除此分区,必须删除表    
ALTER TABLE MYEMP2 DROP PARTITION MYEMP_DEPTNO_50;    

4.重命名表分区

--将分区的名字MyEMP_deptno_50 改为MyEMP_deptno_60;
ALTER TABLE MYEMP2 RENAME PARTITION MYEMP_DEPTNO_50 TO MYEMP_DEPTNO_60;