ORACLE 分区

一、分区技术
分区技术的优点：      
（1）减少维护工作量，独立管理每个分区比管理单个大表要轻松得多。
（2）增强数据库的可用性，如果表的一个或几个分区由于系统故障而不能使用，而表其余的分区仍然可以使用；如果系统故障只影响表的一部分分区，那么，只有这部分分区需要修复，这就比修复整个大表耗费的时间少许多。
（3）均衡I/O，减少竞争，通过把表的不同分区分配到不同的磁盘来平衡I/O改善性能
（4）分区对用户保持透明，最终用户感觉不到分区的存在。
（5）提高查询速度：对大表的查询、增加、修改等操作可以分解到表的不同分区中来并行执行，这样就可以加快运行速度，在数据仓库的TP查询特别有用。

二、创建表分区

1、范围分区
创建该分区后，其中的数据可以根据分区键值指定的范围进行分布，当数据在范围内均匀分布时，性能最好。
当表结构采用范围分区时，首先要考虑分区的列应该符合范围分区的方法；其次要考虑列的数据值的取值范围；最后考虑列的边界问题。

create table ware_retail_part2 --创建一个描述商品零售的数据表
(
   ID integer primary key,--销售编号
   retail_date date,--销售日期
   ware_name varchar2(50)--商品名称
)
partition by hash(id)--对id进行HASH分区
(
   partition par1 tablespace tbs_gwn,--分区par1，表空间tbs_gwn
   partition par2 tablespace USERS --分区par2，表空间users
);

create table ware_retail_part3 --创建一个描述商品零售的数据表
(
   ID integer primary key,--销售编号
   retail_date date,--销售日期
   ware_name varchar2(50)--商品名称
)
partition by hash(id) --对id进行HASH分区
partitions 2 --创建两个分区
store in(tbs_gwn,users);--指定表空间

2、HASH分区（散列分区）
 HASH分区，也叫做散列分区，是在列的取值难以确定的情况下采用的分区方法。
HASH实际上是一种函数算法，当向表中插入数据时，系统会自动根据当前分区列的值计算出HASH值，然后确定应该将该行存放于哪个表空间中。HASH分区通过指定分区编号将数据均匀分布在磁盘设备上，使得这些分区大小一致，这充分降低了I/O磁盘争用的情况，但是，对于范围查询或不等式查询起不到优化的作用。

create table ware_retail_part2 --创建一个描述商品零售的数据表
(
  id integer primary key,--销售编号
  retail_date date,--销售日期
  ware_name varchar2(50)--商品名称
)
partition by hash(id)--对id进行HASH分区
(
  partition par1 tablespace tbs_gwn;--分区par1，表空间tbs_gwn
  partition par2 tablespace users --分区par2，表空间users
);

create table ware_retail_part3 --创建一个描述商品零售的数据表
(
  id integer primary key,--销售编号
  retail_date date,--销售日期
  ware_name varchar2(50)--商品名称
)
partition by hash(id) --对id进行HASH分区
partitions 2 --创建两个分区
store in(tbs_gwn,users);--指定表空间

3、列表分区
关键字是LIST，如果表的某个列的值可以枚举，则可以考虑对表进行列表分区。

create table emp1 --创建一个员工表
(
   ID integer primary key, --编号
   name varchar2(50), --姓名
   province varchar2(20) --省份
)
partition by list(province) --根据province进行列表分区
(
   partition par1 values('山东'),--分区1
   partition par2 values('广东'),--分区2
   partition par3 values('云南') --分区3
);

4、组合分区
结合两个数据分区的方法可以成为一个组合分区方法。首先用第一个数据分布方法对表格进行分区，然后再用第二个数据分区方法对每个分区进行二次分区。
 

create table emp2   --创建一个员工表
(
  ID integer primary key, --编号
  name varchar2(50), --姓名
  province varchar2(20) --省份
)
partition by range(id)--以id作为分区键创建范围分区
subpartition by hash(name)--以name列作为分区键创建hash子分区
subpartitions 2 store in(tbs_gwn,users)--hash子分区公有两个，分别存储在两个不同的命名空间中
(
   partition par1 values less than(5000),--范围分区，id小于5000
   partition par2 values less than(10000),--范围分区，id小于10000
   partition par3 values less than(maxvalue)--范围分区，id不小于10000
);

