分区表

本文主要介绍关于分区表的概念及操作：
1.分区表的概念
2.表分区的具体作用
3.表分区的优缺点
4.何时分区
5.分区的条件及特性
6.分区键
7.表分区的几种类型及操作方法
8.对表分区的维护性操作
9.分区表相关的数据字典


1.分区表的概念
在理解分区表的概念之前，先看一下表空间的概念。
表空间：是一个或多个数据文件的集合，所有的数据对象都存放在指定的表空间中，
但主要存放的是表，所以称作表空间。
注意：表空间是一个用来关系数据存储的逻辑概念，表空间只是和数据文件发生关系，
数据文件是物理的，一个表空间可以包含多个数据文件，而一个数据文件只能隶属一个表空间。
分区表：当表中的数据量不断增大，查询数据的速度就会变慢，应用程序的性能
就会下降，这时就应该考虑对表进行分区。表进行分区后，逻辑上表仍然是一张完整
的表，只是将表中的数据在物理上存放到多个表空间(物理文件上)，这样查询数据时，
不至于每次都扫描整张表。
注意：分区表可以在同一个表空间，也可以不在同一个表空间。


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


3.表分区的优缺点
分区的优点：
①：提高查询性能，只需要搜索特定分区，而非整张表，提高查询速度
②：节约维护时间，单个分区的数据装载，索引重建，备份，维护等将远小于整张表的维护时间。
③：节约维护成本，可以单独备份和恢复每个分区。
④：均衡I/O，将不同的分区映射到不同的磁盘以平衡I/O，提高并发。
分区的缺点：
已经存在的表没有方法可以直接转化为分区表，不过Oracle提供了在线重定义表的功能。


4.何时分区
对于何时分区，Oracle官方文档提供以下两条建议：
①：表的大小超过2GB。
查看表实际使用空间大小：
analyze table emp compute statistics; 
select (num_rows * avg_row_len)/1024/1024 || 'M'
from user_tables 
where table_name = 'EMP';
查看分配给表的物理空间数量：
select segment_name, bytes/1024/1024 || 'M' 
from user_segments 
where segment_type = 'TABLE'; 
或者
Select Segment_Name,Sum(bytes)/1024/1024 || 'M'
From User_Extents 
Group By Segment_Name
②：如果表中包含历史数据，且新数据会被添加到最新的表空间中。典型的例子是一种历史表，
其中只有当前月份的数据可以被修改，而其他十一个月的数据为只读。


5.分区的条件及特性
共性：不同的分区之间必须有相同的逻辑属性，比如表名，列名，数据类型，约束等。
个性：各个分区可以有不同的物理属性，比如pctfree,pctused,tablespaces。
分区独立性：即使某些分区不可用，其它分区仍然柯勇。
特殊性：含有LONG、LONGRAW数据类型的表不能进行分区。


6.分区键
分区表内的每个数据行都能且只能分配到一个分区中。分区键是决定数据行是属于哪个分区中
的一组数据列。Oracle在执行插入，更新，及删除操作时能根据分区键自动地选择分区。分区键
的特点如下：
①由1至16个数据列顺序构成。
②不能包含LEVEL,ROWID或MLSLABEL虚列，也不能包含类型为ROWID的列。
③能包含可为空的列。


7.表分区的几种类型及操作方法
①；范围分区
范围分区将数据基于范围映射到每一个分区，这个范围是你在创建分区时指定的分区键决定的。这种分区
方式最为常用，并且分区键经常使用日期。
使用范围分区的原则：
Ⅰ：每个分区都必须有一个VALUES LESS THEN子句，它指定了一个不包括在该分区中的上限值。分区键
的任何等于或大于这个上限值的记录都会被加入到一下一个高一些的分区中。
Ⅱ：所有分区，除了第一个，都会有一个隐式的下限值，这个值就是此分区的前一个分区的上限值。
Ⅲ：在最高的分区中，MAXVALUE被定义。MAXVALUE代表了一个不确定的值。这个值高于其它分区中的任何分区键
的值，也可以理解为高于任何分区中指定的VALUE LESS THEN的值，同时包括空值。
EXAMPLE1：
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_jan2000 VALUES LESS THAN(TO_DATE('02/01/2000','MM/DD/YYYY')) tablespace sal_range_jan2000,
PARTITION sales_feb2000 VALUES LESS THAN(TO_DATE('03/01/2000','MM/DD/YYYY')) tablespace sal_range_feb2000,
PARTITION sales_mar2000 VALUES LESS THAN(TO_DATE('04/01/2000','MM/DD/YYYY')) tablespace sal_range_mar2000,
PARTITION sales_apr2000 VALUES LESS THAN(TO_DATE('05/01/2000','MM/DD/YYYY')) tablespace sal_range_apr2000
);


EXAMPLE2：
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)
    );
在该例子中，分区子句未指定表空间时则位于缺省的表空间。
当前用户：
SQL> show user;
USER is "SYS"
查询当前用户的缺省表空间
SQL> select username,default_tablespace
  2  from user_users;


