一、分区技术
分区技术的优点:
(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;