5、interval分区
关键字是Interval，Interval分区是Oracle 11g版本新引入的分区方法，是范围分区的一种增强功能，可以实现equi_sized范围分区的自动化。创建的分区作为元数据，只有最开始的分区是永久分区。随着数据的增加会分配更多的部分，并自动创建新的分区和本地索引。 

create table saleRecord
(
  ID number primary key, --编号
  goodsname varchar2(50),--商品名称
  saledate date,--销售日期
  quantity number--销售量
)
partition by range(saledate) --根据saledate进行范围分区
interval (numtoyminterval(1,'year')) --按照每年自动创建一个分区
(
  --2020年之前的数据放入par1分区
  partition par1 values less than (to_date('2020-01-01','yyyy-mm-dd'))
);

 

三、管理表分区

1、添加表分区

alter table emp1
add partition par4 values('安徽') --增加一个分区
storage(initial 10K next 20K) --初始分配区空间10K，第二个区空间20K
tablespace tbs_gwn --指定表空间
nologging; --不产生重做日志

2、删除表分区

1．删除一个表分区
可以使用ALTER TABLE…DROP PARTITION语句删除范围分区和复合分区。删除分区时，该分区的数据也被删除。 

2．删除有数据和全局索引的表分区
如果分区表中包含了数据，并且在表中定义了一个或者多个全局索引，可以使用ALTER TABLE…DROP PARTITION语句删除表分区，这样可以保留全局索引，但是索引会被标识为不可用（UNUSABLE），因而需要重建索引。

--创建表分区
CREATE TABLE emp3 
(
EMPNO    NUMBER(4),                              
ENAME    VARCHAR2(10),                        
JOB      VARCHAR2(9),                       
MGR      NUMBER(4),                           
HIREDATE DATE,                                 
SAL      NUMBER(7,2),                          
COMM     NUMBER(7,2),                         
DEPTNO   NUMBER(2)
)
PARTITION BY range(sal)
(
  PARTITION p1 values less than (1000),
  PARTITION p2 values less than (2000),
  PARTITION p3 values less than (maxvalue)
);

--创建全局索引
create index index_sal on emp3(sal)
global partition by range(sal)
(
  PARTITION p1 values less than (1000),
  PARTITION p2 values less than (2000),
  PARTITION p3 values less than (maxvalue)
);

--插入数据
INSERT INTO emp3 SELECT * FROM emp;
COMMIT;

--删除分区
ALTER TABLE emp3 DROP PARTITION p1;

3．使用DELETE和ALTER TABLE…DROP PARTITION语句
在执行ALTER TABLE…DROP PARTITION语句前首先执行DELETE语句来删除分区的所有数据行，
然后执行ALTER TABLE…DROP PARTITION语句，但是执行DELETE语句时需要更新全局索引。 
4．删除具有完整性约束的分区  
如果分区的表具有完整性约束，则可以采用以下两种办法。
（1）首先禁止完整性约束，然后执行ALTER TABLE…DROP PARTITION，最后激活约束。
（2）首先执行DELETE语句删除分区中的行，然后用ALTER TABLE…DROP PARTITION语句删除分区。

3、合并分区
  Oracle可以对表和索引进行分区，也可以对分区进行合并，从而减少散列分区或者复合分区的个数。在合并表分区之后，Oracle系统将做以下处理：
（1）在合并分区时，HASH列函数将分区内容分布到一个或多个保留分区中；
（2）原来内容所在的分区完全被清除；
（3）与分区对应的索引也被清除；
（4）将一个或多个索引的本地索引分区标识为不可用（UNSABLE）；
（5）需要对不可用的索引进行重建。

create table ware_retail_part --创建一个描述商品零售的数据表
(
  id integer primary key,--销售编号
  retail_date date,--销售日期
  ware_name varchar2(50)--商品名称
)
partition by range(retail_date)--对retail_date根据范围进行分区
(
  --2020年第一个季度为par1分区
  partition par1 values less than(to_date('2020-04-01','yyyy-mm-dd')) tablespace TBS_GWN,
  --2020年第二个季度为par2分区
  partition par2 values less than(to_date('2020-07-01','yyyy-mm-dd')) tablespace TBS_GWN,
  --2020年第三个季度为par3分区
  partition par3 values less than(to_date('2020-10-01','yyyy-mm-dd')) tablespace USERS,
  --2020年第四个季度为par4分区
  partition par4 values less than(to_date('2020-01-01','yyyy-mm-dd')) tablespace USERS
);

--将par1和par2分区合并到par2分区中
alter table ware_retail_part1 merge partitions par1,par2 into partition par2;