Oracle表分区

1、理论知识

Oracle提供了分区技术以支持VLDB(Very Large DataBase)。分区表通过对分区列的判断，把分区列不同的记录，放到不同的分区中，分区完全对应用透明。

Oracle的分区表可以包括多个分区，每个分区都是一个独立的段（SEGMENT），可以存放到不同的表空间中。查询时可以通过查询表来访问各个分区中的数据，也可以通过在查询时直接指定分区的方法来进行查询。

1.1 表空间

是一个或多个数据文件的集合，所有的数据对象都存放在指定的表空间中，但主要存放的是表， 所以称作表空间。

1.2 分区表

当表中的数据量不断增大，查询数据的速度就会变慢，应用程序的性能就会下降，这时就应该考虑对表进行分区。表进行分区后，逻辑上表仍然是一张完整的表，只是将表中的数据在物理上存放到多个表空间(物理文件上)，这样查询数据时，不至于每次都扫描整张表。

1.3 什么时候用表分区

官网的2个建议如下

1、Tablesgreater than 2GB should always be considered for partitioning.(表中的数据大于2GB)

2、Tables containing historical data, in which new data is added into thenewest partition. A typical example is a historical table where only thecurrent month's data is updatable and the other 11 months are read only.(表中包含历史数据，新的数据被增加到新的分区中。一个典型的例子就是历史表，当月的数据可以更改，其他11个月的数据是只读的。)

2、表分区的优缺点

2.1 优点

1、改善查询性能：对分区对象的查询可以仅搜索自己关心的分区，提高检索速度

2、增强可用性：如果表的某个分区出现故障，表在其他分区的数据仍然可用

3、维护方便：如果表的某个分区出现故障，需要修复数据，只修复该分区即可

4、均衡I/O：可以把不同的分区映射到磁盘以平衡I/O，改善整个系统性能

2.2 缺点

1、已经存在的表没有方法可以直接转化为分区表。不过 Oracle 提供了在线重定义表的功能

3、表分区的几种形式

表分区的类型有很多，范围分区，列表分区，散列分区，组合分区以及子分区。ORACLE11G以前两两组合都必须以范围分区作为一级分区的开头，ORACLE 11G 以后就可以各种组合了。ORACLE目前最多支持2级别分区，但这个级别已经够我们使用了，下面就介绍三种分区类型。

3.1 范围分区(Rang)

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

3.1.1 规则

使用范围分区需要考虑到以下几个规则：

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

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

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

3.1.2 应用

假设有一个CUSTOMER表，表中有数据200000行，我们将此表通过CUSTOMER_ID进行分区，每个分区存储100000行，我们将每个分区保存到单独的表空间中，这样数据文件就可以跨越多个物理磁盘。下面是创建表和分区的代码，如下：

CREATETABLECUSTOMER(

CUSTOMER_IDNUMBERNOTNULLPRIMARYKEY,

FIRST_NAMEVARCHAR2(30) NOTNULL,

LAST_NAMEVARCHAR2(30) NOTNULL,

STATUSCHAR(1),

CREATE_DATEDATE

)

PARTITIONBYRANGE(CUSTOMER_ID)

(

PARTITIONCUSTOMER01 VALUESLESSTHAN (100000),

PARTITIONCUSTOMER02 VALUESLESSTHAN (200000)

);

按时间划分：

CREATETABLECUSTOMER(

CUSTOMER_IDNUMBERNOTNULLPRIMARYKEY,

FIRST_NAMEVARCHAR2(30) NOTNULL,

LAST_NAMEVARCHAR2(30) NOTNULL,

STATUSCHAR(1),

CREATE_DATEDATE

)

PARTITIONBYRANGE(CREATE_DATE)

(

PARTITIONCUSTOMER01 VALUESLESSTHAN (TO_DATE('01-05-2017','DD-MM-YYYY')),

PARTITIONCUSTOMER02 VALUESLESSTHAN(TO_DATE('01-06-2017','DD-MM-YYYY')),

PARTITIONCUSTOMER03 VALUESLESSTHAN(TO_DATE('01-07-2017','DD-MM-YYYY')),

PARTITIONCUSTOMER_OTHER VALUESLESSTHAN (MAXVALUE)

);

3.2 列表分区(List)

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

3.2.1 规则

1、该分区列只能有一个，不能像范围分区或者散列分区那样同时指定多个列做为分区依赖列，但它的单个分区对应值可以是多个。

