Partitioned Tables

Partitioned Tables

引自： http://blog.csdn.net/huang_tg/archive/2010/07/08/5721327.aspx

本文主要从以下几个方面讨论Oracle分区表（Partitioned Tables）的概念及操作：

      一.分区表的概念

         分区就是将一个非常大的表分解成许多小的区，以提供更好的性能及可管理性。每个分区在逻辑上必须拥有相同的结构，例如相同的列明，数据类型及约束条件。但是每个分区可以设置不同物理属性，如pctfree，pctused以及tablespace。

      二.分区表的具体作用

         Oracle的表分区功能通过改善可管理性、性能和可用性，从而为各式应用程序带来了极大的好处。通常，分区可以使某些查询以及维护操作的性能大大提高。此外,分区还可以极大简化常见的管理任务，分区是构建千兆字节数据系统或超高可用性系统的关键工具。分区功能能够将表、索引或索引组织表进一步细分为段，这些数据库对象的段叫做分区。每个分区有自己的名称，还可以选择自己的存储特性。从数据库管理员的角度来看，一个分区后的对象具有多个段，这些段既可进行集体管理，也可单独管理，这就使数据库管理员在管理分区后的对象时有相当大的灵活性。但是，从用户及应用的角度来说分区后的表与非分区表完全相同，使用SQL，DML命令访问分区后的表时，无需任何修改。分区表对用户及应用来说是完全透明的。

         分区表有如此多的好处，那么在什么情况下需要使用到分区表呢？Oracle推荐我们在单个表大于2G，或者表中包含历史数据，新的数据都被存放到新的分区中的时候使用。

      三.分区表的优缺点

         分区表有以下优点：

         1.分区可以简化管理操作，如数据的加载，创建或重建索引，允许分区级别的恢复与备份。
         2.提高查询性能，只查询相关的分区而不是整个表，减少了查询锁需要的时间。
         3.并发的DML及SELECT操作，由于可以进行分区级的操作，所以在维护某个分区的时候并不影响其他的分区；
         4.提高可用性，单个的分区损坏不影响其他分区的使用，并且减少了故障的恢复时间。

      四.分区表种类及创建示例

         1.范围分区（Range Partitioning）

           范围分区将数据基于范围映射到每一个分区，这个范围是你在创建分区时指定的分区键决定的。这种分区方式是最为常用的，并且分区键经常采用日期。举个例子：你可能会将销售数据按照月份进行分区。
当使用范围分区时，请考虑以下几个规则：

           每一个分区都必须有一个VALUES LESS THEN子句，它指定了一个不包括在该分区中的上限值。分区键的任何值等于或者大于这个上限值的记录都会被加入到下一个高一些的分区中。

           所有分区，除了第一个，都会有一个隐式的下限值，这个值就是此分区的前一个分区的上限值。

           在最高的分区中，MAXVALUE被定义。MAXVALUE代表了一个不确定的值。这个值高于其它分区中的任何分区键的值，也可以理解为高于任何分区中指定的VALUE LESS THEN的值，同时包括空值。

           以下是一个使用范围分区的例子：

