http://www.cnblogs.com/yumiko/p/6095036.html

前言:
    本文着重阐述分区表的概念、优势，以及常用分区表的使用方法及使用过程中的注意事项。
    对于分区表的日常维护方法，如：索引的维护、分区的合并等，将在后续文章中再详细介绍。
    本文涉及的相应演示，使用的数据库版本为oracle 11.2.0.4。
    本文常用分区表的使用方法，主要以较为常规的范围分区、列表分区、hash分区以及组合分区为主。
    本文主要涉及的数据字典视图包括XX_TAB_PARTITIONS、XX_TAB_SUBPARTITIONS以及XX_PART_TABLES。
    注意示例中，使用红色标记的说明。

1、分区表的概述
    分区表就是通过使用分区技术，将一张大表，拆分成多个表分区（独立的segment），从而提升数据访问的性能，以及日常的可维护性。
    分区表中，每个分区的逻辑结构必须相同。如：列名、数据类型。
    分区表中，每个分区的物理存储参数可以不同。如：各个分区所在的表空间。
    对于应用而言完全透明，分区前后没有变化，不需要进行修改。

需要注意：虽然各个分区可以存放在不同的表空间中，但这些表空间所使用的块大小（block_size）必须一致。
需要注意：除了包含LONG以及LONG RAW字段的表无法使用分区外，其他表均可以使用分区，包括含有LOB字段的表。

2、分区表的优点
    在维护性方面，可以在分区级别，针对单独的分区，进行索引的维护、数据的加载以及备份恢复等操作。大大降低了维护时长。
    在可用性方面，由于各个分区相对独立，当一个分区处于维护或者出现故障时，不会影响到其他分区的正常使用。
    在性能方面，oracle对于用户的请求，只检索需要的分区，从而提升性能。
    在其他方面，由于分区表对于用户是透明的，因此，不需要在分区后，对代码进行修改。

3、分区键的简介
    分区键就是决定表中的数据行，属于哪一个分区的一组数据列。在执行DML操作时，ORACLE会根据分区键选择分区。

4、常用分区表简介及使用方法（含注意事项）
4.1、范围分区（range partition）
范围分区特点：
范围分区主要依据分区键定义时给出的键值范围，根据实际的取值，进行分区的选择，进而在相应分区中存储数据。
范围分区比较合适存在以数字为导向，方便进行数字范围划分的数据列。如：员工表的雇佣日期列、工资列等。
范围分区的数据分布可能不均匀。

范围分区定义规则：
1、在定义范围分区时，每个分区定义必须使用 values less than（value）子句。其中（value）表示该分区的上限值。
2、在定义范围分区时，最后一个分区可以是values less than（maxvalue）。其中（maxvalue）表示该分区存储高于其他分区上限值的数据行。

示例：
示例涉及的测试数据源，来自于ORACLE数据库的测试用户scott下的emp表。
emp表的大致数据情况如下：

SQL>select count(*) from emp;

COUNT(*)
----------
        14

SQL>select * from emp;

     EMPNO ENAME      JOB              MGR HIREDATE              SAL       COMM     DEPTNO
---------- ---------- --------- ---------- -------------- ---------- ---------- ----------
      7369 SMITH      CLERK           7902 17-12月-80            800                    20
      7499 ALLEN      SALESMAN        7698 20-2月 -81           1600        300         30
      7521 WARD       SALESMAN        7698 22-2月 -81           1250        500         30
      7566 JONES      MANAGER         7839 02-4月 -81           2975                    20
      7654 MARTIN     SALESMAN        7698 28-9月 -81           1250       1400         30
      7698 BLAKE      MANAGER         7839 01-5月 -81           2850                    30
      7782 CLARK      MANAGER         7839 09-6月 -81           2450                    10
      7788 SCOTT      ANALYST         7566 19-4月 -87           3000                    20
      7839 KING       PRESIDENT            17-11月-81           5000                    10
      7844 TURNER     SALESMAN        7698 08-9月 -81           1500          0         30
      7876 ADAMS      CLERK           7788 23-5月 -87           1100                    20
      7900 JAMES      CLERK           7698 03-12月-81            950                    30
      7902 FORD       ANALYST         7566 03-12月-81           3000                    20
      7934 MILLER     CLERK           7782 23-1月 -82           1300                    10

已选择14行。

下面采用范围分区的方法创建分区，并将emp表的数据导入该分区表。

这里，使用HIREDATE列作为分区键进行分区操作。
建议，使用dbms_metadata.get_ddl的方法进行emp表结构创建语法的提取工作，并进而修改。

