oracle表分区创建

一、什么是分区表

表分区有以下优点：
1、数据查询：数据被存储到多个文件上，减少了I/O负载，查询速度提高。
2、数据修剪：保存历史数据非常的理想。
3、备份：将大表的数据分成多个文件，方便备份和恢复。
4、并行性：可以同时向表中进行DML操作，并行性性能提高，均衡I/O：可以把不同的分区映射到磁盘以平衡I/O，改善整个系统性能。
5、增强可用性：如果表的某个分区出现故障，表在其他分区的数据仍然可用；
6、维护方便：如果表的某个分区出现故障，需要修复数据，只修复该分区即可；
7、改善查询性能：对分区对象的查询可以仅搜索自己关心的分区，提高检索速度。
8、需要注意的是包含LONG、LONGRAW数据类型的表不能分区,如果表格大于2G需要考虑分区。

二、分区表的种类

1、RANGE 范围分区

说明：针对记录字段的值在某个范围。
规则：
（1）、每一个分区都必须有一个VALUES LESS THEN子句，它指定了一个不包括在该分区中的上限值。
分区键的任何值等于或者大于这个上限值的记录都会被加入到下一个高一些的分区中。
（2）、所有分区，除了第一个，都会有一个隐式的下限值，这个值就是此分区的前一个分区的上限值。
（3）、在最高的分区中，MAXVALUE被定义。MAXVALUE代表了一个不确定的值。这个值高于其它分区中的任何分区键的值，
也可以理解为高于任何分区中指定的VALUE LESS THEN的值，同时包括空值。若不添加maxvalue的分区插入数值一旦超过设置的最大上限会报错。

例一，按date范围创建分区表

CREATE TABLE PART_TAB_CUSTOMER_BY_RANGE 
( 
  CUSTOMER_ID NUMBER NOT NULL PRIMARY KEY, 
  FIRST_NAME VARCHAR2(30) NOT NULL, 
  LAST_NAME VARCHAR2(30) NOT NULL, 
  PHONE VARCHAR2(15) NOT NULL, 
  EMAIL VARCHAR2(80), 
  SEX VARCHAR2(10),
  STATUS VARCHAR2(10),
  INSERT_DATE DATE 
) 
PARTITION BY RANGE (INSERT_DATE) --按时间分区
( 
  PARTITION DATE_RANGE1 VALUES LESS THAN (TO_DATE(' 2001-01-01', 'YYYY-MM-DD')) TABLESPACE part_Data1, 
  PARTITION DATE_RANGE2 VALUES LESS THAN (TO_DATE(' 2007-01-01', 'YYYY-MM-DD')) TABLESPACE part_Data2,
  PARTITION DATE_RANGE3 VALUES LESS THAN (maxvalue) TABLESPACE part_Data3 
) 

例二、按照number范围分区

PARTITION BY RANGE (CUSTOMER_ID) --按id分区
( 
 PARTITION CUS_PART1 VALUES LESS THAN (100000) TABLESPACE part_Data1, 
 PARTITION CUS_PART2 VALUES LESS THAN (200000) TABLESPACE part_Data2,
 PARTITION CUS_PART2 VALUES LESS THAN (maxvalue) TABLESPACE part_Data3
)

2、LIST 列表分区

说明：该分区的特点是某列的值只有有限个值，基于这样的特点我们可以采用列表分区。
规则：默认分区为DEFAULT，若不添加DEFAULT的分区插入数值不属于所设置的分区会报错。
例一、姓氏

CREATE TABLE PART_TAB_CUSTOMER_BY_LIST
( 
  CUSTOMER_ID NUMBER NOT NULL PRIMARY KEY, 
  PHONE VARCHAR2(15) NOT NULL, 
  EMAIL VARCHAR2(80), 
  SEX VARCHAR2(10),
  CORP_ID VARCHAR2(3),
  INSERT_DATE DATE，
  SUM_DATE varchar2(4)
) 
PARTITION BY LIST (SEX) 
( 
  PARTITION MALE      VALUES ('男')    TABLESPACE part_Data1, 
  PARTITION FEMALE    VALUES ('女')    TABLESPACE part_Data2 
) 

例二、varchar2的日期

PARTITION BY LIST (SUM_DATE) 
( 
  PARTITION SUM_DATE1    VALUES ('2012')    TABLESPACE part_Data1, 
  PARTITION SUM_DATE2    VALUES ('2013')    TABLESPACE part_Data2，
  PARTITION SUM_DATE2    VALUES (DEFAULT)    TABLESPACE part_Data2 
) 

3、HASH 散列分区

