Oracle表分区学习

一、分区表：

    随着表的不断增大，对于新纪录的增加、查找、删除等(DML)的维护也更加困难。对于数据库中的超大型表，可通过把它的数据分成若干个小表，从而简化数据库的管理活动。对于每一个简化后的小表，我们称为一个单个的分区。

 

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

   

    对于外部应用程序来说，虽然存在不同的分区，且数据位于不同的表空间，但逻辑上仍然是一张表

     

    可以使用SQL*Loader，IMPDP，EXPDP，Import，Export等工具来装载或卸载分区表中的数据

   

    关于分区表的功能实际上同SQL server 中的分区表是同样的概念，只不过SQLserver中的数据存放到了文件组，相当于Oracle概念中的表空间，

    有兴趣的可以参考：

        SQL server 2005基于已存在的表创建分区

        SQL server 2005 切换分区表

 

二、何时分区

    当表达到GB大小且继续增长

    需要将历史数据和当前的数据分开单独处理，比如历史数据仅仅需要只读，而当前数据则实现DML

   

三、分区的条件及特性

    共性：不同的分区之间必须有相同的逻辑属性，比如表名，列名，数据类型，约束等，

   

    个性：各个分区可以有不同的物理属性，比如pctfree, pctused,and tablespaces.

 

　　分区独立性：即使某些分区不可用，其他分区仍然可用。

 

　　特殊性：含有LONG、LONGRAW数据类型的表不能进行分区

 

四、分区的优点

    1、提高查询性能：只需要搜索特定分区，而非整张表，提高查询速度

    2、节约维护时间：单个分区的数据装载，索引重建，备份，维护等将远小于整张表的维护时间。

    3、节约维护成本：可以单独备份和恢复每个分区

    4、均衡I/O：将不同的分区映射到不同的磁盘以平衡I/O，提高并发

 

五、ORACLE分区类型：

    范围分区、散列分区、列表分区、组合分区

    可以对索引和表分区，全局索引只能按范围分区，可以将其定义在任何类型的分区或非分区表上。

    通常全局索引比局部索引需要更多的维护

    局部索引与基础表是等同分区的，用于反映其基础表的结构

   

    1.Range分区：行映射到基于列值范围的分区

        Range 分区，又成为范围分区，基于分区键值的范围将数据映射到所建立的分区上。这是最通用的分区类型。

        创建范围分区时，必须指定以下内容

            分区方法：range

            分区列

            标识分区边界的分区描述

           

        使用Range 分区的时候，要记住几条规则：

            每个分区都包含VALUES LESS THAN字名，定义了分区的上层边界。任何等于和大于分区键值的二进制值都被添加到下一个高层分区中。

            所有的分区，除了第一个，如果低于VALUES LESS THAN所定义的下层边界，都放在前面的分区中。

            MAXVALUE可以用来定义最高层的分区。MAXVALUE表示了虚拟的无限值

 

        示例：

            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

            );

 

            create table r      --创建基于值范围的分区，分区子句未指定表空间时则位于缺省的表空间

            (a int)

            partition by range (a)

            (

                partition p1 values less than (10),

                partition p2 values less than (20),

                partition p3 values less than (30),

                partition p4 values less than (maxvalue)

            );

 

            select * from r partition(p1)    --查看分区中的数据

       

            一个分区的损坏不会影响其它分区的数据：

            alter table r drop partiton p1

            select * from r

            select * from r partition(p4)

            除分区数据不见外，其它都正常

 

        partition by 用于指定分区方式

        range 表示分区的方式是范围划分

        partition pn 用于指定分区的名字

        values less than 指定分区的上界（上限）

 

        添加分区：

            ALTER TABLE r

            add partition p5 values less than (xxx ) tablespace xx;

 

        查看分区表相关信息：

            SELECT table_name,partition_name,subpartition_count,

            tablespace_name,user_statsfrom user_tab_partitions;

 

        获取创建分区表的元数据：

              set long 10000

              select dbms_metadata.get_ddl('TABLE','R','SCOTT')from dual;

                                        表  表名  用户名   区分大小写

 

    2.Hash分区：散列分区

        Hash分区能够很容易对数据进行分区，因为语法很简单，很容易实现。在下面这种

        情况下，使用hash分区比range分区更好：

            事先不知道需要将多少数据映射到给定范围的时候

            分区的范围大小很难确定，或者很难平衡的时候

            Range分区使数据得到不希望的聚集时

            性能特性，如并行DML、分区剪枝和分区连接很重要的时候

        创建散列分区时，必须指定以下信息

            分区方法：hash

            分区列

            分区数量或单独的分区描述

 

        分裂、删除和合并分区不能应用于Hash分区，但是，Hash分区能够合并和添加。

 

        创建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 为表空间名。

        散列分区表的每个分区都被存储在单独的段中。

       

    3.List分区：列表分区

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

        不同于Range分区和Hash分区，

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

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

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

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

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

        List分区时必须指定的以下内容

            分区方法：list

            分区列

            分区描述，每个描述指定一串文字值(值的列表),它们是分区列(它们限定将被包括在分区中的行)的离散值

       

        示例：

        create table 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

        );

 

        添加分区：

           alter table sales3 add partition hk values ('HK') tablespace xx

 

    4.Composite Partitioning：合成分区、组合分区

        组合分区使用range方法分区，在每个子分区中使用hash方法进行再分区。

        组合分区比range分区更容易管理，充分使用了hash分区的并行优势。组合分区支持历史数据和条块数据两者。

        如添加新的RANGE分区，同时为DML操作提供更高层的并行性。

        创建组合分区时，需要指定如下内容：

            分区方法：range

            分区列

            标识分区边界的分区描述

            子分区方法：hash

            子分区列

            每个分区的子分区数量，或子分区的描述

       

        create table 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)    NOT NULL, 

            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;

 

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

        DBA_TAB_PARTITIONS

        DBA_IND_PARTITIONS

        DBA_TAB_SUBPARTITIONS

        DBA_IND_SUBPARTITIONS

更多信息欢迎访问：Rain Family