数据库分区分表

一、分区表、分区索引概念
 
    为了满足而非常大的数据库的管理，需要创建和使用分区表和分区索引，分区表允许将数据分成成为分区甚至子分区的更小的、更好管理的块。每个分区可以单独管理，可以不依赖其他分区而单独发挥作用，因此可以提供更有利于可用性和性能的结构。 
 
    表或索引可以共享相同的逻辑属性，但是可以有不同的物理属性。例如所有分区/子分区可以共享相同的列和约束，但是可以有不同的表空间。 
 
    最好可以将表或者索引的分区存储到不同的表空间，这样的好处是： 
    ● 减少数据在多个分区中冲突的可能性 
    ● 可以单独备份和恢复每个分区 
    ● 控制分区与磁盘驱动器之间的映射(平衡i/o负载) 
    ● 改善可管理性、可用性和性能 
 
 
二、分区方法 
 
    1、范围分区 
 
    当列数据可以被划分为逻辑范围时(例如年度中的月份)，就可以使用范围分区。当数据在整个范围中能被均等地划分时性能最好。如果所划分的分区范围大小明显不同时，则需要考虑其他的分区方法了。 
 
    创建范围分区时，需要指定分区列、表示分区边界，例如： 
 
    create table sales 
    ( invoice_no number, 
    sale_year int not null, 
    sale_month int not null, 
    sale_day int not null ) 
    partition by range (sale_year, sale_month, sale_day) 
    ( partition sale_q1 values less than (1999, 04, 01) 
    tabllespace tsa, 
    partition sale_q2 values less than (1999, 07, 01) 
    tabllespace tsb, 
    partition sale_q3 values less than (1999, 10, 01) 
    tabllespace tsc, 
    partition sale_q4 values less than (2000, 01, 01) 
    tabllespace tsd ); 
 
    注：要注意使用不同字符集的数据库时，最字符的分类序列有时是不同的。 
 
    2、散列分区 
 
    当数据不太容易进行范围划分时，为了性能和管理的原因又想分区时，就可以使用散列分区方法。散列分区将在指定数量的分区中均等得划分数据。创建散列分区需要指定分区列、分区数量(或单独的分区描述)举例如下： 
 
    create table scubagear 
    (id number, 
    name varchar2(60)) 
    partition by hash (id) 
    partitions 4 
    store in (gear1, gear2, gear3, gear4); 
 
    3、列表分区 
 
    当需要明确得控制如何将行映射到分区时，就需要使用列表分区。可以在每个分区表述中为该分区指定一列离散值。 
 
    列表分区与范围分区、散列分区的区别在于 
    ● 范围分区为分列假设了一个值的自然范围，无法将该值范围之外的分区组织在一起 
    ● 散列分区无法对数据的划分进行控制，在逻辑上是无须的 
 
    需要注意的是：列表分区无法支持多列分区。具体举例如下： 
 
    create table sales_by_region 
    (deptno number, 
    deptname varchar2(20), 
    quarterly_sales number(10,2), 
    state varchar2(2)) 
    partition by list (state) 
    (partition q1_northwest values ('or', 'wa'), 
    partition q1_southwest values ('az', 'ut', 'nm'), 
    partition q1_northeast values ('ny', 'vm', 'nj'), 
    partition q1_southeast values ('fl', 'ga'), 
    partition q1_northcentral values ('sd', 'wi'), 
    partition q1_southcentral values ('ok', 'tx')); 
 
    4、组合分区 
 
    组合分区是在分区中使用范围分区，而在子分区中使用散列分区。组合分区很适合于历史数据和条块数据两者，改善了范围分区及其数据放置的管理型。例如一下举例： 
 
    create table scubaqear (equipno number, equipname varchar(32), price number) 
    partition by range (equipno) subpartition by hash(equipname) 
    subpartitions 8 store in (ts1, ts2, ts3, ts4) 
    (partition p1 values less than (1000), 
    partition p2 values less than (2000), 
    partition p3 values less than (maxvalue)); 
 
 