说明：这类分区是在列值上使用散列算法，以确定将行放入哪个分区中。
规则：当列的值没有合适的条件，没有范围的规律，也没有固定的值，建议使用散列分区。
散列分区为通过指定分区编号来均匀分布数据的一种分区类型，因为通过在I/O设备上进行散列分区，
使得这些分区大小一致。建议分区的数量采用2的n次方，这样可以使得各个分区间数据分布更加均匀。

创建hash分区有两种方法：一种方法是指定分区的名字，另一种方法是指定分区数量。

例一、常规方法指定分区名字

CREATE TABLE PART_TAB_CUSTOMER_BY_HASH
( 
  CUSTOMER_ID NUMBER NOT NULL PRIMARY KEY, 
  FIRST_NAME VARCHAR2(30) NOT NULL, 
  LAST_NAME VARCHAR2(30) NOT NULL, 
  PHONE VARCHAR2(15) NOT NULL, 
  EMAIL VARCHAR2(80), 
  SEX VARCHAR2(10),
  STATUS VARCHAR2(10),
  INSERT_DATE DATE 
) 
PARTITION BY HASH (CUSTOMER_ID) --按id散列
( 
  PARTITION hash1 TABLESPACE part_Data1, 
  PARTITION hash2 TABLESPACE part_Data2
) 

例二、指定分区数量
PARTITION BY HASH (empno) PARTITIONS 2 STORE IN (part_Data1,part_Data2);
–往往我们不需要知道bash分区的名字，因为数据放在哪个分区是oracle根据bash算法存放的，并不是用户指定，
所以当用户插入一条记录，并不能确定放在哪个分区，这个不同于range和list

3、复合分区

说明：顾名思义，复合分区就由range+list+hash两两组合而来，一般分为range+list,list+range,range+hash,list+bash，这里指列出几种常用组合。
规则：如果组合中存在hash，皆把hash分区作为子分区，原因大家可以通过hash分区的性质知道。
例一、range+list，这种分区是基于范围分区和列表分区，表首先按某列进行范围分区，然后再按某列进行列表分区。

CREATE TABLE PART_TAB_SALE_RANGE_LIST 
( 
  PRODUCT_ID VARCHAR2(5), 
  SALES_DATE DATE, 
  SALES_COST NUMBER(10), 
  STATUS VARCHAR2(10),
) 
PARTITION BY RANGE(SALES_DATE) 
  SUBPARTITION BY LIST (STATUS) 
( 
  PARTITION P1 VALUES LESS THAN (TO_DATE('2003-01-01','YYYY-MM-DD')) TABLESPACE part_Data1 
  ( 
    SUBPARTITION P1SUB1 VALUES ('ACTIVE') TABLESPACE part_Data1, 
    SUBPARTITION P1SUB2 VALUES ('INACTIVE') TABLESPACE part_Data2,
    SUBPARTITION P1SUB3 VALUES (DEFAULT) TABLESPACE part_Data3
  ), 
  PARTITION P2 VALUES LESS THAN (TO_DATE('2003-03-01','YYYY-MM-DD')) TABLESPACE part_Data2 
  ( 
    SUBPARTITION P2SUB1 VALUES ('ACTIVE') TABLESPACE part_Data1, 
    SUBPARTITION P2SUB2 VALUES ('INACTIVE') TABLESPACE part_Data2,
    SUBPARTITION P2SUB3 VALUES (DEFAULT) TABLESPACE part_Data3 
  ),
  PARTITION P3 VALUES LESS THAN (maxvalue) TABLESPACE part_Data3
  ( 
    SUBPARTITION P3SUB1 VALUES ('ACTIVE') TABLESPACE part_Data1,
    SUBPARTITION P3SUB2 VALUES ('INACTIVE') TABLESPACE part_Data2,
    SUBPARTITION P3SUB3 VALUES (DEFAULT) TABLESPACE part_Data3
  ) 
) 

例二、range+bash，这种分区是基于范围分区和散列分区，表首先按某列进行范围分区，然后再按某列进行散列分区。

partition by range(transaction_date)
subpartition by hash(transaction_id) subpartitions 3 store in (dinya_space01,dinya_space02,dinya_space03)  
(partition part_01 values less than(to_date(‘2006-01-01’,’yyyy-mm-dd’)),
partition part_02 values less than(to_date(‘2010-01-01’,’yyyy-mm-dd’)),
partition part_03 values less than(maxvalue)
);

三、分区表的操作

1.DML操作

说明：DML操作和平常的表一样，有异于的地方是增加了可以指定表的特定分区才执行DML操作。

例如，查询分区表

