分区表（二）

oracle partition 分区表
分区表概述：
分区表就是通过使用分区技术，将一张大表，拆分成多个表分区（独立的segment），从而提升数据访问的性能，以及日常的可维护性。
分区表中，每个分区的逻辑结构必须相同。如：列名、数据类型、
分区表中，每个分区的物理存储参数可以不同。如：各个分区所在的表空间。
对于应用而言完全透明，分区前后没有变化，不需要进行修改。
需注意：
1.虽然各个分区可以存放在不同的表空间中，但这些表空间所使用的块大小(block_size)必须一致。
2.除了包含LONG以及LONG RAW字段的表无法使用分区表外，其他表均可以使用分区，包括含有LOB字段的表。






分区表的优势：
（1）改善查询性能：对分区对象的查询可以仅搜索自己关心的分区，提高检索速度；
（2）方便数据管理：因为分区表的数据存储在多个部分中，所以按分区加载和删除数据比在大表中加载和删除数据更容易。
（3）方便备份恢复：因为分区表比原表要小，所以针对分区的备份和恢复方法要比备份和恢复整个表的方法多。




一、Oracle数据库提供对表或者分区的方法有几种：
（1）范围分区（range分区）
（2）散列分区（HASH分区）
（3）列表分区（list分区）
（4）复合分区（子分区）




1.范围分区（range分区）
行映射到基于列值范围的分区
range分区又称为范围分区，基于分区键值的范围将数据映射到所建立的分区上。这就是最通用的分区类型。
特点：
范围分区只要依据分区键定义时给出的键值范围，根据实际的取值，进行分区的选择，进而在相应分区中存储数据。
范围分区比较合适存在以数字为导向，方便进行数字范围划分的数据列。
范围分区的数据分布可能不均匀。
创建范围分区时，必须指定以下内容：
分区方法：range
分区列：标识分区边界的描述
使用range分区的时候，要记住几条规则：
1）每个分区都包含values less than字名，定义了分区的上层边界，任何等于和大于分区键值的二进制都被添加到下一个高层分区中。
2）所有的分区，除了第一个，如果低于values less than所定义的下层边界，都放在前面的分区中。
MAXVALUE可以用来定义最高层的分区。MAXVALUE表示了虚拟的无限值。


SQL> select count(*) from emp;
  COUNT(*)
----------
        14


SQL> create table emp1 as select * from emp;
Table created


SQL> select * from emp;
EMPNO ENAME      JOB         MGR HIREDATE          SAL      COMM DEPTNO
----- ---------- --------- ----- ----------- --------- --------- ------
 7369 SMITH      CLERK      7902 1980/12/17     800.00               20
 7499 ALLEN      SALESMAN   7698 1981/2/20     1600.00    300.00     30
 7521 WARD       SALESMAN   7698 1981/2/22     1250.00    500.00     30
 7566 JONES      MANAGER    7839 1981/4/2      2975.00               20
 7654 MARTIN     SALESMAN   7698 1981/9/28     1250.00   1400.00     30
 7698 BLAKE      MANAGER    7839 1981/5/1      2850.00               30
 7782 CLARK      MANAGER    7839 1981/6/9      2450.00               10
 7788 SCOTT      ANALYST    7566 1987/4/19     3000.00               20
 7839 KING       PRESIDENT       1981/11/17    5000.00               10
 7844 TURNER     SALESMAN   7698 1981/9/8      1500.00      0.00     30
 7876 ADAMS      CLERK      7788 1987/5/23     1100.00               20
 7900 JAMES      CLERK      7698 1981/12/3      950.00               30
 7902 FORD       ANALYST    7566 1981/12/3     3000.00               20
 7934 MILLER     CLERK      7782 1982/1/23     1300.00               10
14 rows selected


SQL>CREATE TABLE SCOTT.EMP_RANGE
(
  EMPNO    NUMBER,
  ENAME    VARCHAR2(10 BYTE),
  JOB      VARCHAR2(9 BYTE),
  MGR      NUMBER,
  HIRDATE  DATE,
  SAL      NUMBER,
  COMM     NUMBER,
  DEPTNO   NUMBER
)
PARTITION BY RANGE (HIRDATE)
(  
  PARTITION EMP_RANGE_01 VALUES LESS THAN (TO_DATE(' 1981-01-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')), 
  PARTITION EMP_RANGE_02 VALUES LESS THAN (TO_DATE(' 1982-01-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')),
  PARTITION EMP_RANGE_03 VALUES LESS THAN (MAXVALUE)
);




SQL> insert into emp_range select * from emp;
14 rows inserted


SQL> commit;
Commit complete


SQL> select table_name,partitioning_type,partition_count,status from dba_part_tables where table_name='EMP_RANGE';
TABLE_NAME                     PARTITIONING_TYPE PARTITION_COUNT STATUS
------------------------------ ----------------- --------------- --------
EMP_RANGE                      RANGE                           3 VALID


SQL>  select partition_name,num_rows,tablespace_name,segment_created from dba_tab_partitions where table_name='EMP_RANGE';
PARTITION_NAME                   NUM_ROWS TABLESPACE_NAME                SEGMENT_CREATED
------------------------------ ---------- ------------------------------ ---------------
EMP_RANGE_01                              USERS                          YES
EMP_RANGE_02                              USERS                          YES
EMP_RANGE_03                              USERS                          YES


SQL> select * from emp_range partition(emp_range_01);
     EMPNO ENAME      JOB              MGR HIRDATE            SAL       COMM     DEPTNO
---------- ---------- --------- ---------- ----------- ---------- ---------- ----------
      7369 SMITH      CLERK           7902 1980/12/17         800                    20


 