2、在分区时必须确定分区列可能存在的值，一旦插入的列值不在分区范围内，则插入/更新就会失败，因此通常建议使用列表分区时，要创建一个default分区存储那些不在指定范围内的记录，类似范围分区中的maxvalue分区。

3.2.2 应用

在根据某字段，如城市代码分区时，可以指定default，把非分区规则的数据，全部放到这个default分区。

CREATETABLECUSTADDR(

IDNUMBERNOTNULL,

AREACODEVARCHAR2(32)

)

PARTITIONBYLIST(AREACODE)

( PARTITIONT_LIST025 VALUES ('025'),

PARTITIONT_LIST372 VALUES ('372'),

PARTITIONT_LIST510 VALUES ('510'),

PARTITIONP_OTHER VALUES (DEFAULT)

);

3.3 散列分区

对于那些无法有效划分范围的表，可以使用Hash分区。Hash分区会将表中的数据平均分配到你指定的几个分区中，列所在分区是依据分区列的Hash值自动分配，因此你并不能控制也不知道哪条记录会被放到哪个分区中，Hash分区也可以支持多个依赖列。

3.3.1 规则

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

3.3.2 应用

散列分区创建分区有两种方式：明确指定分区名称和系统自动生成分区名称。

明确指定：

CREATETABLEHASH_TABLE (

  COL NUMBER(8),

  INF VARCHAR2(100)

)

PARTITIONBYHASH(COL)

(

  PARTITIONPART01,

  PARTITIONPART02,

  PARTITIONPART03

);

系统生成：

CREATETABLEEMP(

 EMPNO NUMBER (4),

 ENAME VARCHAR2 (30),

 SAL   NUMBER

)

PARTITIONBY  HASH (EMPNO) PARTITIONS8;

4、表分区的维护性操作

4.1 添加分区

以下代码pdba表添加了一个P4分区

ALTERTABLE pdba

ADDPARTITION P4 VALUES LESS THAN(TO_DATE('2017-06-01','YYYY-MM-DD'));

注意：以上添加的分区界限应该高于最后一个分区界限。

4.2 删除分区

以下代码删除了pdba的P4表分区

ALTERTABLE pdba DROPPARTITION P4;

注意：如果删除的分区是表中唯一的分区，那么此分区将不能被删除，要想删除此分区，必须删除表。

4.3 删除分区表中的数据

删除某个分区表中的数据并不会删除分区，也不会删除其它分区中的数据。当表中即使只有一个分区时，也可以删除该分区的数据。

以下代码删除了pdba的P4分区的数据

ALTERTABLE pdba TRUNCATEPARTITION P4;

4.4 合并分区

合并分区是将相邻的分区合并成一个分区，结果分区将采用较高分区的界限，值得注意的是，不能将分区合并到界限较低的分区。以下代码实现了P1 P2分区的合并：

ALTERTABLE pdba MERGEPARTITIONS P1,P2INTOPARTITION P2;

4.5 拆分分区

拆分分区将一个分区拆分两个新分区，拆分后原来分区不再存在。注意不能对HASH类型的分区进行拆分。

ALTERTABLE pdba splitPARTITION P2 AT(TO_DATE('2003-02-01','YYYY-MM-DD')) INTO (PARTITION P21,PARTITION P22);

4.6 接合分区

结合分区是将散列分区中的数据接合到其它分区中，当散列分区中的数据比较大时，可以增加散列分区，然后进行接合，值得注意的是，接合分区只能用于散列分区中。通过以下代码进行接合分区：

ALTERTABLE EMP COALESCEPARTITION;

注意：每接合一次，分区就减少一个，当接合到只有最后一个分区的时候，接合将不能进行。

4.7 重命名分区

以下代码将P21更改为P2

ALTERTABLE pdba RENAMEPARTITION P21 TO P2;

5、表分区相关查询SQL

5.1 查看所有分区

SELECT *FROM USER_TAB_PARTITIONS;

5.2 查看当前用户下哪些表是分区表

SELECT *FROM USER_PART_TABLES;

5.3 查看表上有多少个分区

SELECT *FROM USER_TAB_PARTITIONS WHERETABLE_NAME='表名';

5.4 查看一个表中的某个分区数据

SELECT *FROM表名PARTITION (分区名);

5.5 跨分区查询

selectsum(cn)from (

selectcount(*)cn from pdba PARTITION(p2)

unionall

selectcount(*)cn from pdba PARTITION(p3)

);