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