2.HASH分区(hash partition)
HASH分区特点：
hash分区主要是通过hash算法确定相应数据行应该被存放到哪个分区中。
hash分区比较合列差异值很多的数据列。
HASH分区的注意事项：
对于HASH分区，无法控制一条数据在分区之间的具体分布。具体分布由hash算法决定。
对于hash分区，如果更改分区的数量，将导致所有数据在分区间的重新分布。
HASH分区定义规则：
在定义HASH分区时，其分区数量应为2的N次方，比如：2,4,8,16等。
Hash分区能够很容易对数据进行分区，因为语法很简单，很容易实现。在下面这种情况下，使用hash分区比range分区更好：
（1）事先不知道需要将多少数据映射到给定范围的时候。
（2）分区的范围大小很难确实，或者很难平衡的时候。
（3）Range分区使诗句得到不希望的聚集时。
（4）性能特性，如并行DML、分区剪枝和分区连接很重要的时候
创建散列分区时，必须指定以下信息：
分区方法：hash
分区列：
分区数量或单独的分区描述
分裂、删除和合并分区不能应用于hash分区，但是，hash分区能够合并和添加。
创建hash分区有两种方法：一种方法是指定分区数量，另一种方法是指定分区的名字,但两者不能同时指定。
方法一：指定分区数量：
CREATE TABLE SCOTT.EMP_hash_1
(
  EMPNO    NUMBER,
  ENAME    VARCHAR2(10 BYTE),
  JOB      VARCHAR2(9 BYTE),
  MGR      NUMBER,
  HIRDATE  DATE,
  SAL      NUMBER,
  COMM     NUMBER,
  DEPTNO   NUMBER
)
PARTITION BY hash (ename) partitions 2;
方法二：指定分区的名字：
CREATE TABLE SCOTT.EMP_hash_2
(
  EMPNO    NUMBER,
  ENAME    VARCHAR2(10 BYTE),
  JOB      VARCHAR2(9 BYTE),
  MGR      NUMBER,
  HIRDATE  DATE,
  SAL      NUMBER,
  COMM     NUMBER,
  DEPTNO   NUMBER
)
PARTITION BY hash (ename)
(partition hash_01,
partition hash_02);




示例：
SQL> CREATE TABLE SCOTT.EMP_hash_2
  2  (
  3    EMPNO    NUMBER,
  4    ENAME    VARCHAR2(10 BYTE),
  5    JOB      VARCHAR2(9 BYTE),
  6    MGR      NUMBER,
  7    HIRDATE  DATE,
  8    SAL      NUMBER,
  9    COMM     NUMBER,
 10    DEPTNO   NUMBER
 11  )
 12  PARTITION BY hash (ename)
 13  (partition hash_01,
 14  partition hash_02);
Table created


SQL> insert into emp_hash_2 select * from emp;
14 rows inserted
SQL> select * from emp_hash_2
  2  ;
     EMPNO ENAME      JOB              MGR HIRDATE            SAL       COMM     DEPTNO
---------- ---------- --------- ---------- ----------- ---------- ---------- ----------
      7499 ALLEN      SALESMAN        7698 1981/2/20         1600        300         30
      7521 WARD       SALESMAN        7698 1981/2/22         1250        500         30
      7654 MARTIN     SALESMAN        7698 1981/9/28         1250       1400         30
      7698 BLAKE      MANAGER         7839 1981/5/1          2850                    30
      7844 TURNER     SALESMAN        7698 1981/9/8          1500          0         30
      7876 ADAMS      CLERK           7788 1987/5/23         1100                    20
      7900 JAMES      CLERK           7698 1981/12/3          950                    30
      7934 MILLER     CLERK           7782 1982/1/23         1300                    10
      7369 SMITH      CLERK           7902 1980/12/17         800                    20
      7566 JONES      MANAGER         7839 1981/4/2          2975                    20
      7782 CLARK      MANAGER         7839 1981/6/9          2450                    10
      7788 SCOTT      ANALYST         7566 1987/4/19         3000                    20
      7839 KING       PRESIDENT            1981/11/17        5000                    10
      7902 FORD       ANALYST         7566 1981/12/3         3000                    20
14 rows selected
SQL> select * from emp_hash_2 partition(HASH_01);
     EMPNO ENAME      JOB              MGR HIRDATE            SAL       COMM     DEPTNO
---------- ---------- --------- ---------- ----------- ---------- ---------- ----------
      7499 ALLEN      SALESMAN        7698 1981/2/20         1600        300         30
      7521 WARD       SALESMAN        7698 1981/2/22         1250        500         30
      7654 MARTIN     SALESMAN        7698 1981/9/28         1250       1400         30
      7698 BLAKE      MANAGER         7839 1981/5/1          2850                    30
      7844 TURNER     SALESMAN        7698 1981/9/8          1500          0         30
      7876 ADAMS      CLERK           7788 1987/5/23         1100                    20
      7900 JAMES      CLERK           7698 1981/12/3          950                    30
      7934 MILLER     CLERK           7782 1982/1/23         1300                    10
8 rows selected
SQL> analyze table emp_hash_2 compute statistics;
Table analyzed


SQL> select partition_name,num_rows,tablespace_name,segment_created from dba_tab_partitions where table_name='EMP_HASH_2';
PARTITION_NAME                   NUM_ROWS TABLESPACE_NAME                SEGMENT_CREATED
------------------------------ ---------- ------------------------------ ---------------
HASH_01                                 8 USERS                          YES
HASH_02                                 6 USERS                          YES


