oracle 分区表设计

 

一、分区表概述：

       对于数据库中的超大型表，可通过把它的数据分成若干个小表，从而简化数据库的管理活动。对于每一个简化后的小表，我们称为一个单个的分区。对于外部应用程序来说，虽然存在不同的分区，但逻辑上仍然是一张表。

       对于分区的访问，我们不需要使用特殊的SQL查询语句或特定的DML语句，而且可以单独的操作单个分区，而不是整个表。同时可以将不同分区的数据放置到不同的表空间，比如将不同年份的销售数据，存放在不同的表空间，即年的销售数据存放到TBS_2001,2002年的销售数据存放到TBS_2002,依次类推，从而实现了分散存储，这将大大的简化大容量表的管理，提高查询性能及I/O并发等。

 

二、分区表作用于优点：

        作用：分区可选择将表分离在不同的表空间上,用分而治之的方法来支撑海量数据的大表.将大表分割成较小的分区可以改善表的维护、备份、恢复、事务及查询性能。

       

        优点：

               1、 增强可用性：如果表的一个分区由于系统故障而不能使用，表的其余好的分区仍可以使用；
               2、 减少修复时间：如果系统故障只影响表的一部份分区，那么只有这部份分区需要修复，可能比整个大表修复花的时间更少；
               3、 维护轻松：管理每个公区比管理单个大表要轻松得多；
               4、 均衡I/O：可以把表的不同分区分配到不同的磁盘来平衡I/O改善性能；
               5、 改善性能：对大表的查询、增加、修改等操作可以分解到表的不同分区来并行执行，可使运行速度更快。
               6、 分区对用户透明，最终用户感觉不到分区的存在。

 

三、何时进行分区：

        当表的大小达到GB级别且持续增长，或对于OLTP系统，表的记录超过1000万时，都应考虑对表进行分区。

        官网的2个建议如下：

       （1）Tables greater than 2GB should always be considered for partitioning.

       （2）Tables containing historical data, in which new data is added into the newest partition. A typical example is a historical table where only the current month's data is updatable and the other 11 months are read only.

 

 

