分区表-转载

Chenzw's Blog:http://blog.youkuaiyun.com/ziwen00/article/details/9158725

假设我们现在正在酝酿经营一家图书馆，最初，我们只有十本书提供给大家来阅读和购买。对于十本书而言，我们可能只需要一个书架格子将其作为保存这十本书的容器就足够了，因为任何一个人都可以很轻松的扫一眼就可以将这十本书的书名大概记住，并且挑选出合适自己的书籍来看。

    但是随着我们书籍越来越多，我们需要更大的容器来保存我们的科教类、IT类、历史类、人文类等等书籍，大家都知道的一个生活常识就是，我们肯定不能够将所有类型的书籍都扔到一个书架容器上摆着，最科学的，就是分区，将不同的书籍放到不同的地方去，这样，假如我们在西安五路口的新华书店，那么就只需要跑到四楼的IT分区,去找相关的IT书籍。

    在生活中是这个样子，在数据库中呢？

    现在的数据库业务数据和日志数据，以几何级数的速度在增长着。以我之前从事社保行业为例，社保的日志动辄几十G，其实，其中几乎涵盖了从系统试运行到运行一两年之后的数据，这样一个巨大的表，让数据的逻辑备份、查询、甚至基于日志表的回退都产生了巨大的效率问题。

    如何能够快速有效的删除三个月之前的日志信息，如何能够快速的检索当前月份的信息，如何充分利用多块磁盘空间（裸设备）的物理环境，增加并行度平衡I/O从而改善性能增强数据库的可用性？Oracle在8.0版本之后提出的表的分区功能就是一个非常关键的实用技术。

    早在8.0.5版本中，Oracle就将范围分区技术引入，现在分区功能已经越来越强大，包括支持扩展分区功能、Interval分区、外键分区、模拟列分区、以及分区建议器等。那么，分区到底有什么好处呢？我们为什么要使用分区呢？在什么环境下使用分区比较合适呢？在金牌管理群中，有一个哥们请教一个多用户下一张10W频繁表的增加、删除、修改表的问题，很多朋友给出了修改其成为分区表的建议。其实，这张表能否将其设置为分区表，是需要看具体的业务使用环境的，多用户下频繁的增删改查，我认为应当使用全局临时表而非使用分区表，这个并不是分区表的典型应用环境，相反，应当是全局临时表的应用环境，并且在一本参考书中，找到了一位大牛的建议作为佐证：“分区表应用在大表更合适，至少要大于100万条的记录才可以考虑使用分区表”，例如社保系统中，使用分区表解决日志问题冗余问题就是一个最佳使用场景。

    这个结论是非常容易理解的，就类似于我们最前面引入的一个例子，我们开书店，只有十本书，假设未来也只会有那么几本，那么我们为什么要费尽心思的分成不同的区域呢？那岂不是画蛇添足了么？既不经济也不和谐，只有当我们采购的书籍的量非常大的时候，我们才会去考虑使用分区技术来解决查找效率问题。

    假设现在用户希望找到一本《数据结构》的教科书，我们在100W本书籍里面大海捞针的去找快呢？还是在10W本IT类书籍中去查找快呢？答案是显而易见的。当我们只有10本书呢？我们还会建立分区吗？我们直接肉眼全部扫描一下，直接得到这本书就好了。因此我们需要在特定的环境下选择特定的技术解决方案，目的是追求更高的效能，更高的性能带来的必然是用户更高的满意度，更高的满意度带来的必然是更好的经济效益，更好的经济效益带来的必然是图书管理员们更美好的生活。

 

那么就让我们看一下Oracle提供的分区功能到底有什么样子的好处呢？

 

1）由于Oracle数据库可以将分区指定为不同的表空间,而不同的表空间是可以指向不同的磁盘设备的,在很多时候,磁盘设备的I/O速度是系统进行数据操作的瓶颈,而分区并行,则起到了优化物理硬件资源,从而缩短了执行的时间的作用。

2）分区，就类似于我们将图书分类放到了不同的区间中，这个时候，我们就可以只针对其中的某一类图书进行操作，比如进行该类书籍的查询、上架、下架、甚至将该类图书付之一炬，这带来了两个特别大的优势，第一，我们无视其他分区的数据；第二，我们可以很方便的对本区的数据进行删除操作。

3）提高了特定的查询速度，比如我们现在只需要查找IT类图书的数量，因为我们能够在第一意识中，排除其他非IT类书籍，必然会提高查询的性能。