3.列表分区（list分区）
列表分区特点：
列表分区主要依据分区键定义时给出的取值列表，根据实际的取值，进行分区的选择，进而在相应分区中存储数据。
列表分区比较适合列唯一取值有限，且较为固定的数据列。如：员工表的部门列。
列表分区的数据分布可能不均匀。
列表分区定义规则：
1.在定义范围分区时，每个分区必须使用values('value01','value02')子句。表示该分区存储包含相关value值的数据行。
2.在定义范围分区时，最后一个分区可以是values(default)。表示该分区存储未在其他分区定义的数据行。
list分区可以控制将行映射到分区中去。可以在每个分区的键撒花姑娘定义离散的值。
不同range分区和hash分区，
range分区与分区相关联，为分区列假设了一个值的自然范围，故不可能将该值的范围以外的分区组织到一起。
hash分区时不允许对数据的划分进行控制，因为系统使用的是散列函数来划分数据。
list分区的优点在于按照自然的方式将无序和不行馆的数据集合分组。
list分区不支持多列分区，如果将表按列分区，那么分区键就只能有表的一个单独列组成。
range分区和hash分区可以对多列进行分区。
示例：
SQL> conn sys/oracle@rac
Connected to Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 
Connected as sys@RAC AS SYSDBA
SQL> create tablespace list_01 datafile '+DATA' size 100M;
SQL> create tablespace list_02 datafile '+DATA' size 100M;
SQL> create tablespace list_03 datafile '+DATA' size 100M;
SQL> create tablespace list_04 datafile '+DATA' size 100M;
SQL> conn scott/scott@rac
Connected to Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 
Connected as scott@rac
SQL> create table list_test(
  2  transaction_id number primary key,
  3  item_id number(8) not null,
  4  item_description varchar2(300))
  5  partition by list (item_id)
  6  (partition partlist_01 values (1001,1002) tablespace list_01,
  7  partition partlist_02 values (1003,1004) tablespace list_02,
  8  partition partlist_03 values (1005) tablespace list_03,
  9  partition  values (default) tablespace list_04);
Table created
SQL> insert into list_test values (1,1001,'youqi');
1 row inserted
SQL> insert into list_test values (2,1002,'youqi');
1 row inserted
SQL> insert into list_test values (3,1003,'muliao');
1 row inserted
SQL> insert into list_test values (4,1004,'muliao');
1 row inserted
SQL> insert into list_test values (5,1005,'gangcai');
1 row inserted
SQL> insert into list_test values (6,1006,'gangcai');
1 row inserted
SQL> insert into list_test values (8,1008,'gangcai');
1 row inserted
SQL> commit;
Commit complete
SQL> select * from list_test;
TRANSACTION_ID   ITEM_ID ITEM_DESCRIPTION
-------------- --------- --------------------------------------------------------------------------------
             1      1001 youqi
             2      1002 youqi
             3      1003 muliao
             4      1004 muliao
             5      1005 gangcai
             6      1006 gangcai
             8      1008 gangcai
7 rows selected
SQL> select * from list_test partition(partlist_04);
TRANSACTION_ID   ITEM_ID ITEM_DESCRIPTION
-------------- --------- --------------------------------------------------------------------------------
             6      1006 gangcai
             8      1008 gangcai
4.复合分区（子分区）
组合分区的特点：
组合分区中，主要通过在不同列上，使用“范围分区”，“列表分区”以及“HASH分区”不同组合方式，进而实现组合分区。
组合分区中，分区本身没有相应的segment，可以认为是一个逻辑容器，只有子分区拥有实际的segment，用于存放数据。
组合分区注意事项：
在11g之前，组合分区只要有两种组合方式：“range-hash”以及“range-list”。
在11g之后，组合分区新增了四种组合方式：“range-range”、“list-range”、“list-hash"以及”list-list“。


CREATE TABLE sales
  ( prod_id       NUMBER(6)
  , cust_id       NUMBER
  , time_id       DATE
  , channel_id    CHAR(1)
  , promo_id      NUMBER(6)
  , quantity_sold NUMBER(3)
  , amount_sold   NUMBER(10,2)
  )
 PARTITION BY RANGE (time_id) SUBPARTITION BY HASH (cust_id)
  SUBPARTITIONS 8 STORE IN (ts1, ts2, ts3, ts4)
 ( PARTITION sales_q1_2006 VALUES LESS THAN (TO_DATE('01-APR-2006','dd-MON-yyyy'))
 , PARTITION sales_q2_2006 VALUES LESS THAN (TO_DATE('01-JUL-2006','dd-MON-yyyy'))
 , PARTITION sales_q3_2006 VALUES LESS THAN (TO_DATE('01-OCT-2006','dd-MON-yyyy'))
 , PARTITION sales_q4_2006 VALUES LESS THAN (TO_DATE('01-JAN-2007','dd-MON-yyyy'))
 );


关于表分区的一些日常维护性操作：
（1）增加分区（add）
（2）移动分区（move）
（3）截断分区（truncate）
（4）删除分区（drop）
（5）拆分分区（split）
（6）合并分区（merge）----hash分区不适用
（7）交换分区（exchange）
（8）收缩分区（coalesce）---仅适用于hash分区