CREATE TABLE sales_range
(salesman_id NUMBER(5),
salesman_name VARCHAR2(30),
sales_amount NUMBER(10),
sales_date DATE)
PARTITION BY RANGE(sales_date)
(
PARTITION sales_jan2010 VALUES LESS THAN(TO_DATE('02/01/2010','MM/DD/YYYY')),
PARTITION sales_feb2010 VALUES LESS THAN(TO_DATE('03/01/2010','MM/DD/YYYY')),
PARTITION sales_mar2010 VALUES LESS THAN(TO_DATE('04/01/2010','MM/DD/YYYY')),
PARTITION sales_apr2010 VALUES LESS THAN(TO_DATE('05/01/2010','MM/DD/YYYY'))
);

           当然我们还可以在每个分区声明后面加上TABLESPACE关键字将不同的分区放到不同的表空间中，例如：PARTITION sales_jan2000 VALUES LESS THAN(TO_DATE('02/01/2000','MM/DD/YYYY')) TABLESPACE sales_jan,

         2.列表分区（List Partitioning）

           该分区的特点是某列的值只有几个，基于这样的特点我们可以采用列表分区。例如：

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'),
PARTITION sales_east VALUES ('New York', 'Virginia', 'Florida'),
PARTITION sales_central VALUES('Texas', 'Illinois'),
PARTITION sales_other VALUES(DEFAULT)
);

           列表分区与散列分区及范围分区不同，列表分区不支持列组合。如果某个表使用了列表分区，那么key只能由一个单独的列构成。对于列表分区一般都有一个以DEFAULT为key的分区，这个分区是用来存放在无法归属到其他分区的数据的。避免因为出现无法归属的行数据时出现错误。

         3.散列分区（Hash Partitioning）

           这类分区是在列值上使用散列算法，以确定将行放入哪个分区中。当列的值没有合适的条件时，建议使用散列分区。散列分区为通过指定分区编号来均匀分布数据的一种分区类型，因为通过在I/O设备上进行散列分区，使得这些分区大小一致。以下是一个散列分区的例子：

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 (ts1, ts2, ts3, ts4);

           hash分区最主要的机制是根据hash算法来计算具体某条纪录应该插入到哪个分区中,hash算法中最重要的是hash函数，Oracle中如果你要使用hash分区，只需指定分区的数量即可。建议分区的数量采用2的n次方，这样可以使得各个分区间数据分布更加均匀。

         4.复合分区（Composite Partitioning）

           复合分区是先将表用范围分区然后再将每个分区以散列分区或者列表分区的形式划分为子分区。即范围-散列分区（range-hash），范围-列表分区（range-list）。以下分别给出两种分区的例子：

CREATE TABLE bimonthly_regional_sales
(deptno NUMBER,
item_no VARCHAR2(20),
txn_date DATE,
txn_amount NUMBER,
state VARCHAR2(2))
PARTITION BY RANGE (txn_date)
SUBPARTITION BY LIST (state)

SUBPARTITION TEMPLATE(
SUBPARTITION east VALUES('NY', 'VA', 'FL') TABLESPACE ts1,
SUBPARTITION west VALUES('CA', 'OR', 'HI') TABLESPACE ts2,
SUBPARTITION central VALUES('IL', 'TX', 'MO') TABLESPACE ts3)
(
PARTITION janfeb_2000 VALUES LESS THAN (TO_DATE('1-MAR-2000','DD-MON-YYYY')),
PARTITION marapr_2000 VALUES LESS THAN (TO_DATE('1-MAY-2000','DD-MON-YYYY')),
PARTITION mayjun_2000 VALUES LESS THAN (TO_DATE('1-JUL-2000','DD-MON-YYYY'))
);

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)
SUBPARTITION TEMPLATE(
SUBPARTITION sp1 TABLESPACE ts1,
SUBPARTITION sp2 TABLESPACE ts2,
SUBPARTITION sp3 TABLESPACE ts3,
SUBPARTITION sp4 TABLESPACE ts4)
(PARTITION sales_jan2000 VALUES LESS THAN(TO_DATE('02/01/2000','MM/DD/YYYY'))
PARTITION sales_feb2000 VALUES LESS THAN(TO_DATE('03/01/2000','MM/DD/YYYY'))
PARTITION sales_mar2000 VALUES LESS THAN(TO_DATE('04/01/2000','MM/DD/YYYY'))
PARTITION sales_apr2000 VALUES LESS THAN(TO_DATE('05/01/2000','MM/DD/YYYY'))
PARTITION sales_may2000 VALUES LESS THAN(TO_DATE('06/01/2000','MM/DD/YYYY')));

      五.分区表的简单维护性操作：
         1.添加分区
           以下代码给SALES表添加了一个P3分区
           ALTER TABLE SALES ADD PARTITION P3 VALUES LESS THAN(TO_DATE('2003-06-01','YYYY-MM-DD'));
           注意：以上添加的分区界限应该高于最后一个分区界限。
           以下代码给SALES表的P3分区添加了一个P3SUB1子分区
           ALTER TABLE SALES MODIFY PARTITION P3 ADD SUBPARTITION P3SUB1 VALUES('COMPLETE');
         2.删除分区
           以下代码删除了P3表分区：
           ALTER TABLE SALES DROP PARTITION P3;
           在以下代码删除了P4SUB1子分区：
           ALTER TABLE SALES DROP SUBPARTITION P4SUB1;
           注意：如果删除的分区是表中唯一的分区，那么此分区将不能被删除，要想删除此分区，必须删除表。
         3.截断分区
           截断某个分区是指删除某个分区中的数据，并不会删除分区，也不会删除其它分区中的数据。当表中即使只有一个分区时，也可以截断该分区。通过以下代码截断分区：
           ALTER TABLE SALES TRUNCATE PARTITION P2;
           通过以下代码截断子分区：
           ALTER TABLE SALES TRUNCATE SUBPARTITION P2SUB2;
         4.合并分区
           合并分区是将相邻的分区合并成一个分区，结果分区将采用较高分区的界限，值得注意的是，不能将分区合并到界限较低的分区。以下代码实现了P1 P2分区的合并：
           ALTER TABLE SALES MERGE PARTITIONS P1,P2 INTO PARTITION P2;
         5.拆分分区
           拆分分区将一个分区拆分两个新分区，拆分后原来分区不再存在。注意不能对HASH类型的分区进行拆分。
           ALTER TABLE SALES SBLIT PARTITION P2 AT(TO_DATE('2003-02-01','YYYY-MM-DD')) INTO (PARTITION P21,PARTITION P22);
         6.接合分区(coalesca)
           结合分区是将散列分区中的数据接合到其它分区中，当散列分区中的数据比较大时，可以增加散列分区，然后进行接合，值得注意的是，接合分区只能用于散列分区中。通过以下代码进行接合分区：
           ALTER TABLE SALES COALESCA PARTITION;
         7.重命名表分区
           以下代码将P21更改为P2
           ALTER TABLE SALES RENAME PARTITION P21 TO P2;
         8.相关查询
           跨分区查询
           select sum( *) from (select count(*) cn from t_table_SS PARTITION (P200709_1) union all select count(*) cn from t_table_SS PARTITION (P200709_2));
           查询表上有多少分区
           SELECT * FROM useR_TAB_PARTITIONS WHERE TABLE_NAME='tableName'
           查询索引信息
           select object_name,object_type,tablespace_name,sum(value) from v$segment_statistics where statistic_name IN('physical reads','physical write','logical reads')and object_type='INDEX' group by object_name,object_type,tablespace_name order by 4 desc