SQL> select dbms_metadata.get_ddl('TABLE','EMP','SCOTT') from dual;

DBMS_METADATA.GET_DDL('TABLE','EMP','SCOTT')
--------------------------------------------------------------------------------

CREATE TABLE "SCOTT"."EMP"
   (    "EMPNO" NUMBER(4,0),
        "ENAME" VARCHAR2(10),
        "JOB" VARCHAR2(9),
        "MGR" NUMBER(4,0),
        "HIREDATE" DATE,
        "SAL" NUMBER(7,2),
        "COMM" NUMBER(7,2),
        "DEPTNO" NUMBER(2,0),
         CONSTRAINT "PK_EMP" PRIMARY KEY ("EMPNO")
USING INDEX PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS
STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1
BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
TABLESPACE "USERS" ENABLE,
         CONSTRAINT "FK_DEPTNO" FOREIGN KEY ("DEPTNO")
          REFERENCES "SCOTT"."DEPT" ("DEPTNO") ENABLE
   ) SEGMENT CREATION IMMEDIATE
PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255
NOCOMPRESS LOGGING
STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1
BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
TABLESPACE "USERS"

SQL>CREATE TABLE "EMPLOYEE_RANGE_PART"
( "EMPNO" NUMBER(4,0),
    "ENAME" VARCHAR2(10),
    "JOB" VARCHAR2(9),
    "MGR" NUMBER(4,0),
    "HIREDATE" DATE,
    "SAL" NUMBER(7,2),
    "COMM" NUMBER(7,2),
    "DEPTNO" NUMBER(2,0)
)
PARTITION BY RANGE (HIREDATE)
(
PARTITION EMPLOYEE_PART01 VALUES LESS THAN (TO_DATE('1981-01-01','yyyy-mm-dd'))
TABLESPACE USERS,
PARTITION EMPLOYEE_PART02 VALUES LESS THAN (TO_DATE('1982-01-01','yyyy-mm-dd'))
TABLESPACE USERS,
PARTITION EMPLOYEE_PART03 VALUES LESS THAN (MAXVALUE)
TABLESPACE USERS
);

表已创建。

SQL >insert into EMPLOYEE_RANGE_PART select * from emp;
已创建 14 行。

SQL >commit;

提交完成。

查看此时分区表的状态，以及emp表的数据在分区表中的分布情况。

注意，新建的数据表，统计信息未必收集，可通过analyze table tablename compute statistics进行收集。

SQL >select TABLE_NAME,PARTITIONING_TYPE,PARTITION_COUNT,STATUS from user_part_tables where TABLE_NAME='EMPLOYEE_RANGE_PART';

TABLE_NAME                     PARTITION PARTITION_COUNT STATUS
------------------------------ --------- --------------- --------
EMPLOYEE_RANGE_PART            RANGE 3 VALID

SQL >select PARTITION_NAME,NUM_ROWS,TABLESPACE_NAME,SEGMENT_CREATED from user_tab_partitions where TABLE_NAME='EMPLOYEE_RANGE_PART';

PARTITION_NAME                   NUM_ROWS TABLESPACE_NAME                SEGM
------------------------------ ---------- ------------------------------ ----
EMPLOYEE_PART03 3 TEST_TBS_03                    YES
EMPLOYEE_PART02 10 TEST_TBS_02                    YES
EMPLOYEE_PART01 1 TEST_TBS_01                    YES

查询单独分区的数据信息。
SQL >select * from EMPLOYEE_RANGE_PART partition(EMPLOYEE_PART01);

     EMPNO ENAME      JOB              MGR HIREDATE              SAL       COMM     DEPTNO
---------- ---------- --------- ---------- -------------- ---------- ---------- ---------- 7369 SMITH      CLERK 7902 17-12月-80 800 20

4.2、列表分区（list partition）
列表分区特点：列表分区主要依据分区键定义时给出的取值列表，根据实际的取值，进行分区的选择，进而在相应分区中存储数据。
列表分区比较合适列唯一取值有限，且较为固定的数据列。如：员工表的部门列。
列表分区的数据分布可能不均匀。

列表分区定义规则：
1、在定义范围分区时，每个分区定义必须使用 values（'value01','value02'....）子句。表示该分区存储包含相关value值的数据行。
2、在定义范围分区时，最后一个分区可以是values（DEFAULT）。表示该分区存储未在其他分区定义的数据行。

示例：
本示例数据来源，与上一节相同，均为emp表。
本示例中，将使用JOB列作为分区键进行分区操作。

首先，看一下JOB列中，目前涉及的工作分类有哪些。
SQL >select job,count(*) job from emp group by job ;