四、分区表分类：

       分区方法：范围分区、散列分区、列表分区、组合分区。

 

       Range partitioning（范围分区）：基于分区键值的范围将数据映射到所建立的分区上。这是最通用的分区类型。

    特点：通过对分区字段值的范围进行分区

        1.范围分区通过对分区字段值的范围进行分区。

        2.范围分区特别适合于按时间周期进行数据的存储，日、周、月、年等。

        3.范围分区的数据可能不均匀。

        4.范围分区与记录值相关，实施难度和可维护性相对较差。

       示例：

            create table sal_range   

            (salesman_id number(5),

            salesman_name varchar2(30),

            sales_amount number(10),

            sales_date date)

            partition by range (sales_date)  --创建基于日期的范围分区并存储到不同的表空间

            (

            partition sal_jan2000values less than(to_date('02/01/2000',

                'DD/MM/YYYY')) tablespace sal_range_jan2000,

            partition sal_feb2000values less than(to_date('03/01/2000',

                'DD/MM/YYYY')) tablespace sal_range_feb2000,

            partition sal_mar2000values less than(to_date('04/01/2000',

                'DD/MM/YYYY')) tablespace sal_range_mar2000,

            partition sal_apr2000values less than(to_date('05/01/2000',

                'DD/MM/YYYY')) tablespace sal_range_apr2000

            );

 

        Hash partitioning（哈希分区）：如果数据不容易用范围分区，但你想提升性能和表的易管理性。 Hash分区提供了一个在指定数量的分区内交叉均匀分布数据的方法。行根据分区键的hash值映射到相应分区中。创建和使用hash分区你可以灵活放置数据，可以通过交叉访问在不同I/O设备上的分区提升性能。

       特点：

              1、基于分区字段的hash值，自动将记录插入都指定分区。

              2、分区数一般是2的幂。

              3、易于实施，整理性能最佳。

              4、适合于存储均匀分布的静态数据。

              5、数据管理能力弱，对数据值无法控制。

       示例：

        创建hash分区有两种方法：一种方法是指定分区数量，另一种方法是指定分区的名字，

        但两者不能同时指定。

        方法一：指定分区数量

        create table dept2 (deptno number,deptname varchar2(32))

        partition by hash(deptno) partitions 4;

        方法二：指定分区的名字

        create table dept3 (deptno number,deptname varchar2(32))

        partition by hash(deptno)             

        (partition p1 tablespace p1,

        partition p2 tablespace p2);

        create table sales_hash

        (salesman_id number(5),

        salesman_name varchar2(30),

        sales_amount number(10),

        week_no number(2))

        partition by hash (salesman_id)

        partitions 4

        store in (data1,data2,data3,data4)

       

        data1,data2,data3,data4 为表空间名。

 

       List partitioning（列表分区）：可以控制如何将行映射到分区中去。可以在每个分区的键上定义离散的值。

       不同于Range分区和Hash分区，

            Range分区与分区相关联，为分区列假设了一个值的自然范围，故不可能将该值的范围以外的分区组织到一起。

            hash分区时不允许对数据的划分进行控制，因为系统使用的是散列函数来划分数据的。

        List分区的优点在于按照自然的方式将无序和不相关的数据集合分组。

        List分区不支持多列分区，如果将表按列分区，那么分区键就只能有表的一个单独列组成。

        Range分区和Hash分区可以对多列进行分区。

        特点：

              1、通过对分区字段的离散值进行分区。

              2、是不排序的，而且分区之间没有关联关系。

              3、适合于对数据离散值进行控制。

              4、只支持单个字段。

        示例：

        createtable sales_list

        (salesman_id number(5),

        salesman_name varchar2(30),

        sales_state varchar2(20),

        sales_amount number(10),

        sales_date date)

        partition by list (sales_state)

        (

        partition sales_west values ('California','Hawaii') tablespace x,

        partition sales_east values ('New York','Virginia') tablespace y,

        partition sales_central values ('Texas','Illinois') tablespace z,

        partition sales_other values(DEFAULT) tablespace o

 

       Composite Partitioning（组合分区）：

       特点：

               1、组合类型：range-hash，range-list。在Oracle 11g中又增加了range-range,list-range,list-list,list-hash。

               2、既适合于历史数据，又适合于数据均匀分布。

               3、实现粒度更细的操作。

               4、支持复合local indexs，不支持复合global indexs。

  

       示例：

       createtable sales_composite

        (salesman_id number(5),

        salesman_name varchar2(30),

        sales_amount number(10),

        sales_date date)

        partition by range(sales_date)

        subpartition by hash(salesman_id)

        subpartitions 4

        store in (tbs1,tbs2,tbs3,tbs4)

        (partition sales_jan2000values less than(to_date('02/01/2000','DD/MM/YYYY')),

        partition sales_feb2000 values less than(to_date('03/01/2000','DD/MM/YYYY')),

        partition sales_mar2000 values less than(to_date('04/01/2000','DD/MM/YYYY'))

        );

        create table T_TRACK 

        (

            N_TRACK_ID           NUMBER(20)    NOTNULL, 

            C_COMP_CDE           VARCHAR2(6),

            T_TRACK_TM           DATE           NOT NULL,

            C_CAR_NO             VARCHAR2(50)

        )

        partition by range(T_TRACK_TM)

        subpartition by list(C_COMP_CDE)

        (

            partition P_2009_11 values less than (to_date('2009-12-01','yyyy-MM-dd'))

                    (

subpartition P_2009_11_P1013 values('P1013')

                    )

        );

 

五、分区表相关操作：

       1.添加分区

        alter table T_TRACK add partition P_2005_04

        values less than(to_date('2005-05-01','yyyy-MM-dd'))

        (

            subpartition P_2005_04_P1013 values('P1013'),

            subpartition P_2005_04_P1013 values('P1014'),

            subpartition P_2005_04_P1013 values('P1015'),

            subpartition P_2005_04_P1013 values('P1016')

        )

        2.删除分区

        alter table T_TRACK drop partition p_2005_04;

        3.添加子分区

        alter table T_TRACK

        modify partition P_2005_01

        add subpartition P_2005_01_P1017values('P1017');

        4.删除子分区

        alter table T_TRACK drop subpartition p_2005_01_p1017;

        5.截断一个分区表中的一个分区的数据：

        alter table sales3  truncate partition sp1

            这种方式会使全局分区索引无效

        alter table sales3 truncate partition sp1 update indexes

            这种方式全局分区索引不会无效

        6.截断分区表的子分区

        alter table comp truncate subpartition sub1

        7.截断带有约束的分区表

        a、禁用约束

          alter table sales disable constraint dname_sales1

        b、截断分区

          alter table sales truncate partitoin dec

        c、启用约束

          alter table sales enable constraint dname_sales1

        8.查看一个表是不是分区表

        select table_name,partitionedfrom user_tables;

        TABLE_NAME PAR

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

        DEPT                           NO

        DEPT3                          YES

        9.将一个表的分区从一个表空间移动到另一个表空间

        a、查看分区在哪个表空间

          SELECT TABLE_OWNER,TABLE_NAME,PARTITION_NAME,TABLESPACE_NAME,

          SUBPARTITION_COUNT

          FROM DBA_TAB_PARTITIONSWHERE TABLE_OWNER='SCOTT';

        b、移动分区

          alter table sales move partiton sp1 tablespace tp;

        c、检查是否移动成功

          SELECT TABLE_OWNER,TABLE_NAME,PARTITION_NAME,TABLESPACE_NAME,

          SUBPARTITION_COUNT

          FROM DBA_TAB_PARTITIONSWHERE TABLE_OWNER='SCOTT';

        移动表空间后，要重建索引，否则索引会变得无效

        alter index xxx rebuild       

        10.合并分区：

        alter table sales3 merge partitons sp1,sp3 into partition sp3

        合并后的分区名，不能是边界值较低的那个

        11.删除分区：

        alter table scott.sales_composite drop partition SALES_JAN2000;

        12. 与分区表相关的数据字典视图：

        DBA_TAB_PARTITIONS

        DBA_IND_PARTITIONS

        DBA_TAB_SUBPARTITIONS 

        DBA_IND_SUBPARTITIONS

 

六、选择分区表类型和分区列：

       分区表类型根据实际情况而定，才能提高整体性能。一般范围分区使用较多。

　　分区的字段一定要是经常用以提取数据的字段(大多数查询sql的where条件中有的字段)，否则会在提取过程中导致遍历多个分区，这样比没有分区还要慢。

　　分区字段要选择合适，数据较为均匀分布到各个分区，不要太多也不要太少，而且根据分区字段可以很快定位到分区范围。

　　一般情况下，尽量让业务操作在同一个分区内部完成。

       注意：分区字段不能使用函数转换后在分区，如，将某数字字符串字段，先TO_NUMER(COL_NAME)后分区。

 

七、分区表表空间规划：

        在大型表上使用表分区是否需要把分区放在不同的表空间？？？？

       

 

八、分区表索引：

        oracle 分区索引：http://blog.csdn.net/gybyylx/article/details/8119121