--显示数据库所有分区表的信息：
select * from DBA_PART_TABLES 
--显示当前用户可访问的所有分区表信息:
select * from ALL_PART_TABLES 
--显示当前用户所有分区表的信息：
select * from USER_PART_TABLES 
--显示表分区信息 显示数据库所有分区表的详细分区信息：
select * from DBA_TAB_PARTITIONS 
--显示当前用户可访问的所有分区表的详细分区信息：
select * from ALL_TAB_PARTITIONS 
--显示当前用户所有分区表的详细分区信息：
select * from USER_TAB_PARTITIONS 
--显示子分区信息 显示数据库所有组合分区表的子分区信息：
select * from DBA_TAB_SUBPARTITIONS 
--显示当前用户可访问的所有组合分区表的子分区信息：
select * from ALL_TAB_SUBPARTITIONS 
--显示当前用户所有组合分区表的子分区信息：
select * from USER_TAB_SUBPARTITIONS 
--显示分区列 显示数据库所有分区表的分区列信息：
select * from DBA_PART_KEY_COLUMNS 
--显示当前用户可访问的所有分区表的分区列信息：
select * from ALL_PART_KEY_COLUMNS 
--显示当前用户所有分区表的分区列信息：
select * from USER_PART_KEY_COLUMNS 
--显示子分区列 显示数据库所有分区表的子分区列信息：
select * from DBA_SUBPART_KEY_COLUMNS 
--显示当前用户可访问的所有分区表的子分区列信息：
select * from ALL_SUBPART_KEY_COLUMNS 
--显示当前用户所有分区表的子分区列信息：
select * from USER_SUBPART_KEY_COLUMNS 
--怎样查询出oracle数据库中所有的的分区表
select * from user_tables a where a.partitioned='YES'
--删除一个表的数据是
truncate table table_name;
--删除分区表一个分区的数据是
alter table table_name truncate partition p5;

 

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/276487/viewspace-694564/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/276487/viewspace-694564/