JOB              JOB
--------- ----------
CLERK 4
SALESMAN 4
PRESIDENT 1
MANAGER 3
ANALYST 2

从上面的信息可以看出，目前涉及五种职位。
下面采用列表分区的方法进行分区表的创建，并倒入emp中的数据。其中，涉及PRESIDENT，MANAGER以及ANALYST三种职位的数据，存放在分区一；涉及CLERK职位的数据，存放在分区二；
涉及SALESMAN职位的数据，以及未来可能出现的新职位的数据，存放在分区三；

SQL > CREATE TABLE "EMPLOYEE_LIST_PART"
( "EMPNO" NUMBER(4,0),
    "ENAME" VARCHAR2(10),
    "JOB" VARCHAR2(9),
    "MGR" NUMBER(4,0),
    "HIREDATE" DATE,
    "SAL" NUMBER(7,2),
    "COMM" NUMBER(7,2),
    "DEPTNO" NUMBER(2,0)
)
PARTITION BY LIST (JOB)
(
PARTITION EMPLOYEE_PART01 VALUES ('PRESIDENT','MANAGER','ANALYST')
TABLESPACE USERS,
PARTITION EMPLOYEE_PART02 VALUES ('CLERK')
TABLESPACE USERS,
PARTITION EMPLOYEE_PART03 VALUES (DEFAULT)
TABLESPACE USERS
);

表已创建。

SQL >insert into EMPLOYEE_LIST_PART select * from emp;
已创建 14 行。

SQL>commit;

提交完成。

首先观察一下EMPLOYEE_PART02分区。

SQL >select SEGMENT_NAME,PARTITION_NAME,HEADER_FILE,HEADER_BLOCK,BLOCKS from user_segments where PARTITION_NAME='EMPLOYEE_PART02';

SEGMENT_NAME         PARTITION_NAME       HEADER_FILE HEADER_BLOCK     BLOCKS
-------------------- -------------------- ----------- ------------ ----------
EMPLOYEE_LIST_PART   EMPLOYEE_PART02 10 1169 1024
EMPLOYEE_RANGE_PART EMPLOYEE_PART02 10 145 1024

从上面的查询结果可以看到，前面创建的两张分区表，都采用相同的EMPLOYEE_PART02名字，进行了分区的命名。

虽然分区均处于相同的表空间下，但相互之前并未受到影响。

由此可以得出结论：

1、对于普通分区表，只要不是同一张的分区表，分区的命名可以相同；
2、对于组合分区表，同一张表的子分区命名，不能够相同。（创建时会有提示，此处只给出结论，不再进行演示）

查看此时分区表的状态以及数据在分区间的分布情况:
SQL >select TABLE_NAME,PARTITIONING_TYPE,PARTITION_COUNT,STATUS from user_part_tables where TABLE_NAME='EMPLOYEE_LIST_PART';

TABLE_NAME                     PARTITION PARTITION_COUNT STATUS
------------------------------ --------- --------------- --------
EMPLOYEE_LIST_PART             LIST 3 VALID

SQL >select PARTITION_NAME,NUM_ROWS,TABLESPACE_NAME,SEGMENT_CREATED from user_tab_partitions where TABLE_NAME='EMPLOYEE_LIST_PART';

PARTITION_NAME         NUM_ROWS TABLESPACE_NAME                SEGM
-------------------- ---------- ------------------------------ ----
EMPLOYEE_PART03 4 TEST_TBS_03                    YES
EMPLOYEE_PART02 4 TEST_TBS_02                    YES
EMPLOYEE_PART01 6 TEST_TBS_01                    YES

查询单独分区的数据信息:
SQL >select * from EMPLOYEE_LIST_PART partition(EMPLOYEE_PART01);

     EMPNO ENAME      JOB              MGR HIREDATE              SAL       COMM     DEPTNO
---------- ---------- --------- ---------- -------------- ---------- ---------- ----------
7566 JONES      MANAGER 7839 02-4月 -81 2975 20
7698 BLAKE      MANAGER 7839 01-5月 -81 2850 30
7782 CLARK      MANAGER 7839 09-6月 -81 2450 10
7788 SCOTT      ANALYST 7566 19-4月 -87 3000 20
7839 KING       PRESIDENT 17-11月-81 5000 10
7902 FORD       ANALYST 7566 03-12月-81 3000 20

已选择6行。

4.3、HASH分区（hash partition）
HASH分区特点： HASH分区主要通过hash算法确定相应数据行应该被存放到哪个分区中。
HASH分区比较适合列差异值很多的数据列。