4）节约维护的成本，传统的维护操作，例如重建索引等，因为有锁机制的存在，是会影响到其他人的读取和更新删除操作的，但是由于有了分区，我们在维护IT类书籍的时候，就不会对其他分类的数据造成影响，这个时候，可以降低因为维护数据而对其他系统数据产生的各种影响。

5）从安全的角度上而言，我们将不同的分区放在不同的表空间当中，就类似于我们没有将所有的鸡蛋放到一个篮子里面，必然会带来安全方面的提升。

 

Oracle提供了下面几种比较常见的分区方式：

 

1）范围分区（partition by range）

 

    我们可以想象范围分区的前提，假如我们现在有100W本书，如果分成10个分区，能够比较平均的将每一类维持在10W本左右，是最理想不过了，这样看起来即工整又漂亮。因此，我们在建立范围分区的时候，如何选择范围分区的依据从而能够让数据均匀分布，是一个需要重点关注的问题。

    在很多情况下，例如上述提到的社保系统中的日志表，使用日期进行分类就是一个比较好的分区方案，因为每个月产生的数据量都是大致相同的。下面，我们通过脚本来创建一个基于月份的范围分区表（假设插入的数据全部都是2013年的数据）：

DROP TABLE PART_LOG_CHENZW PURGE;

 

CREATE TABLE PART_LOG_CHENZW(

       LOG_ID NUMBER(20) PRIMARY KEY,

       LOG_DATE DATE,

       LOG_DESC VARCHAR2(20)

)

PARTITION BY RANGE(LOG_DATE)

(

 PARTITION PART_LOG_01 VALUES LESS THAN (TO_DATE('2013-01-01','YYYY-MM-DD')) TABLESPACE DATA,

 PARTITION PART_LOG_02 VALUES LESS THAN (TO_DATE('2013-03-01','YYYY-MM-DD')) TABLESPACE DATA,

 PARTITION PART_LOG_03 VALUES LESS THAN (TO_DATE('2013-05-01','YYYY-MM-DD')) TABLESPACE DATA,

 PARTITION PART_LOG_04 VALUES LESS THAN (TO_DATE('2013-07-01','YYYY-MM-DD')) TABLESPACE DATA,

 PARTITION PART_LOG_05 VALUES LESS THAN (TO_DATE('2013-09-01','YYYY-MM-DD')) TABLESPACE DATA,

 PARTITION PART_LOG_06 VALUES LESS THAN (TO_DATE('2013-10-01','YYYY-MM-DD')) TABLESPACE DATA,

 PARTITION PART_LOG_07 VALUES LESS THAN (MAXVALUE) TABLESPACE DATA

);

 

    上面的例子就是一个使用范围分区的典型例子，在创建表的时候使用PARTITION BY RANGE关键字来指出进行分区的策略,其中PARTITION BY LOG(LOG_DATE)指的是使用LOG_DATE来作为分区的字段,里面根据取值的大小,命名了7个分区来存放数据,每个分区还可以指定不同的表空间,第七个分区使用MAXVALUE来避免有数值没有被上面的范围圈定,这个就类似于SWITCH语法中的DEFAULT,不同的,这个并不是fall through的。

 

    下面，我们来生成10万条数据来插入到表中，查看一下表中发生了什么样子的变化？

INSERT INTO PART_LOG_CHENZW

    (LOG_ID, LOG_DATE, LOG_DESC)

    SELECT LEVEL,

           TO_DATE('2013-01-01', 'YYYY-MM-DD') +

           NUMTODSINTERVAL(CEIL(DBMS_RANDOM.VALUE(0, 365)), 'DAY'),

           LEVEL || 'DESC'

      FROM DUAL

    CONNECT BY LEVEL <= 100000;

 

    首先，我们可以查询一下落在7、8月份的日志记录，就如同我们去新华书店只是去查找IT类的书籍一样，我们知道我们应当去那里寻找名字为PART_LOG_05分区的数据：

SELECT COUNT(1) FROM PART_LOG_CHENZW PARTITION (PART_LOG_05);

下面是在我本机的显示结果：

06:50:40 ChenZw> SELECT COUNT(1) FROM PART_LOG_CHENZW PARTITION (PART_LOG_05);

 

  COUNT(1)

