10.读书笔记收获不止Oracle之 表设计之分区表

10.读书笔记收获不止Oracle之 表设计之分区表

在数据量日益增长的海量数据库时代，分区表技术显得尤为重要，可以说使用得到与否将决定系统的生死。

 

1. 分区表类型及原理

分区表类型有范围分区、列表分区、HASH分区及组合分区四种。

其中范围分区应用最为广泛，需要重点学习和掌握，而列表分区次之，在某些场合考虑使用组合分区，而HASH分区在应用中使用的场景不多。

1.1         范围分区

范围分区最常见的是按时间列进行分区。

create table range_part_tab(idnumber,deal_date date,area_code number,contents varchar2(4000))

   partition by range(deal_date)

    (

   partition p1 values less than ( TO_DATE('2012-02-01','YYYY-MM-DD')),

   partition p2 values less than (TO_DATE('2012-03-01','YYYY-MM-DD')),

   partition p3 values less than (TO_DATE('2012-04-01','YYYY-MM-DD')),

    partition p4 values less than (TO_DATE('2012-05-01','YYYY-MM-DD')),

   partition p5 values less than (TO_DATE('2012-06-01','YYYY-MM-DD')),

    partition p6 values less than (TO_DATE('2012-07-01','YYYY-MM-DD')),

  partition p7 values less than (TO_DATE('2012-08-01','YYYY-MM-DD')),

  partition p8 values less than (TO_DATE('2012-09-01','YYYY-MM-DD')),

  partition p9 values less than (TO_DATE('2012-10-01','YYYY-MM-DD')),

  partition p10 values less than (TO_DATE('2012-11-01','YYYY-MM-DD')),

   partition p11 values less than (TO_DATE('2012-12-01','YYYY-MM-DD')),

  partition p12 values less than (TO_DATE('2013-01-01','YYYY-MM-DD')),

  partition p_max values less than (maxvalue)

 );

然后进行插入：

SQL> insert into range_part_tab(id,deal_date,area_code,contents)

   selectrownum,to_date(to_char(sysdate-365,'J')+TRUNC(dbms_random.value(0,365)),'J'),

   ceil(dbms_random.value(590,599)),

   rpad('*',400,'*')

   from dual

   connect by rownum<= 100000;

 

100000 rows created.

以上构造了10万条记录插入到分区表中。

Values less than是范围分区特定的语法，用于指明具体的范围。共建立了13个分区。

 

1.2         列表分区

列表分区最常见的分区就是以地区列作为分区。

create table list_part_tab (id number,deal_date date,area_codenumber,contents varchar2(4000))

    partition bylist(area_code)

    (partition p_591 values(591),

    partition p_592 values(592),

    partition p_593 values(593),

    partition p_594 values(594),

    partition p_595 values(595),

    partition p_596 values(596),

    partition p_597 values(597),

    partition p_598 values(598),

    partition p_599 values(599),

    partition p_other values(default)

);

然后插入：

insert into list_part_tab(id,deal_date,area_code,contents)

    selectrownum,to_date(to_char(sysdate-365,'J')+TRUNC(DBMS_RANDOM.VALUE(0,365)),'J'),

    ceil(dbms_random.value(590,599)),

    rpad('*',400,'*')

    from dual

    connect by rownum <=100000;

关键字为partition by list. 建立了10个分区。

 

1.3         散列分区

创建散列分区如下：

create table hash_part_tab (id number,deal_date date,area_codenumber,contents varchar2(4000))

    partition by hash(deal_date)

    partitions 12;

插入10万条：

insert into hash_part_tab(id,deal_date,area_code,contents)

selectrownum,to_date(to_char(sysdate-365,'J')+TRUNC(DBMS_RANDOM.value(0,365)),'J'),

ceil(dbms_random.value(590,599)),

rpad('*',400,'*')

from dual

connect by rownum <= 100000;

关键字：partition by hash.

散列分区与之前两个分区的明显差别在于，没有指定分区名，而仅仅是指定了分区个数。

1.4         组合分区

11g以前主要支持范围-列表和范围-散列这两种组合。实际应用中最常用的是范围-列表（range-list）组合。

create table range_list_part_tab (id number,deal_date date,area_codenumber,contents varchar2(4000))

           partition byrange(deal_date)

           subpartition bylist(area_code)

           subpartition template

           (subpartition p_591values (591),

           subpartition p_592values (592),

           subpartition p_593values (593),

           subpartition p_594values (594),

           subpartition p_595values (595),

           subpartition p_596values (596),

           subpartition p_597values (597),

           subpartition p_598values (598),

           subpartition p_599values (599),

           subpartition p_othervalues (default))