HASH分区的注意事项：
对于HASH分区，无法控制一条数据在分区间的具体分布。具体分布由hash算法决定。对于HASH分区，如果更改分区的数量，将导致所有数据在分区间的重新分布。

HASH分区定义规则：
在定义HASH分区时，其分区数量应为2的N次方，如：2，4，8，16等

示例说明：
此示例的数据源依然来源于emp表。
关于HASH分区的创建，有标准写法以及简易写法。

标准写法示例：
SQL >CREATE TABLE "EMPLOYEE_HASH_PART"
( "EMPNO" NUMBER(4,0),
    "ENAME" VARCHAR2(10),
    "JOB" VARCHAR2(9),
    "MGR" NUMBER(4,0),
    "HIREDATE" DATE,
    "SAL" NUMBER(7,2),
    "COMM" NUMBER(7,2),
    "DEPTNO" NUMBER(2,0)
)
PARTITION BY HASH (ENAME)
(
PARTITION EMPLOYEE_PART01 TABLESPACE users,
PARTITION EMPLOYEE_PART02 TABLESPACE users
);

表已创建。

SQL >insert into EMPLOYEE_HASH_PART select * from emp;
已创建 14 行。

SQL >commit;
提交完成。

查看标准写法下，HASH分区表的状态及数据分布情况
SQL >select TABLE_NAME,PARTITIONING_TYPE,PARTITION_COUNT,STATUS from user_part_tables where TABLE_NAME='EMPLOYEE_HASH_PART';

TABLE_NAME                     PARTITION PARTITION_COUNT STATUS
------------------------------ --------- --------------- --------
EMPLOYEE_HASH_PART             HASH 2 VALID

SQL > select PARTITION_NAME,NUM_ROWS,TABLESPACE_NAME,SEGMENT_CREATED from user_tab_partitions where TABLE_NAME='EMPLOYEE_HASH_PART';

PARTITION_NAME         NUM_ROWS TABLESPACE_NAME                SEGM
-------------------- ---------- ------------------------------ ----
EMPLOYEE_PART02 6 TEST_TBS_02                    YES
EMPLOYEE_PART01 8 TEST_TBS_01                    YES

简单写法示例：
CREATE TABLE "EMPLOYEE_HASH_PART_SAMPLE"
   ( "EMPNO" NUMBER(4,0),
     "ENAME" VARCHAR2(10),
     "JOB" VARCHAR2(9),
     "MGR" NUMBER(4,0),
     "HIREDATE" DATE,
     "SAL" NUMBER(7,2),
     "COMM" NUMBER(7,2),
     "DEPTNO" NUMBER(2,0)
   )
   PARTITION BY HASH (ENAME) PARTITIONS 2
   STORE IN (USERS,USERS);

SQL > select PARTITION_NAME,NUM_ROWS,TABLESPACE_NAME,SEGMENT_CREATED from user_tab_partitions where TABLE_NAME='EMPLOYEE_HASH_PART_SAMPLE';

PARTITION_NAME         NUM_ROWS TABLESPACE_NAME                SEGM
-------------------- ---------- ------------------------------ ----
SYS_P11074 6 TEST_TBS_02                    YES
SYS_P11073 8 TEST_TBS_01                    YES

从上图可以看到，相较于标准写法，简单写法创建的分区名字，由oracle自动指定。

4.4、组合分区（composite partition）
组合分区的特点：
组合分区中，主要通过在不同列上，使用“范围分区”、“列表分区”以及“HASH分区”不同组合方式，进而实现组合分区。
组合分区中，分区本身没有相应的segment，可以认为是一个逻辑容器，只有子分区拥有实际的segment，用于存放数据。

组合分区的注意事项：
在11g以前，组合分区主要有两种组合方式：“RANGE-HASH”以及“RANGE-LIST”。
在11g以后，组合分区新增了四种组合方式：“RANGE-RANGE”、“LIST-RANGE”、“LIST-HASH”以及“LIST-LIST”。

示例说明：
关于本示例的数据源依然选择emp表为参考。
关于本示例的演示，将采用LIST-LIST的组合方式，创建组合分区。
关于其他组合方式效仿即可。