三、分区表的创建 
 
    1、创建范围分区表 
 
    使用partition by range子句来表明范围分区，使用partition子句标识各个分区范围，另外partition子句下级子句可以指定特别用于该分区段的物理属性，如果没有重载，则自动继承基础表的属性。 
 
    重新修改上面的例子： 
 
    create table sales 
    ( invoice_no number, 
    sale_year int not null, 
    sale_month int not null, 
    sale_day int not null ) 
    storage (initial 100k next 50k) logging 
    partition by range (sale_year, sale_month, sale_day) 
    ( partition sale_q1 values less than (1999, 04, 01) 
    tabllespace tsa storage (initial 20k, next 10k), 
    partition sale_q2 values less than (1999, 07, 01) 
    tabllespace tsb, 
    partition sale_q3 values less than (1999, 10, 01) 
    tabllespace tsc, 
    partition sale_q4 values less than (2000, 01, 01) 
    tabllespace tsd ) 
    enable row movment; 
 
    说明：在表层级指定了存储参数个logging属性，而在分区sale_q1中的存储参数进行重置，原因是第一季度业务较少。另外使用enable row movment子句，表示如果键值更改了，就允许将行迁移到新分区。 
 
    另外创建一个范围分区的全局索引如下： 
 
    create index month_ix on sales(sales_month) 
    grobal partition by range(sales_month) 
    (partition pm1_ix values less than (2) 
    partition pm2_ix values less than (3) 
    partition pm3_ix values less than (4) 
    partition pm4_ix values less than (5) 
    partition pm5_ix values less than (6) 
    partition pm6_ix values less than (7) 
    partition pm7_ix values less than (8) 
    partition pm8_ix values less than (9) 
    partition pm9_ix values less than (10) 
    partition pm10_ix values less than (11) 
    partition pm11_ix values less than (12) 
    partition pm12_ix values less than (maxvalue)); 
 
    2、创建散列分区表 
 
    使用partition by hash子句来表明散列分区，使用partitions子句来指定要创建的分区数量，另外使用partition子句来命名各个分区及其表空间，但是只能指定tablespace属性，其他的属性只能继承于表层次。举例如下： 
 
    create table dept (deptno number, dept name varchar2(32)) 
    storage (initial 10k) 
    partition by hash (deptno) 
    (partition p1 tablespace ts1, partition p2 tablespace ts2, 
    partition p3 tablespace ts3, partition p4 tablespace ts4); 
 
    为上表创建局部索引，则oracle会自动创建一个与基础表同分区的索引。 
 
    create index locd_dept_ix on dept(deptno) local 
 
    3、创建列表分区表 
 
    使用partition by list子句来表明列表分区，使用partition子句指定一串文字值，即为分区列的离散值。另外partition子句下级子句可以指定特别用于该分区段的物理属性，如果没有重载，则自动继承基础表的属性。 
 
    此类型基本与上面的举例相同，不再重新举例。 
 
    4、创建组合分区表 
 
    先使用partition by range子句，然后指定一个与partition by hash语句遵从语法和规则的subpartition by hash子句来表明组合分区，各个partition子句后面紧跟subpartition或subpartitions子句。 
 
    另外可以为每个(范围)分区指定不同的属性，另外还可以使用store in子句来指定不同的不同的表空间。 
 
    create table emp (deptno number, empname varchar(32), grade number) 
    partition by range (deptno) subpartition by hash(empname) 
    subpartitions 8 store in (ts1, ts3, ts5, ts7) 
    (partition p1 values less than (1000) pctfree 40, 
    partition p2 values less than (2000) 
    store in (ts2, ts4, ts6, ts8), 
    partition p3 values less than (maxvalue) 
    (subpartition p3_s1 tablespace ts4, 
    subpartition p3_s2 tablespace ts5)); 
 
    另创建一个局部索引，且分段分布于表空间ts7、ts8、ts9 
 
    create index emp_ix on emp(deptno) 
    local store in (ts7, ts8, ts9); 
 
    5、创建分局索引结构表 
 
    可以对索引结构表使用范围分区或散列分区，只有范围分区索引结构表才能包含lob数据类型的列。 
 
    创建范围分区或散列分区索引结构表与创建普通表相似，但也有区别，区别在于： 
    ● 创建该表时需要指定organization index子句，需要时还要指定including和overflow子句 
    ● partition或partitions子句可以有overflow下级子句，允许在分区层次上指定溢出段的属性 
 
    注：索引结构表的分区列集合必须是主键列的子集，因为索引结构表的行是按表的主键索引存储的，通过将分区键选成主键的子集，插入操作就只需要校验在单个分区中的主键的唯一性，因此对分区的维护就互不依赖了。 
 
    a.创建范围分区索引结构表 
 
    create table sales(acct_no number(5), 
    acct_name char(30), 
    amount_of_sale number(6), 
    week_no integer, 
    sale_detailes varchar2(1000), 
    primary key (acct_no, acct_name, week_no)) 
    organization index 
    including week_no 
    overflow tablespace overflow_here 
    partition by range (week_no) 
    (partition values less than (5) 
    tablespace ts1, 
    partition values less than (9) 
    tablespace ts2 overflow tablespace overflow_ts2, 
    ... 
    partition values less than (maxvalue) 
    tablespace ts13); 
 
    说明： 
    1、including子句指定将week_no列之后的所有列都存储在溢出段中。 
    2、每个分区有一个溢出段，都存储在相同的表空间(overflow_here)中。 
    3、通过overflow tablespace子句指定各个分区层次的溢出表空间。 
 
 
    b. 创建散列分区索引结构表 
 
    create table sales(acct_no number(5), 
    acct_name char(30), 
    amount_of_sale number(6), 
    week_no integer, 
    sale_details varchar2(1000), 
    primary key (acct_no, acct_name, week_no)) 
    organization index 
    including week_no 
    overflow 
    partition by hash (week_no) 
    partitions 16 
    store in (ts1, ts2, ts3, ts4) 
    overflow store in (ts3, ts6, ts9); 
 
    建议在创建具有可变分区键的散列分区索引结构表时，明确指定row movement enable子句，因为一个好的散列函数会将各行做一个很好的平衡分布，所以改变主键列很有可能会移动到其他分区。 
 
    6 、多个数据块大小的分区限制 
 
    若在具有多个数据块大小的表空间中创建分区对象时需要特别留意，因为分区对象存储到这些表空间时会受到某些限制。例如以下的分区必须存储在具有相同数据块大小的表空间中： 
 
    ● 常规表 
    ● 索引 
    ● 索引结构表的主键索引段 
    ● 索引结构表的溢出段 
    ● 在外存储的lob列