Oracle 分区表



--==================

-- Oracle 分区表

--==================

 

一、分区表：

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

 

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

   

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

    

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

   

   关于分区表的功能实际上同SQLserver 中的分区表是同样的概念，只不过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 byrange (sales_date)  --创建基于日期的范围分区并存储到不同的表空间

           (

           partition sal_jan2000 values less than(to_date('02/01/2000',

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

           partition sal_feb2000 values less than(to_date('03/01/2000',

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

           partition sal_mar2000 values less than(to_date('04/01/2000',

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

           partition sal_apr2000 values less than(to_date('05/01/2000',

               'DD/MM/YYYY')) tablespace sal_range_apr2000

           );

 

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

           (a int)

           partition byrange (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 rdrop partiton p1

           select * from r

           select * from r partition (p4)

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

 

       partition by用于指定分区方式

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

       partition pn 用于指定分区的名字

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

 

       添加分区：

           ALTER TABLE r

           add partition p5values less than (xxx) tablespace xx;

 

       查看分区表相关信息：

           SELECT table_name,partition_name,subpartition_count,

           tablespace_name,user_stats from 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 byrange(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 byrange(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_TRACKadd 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_TRACKdrop partition p_2005_04;

 

   3.添加子分区

       alter table T_TRACK

       modify partition P_2005_01

       add subpartition P_2005_01_P1017 values('P1017');

 

   4.删除子分区

       alter table T_TRACKdrop subpartition p_2005_01_p1017;

 

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

       alter table sales3 truncate partition sp1

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

       alter table sales3truncate partition sp1update indexes

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

 

   6.截断分区表的子分区

       alter table comptruncate 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 xxxrebuild

 

 

   10.合并分区：

       alter table sales3 merge partitons sp1,sp3into partition sp3

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

 

   11.删除分区：

       alter table scott.sales_compositedrop partition SALES_JAN2000;

 

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

       DBA_TAB_PARTITIONS

       DBA_IND_PARTITIONS

       DBA_TAB_SUBPARTITIONS

       DBA_IND_SUBPARTITIONS

 

       

       Oracle关于分区的在线文档：http://download.oracle.com/docs/cd/B19306_01/server.102/b14220/partconc.htm#sthref2604

 

七、更多参考

 

Oracle冷备份

 

SPFILE错误导致数据库无法启动

 

Oracle用户、对象权限、系统权限

 

Oracle角色、配置文件

 

 Oracle联机重做日志文件(ONLINE LOG FILE)

 

 Oracle控制文件(CONTROLFILE)

 

 Oracle表空间与数据文件

 

Oracle归档日志

转自：http://blog.csdn.net/leshami/article/details/5925572