测试数据来源于scott下的emp表。
--创建分区表TEST_RANGE_PARTITION
SQL>CREATE TABLE SCOTT.EMP_RANGE
(
  EMPNO    NUMBER(4,0),
  ENAME    VARCHAR2(10),
  JOB      VARCHAR2(9),
  MGR      NUMBER(4,0),
  HIRDATE  DATE,
  SAL      NUMBER(7,2),
  COMM     NUMBER(7,2),
  DEPTNO   NUMBER(2,0)
)
PARTITION BY RANGE (SAL)
(partition "TEST_RANGE_SAL_01" values less than (1000),
partition "TEST_RANGE_SAL_02" values less than (2000),
partition "TEST_RANGE_SAL_03" values less than (3000),
partition "TEST_RANGE_SAL_MAX" values less than (MAXVALUE)
);
SQL> insert into emp_range select * from emp;
14 rows inserted
SQL> commit;
Commit complete
通过下面的方法，了解关于上面创建分区表的数据分布基本情况。
---查询分区表各分区的条件以及数据库分布情况
---可以看到此时NUM_ROWS列为空，主要是因为表的统计信息未收集导致的。
SQL> select a.table_name,partitioning_type,partition_name,high_value,num_rows from user_part_tables a,user_tab_partitions b where a.table_name=b.table_name and a.table_name='EMP_RANGE';
TABLE_NAME                     PARTITIONING_TYPE PARTITION_NAME                 HIGH_VALUE                                                                         NUM_ROWS
------------------------------ ----------------- ------------------------------ -------------------------------------------------------------------------------- ----------
EMP_RANGE                      RANGE             TEST_RANGE_SAL_01              1000                                                                             
EMP_RANGE                      RANGE             TEST_RANGE_SAL_02              2000                                                                             
EMP_RANGE                      RANGE             TEST_RANGE_SAL_03              3000                                                                             
EMP_RANGE                      RANGE             TEST_RANGE_SAL_MAX             MAXVALUE                                                                         


SQL> analyze table EMP_RANGE compute statistics;
Table analyzed


SQL> select a.table_name,partitioning_type,partition_name,high_value,num_rows from user_part_tables a,user_tab_partitions b where a.table_name=b.table_name and a.table_name='EMP_RANGE';
TABLE_NAME                     PARTITIONING_TYPE PARTITION_NAME                 HIGH_VALUE                                                                         NUM_ROWS
------------------------------ ----------------- ------------------------------ -------------------------------------------------------------------------------- ----------
EMP_RANGE                      RANGE             TEST_RANGE_SAL_01              1000                                                                                      2
EMP_RANGE                      RANGE             TEST_RANGE_SAL_02              2000                                                                                      6
EMP_RANGE                      RANGE             TEST_RANGE_SAL_03              3000                                                                                      3
EMP_RANGE                      RANGE             TEST_RANGE_SAL_MAX             MAXVALUE                                                                                  3


2.增加分区维护操作（add）
增加分区维护操作，主要针对当前分区表进行添加新分区的操作。
当分区表存在默认条件分区，如：range分区表的maxvalue分区，list分区表的default分区，此时增加分区操作会报错。
下面通过增加分区操作，直接为测试表增加分区TEST_RANGE_SAL_04
SQL> alter table emp_range add partition test_range_sal_04 values less than (4000);
alter table emp_range add partition test_range_sal_04 values less than (4000)
ORA-14074: 分区界限必须调整为高于最后一个分区界限
可以看到针对存在默认条件的分区表，无法执行增加分区操作。
解决办法：
1.删除原默认条件分区，待增加分区后，再重新添加默认条件分区。
2.使用拆分分区（split）的方式。
SQL> alter table emp_range drop partition test_range_sal_max;
Table altered


SQL> analyze table emp_range compute statistics;
Table analyzed


SQL> select a.table_name,partitioning_type,partition_name,high_value,num_rows from user_part_tables a,user_tab_partitions b where a.table_name=b.table_name and a.table_name='EMP_RANGE';
TABLE_NAME                     PARTITIONING_TYPE PARTITION_NAME                 HIGH_VALUE                                                                         NUM_ROWS
------------------------------ ----------------- ------------------------------ -------------------------------------------------------------------------------- ----------
EMP_RANGE                      RANGE             TEST_RANGE_SAL_01              1000                                                                                      2
EMP_RANGE                      RANGE             TEST_RANGE_SAL_02              2000                                                                                      6
EMP_RANGE                      RANGE             TEST_RANGE_SAL_03              3000                                                                                      3


SQL> alter table emp_range add partition test_range_sal_04 values less than (4000);
Table altered


SQL> alter table emp_range add partition test_range_sal_05 values less than (maxvalue);
Table altered


SQL> analyze table emp_range compute statistics;
Table analyzed


SQL> select a.table_name,partitioning_type,partition_name,high_value,num_rows from user_part_tables a,user_tab_partitions b where a.table_name=b.table_name and a.table_name='EMP_RANGE';
TABLE_NAME                     PARTITIONING_TYPE PARTITION_NAME                 HIGH_VALUE                                                                         NUM_ROWS
------------------------------ ----------------- ------------------------------ -------------------------------------------------------------------------------- ----------
EMP_RANGE                      RANGE             TEST_RANGE_SAL_01              1000                                                                                      2
EMP_RANGE                      RANGE             TEST_RANGE_SAL_02              2000                                                                                      6
EMP_RANGE                      RANGE             TEST_RANGE_SAL_03              3000                                                                                      3
EMP_RANGE                      RANGE             TEST_RANGE_SAL_04              4000                                                                                      0
EMP_RANGE                      RANGE             TEST_RANGE_SAL_05              MAXVALUE                                                                                  0


需要注意的是：对于默认条件的分区进行删除，其数据不会分布到其他分区，而是删除数据。因此在生产环境使用需慎重。