----------

     16981

    通过上面的结果可以很明显的看出，有16981条数据落在了7、8月份的分区中。我们通过指定分区，将全部的注意力只放到此1.6W条左右的数据而不是全部的10W条数据上，可以很明显带来效能的提升。

 

    有一个问题随之而来，我们应当如何知道，图书馆里面都有什么样子的分区呢？我们如何才能知道正确的分区呢？在新华书店中，往往在上电梯的时候，都会有一个指示牌，告诉你，IT类书籍在4楼，人文社科类图书在1楼....其实，Oracle数据库也提供了这个指引牌，那就是下面两个数据字典：DBA_PART_TABLES和DBA_TAB_PARTITIONS，下面我们就翻一下两个“指示牌”中的内容：

 

SQL> 

SELECT T.OWNER AS "所有者",

       T.TABLE_NAME AS "表名",

       T.PARTITIONING_TYPE AS "分区类型",

       T.SUBPARTITIONING_TYPE AS "子分区类型",

       T.PARTITION_COUNT AS "子分区数量",

       T.PARTITIONING_KEY_COUNT AS "分区键中列的数量",

       T.SUBPARTITIONING_KEY_COUNT AS "子分区键中列的数量",

       T.STATUS AS "分区表状态",

       T.DEF_TABLESPACE_NAME AS "默认表空间"

  FROM DBA_PART_TABLES T

 WHERE T.TABLE_NAME IN ('PART_LOG_CHENZW');

 

所有者    表名               分区类型  子分区类型 子分区数量 分区键中列的数量 子分区键中列的数量 分区表状态 默认表空间

-------- -----------------  ------- ---------- ---------- -------------- ---------------- ---------- ------------------------------

CHENZW   PART_LOG_CHENZW    RANGE     NONE     7          1              0                VALID      DATA

 

    通过上面我们可以看到，表PART_LOG_CHENZW现在有7个分区，但是，我们并不能知道这7个分区都是存放什么样子的数据的？我们到什么地方去找到我们希望的7、8月的数据呢？

SQL> 

SELECT T.TABLE_OWNER AS "所有者",

      T.TABLE_NAME AS "表名",

      T.COMPOSITE AS "是否组合分区",

      T.PARTITION_NAME AS "分区名",

      T.SUBPARTITION_COUNT AS "子分区数",

      T.HIGH_VALUE AS "分区上限",

      T.HIGH_VALUE_LENGTH AS "分区上限长度",

      T.PARTITION_POSITION AS "分区在表中位置",

      T.TABLESPACE_NAME AS "所在表空间"

 FROM DBA_TAB_PARTITIONS T

WHERE T.TABLE_NAME IN ('PART_LOG_CHENZW')

/

 

所有者  表名             是否组合分区 分区名         子分区数 分区上限                                                                         分区上限长度 分区在表中位置 所在表空间

------- ---------------- ------------ ------------ ---------- -------------------------------------------------------------------------------- ------------ -------------- ----------