SELECT * FROM PART_TAB_SALE_RANGE_LIST;--不指定分区直接查询
SELECT * FROM PART_TAB_SALE_RANGE_LIST PARTITION(P2);--指定分区查询
SELECT * FROM PART_TAB_SALE_RANGE_LIST SUBPARTITION(P1SUB2);--指定小分区查询

对于分区表，指定分区执行DML效率更高，但，如果指定了分区，而条件中的数据又不在该分区中时，将不会产生任何DML操作。

2.DDL操作

1）添加分区

（1）对range分区表添加分区

ALTER TABLE PART_TAB_SALE_RANGE_LIST ADD PARTITION P3 VALUES LESS THAN(TO_DATE('2009-06-01','YYYY-MM-DD')); 

注意：增加一个分区的时候，增加的分区的条件必须大于现有分区的最大值，否则系统将提示ORA-14074 partition bound must collate higher than that of the last partition 错误。

（2）对range分区表list子分区添加分区

ALTER TABLE PART_TAB_SALE_RANGE_LIST MODIFY PARTITION P3 ADD SUBPARTITION P3SUB1 VALUES('COMPLETE'); 

2）删除分区

（1）对range分区表删除分区

ALTER TABLE PART_TAB_SALE_RANGE_LIST DROP PARTITION P3;

（2）对range分区表list子分区删除子分区

ALTER TABLE PART_TAB_SALE_RANGE_LIST DROP SUBPARTITION P4SUB1;

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

3）截断分区

说明：截断某个分区是指清空某个分区中的数据，并不会删除分区，也不会删除其它分区中的数据。当表中即使只有一个分区时，也可以截断该分区。
注意：如果截断的分区表有约束，需要先关闭约束。alter table sales disable/enable constraint restraint_name，截断分区会使全局索引无效，需要重建。

(1)清空分区：

   ALTER TABLE SALES TRUNCATE PARTITION P2;--这种方式使全局分区索引无效
   ALTER TABLE SALES TRUNCATE PARTITION P2 update indexes;--这种方式使全局分区索引有效UPDATE GLOBAL INDEXES

(2)清空子分区：

 ALTER TABLE PART_TAB_SALE_RANGE_LIST TRUNCATE SUBPARTITION P2SUB2; 
 ALTER TABLE PART_TAB_SALE_RANGE_LIST TRUNCATE SUBPARTITION P2SUB2 update indexes;

4）合并分区

说明：合并分区是将相邻的分区合并成一个分区，结果分区将采用较高分区的界限，值得注意的是，不能将分区合并到界限较低的分区。

ALTER TABLE PART_TAB_SALE_RANGE_LIST MERGE PARTITIONS P1,P2 INTO PARTITION P2; 

注意：在本例中将原有的表的part_01分区和part_02分区进行了合并，合并后的分区为part_02,
如果在合并的时候把合并后的分区定为part_01的时候，系统将提示ORA-14275 cannot reuse lower-bound partition as resulting partition 错误。

5）拆分分区

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

ALTER TABLE PART_TAB_SALE_RANGE_LIST SBLIT PARTITION P2 AT(TO_DATE('2003-03-01','YYYY-MM-DD')) INTO (PARTITION P21,PARTITION P22); 

6）接合分区

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

ALTER TABLE PART_TAB_SALE_RANGE_LIST COALESCA PARTITION;

7）重命名表分区

ALTER TABLE SALES RENAME PARTITION P21 TO P2;

8）移动分区

说明：把分区移动到令一个表空间，移动后要重建索引

alter table sales move partiton sp1 tablespace tablespace_name;
alter index index_name rebuild;

–查询是否移动成功 SELECT TABLE_OWNER,TABLE_NAME,PARTITION_NAME,TABLESPACE_NAME,SUBPARTITION_COUNT FROM DBA_TAB_PARTITIONS WHERE TABLE_OWNER='SCOTT';

四、表分区相关的数据字典表

–显示表分区信息 显示数据库所有分区表的详细分区信息： select * from DBA_TAB_PARTITIONS
–显示子分区信息 显示数据库所有组合分区表的子分区信息： select * from DBA_TAB_SUBPARTITIONS
–显示数据库所有分区表的信息： select * from DBA_PART_TABLES
–显示数据库可访问的所有分区表的分区列信息：select * from DBA_PART_KEY_COLUMNS

DBA_IND_PARTITIONS
DBA_IND_SUBPARTITIONS

–查询索引信息

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 

五、局部索引与全局索引

表可以按range，hash，list分区，表分区后，其上的索引和普通表上的索引有所不同，oracle对于分区表上的索引分为2类，即局部索引和全局索引。