3.移动分区维护操作（move）
移动分区操作，只要是将分区从一个表空间迁移至另一个表空间中
查看当前分区对应的表空间情况
SQL> select table_name,partition_name,tablespace_name from user_tab_partitions where table_name='EMP_RANGE';
TABLE_NAME                     PARTITION_NAME                 TABLESPACE_NAME
------------------------------ ------------------------------ ------------------------------
EMP_RANGE                      TEST_RANGE_SAL_01              USERS
EMP_RANGE                      TEST_RANGE_SAL_02              USERS
EMP_RANGE                      TEST_RANGE_SAL_03              USERS
EMP_RANGE                      TEST_RANGE_SAL_04              USERS
EMP_RANGE                      TEST_RANGE_SAL_05              USERS


SQL> alter table emp_range move partition TEST_RANGE_SAL_02 tablespace LIST_01;
Table altered


SQL> select table_name,partition_name,tablespace_name from user_tab_partitions where table_name='EMP_RANGE';
TABLE_NAME                     PARTITION_NAME                 TABLESPACE_NAME
------------------------------ ------------------------------ ------------------------------
EMP_RANGE                      TEST_RANGE_SAL_01              USERS
EMP_RANGE                      TEST_RANGE_SAL_02              LIST_01
EMP_RANGE                      TEST_RANGE_SAL_03              USERS
EMP_RANGE                      TEST_RANGE_SAL_04              USERS
EMP_RANGE                      TEST_RANGE_SAL_05              USERS


4.截断分区维护操作（truncate）
截断分区维护操作，性对于传统的delete操作，删除数据的效率会更高，而且会降低高水位线。
---查看当前测试表分区情况及分区中的记录数
SQL> select table_name,partition_name,tablespace_name,num_rows from user_tab_partitions where table_name='EMP_RANGE';
TABLE_NAME                     PARTITION_NAME                 TABLESPACE_NAME                  NUM_ROWS
------------------------------ ------------------------------ ------------------------------ ----------
EMP_RANGE                      TEST_RANGE_SAL_01              USERS                                   2
EMP_RANGE                      TEST_RANGE_SAL_02              LIST_01                                 6
EMP_RANGE                      TEST_RANGE_SAL_03              USERS                                   3
EMP_RANGE                      TEST_RANGE_SAL_04              USERS                                   0
EMP_RANGE                      TEST_RANGE_SAL_05              USERS                                   0
---执行截断分区操作
SQL> alter table EMP_RANGE truncate partition TEST_RANGE_SAL_02;
Table truncated
---重新手机最新的测试表的统计信息
SQL> analyze table EMP_RANGE compute statistics;
Table analyzed
---验证截断后，分区的记录数的变化
SQL> select table_name,partition_name,tablespace_name,num_rows from user_tab_partitions where table_name='EMP_RANGE';
TABLE_NAME                     PARTITION_NAME                 TABLESPACE_NAME                  NUM_ROWS
------------------------------ ------------------------------ ------------------------------ ----------
EMP_RANGE                      TEST_RANGE_SAL_01              USERS                                   2
EMP_RANGE                      TEST_RANGE_SAL_02              LIST_01                                 0
EMP_RANGE                      TEST_RANGE_SAL_03              USERS                                   3
EMP_RANGE                      TEST_RANGE_SAL_04              USERS                                   0
EMP_RANGE                      TEST_RANGE_SAL_05              USERS                                   0
从上面的演示中可以看到，通过truncate操作，测试表EMP_RANGE_SAL_02分区数据被清空。


5.删除分区维护操作（drop）
对于分区的删除操作，需要注意，在删除分区后，分区所记录的数据，不会重分布至其他分区中，而是一并删除
---检查当前分区表的分区情况，以及数据的分布情况
SQL> select table_name,partition_name,tablespace_name,num_rows from user_tab_partitions where table_name='EMP_RANGE';
TABLE_NAME                     PARTITION_NAME                 TABLESPACE_NAME                  NUM_ROWS
------------------------------ ------------------------------ ------------------------------ ----------
EMP_RANGE                      TEST_RANGE_SAL_01              USERS                                   2
EMP_RANGE                      TEST_RANGE_SAL_02              LIST_01                                 0
EMP_RANGE                      TEST_RANGE_SAL_03              USERS                                   3
EMP_RANGE                      TEST_RANGE_SAL_04              USERS                                   0
EMP_RANGE                      TEST_RANGE_SAL_05              USERS                                   0
---执行分区的删除操作
SQL> alter table EMP_RANGE drop partition TEST_RANGE_SAL_03;
Table altered
---再次检查分区表的分区情况，以及数据的分布情况
SQL>  select table_name,partition_name,tablespace_name,num_rows from user_tab_partitions where table_name='EMP_RANGE';
TABLE_NAME                     PARTITION_NAME                 TABLESPACE_NAME                  NUM_ROWS
------------------------------ ------------------------------ ------------------------------ ----------
EMP_RANGE                      TEST_RANGE_SAL_01              USERS                                   2
EMP_RANGE                      TEST_RANGE_SAL_02              LIST_01                                 0
EMP_RANGE                      TEST_RANGE_SAL_04              USERS                                   0
EMP_RANGE                      TEST_RANGE_SAL_05              USERS                                   0


6.查分分区维护操作（split）
在“增加分区维护操作“部分，提高了对于存在默认条件的分区表增加分区的两种方法，这里讲介绍通过拆分分区的方法来增加分区。
注意：在目标分区拆分后，被拆分的分区会按照拆分规则，将数据进行重新分布。
示例：
SQL> CREATE TABLE SCOTT.EMP_RANGE
  2  (
  3    EMPNO    NUMBER(4,0),
  4    ENAME    VARCHAR2(10),
  5    JOB      VARCHAR2(9),
  6    MGR      NUMBER(4,0),
  7    HIRDATE  DATE,
  8    SAL      NUMBER(7,2),
  9    COMM     NUMBER(7,2),
 10    DEPTNO   NUMBER(2,0)
 11  )
 12  PARTITION BY RANGE (SAL)
 13  (partition "TEST_RANGE_SAL_01" values less than (1000),
 14  partition "TEST_RANGE_SAL_02" values less than (2000),
 15  partition "TEST_RANGE_SAL_03" values less than (3000),
 16  partition "TEST_RANGE_SAL_MAX" values less than (MAXVALUE)
 17  );