USERNAME                       DEFAULT_TABLESPACE
------------------------------ ------------------------------
SYS                            SYSTEM
查询表r所在表空间
SQL> select table_name,tablespace_name
  2  from user_tab_partitions
  3  where table_name='R';


TABLE_NAME                     TABLESPACE_NAME
------------------------------ ------------------------------
R   SYSTEM
R                              SYSTEM
R                              SYSTEM
R                              SYSTEM
通过如上几个子句，的确证明了分区子句为指定表空间时则位于缺省的表空间。


EXAMPLE3：
在EXAMPLE2中创建表r的时候指定了MAXVALUE，那如果没有指定，插入一个超过指定大小的数字，
能否插入？
SQL> create table r (a int)
  2  partition by range (a)
  3  (
  4  partition p1 values less than (10),
  5  partition p2 values less than (20),
  6  partition p3 values less than (30)
  7  );
SQL> insert into r values(35);
insert into r values(35)
            *
ERROR at line 1:
ORA-14400: inserted partition key does not map to any partition
显而易见，在没有指定MAXVALUE的时候插入超过指定大小的数字是不可以的。 
注意：VALUE LESS THEN意味着即使插入30仍然会报错，不要误认为<=。


查询分区中数据：
select * from r partition(p1)


一个分区的损坏不会影响其它分区的数据：
alter table r drop partition p1
select * from r
select * from r partition(p4)
除分区数据不见外，其它都正常。


添加分区：
①没有maxvalue分区
创建分区表：
SQL> create table r1 (id number)
  2  partition by range(id)
  3  (
  4  partition p1 values less than (10),
  5  partition p2 values less than (20),
  6  partition p3 values less than (30)
  7 )
  8  ;
 Table created.
查看分区表信息：
SQL> select table_name,partition_name,high_value
  2  from user_tab_partitions
  3  where table_name='R1';


TABLE_NAME PARTITION_ HIGH_VALUE
---------- ---------- ----------
R1         P1         10
R1         P2         20
R1         P3         30
添加分区：
SQL> alter table r1 add partition p4 values less than (40);


Table altered.
SQL> alter table r1 add partition p5 values less than (5);
alter table r1 add partition p5 values less than (5)
                             *
ERROR at line 1:
ORA-14074: partition bound must collate higher than that of the last partition
在添加p4的成功,p5的时候失败(有maxvalue分区实验中讲明如何分区)
再次查看分区表信息：
SQL> select table_name,partition_name,high_value
  2  from user_tab_partitions
  3  where table_name='R1';


TABLE_NAME PARTITION_ HIGH_VALUE
---------- ---------- ----------
R1         P1         10
R1         P2         20
R1         P3         30
R1         P4         40
以上结果确实表名了p4添加成功,但是p5没有添加成功。
②有maxvalue分区
创建分区表：
SQL> create table r2 (id number)
  2  partition by range(id)
  3  (
  4  partition p1 values less than (10),
  5  partition p2 values less than (20),
  6  partition p3 values less than (30),
  7  partition p4 values less than (maxvalue)
  8  );


Table created.
查看分区表信息：
SQL> select table_name,partition_name,high_value
  2  from user_tab_partitions
  3  where table_name='R2';


TABLE_NAME PARTITION_ HIGH_VALUE
---------- ---------- ----------
R2         P1         10
R2         P2         20
R2         P3         30
R2         P4         MAXVALUE
添加分区：
SQL> alter table r2 add partition p5 values less than (40);
alter table r2 add partition p5 values less than (40)
                             *
ERROR at line 1:
ORA-14074: partition bound must collate higher than that of the last partition
很不幸，再添加分区p5的时候并没有成功，原来有了maxvalue就不能直接add partition,
而是需要max分区split。如下：
SQL> alter table r2 split partition p4 at (40) into
  2  (partition p5, partition p4);


Table altered.
再次查看分区表信息：
SQL> select table_name,partition_name,high_value
  2  from user_tab_partitions
  3  where table_name='R2';


TABLE_NAME PARTITION_ HIGH_VALUE
---------- ---------- ----------
R2         P1         10
R2         P2         20
R2         P3         30
R2         P5         40
R2         P4         MAXVALUE
可以发现已经成功的添加了分区p5。不难发现对于有maxvalue分区的分区表来说，
其实切割最后一个分区。
在进行r1表添加分区p5的时候所报的错误与r2进行传统添加分区报的错误相同，那么我们是否
可以使用切割一个分区来进行添加呢？
SQL> alter table r1 split partition p1 at (5) into   
  2  (partition p5, partition p1);


Table altered.
查看r1分区表信息：
SQL> select table_name,partition_name,high_value
  2  from user_tab_partitions
  3  where table_name='R1';


TABLE_NAME PARTITION_ HIGH_VALUE
---------- ---------- ----------
R1         P5         5
R1         P1         10
R1         P2         20
R1         P3         30
R1         P4         40
好吧，添加成功了。;-)