(

           partition p1 valuesless than (to_date('2012-02-01','YYYY-MM-DD')),

           partition p2 valuesless than (to_date('2012-03-01','YYYY-MM-DD')),

           partition p3 valuesless than (to_date('2012-04-01','YYYY-MM-DD')),

           partition p4 valuesless than (to_date('2012-05-01','YYYY-MM-DD')),

           partition p5 valuesless than (to_date('2012-06-01','YYYY-MM-DD')),

           partition p6 valuesless than (to_date('2012-07-01','YYYY-MM-DD')),

           partition p7 valuesless than (to_date('2012-08-01','YYYY-MM-DD')),

           partition p8 valuesless than (to_date('2012-09-01','YYYY-MM-DD')),

           partition p9 valuesless than (to_date('2012-10-01','YYYY-MM-DD')),

           partition p10 valuesless than (to_date('2012-11-01','YYYY-MM-DD')),

           partition p11 valuesless than (to_date('2012-12-01','YYYY-MM-DD')),

           partition p12 valuesless than (to_date('2013-01-01','YYYY-MM-DD')),

           partition p_maxvalues less than (maxvalue)

);

插入如下：

insert into range_list_part_tab(id,deal_date,area_code,contents)

selectrownum,to_date(to_char(sysdate-365,'J')+TRUNC(DBMS_RANDOM.VALUE(0,365)),'J'),

ceil(dbms_random.value(590,599)),

rpad('*',400,'*')

from dual

connect by rownum <= 100000;

主要是增加了subpartition by list(area_code)这个模块。

组合分区是由主分区和从分区组成的。比如范围-列表分区，就表示主分区是范围分区，而从分区是列表分区。

1.5         分区原理

创建表

SQL> create table norm_tab (id number,deal_date date ,area_codenumber,contents varchar2(4000));

Table created.

插入内容：

insert into norm_tab (id,deal_date,area_code,contents)

               selectrownum,to_date(to_char(sysdate-365,'J')+TRUNC(DBMS_RANDOM.value(0,365)),'J'),

   ceil(dbms_random.value(590,599)),

    rpad('*',400,'*')

    from dual

    connect by rownum <=10000;

然后来对比普通表盒分区表在段分配上的差异，以范围分区表和普通表进行试验对比。

SQL>set linesize 666

Set pagesize 5000

Col segment_name format a20

Col partition_name format a20

Col segment_type format a20

Select segment_name,partition_name,segment_type,bytes/1024/1024,tablespace_namefrom user_segments where segment_name IN ( 'RANGE_PART_TAB','NORM_TAB');

SEGMENT_NAME           PARTITION_NAME   SEGMENT_TYPE           BYTES/1024/1024 TABLESPACE_NAME

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

NORM_TAB                                         TABLE                            5 TBS_TOAD

RANGE_PART_TAB          P_MAX                TABLE PARTITION                48 TBS_TOAD

 

看下散列分区：

set linesize 666

Set pagesize 5000

Col segment_name format a20

Col partition_name format a20

Col segment_type format a20

Selectsegment_name,partition_name,segment_type,bytes/1024/1024,tablespace_name fromuser_segments where segment_name IN ( 'HASH_PART_TAB');

SEGMENT_NAME           PARTITION_NAME   SEGMENT_TYPE           BYTES/1024/1024 TABLESPACE_NAME

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

HASH_PART_TAB            SYS_P419                      TABLE PARTITION                 8 TBS_TOAD

HASH_PART_TAB            SYS_P420                      TABLE PARTITION                 8 TBS_TOAD

HASH_PART_TAB            SYS_P421                      TABLE PARTITION                 8 TBS_TOAD

HASH_PART_TAB            SYS_P422                      TABLE PARTITION                 8 TBS_TOAD

HASH_PART_TAB            SYS_P423                      TABLE PARTITION                 8 TBS_TOAD

HASH_PART_TAB            SYS_P424                      TABLE PARTITION                 8 TBS_TOAD

HASH_PART_TAB            SYS_P425                      TABLE PARTITION                 8 TBS_TOAD

HASH_PART_TAB            SYS_P426                      TABLE PARTITION                 8 TBS_TOAD

HASH_PART_TAB            SYS_P427                      TABLE PARTITION                 8 TBS_TOAD

HASH_PART_TAB            SYS_P428                      TABLE PARTITION                 8 TBS_TOAD

HASH_PART_TAB            SYS_P429                      TABLE PARTITION                 8 TBS_TOAD

HASH_PART_TAB            SYS_P430                      TABLE PARTITION                 8 TBS_TOAD

 

HASH分区的分区名是系统自己命名的。

HASH分区最大的好处在于，将数据根据一定的HASH算法，均匀分布到不同的分区中区，避免查询数据时集中在一个地方，避免热点块的竞争，改善IO。HASH可以精确匹配，无法范围扫描。

分区表也有额外的开销，如果分区数量过多，ORACLE就需要管理过多的段，在操作分区表时容易引发ORACLE内部大量的递归调用。

一般来说，大表才建议分区，记录数在100万以下的表，基本不建议分区。