Table created


SQL> insert into emp_range select * from emp;
14 rows inserted


SQL> commit;
Commit complete
--重新收集测试表的统计信息
SQL> analyze table emp_range compute statistics;
Table analyzed
--此时查看，数据在分区间的分布情况
SQL> select table_name,partition_name,tablespace_name,num_rows from user_tab_partitions where table_name='EMP_RANGE';
TABLE_NAME                     PARTITION_NAME                 TABLESPACE_NAME                  NUM_ROWS
------------------------------ ------------------------------ ------------------------------ ----------
EMP_RANGE                      TEST_RANGE_SAL_01              USERS                                   2
EMP_RANGE                      TEST_RANGE_SAL_02              USERS                                   6
EMP_RANGE                      TEST_RANGE_SAL_03              USERS                                   3
EMP_RANGE                      TEST_RANGE_SAL_MAX             USERS                                   3
--查看TEST_RANGE_SAL_MAX的具体数据信息
SQL> select * from emp_range partition(TEST_RANGE_SAL_MAX);
EMPNO ENAME      JOB         MGR HIRDATE           SAL      COMM DEPTNO
----- ---------- --------- ----- ----------- --------- --------- ------
 7788 SCOTT      ANALYST    7566 1987/4/19     3000.00               20
 7839 KING       PRESIDENT       1981/11/17    5000.00               10
 7902 FORD       ANALYST    7566 1981/12/3     3000.00               20
需求：
将SAL>=3000且SAL<=4000的数据放入新的分区TEST_RANGE_SAL_04
将SAL>=4000的数据保留在分区TEST_RANGE_SAL_MAX中。
--针对目标分区，执行拆分分区维护操作
--依据上面的需求，将数据拆分至分区TEST_RANGE_SAL_04以及TEST_RANGE_SAL_MAX中。
SQL> alter table emp_range split partition TEST_RANGE_SAL_MAX at (4000) into (partition TEST_RANGE_SAL_04,partition TEST_RANGE_SAL_MAX);
Table altered
--查看此时测试分区表的分区情况，以及数据分布情况
SQL> select table_name,partition_name,tablespace_name,num_rows from user_tab_partitions where table_name='EMP_RANGE';
TABLE_NAME                     PARTITION_NAME                 TABLESPACE_NAME                  NUM_ROWS
------------------------------ ------------------------------ ------------------------------ ----------
EMP_RANGE                      TEST_RANGE_SAL_01              USERS                                   2
EMP_RANGE                      TEST_RANGE_SAL_02              USERS                                   6
EMP_RANGE                      TEST_RANGE_SAL_03              USERS                                   3
EMP_RANGE                      TEST_RANGE_SAL_04              USERS                          
EMP_RANGE                      TEST_RANGE_SAL_MAX             USERS                          
--重新收集测试表的统计信息
SQL> analyze table emp_range compute statistics;
Table analyzed
--查看此时测试分区表的分区情况，以及数据分布情况
SQL> select table_name,partition_name,tablespace_name,num_rows from user_tab_partitions where table_name='EMP_RANGE';
TABLE_NAME                     PARTITION_NAME                 TABLESPACE_NAME                  NUM_ROWS
------------------------------ ------------------------------ ------------------------------ ----------
EMP_RANGE                      TEST_RANGE_SAL_01              USERS                                   2
EMP_RANGE                      TEST_RANGE_SAL_02              USERS                                   6
EMP_RANGE                      TEST_RANGE_SAL_03              USERS                                   3
EMP_RANGE                      TEST_RANGE_SAL_04              USERS                                   2
EMP_RANGE                      TEST_RANGE_SAL_MAX             USERS                                   1
--验证分区中实际的数据内容
SQL> select * from emp_range partition(TEST_RANGE_SAL_04);
EMPNO ENAME      JOB         MGR HIRDATE           SAL      COMM DEPTNO
----- ---------- --------- ----- ----------- --------- --------- ------
 7788 SCOTT      ANALYST    7566 1987/4/19     3000.00               20
 7902 FORD       ANALYST    7566 1981/12/3     3000.00               20


SQL> select * from emp_range partition(TEST_RANGE_SAL_MAX);
EMPNO ENAME      JOB         MGR HIRDATE           SAL      COMM DEPTNO
----- ---------- --------- ----- ----------- --------- --------- ------
 7839 KING       PRESIDENT       1981/11/17    5000.00               10


7.合并分区维护操作（merge）
合并分区操作，主要是将不同的分区，通过分区合并，进行整合。
需要注意：
1）对于list分区，合并的分区无限制要求
2）对于range分区，合并的分区必须相邻，否则无法进行合并操作。
3）对于hash分区，无法进行合并操作。
此外，对于range分区，下限值由边界值较低的分区决定，上限值由边界值较高的分区决定。
演示实例:
通过合并分区技术，将测试表的分区test_range_sal_01以及分区test_range_sal_02进行合并，具体如下：
----查看当前分区表的分区情况：
SQL> select table_name,partition_name,tablespace_name,num_rows from user_tab_partitions where table_name='EMP_RANGE';
TABLE_NAME                     PARTITION_NAME                 TABLESPACE_NAME                  NUM_ROWS
------------------------------ ------------------------------ ------------------------------ ----------
EMP_RANGE                      TEST_RANGE_SAL_01              USERS                                   2
EMP_RANGE                      TEST_RANGE_SAL_02              USERS                                   6
EMP_RANGE                      TEST_RANGE_SAL_03              USERS                                   3
EMP_RANGE                      TEST_RANGE_SAL_04              USERS                                   2
EMP_RANGE                      TEST_RANGE_SAL_MAX             USERS                                   1