查看分区表的相关信息：
select table_name,partition_name,subpartition_count,
tablespace_name,user_stats
from user_tab_partitions;


②列表分区
Ⅰ：列表分区可以控制如何将行映射到分区中去，可以在每个分区的键值上定义离散的值，不同于范围分区和哈希分区。
Ⅱ：范围分区与分区相关联，为分区列假设了一个值的自然范围，故不可能将该值的范围以外的分区组织到一起。
Ⅲ：哈希分区时不允许对数据的划分进行控制，因为系统使用的是散列函数来划分数据的。
Ⅳ：列表分区的优点在于按照自然的方式将无序和不相关的数据集合分组。
Ⅴ：列表分区不支持多列分区，如果将表按列分区，那么分区键就只能有表的一个单独列组成。范围分区和哈希分区
可以对多列进行分区。
EXAMPLE1：
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)
);
注意：PARTITION sales_other VALUES(DEFAULT)
通过这句，我们可以避免当数据全部不匹配以上的列表数据时进行存储，而不是报错。


③哈希分区(散列分区)
哈希分区能够很容易对数据进行分区，因为语法很简单，很容易时间。在下面几种情况下使用哈希分区比范围分区更好：
Ⅰ：事先不知道需要将多少数据映射到给定范围的时候。
Ⅱ：分区的范围大小很难确定，或者很难平衡的时候。
Ⅲ：范围分区时是数据得到不希望的聚集时。
Ⅳ：性能特性，如并行DML、分区剪枝和分区连接很重要的时候。
注意事项：
分裂(splitting)、删除(dropping)、和合并(merging)分区不能应用与哈希分区，但是哈希分区能够合并(coalesced)
和添加。
创建哈希分区有两种方法：
Ⅰ：指定分区数量
Ⅱ：指定分区的名字，但是两者不能同时指定。
EXAMPLE1：指定分区数量
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);
EXAMPLE2：指定分区的名字
create table dept3 (deptno number,deptname varchar2(32))
        partition by hash(deptno)             
        (partition p1 tablespace p1,
        partition p2 tablespace p2);
建议：哈希分区最主要的机制是根据哈希算法来计算具体某条记录应该插入到哪个分区中，哈希算法中
最重要的是哈希函数，Oracle中如果你要使用哈希分区，如果指定数量，建议分区的数量采用2的n次方，这样可以
使得各个分区间数据分布更加均匀。


④组合分区、合成分区
组合分区是基于范围分区和列表分区，表首先按某列进行范围分区，然后再按某列进行列表或者哈希分区，分区之中
的分区被称为子分区。
组合分区比范围分区更容易管理，充分使用了哈希分区的并行优势。组合分区支持历史数据和条块数据两者。
如添加新的范围分区，同时为DML操作提供更高层的并行性。
EXAMPLE1：
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')));
 
EXAMPLE2：
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'))
);
 
8.对表分区的维护性操作
.添加分区
        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')
        )
 
.删除分区
        alter table T_TRACK drop partition p_2005_04;


.添加子分区
        alter table T_TRACK
        modify partition P_2005_01
        add subpartition P_2005_01_P1017 values('P1017');

.删除子分区
        alter table T_TRACK drop subpartition p_2005_01_p1017;


.截断一个分区表中的一个分区的数据：
        alter table sales3  truncate partition sp1
            这种方式会使全局分区索引无效
        alter table sales3 truncate partition sp1 update indexes
            这种方式全局分区索引不会无效

.截断分区表的子分区
        alter table comp truncate subpartition sub1

.截断带有约束的分区表
        a、禁用约束
          alter table sales disable constraint dname_sales1
        b、截断分区
          alter table sales truncate partitoin dec
        c、启用约束
          alter table sales enable constraint dname_sales1


.查看一个表是不是分区表
        select table_name,partitioned from user_tables;
        TABLE_NAME                     PAR
        ------------------------------ ---
        DEPT                           NO
        DEPT3                          YES


.将一个表的分区从一个表空间移动到另一个表空间
        a、查看分区在哪个表空间
          SELECT TABLE_OWNER,TABLE_NAME,PARTITION_NAME,TABLESPACE_NAME,
          SUBPARTITION_COUNT
          FROM DBA_TAB_PARTITIONS WHERE 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_PARTITIONS WHERE TABLE_OWNER='SCOTT';
 
        移动表空间后，要重建索引，否则索引会变得无效
        alter index xxx rebuild


.合并分区(merge)：
        alter table sales3 merge partitons sp1,sp3 into partition sp3
        合并后的分区名，不能是边界值较低的那个

.合并分区(coalesca)
alter table sales coalesca partition


.删除分区：
        alter table scott.sales_composite drop partition SALES_JAN2000;


9.分区表相关的数据字典
DBA_TAB_PARTITIONS
DBA_IND_PARTITIONS
DBA_TAB_SUBPARTITIONS
DBA_IND_SUBPARTITIONS

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

转载于:http://blog.itpub.net/29173997/viewspace-1149221/