CHENZW  PART_LOG_CHENZW  NO           PART_LOG_01           0 TO_DATE(' 2013-01-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA           83              1 DATA

CHENZW  PART_LOG_CHENZW  NO           PART_LOG_02           0 TO_DATE(' 2013-03-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA           83              2 DATA

CHENZW  PART_LOG_CHENZW  NO           PART_LOG_03           0 TO_DATE(' 2013-05-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA           83              3 DATA

CHENZW  PART_LOG_CHENZW  NO           PART_LOG_04           0 TO_DATE(' 2013-07-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA           83              4 DATA

CHENZW  PART_LOG_CHENZW  NO           PART_LOG_05           0 TO_DATE(' 2013-09-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA           83              5 DATA

CHENZW  PART_LOG_CHENZW  NO           PART_LOG_06           0 TO_DATE(' 2013-10-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA           83              6 DATA

CHENZW  PART_LOG_CHENZW  NO           PART_LOG_07           0 MAXVALUE                                                                                    8              7 DATA

 

7 rows selected

    从上面的结果可以看到，如果我们希望找到7、8月份的数据，就可以根据分区上限定位到PART_LOG_05分区。

 

    最后，我们可以通过查询数据字典USER_SEGMENTS来查看分区表占用的磁盘空间信息，如下：

SELECT T.SEGMENT_NAME AS "段名",

       T.PARTITION_NAME AS "分区名",

       T.SEGMENT_TYPE AS "分区类型",

       T.BYTES / POWER(1024, 2) || 'M' AS "分区大小",

       T.TABLESPACE_NAME AS "表空间"

  FROM USER_SEGMENTS T

 WHERE T.SEGMENT_NAME IN ('PART_LOG_CHENZW');

 

    得到的结果如下：

段名             分区名       分区类型           分区大小   表空间

---------------- ------------ ------------------ ---------- -------

PART_LOG_CHENZW  PART_LOG_01  TABLE PARTITION    .0625M     DATA

PART_LOG_CHENZW  PART_LOG_02  TABLE PARTITION    .5625M     DATA

PART_LOG_CHENZW  PART_LOG_03  TABLE PARTITION    .5625M     DATA

PART_LOG_CHENZW  PART_LOG_04  TABLE PARTITION    .5625M     DATA

PART_LOG_CHENZW  PART_LOG_05  TABLE PARTITION    .625M      DATA

PART_LOG_CHENZW  PART_LOG_06  TABLE PARTITION    .3125M     DATA

PART_LOG_CHENZW  PART_LOG_07  TABLE PARTITION    .875M      DATA

 

7 rows selected

 

2）列表分区（partition by list）

   假设我们现在只有四种类型的10W本图书,分别为0 教育类、1 IT类、2 人文类、3社科类，只有这四种情况，我们就可以通过图书的类型进行分区。通常情况下，如果你的数据中的某一项是可以被枚举的，那么，此列就可以用作列表分区的分区字段。

DROP TABLE PART_BOOK_CHENZW PURGE;

 

CREATE TABLE PART_BOOK_CHENZW(

       BOOK_ID NUMBER(20) PRIMARY KEY,

       BOOK_DATE DATE,

       BOOK_TYPE NUMBER(2) NOT NULL,

       BOOK_DESC VARCHAR2(20)

)

PARTITION BY LIST(BOOK_TYPE)

(

 PARTITION PART_BOOK_01 VALUES(0) TABLESPACE DATA,

 PARTITION PART_BOOK_02 VALUES(1) TABLESPACE DATA,

 PARTITION PART_BOOK_03 VALUES(2) TABLESPACE DATA,

 PARTITION PART_BOOK_04 VALUES(3) TABLESPACE DATA

);

 

下面的脚本用于生成相应的数据：

INSERT INTO PART_BOOK_CHENZW

    (BOOK_ID, BOOK_DATE, BOOK_TYPE,BOOK_DESC)

    SELECT LEVEL,

           TO_DATE('2013-01-01', 'YYYY-MM-DD') +

           NUMTODSINTERVAL(CEIL(DBMS_RANDOM.VALUE(0, 365)), 'DAY'),

           MOD(LEVEL,4),

           LEVEL || 'DESC'

      FROM DUAL

    CONNECT BY LEVEL <= 100000;

 

3）散列分区（partition by hash）

    个人觉着散列分区的更多的作用在于分散数据，通过将数据均匀分布从而规避I/O瓶颈，但是这个在平时的生产中确实比较少用到，所以，建议仅仅作为了解了。

DROP TABLE PART_BOOK_CHENZW PURGE;

 

CREATE TABLE PART_BOOK_CHENZW(

       BOOK_ID NUMBER(20) PRIMARY KEY,

       BOOK_DATE DATE,

       BOOK_TYPE NUMBER(2) NOT NULL,

       BOOK_DESC VARCHAR2(20)

)

PARTITION BY HASH(BOOK_ID)

(

 PARTITION PART_BOOK_01 TABLESPACE DATA,

 PARTITION PART_BOOK_02 TABLESPACE DATA,

 PARTITION PART_BOOK_03 TABLESPACE DATA,

 PARTITION PART_BOOK_04 TABLESPACE DATA

);

 

INSERT INTO PART_BOOK_CHENZW

    (BOOK_ID, BOOK_DATE, BOOK_TYPE,BOOK_DESC)

    SELECT LEVEL,

           TO_DATE('2013-01-01', 'YYYY-MM-DD') +

           NUMTODSINTERVAL(CEIL(DBMS_RANDOM.VALUE(0, 365)), 'DAY'),

           MOD(LEVEL,4),

           LEVEL || 'DESC'

      FROM DUAL

    CONNECT BY LEVEL <= 100000;

 

4）组合分区（partition by xxx subpartition by xxx）

    假如我们现在有10W本月刊类杂志提供给消费者查看和购买，我们如何做分区合适呢？首先我们可以先将图书根据种类进行分区，然后再对每个分区进行月份的分区，这样对于大量的图书而言，我们就可以根据实际的业务需要，将一大堆数据通过分区拆解为若干个小标进行管理，从而降低了管理的粒度，做到“精细化管理”。

 

    下面是一个进行组合分区的例子：

--删除测试表

DROP TABLE PART_BOOK_CHENZW PURGE;

 

--创建列表-范围组合分区

CREATE TABLE PART_BOOK_CHENZW(

       BOOK_ID NUMBER(20) PRIMARY KEY,

       BOOK_DATE DATE,

       BOOK_TYPE NUMBER(2) NOT NULL,

       BOOK_DESC VARCHAR2(20)

)

PARTITION BY LIST(BOOK_TYPE)

SUBPARTITION BY RANGE(BOOK_DATE)

SUBPARTITION TEMPLATE

(

 SUBPARTITION PART_LOG_01 VALUES LESS THAN (TO_DATE('2013-01-01','YYYY-MM-DD')) TABLESPACE DATA,

 SUBPARTITION PART_LOG_02 VALUES LESS THAN (TO_DATE('2013-03-01','YYYY-MM-DD')) TABLESPACE DATA,

 SUBPARTITION PART_LOG_03 VALUES LESS THAN (TO_DATE('2013-05-01','YYYY-MM-DD')) TABLESPACE DATA,

 SUBPARTITION PART_LOG_04 VALUES LESS THAN (TO_DATE('2013-07-01','YYYY-MM-DD')) TABLESPACE DATA,

 SUBPARTITION PART_LOG_05 VALUES LESS THAN (TO_DATE('2013-09-01','YYYY-MM-DD')) TABLESPACE DATA,

 SUBPARTITION PART_LOG_06 VALUES LESS THAN (TO_DATE('2013-10-01','YYYY-MM-DD')) TABLESPACE DATA,

 SUBPARTITION PART_LOG_07 VALUES LESS THAN (MAXVALUE) TABLESPACE DATA

)

(

 PARTITION PART_BOOK_01 VALUES(0) TABLESPACE DATA,

 PARTITION PART_BOOK_02 VALUES(1) TABLESPACE DATA,

 PARTITION PART_BOOK_03 VALUES(2) TABLESPACE DATA,

 PARTITION PART_BOOK_04 VALUES(3) TABLESPACE DATA

);

--生成测试数据

INSERT INTO PART_BOOK_CHENZW

    (BOOK_ID, BOOK_DATE, BOOK_TYPE,BOOK_DESC)

    SELECT LEVEL,

           TO_DATE('2013-01-01', 'YYYY-MM-DD') +

           NUMTODSINTERVAL(CEIL(DBMS_RANDOM.VALUE(0, 365)), 'DAY'),

           MOD(LEVEL,4),

           LEVEL || 'DESC'

      FROM DUAL

    CONNECT BY LEVEL <= 100000;

 

上面的组合分区和单一的范围分区有什么区别呢?我们通过查询数据字典看一下:

SQL>

SELECT T.TABLE_OWNER AS "所有者",

      T.TABLE_NAME AS "表名",

      T.COMPOSITE AS "是否组合分区",

      T.PARTITION_NAME AS "分区名",

      T.SUBPARTITION_COUNT AS "子分区数",

      T.HIGH_VALUE AS "分区上限",

      T.HIGH_VALUE_LENGTH AS "分区上限长度",

      T.PARTITION_POSITION AS "分区在表中位置",

      T.TABLESPACE_NAME AS "所在表空间"

 FROM DBA_TAB_PARTITIONS T

WHERE T.TABLE_NAME IN ('PART_BOOK_CHENZW')

/

 

所有者  表名              是否组合分区 分区名          子分区数 分区上限 分区上限长度 分区在表中位置 所在表空间

------- ----------------- ------------ ------------- ---------- -------- ------------ -------------- -----------

CHENZW  PART_BOOK_CHENZW  YES          PART_BOOK_01           7 0                   1              1 DATA

CHENZW  PART_BOOK_CHENZW  YES          PART_BOOK_02           7 1                   1              2 DATA

CHENZW  PART_BOOK_CHENZW  YES          PART_BOOK_03           7 2                   1              3 DATA

CHENZW  PART_BOOK_CHENZW  YES          PART_BOOK_04           7 3                   1              4 DATA

重点在上述分区的“子分区数”，我们可以看到，每一个按照列表的分区下面，又被按照日期被分为7个分区。