Oracle11g 学习笔记-11（表分区）

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

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

SQL>  create table ware范围分区：_retail_part1--创建一个描述商品零售的数据表
  2   (
  3   id integer primary key,--销售编号
  4   retail_date date,--销售日期
  5   ware_name varchar2(50)--商品名称
  6   )
  7   partition by range(retail_date)
  8  (
  --2011年第一个季度位par_01分区
  9  partition par_01 values less than(to_date('2011-04-01','yyyy-mm-dd')) tablespace users,
  --2011年第二个季度位par_02分区
 10  partition par_02 values less than(to_date('2011-07-01','yyyy-mm-dd')) tablespace users,
 --2011年第三个季度位par_03分区
 11  partition par_03 values less than(to_date('2011-10-01','yyyy-mm-dd')) tablespace users,
 --2011年第四个季度位par_04分区
 12  partition par_04 values less than(to_date('2012-01-01','yyyy-mm-dd')) tablespace users
 13   );

表已创建。

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

SQL>  create table ware范围分区：_retail_part2--创建一个描述商品零售的数据表
  2   (
  3   id integer primary key,--销售编号
  4   retail_date date,--销售日期
  5   ware_name varchar2(50)--商品名称
  6   )
  7   partition by hash(id)
  8  (
  9  partition par_01 tablespace users,
 10  partition par_02 tablespace users
 11   );

表已创建。

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

SQL> create table clients--创建一个客户信息表
  2  (
  3    id integer primary key,--客户编号
  4    name varchar2(50),--客户名称
  5    province varchar2(20)--客户省份
  6  )
  7  partition by list(province)--列表分区
  8  (
  9    partition shandong values('山东省'),
 10    partition guandong values('广东省')，
 11    partition yunnan values('云南省')
 12  );

表已创建。

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

SQL> create table person2--创建一个个人信息表
  2  (
  3  id number primary key,--个人编号
  4  name varchar2(20),--姓名
  5  sex varchar2(2)--性别
  6  )partition by range(id)--以id作为分区创建范围分区
  7  subpartition by hash(name)--以name列作为分区键创建hash子分区
  8  subpartitions 2 store in(tbs_test_1,users)--hsah子分区共有两个，分别储存在两个不同命名的空间中
  9  (
 10  partition par1 values less than(5000),--范围分区，id小于5000
 11  partition par2 values less than(10000),--范围分区，id小于10000
 12  partition par3 values less than(maxvalue)--范围分区，id不小于10000
 13  );

表已创建。

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

SQL> create table saleRecord
  2  (
  3  id number primary key,--编号
  4  goodsname varchar2(50),--商品名称
  5  saledate date,--销售日期
  6  quantity number--销售量
  7  )
  8  partition by range(saledate)
  9  interval (numtoyminterval(1,'year'))
 10  (
 --设置分区兼职日期小于2012-01-01
 11  partition par_list values less than (to_date('2012-01-01','yyyy-mm-dd'))
 12  );

表已创建。

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

SQL> alter table clients 
  2  add partition hebei values('河北省')--clients表里添加列表表分区
  3  storage(initial 10k next 20k)  tablespace users
  4  nologging;

表已更改。

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

SQL> alter table ware_retail_part drop partition par_01;

表已更改。

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

alter table table_name drop partition par_name;

使用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语句删除分区。
10-【管理表分区】 merge 写合并分区SQL…加注释

SQL> create table sales
  2  (
  3  id number primary key,--编码
  4  goodsname varchar2(10),--商品名
  5  saledate date--销售日期
  6  )
  7  partition by range(saledate)--按照日期分区
  8  (
  9  --第一季度
 10  partition part_sea1 values less than(to_date('2011-04-01','yyyy-mm-dd')) tablespace tbs_test_1,
 11  --第二季度
 12  partition part_sea2 values less than(to_date('2011-07-01','yyyy-mm-dd')) tablespace tbs_test_2,
 13  --第三季度
 14  partition part_sea3 values less than(to_date('2011-10-01','yyyy-mm-dd')) tablespace tbs_test_1,
 15  --第四季度
 16  partition part_sea4 values less than(to_date('2012-01-01','yyyy-mm-dd')) tablespace tbs_test_2
 17  );

表已创建。

SQL> create index index_3_4 on sales(saledate)
  2  local(
  3  partition par_sea1 tablespace tbs_test_1,
  4  partition par_sea2 tablespace  tbs_test_2,
  5  partition par_sea3 tablespace tbs_test_1,
  6  partition par_sea4 tablespace  tbs_test_2
  7  );

索引已创建。
--并入分区
alter table sales merge partitions par_sea3,par_sea4 into partition part_sea4;
--重建局部索引
alter table sales modify partition par_sea4 rebulid unusable local indexes;