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',

partition p3 values less than (TO_DATE('2012-04-01',

partition p4 values less than (TO_DATE('2012-05-01',

partition p5 values less than (TO_DATE('2012-06-01',

partition p6 values less than (TO_DATE('2012-07-01',

partition p7 values less than (TO_DATE('2012-08-01',

partition p8 values less than (TO_DATE('2012-09-01',

partition p9 values less than (TO_DATE('2012-10-01',

partition p10 values less than (TO_DATE('2012-11-01',

partition p11 values less than (TO_DATE('2012-12-01',

partition p12 values less than (TO_DATE('2013-01-01',

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,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,contents)

selectrownum,'J')+TRUNC(DBMS_RANDOM.VALUE(0,

ceil(dbms_random.value(590,

rpad('*','*')

from dual

connect by rownum <=100000;

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

1.3散列分区

创建散列分区如下：

create table hash_part_tab (id number,contents varchar2(4000))

partition by hash(deal_date)

partitions 12;

插入10万条：

insert into hash_part_tab(id,contents)

selectrownum,'J')+TRUNC(DBMS_RANDOM.value(0,

ceil(dbms_random.value(590,

rpad('*','*')

from dual

connect by rownum <= 100000;

关键字：partition by hash.

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

1.4组合分区

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

create table range_list_part_tab (id number,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',

partition p2 valuesless than (to_date('2012-03-01',

partition p3 valuesless than (to_date('2012-04-01',

partition p4 valuesless than (to_date('2012-05-01',

partition p5 valuesless than (to_date('2012-06-01',

partition p6 valuesless than (to_date('2012-07-01',

partition p7 valuesless than (to_date('2012-08-01',

partition p8 valuesless than (to_date('2012-09-01',

partition p9 valuesless than (to_date('2012-10-01',

partition p10 valuesless than (to_date('2012-11-01',

partition p11 valuesless than (to_date('2012-12-01',

partition p12 valuesless than (to_date('2013-01-01',

partition p_maxvalues less than (maxvalue)

);

插入如下：

insert into range_list_part_tab(id,'*')

from dual

connect by rownum <= 100000;

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

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

1.5分区原理

创建表

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

Table created.

插入内容：

insert into norm_tab (id,contents)

selectrownum,'*')

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,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万以下的表，基本不建议分区。

（编辑：李大同）

【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!