示例：
首先按照部门（deptno）进行分区，针对相同部门，再按照职位（JOB）是否为manager再次进行子分区。具体如下:
SQL >CREATE TABLE "EMPLOYEE_LIST_LIST_PART"
( "EMPNO" NUMBER(4,0),
    "ENAME" VARCHAR2(10),
    "JOB" VARCHAR2(9),
    "MGR" NUMBER(4,0),
    "HIREDATE" DATE,
    "SAL" NUMBER(7,2),
    "COMM" NUMBER(7,2),
    "DEPTNO" NUMBER(2,0)
)
PARTITION BY LIST (DEPTNO)
SUBPARTITION BY LIST (JOB)
(
PARTITION EMPLOYEE_DEPTNO_10 VALUES (10) TABLESPACE users
    ( SUBPARTITION EMPLOYEE_10_JOB_MAGAGER VALUES ('MANAGER'),
      SUBPARTITION EMPLOYEE_10_JOB_DEFAULT VALUES (DEFAULT)
    ),
PARTITION EMPLOYEE_DEPTNO_20 VALUES (20) TABLESPACE users
    ( SUBPARTITION EMPLOYEE_20_JOB_MAGAGER VALUES ('MANAGER'),
      SUBPARTITION EMPLOYEE_20_JOB_DEFAULT VALUES (DEFAULT)
    ),
PARTITION EMPLOYEE_DEPTNO_OTHERS VALUES (DEFAULT) TABLESPACE users
    ( SUBPARTITION EMPLOYEE_30_JOB_MAGAGER VALUES ('MANAGER'),
      SUBPARTITION EMPLOYEE_30_JOB_DEFAULT VALUES (DEFAULT)
    )
);

表已创建。

SQL > insert into EMPLOYEE_LIST_LIST_PART select * from emp;

已创建 14 行。

SQL > commit;

提交完成。

需要注意：关于表空间（tablespace）的指定，可以放在分区一级指定，也可以放在子分区一级指定。

本示例中，表空间的指定，放在了分区一级指定。对于子分区直接套用分区指定。

SQL >select PARTITION_NAME,SUBPARTITION_COUNT,COMPOSITE,NUM_ROWS, TABLESPACE_NAME,SEGMENT_CREATED from user_tab_partitions where TABLE_NAME='EMPLOYEE_LIST_LIST_PART';

PARTITION_NAME          SUBPARTITION_COUNT COM   NUM_ROWS TABLESPACE_NAME SEGM
----------------------- ------------------ --- ---------- --------------- ----
EMPLOYEE_DEPTNO_OTHERS 2 YES 6 TEST_TBS_03 NONE
EMPLOYEE_DEPTNO_20 2 YES 5 TEST_TBS_02 NONE
EMPLOYEE_DEPTNO_10 2 YES 3 TEST_TBS_01 NONE

SQL >select TABLE_NAME,PARTITIONING_TYPE,PARTITION_COUNT, SUBPARTITIONING_TYPE,STATUS from user_part_tables where TABLE_NAME='EMPLOYEE_LIST_LIST_PART';

TABLE_NAME                     PARTITION PARTITION_COUNT SUBPARTIT STATUS
------------------------------ --------- --------------- --------- --------
EMPLOYEE_LIST_LIST_PART LIST 3 LIST      VALID

从上图可以看到，对于组合分区，虽然成功创建了分区，但oracle并未对分区一级，分配实际的segment。

下面再观察下子分区的创建情况：

SQL >select PARTITION_NAME,SUBPARTITION_NAME,TABLESPACE_NAME,NUM_ROWS,SEGMENT_CREATED from USER_TAB_SUBPARTITIONS where TABLE_NAME='EMPLOYEE_LIST_LIST_PART';

PARTITION_NAME          SUBPARTITION_NAME              TABLESPACE_NAME   NUM_ROWS SEG
----------------------- ------------------------------ --------------- ---------- ---
EMPLOYEE_DEPTNO_10      EMPLOYEE_10_JOB_MAGAGER        TEST_TBS_01 1 YES
EMPLOYEE_DEPTNO_10      EMPLOYEE_10_JOB_DEFAULT        TEST_TBS_01 2 YES
EMPLOYEE_DEPTNO_20      EMPLOYEE_20_JOB_MAGAGER        TEST_TBS_02 1 YES
EMPLOYEE_DEPTNO_20      EMPLOYEE_20_JOB_DEFAULT        TEST_TBS_02 4 YES
EMPLOYEE_DEPTNO_OTHERS EMPLOYEE_30_JOB_MAGAGER        TEST_TBS_03 1 YES
EMPLOYEE_DEPTNO_OTHERS EMPLOYEE_30_JOB_DEFAULT        TEST_TBS_03 5 YES

从上图可以明显看到，针对子分区一级，oracle确实分配了实际的segment用于数据的存储。
此外，需要再次强调一点，对于同一张组合分区表而言，子分区的名字不能相同，即使不在相同的分区里！

ITPub博客

ORACLE分区表梳理系列（一）- 分区表概述、分类、使用方法及注意事项

最新文章