SQL> alter table emp_range merge partitions test_range_sal_01,test_range_sal_02 into partition test_range_sal_00;
Table altered


SQL> select table_name,partition_name,tablespace_name,num_rows from user_tab_partitions where table_name='EMP_RANGE';
TABLE_NAME                     PARTITION_NAME                 TABLESPACE_NAME                  NUM_ROWS
------------------------------ ------------------------------ ------------------------------ ----------
EMP_RANGE                      TEST_RANGE_SAL_00              USERS                          
EMP_RANGE                      TEST_RANGE_SAL_03              USERS                                   3
EMP_RANGE                      TEST_RANGE_SAL_04              USERS                                   2
EMP_RANGE                      TEST_RANGE_SAL_MAX             USERS                                   1


SQL> analyze table emp_range compute statistics;
Table analyzed


SQL> select table_name,partition_name,tablespace_name,num_rows from user_tab_partitions where table_name='EMP_RANGE';
TABLE_NAME                     PARTITION_NAME                 TABLESPACE_NAME                  NUM_ROWS
------------------------------ ------------------------------ ------------------------------ ----------
EMP_RANGE                      TEST_RANGE_SAL_00              USERS                                   8
EMP_RANGE                      TEST_RANGE_SAL_03              USERS                                   3
EMP_RANGE                      TEST_RANGE_SAL_04              USERS                                   2
EMP_RANGE                      TEST_RANGE_SAL_MAX             USERS                                   1


8.交换分区维护操作（exchange）
交换分区技术，主要是将一个非分区表的数据同“一个分区表的一个分区”进行数据交换。支持双向交换，既可以从分区表的分区中迁移到分分区表，也可以从非分区表迁移至分区表的分区中。
原则上，非分区表的结构、数据分布等，要复合分区表的目标分区的定义规则。
示例：
---清空测试分区表的数据
SQL> truncate table emp_range;
Table truncated


SQL> select table_name,partition_name,tablespace_name,num_rows from user_tab_partitions where table_name='EMP_RANGE';
TABLE_NAME                     PARTITION_NAME                 TABLESPACE_NAME                  NUM_ROWS
------------------------------ ------------------------------ ------------------------------ ----------
EMP_RANGE                      TEST_RANGE_SAL_00              USERS                                   8
EMP_RANGE                      TEST_RANGE_SAL_03              USERS                                   3
EMP_RANGE                      TEST_RANGE_SAL_04              USERS                                   2
EMP_RANGE                      TEST_RANGE_SAL_MAX             USERS                                   1


SQL> analyze table emp_range compute statistics;
Table analyzed


SQL> select table_name,partition_name,tablespace_name,num_rows from user_tab_partitions where table_name='EMP_RANGE';
TABLE_NAME                     PARTITION_NAME                 TABLESPACE_NAME                  NUM_ROWS
------------------------------ ------------------------------ ------------------------------ ----------
EMP_RANGE                      TEST_RANGE_SAL_00              USERS                                   0
EMP_RANGE                      TEST_RANGE_SAL_03              USERS                                   0
EMP_RANGE                      TEST_RANGE_SAL_04              USERS                                   0
EMP_RANGE                      TEST_RANGE_SAL_MAX             USERS                                   0
--创建一张基于emp表，sal<2000的测试非分区表emp_test
SQL> create table emp_test as select * from emp where sal < 2000;
Table created


SQL> select count(*) from emp_test;
  COUNT(*)
----------
         8
--执行分区交换动作
SQL> alter table emp_range exchange partition test_range_sal_00 with table emp_test;
Table altered




SQL> select table_name,partition_name,tablespace_name,num_rows from user_tab_partitions where table_name='emp_range';
TABLE_NAME                     PARTITION_NAME                 TABLESPACE_NAME                  NUM_ROWS
------------------------------ ------------------------------ ------------------------------ ----------


SQL> select table_name,partition_name,tablespace_name,num_rows from user_tab_partitions where table_name='EMP_RANGE';
TABLE_NAME                     PARTITION_NAME                 TABLESPACE_NAME                  NUM_ROWS
------------------------------ ------------------------------ ------------------------------ ----------
EMP_RANGE                      TEST_RANGE_SAL_00              USERS                          
EMP_RANGE                      TEST_RANGE_SAL_03              USERS                                   0
EMP_RANGE                      TEST_RANGE_SAL_04              USERS                                   0
EMP_RANGE                      TEST_RANGE_SAL_MAX             USERS                                   0


SQL> analyze table emp_range compute statistics;
Table analyzed


SQL> select table_name,partition_name,tablespace_name,num_rows from user_tab_partitions where table_name='EMP_RANGE';
TABLE_NAME                     PARTITION_NAME                 TABLESPACE_NAME                  NUM_ROWS
------------------------------ ------------------------------ ------------------------------ ----------
EMP_RANGE                      TEST_RANGE_SAL_00              USERS                                   8
EMP_RANGE                      TEST_RANGE_SAL_03              USERS                                   0
EMP_RANGE                      TEST_RANGE_SAL_04              USERS                                   0
EMP_RANGE                      TEST_RANGE_SAL_MAX             USERS                                   0


SQL> select count(*) from emp_test;
  COUNT(*)
