Oracle学习总结11——表分区

1-什么是分区？优势在哪？
当表中的数据量不断增大，查询数据的速度就会变慢，应用程序的性能就会下降，这时就应该考虑对表进行分区。
表进行分区后，逻辑上表仍然是一张完整的表，只是将表中的数据在物理上存放到多个“表空间”(物理文件上)，这样查询数据时，不至于每次都扫描整张表而只是从当前的分区查到所要的数据大大提高了数据查询的速度。
那么使用分区技术有哪些优点呢?具体如下:
(1)减少维护工作量，独立管理每个分区比管理单个大表要轻松得多。
(2) 增强数据库的可用性，如果表的一个或几个分区由于系统故障而不能使用，而表其余分区仍然可以使用;如果系统故障只影响表的一部分分区，那么，只有这部分分区需要修复这就比修复整个大表耗费的时间少许多。
(3)均衡I/O，减少竞争，通过把表的不同分区分配到不同的磁盘来平衡I/O改善性能。
(4)分区对用户保持透明，最终用户感觉不到分区的存在。
(5)提高查询速度:对大表的查询、增加、修改等操作可以分解到表的不同分区中来并行执行，这样就可以加快运行速度，在数据仓库的TP查询特别有用。

2-【创建表分区】 create rangel敲创建范围分区的代码SQL…加注释

create table ware_retail_part--描述商品零售数据表
(
id integer primary key,--商品编号，主键
retil_date,--销售日期
ware_name varchar2(50)--商品名称
)
--建立分区
partition by range(retail_date)
(
--第一季度为par_01分区
partition par_01 values less than(to_date('2011-04-01','yyyy-mm-dd'))tablespace users,
--第二季度为par_02分区
partition par_02 values less than(to_date('2011-07-01','yyyy-mm-dd'))tablespace users,
--第三季度为par_03分区
partition par_03 values less than(to_date('2011-08-01','yyyy-mm-dd'))tablespace users,
--第四季度为par_04分区
partition par_04 values less than(to_date('2011-09-01','yyyy-mm-dd'))tablespace users
);

3-【创建表分区】 createhash1 敲创建HASH分区的代码SQL…加注释

--散列分区，列的取值难以确定的情况下，按照hash算法将数据相对均匀的分布在磁盘上
--创建描述商品零售的数据表
create table ware_retail_part3
(
id integer primary key,
retail_date,
ware_name varchar2(50)
)
--分区键为ID，创建两个散列分区
partition by hash(id)
(
partition par_01 tablespace users,
partition par_02 tablespace users
);

4-【创建表分区】 List 敲创建列表分区的代码SQL…加注释

--如果表的某一列的值可以枚举，则可以对表进行列表分区
--创建表客户信息
create table clients
(
id integer primary key,
name varchar2(50),
province varchar2(20)
)
--按照省份进行分区
partition by list(province)
(
partition shandong values('山东省'),
partition guangdong values('广东省'),
partition yunnan values('云南省')
);

5-【创建表分区】 group 敲创建组合分区的代码SQL…加注释

--结合两个数据分区的方法可以成为一个组合分区方法。首先用第一个数据分布方法对表格进行分区，然后再用第二个数据分区方法对每个分区进行二次分区。
--创建表保存人员信息
 create table person2
 (
 id integer primary key,
 name varchar2(50),
 sex varchar2(2)
 )
 --按照id作为分区键分区
 partition by range(id)
 --在上一个分区的情况下，建立hash分区以人的名称进行分区
 subpartition by hash(name)
  --按照name创建两个hash分区
 subpartitions 2 store in(tbsp_1,tbsp_2)
 (
 --三个按照id范围的分区
 partition par1 values less than(5000),
partition par2 values less than(10000),
partition par3 values less than(maxvalue)
);

6-【创建表分区】 inteval 敲创建Interval分区的代码SQL…加注释

--创建销售记录表
create table saleRecord
(
id number primary key,
goodsname varchar2(50),
saledate date,
quantity number
)
--按照销售时间范围分区
partition by range(saledate)
--在上一个基础上，使用interval按年份自动分区
--numtoyminterval函数的功能：将数字转换成interval year
interval(numtoyminterval(1,'year'))-- --根据n的数值来分区，两年分一次，自动分区
(
--键值小于2012-01-01的
partition par_fist values less than (to_date('2012-01-01','yyyy-mm-dd'))
);
--传统分区的表可以通过alter table中的set interval选项拓展成自动分区

7-【管理表分区】 addpartition 敲添加表分区的代码SQL…加注释

--为clients表增加分区（列表表分区）
alter table clients add partition hebei values('河北省')
--初始10k，下一个是20K，指定表空间users，以不增加日志的方式增加
storage(initial 10k next 20k)tablespace users
nologging;

8-写出 范围分区和复合分区的删除SQL…

alter table ware_retail_part drop partition par_01;

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

alter table table_name drop partition par_name;

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

10-【管理表分区】 merge 写合并分区SQL…加注释

合并分区后，该分区继承原来两个分区的边界，原来的两个分区和对应的索引都被删掉，如果被合并的分区非空，则该分区被标记为unsable，不能对hash分区表进行合并操作，并入范围分区是将两个以上的分区合并到一个已经存在的分区里，合并后一般索引要重建。

--创建表和分区
create table sales
(
id number primary key,--编码
goodsname varchar2(10),--商品名
saldate date--销售日期
)
partition by range(saledate)--按照日期分区
(
--第一季度
partition part_sea1 values less than(to_date('2011-04-01','yyyy-mm-dd')) tablespace tbsp_1,
--第二季度
partition part_sea2 values less than(to_date('2011-07-01','yyyy-mm-dd')) tablespace tbsp_2,
--第三季度
partition part_sea3 values less than(to_date('2011-07-01','yyyy-mm-dd')) tablespace tbsp_1,
--第四季度
partition part_sea4 values less than(to_date('2012-01-01','yyyy-mm-dd')) tablespace tbsp_2
);
--创建局部索引
create index index_3_4 on sales(saledate)
local(
partition par_sea1 tablespace tbsp_1,
partition par_sea2 tablespace tbsp_2,
partition par_sea3 tablespace tbsp_1,
partition par_sea4 tablespace tbsp_2,
);
--并入分区
alter table sales merge part_sea3,part_sea4 into partition part_sea4;
--重建局部索引
alter table sales modify partition part_sea4 rebulid unusable local indexes;

11.重命名索引分区

--重命名索引分区
alter index index_saleprice
rename partition p3 to p_new;

分区很重要，可能没接触过，但是对于大数据表的存储和效率都至关重要。学完本节课程，尝试连接业务库，找找哪些表是大数据表，本地创建一个，试着分区后看看查询和归档效果…