Oracle怎么新增删除表分区

本文已参与「新人创作礼」活动，一起开启掘金创作之路。

一、Oracle分区简介

      Oracle在实际业务生产环境中，经常会遇到随着业务量的逐渐增加，表中的数据行数的增多，Oracle对表的管理和性能的影响也随之增大。
对表中数据的查询、表的备份的时间将大大提高，以及遇到特定情况下，要对表中数据进行恢复，也随之数据量的增大而花费更多的时间。
这个时候，Oracle数据库提供了分区这个机制，通过把一个表中的行进行划分，归为几部分。可以减少大数据量表的管理和性能问题。利用这种分区方式把表数据进行划分的机制称为表分区，各个分区称为分区表

二、Oracle分区优缺点

一、首先我们讲一下表分区的优缺点

表分区有以下优点：
1、改善查询性能：对分区对象的查询可以仅搜索自己关心的分区，提高检索速度。
2、增强可用性：如果表的某个分区出现故障，表在其他分区的数据仍然可用；
3、维护方便：如果表的某个分区出现故障，需要修复数据，只修复该分区即可；
4、均衡I/O：可以把不同的分区映射到磁盘以平衡I/O，改善整个系统性能。

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

三、oracle删除分区数据

方法1：通过删除分区的方式

删除分区，数据也自动删除。语法：

alter table 表名 DROP PARTITION 表中分区的名称;

删除表中分区和数据后，会造成索引失效，建议删除分区之后删除表中所有索引，在次重新建立索引。

方法2：清数据

语法：

alter table 表名 TRUNCATE PARTITION 表中分区的名称;

四、分区表的种类及创建分区

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

4、复合分区

说明：顾名思义，复合分区就由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操作。

例如，查询分区表。where条件应该放到分区后面

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);--指定小分区查询
SELECT * FROM PART_TAB_SALE_RANGE_LIST PARTITION(P2) where 1=1;--指定分区查询,增加where条件

对于分区表，指定分区执行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类，即局部索引和全局索引。

八、分区相关操作

1）添加分区

以下命令给SALES表添加了一个P3分区

ALTER TABLE SALES ADD PARTITION P3 VALUES LESS THAN(TO_DATE('2018-09-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;