----------
         0
---再次执行分区交换动作，查看分区表和非分区表的记录变化
SQL> alter table emp_range exchange partition test_range_sal_00 with table emp_test;
Table altered


SQL> analyze table emp_range compute statistics;
Table analyzed


SQL> select table_name,partition_name,tablespace_name,num_rows from user_tab_partitions where table_name='EMP_RANGE';
TABLE_NAME                     PARTITION_NAME                 TABLESPACE_NAME                  NUM_ROWS
------------------------------ ------------------------------ ------------------------------ ----------
EMP_RANGE                      TEST_RANGE_SAL_00              USERS                                   0
EMP_RANGE                      TEST_RANGE_SAL_03              USERS                                   0
EMP_RANGE                      TEST_RANGE_SAL_04              USERS                                   0
EMP_RANGE                      TEST_RANGE_SAL_MAX             USERS                                   0


SQL> select count(*) from emp_test;
  COUNT(*)
----------
         8
可以看到，此时分区表的数据又再次转移回非分区表，证明了前面所述，分区交换技术，即可以从分区表中迁移到非分区表，也可以从非分区表迁移至分区表的分区中。


--若非分区表的数据，不符合分区表的规则，此时交换分区会抛出ora-14099的错误
SQL> truncate table emp_test;
Table truncated


SQL> insert into emp_test select * from emp;
14 rows inserted


SQL> commit;
Commit complete


SQL> alter table emp_range exchange partition test_range_sal_00 with table emp_test;
alter table emp_range exchange partition test_range_sal_00 with table emp_test
ORA-14099: 表中不是所有行都符合所指定的分区
解决办法：
通过without validation子句，可以避免数据校验，而交换成功，但会存在与分区规则不符的数据，因此该方法需慎重。
SQL> alter table emp_range exchange partition test_range_sal_00 with table emp_test without validation;
Table altered


SQL> select table_name,partition_name,tablespace_name,num_rows from user_tab_partitions where table_name='EMP_RANGE';
TABLE_NAME                     PARTITION_NAME                 TABLESPACE_NAME                  NUM_ROWS
------------------------------ ------------------------------ ------------------------------ ----------
EMP_RANGE                      TEST_RANGE_SAL_00              USERS                                   8
EMP_RANGE                      TEST_RANGE_SAL_03              USERS                                   0
EMP_RANGE                      TEST_RANGE_SAL_04              USERS                                   0
EMP_RANGE                      TEST_RANGE_SAL_MAX             USERS                                   0


SQL> analyze table emp_range compute statistics;
Table analyzed


SQL> select table_name,partition_name,tablespace_name,num_rows from user_tab_partitions where table_name='EMP_RANGE';
TABLE_NAME                     PARTITION_NAME                 TABLESPACE_NAME                  NUM_ROWS
------------------------------ ------------------------------ ------------------------------ ----------
EMP_RANGE                      TEST_RANGE_SAL_00              USERS                                  14
EMP_RANGE                      TEST_RANGE_SAL_03              USERS                                   0
EMP_RANGE                      TEST_RANGE_SAL_04              USERS                                   0
EMP_RANGE                      TEST_RANGE_SAL_MAX             USERS                                   0
若打算采用交换分区的方法，以实现分区表到分区表的转换，可以采用先创建一个只有默认条件的单一分区的表分区，在分区交换数据后，根据实际需要，通过前面提到的“拆分分区”的方法进行分区操作。


9.收缩分区维护操作（coalesce）
收缩分区维护操作，仅仅可以在hash分区以及组合分区的hash子分区上进行使用。
通过使用收缩分区技术，可以收缩当前hash分区的分区数量。
对于hash分区的数据，在收缩过程中，oracle会自动完成数据在分区键的重分布。
示例：
SQL> CREATE TABLE SCOTT.EMP_HASH
  2  (
  3    EMPNO    NUMBER(4,0),
  4    ENAME    VARCHAR2(10),
  5    JOB      VARCHAR2(9),
  6    MGR      NUMBER(4,0),
  7    HIRDATE  DATE,
  8    SAL      NUMBER(7,2),
  9    COMM     NUMBER(7,2),
 10    DEPTNO   NUMBER(2,0)
 11  )
 12  PARTITION BY hash(ename)
 13  (
 14  partition employee_part01,
 15  partition employee_part02
 16  );
Table created


SQL> insert into emp_hash select * from emp;
14 rows inserted


SQL> commit;
Commit complete


SQL> analyze table emp_hash compute statistics;
Table analyzed




SQL> select table_name,partition_name,tablespace_name,num_rows from user_tab_partitions where table_name='EMP_HASH';
TABLE_NAME                     PARTITION_NAME                 TABLESPACE_NAME                  NUM_ROWS
------------------------------ ------------------------------ ------------------------------ ----------
EMP_HASH                       EMPLOYEE_PART01                USERS                                   8
EMP_HASH                       EMPLOYEE_PART02                USERS                                   6


SQL> alter table emp_hash coalesce partition;
Table altered


SQL> select table_name,partition_name,tablespace_name,num_rows from user_tab_partitions where table_name='EMP_HASH';
TABLE_NAME                     PARTITION_NAME                 TABLESPACE_NAME                  NUM_ROWS
------------------------------ ------------------------------ ------------------------------ ----------
EMP_HASH                       EMPLOYEE_PART01                USERS                                   8
需要注意：
当hash分区中只有一个分区时，此时无法进行收缩操作。
SQL> alter table emp_hash coalesce partition;
alter table emp_hash coalesce partition
ORA-14285: 不能 COALESCE (结合) 此散列分区的表或索引的唯一分区