分区：分而治之

Oracle提供了两种关键的可伸缩性功能，通过他们即使处理非常大的数据库也能获得良好的性能，他们是并行机制和分区。

• 并行机制：Oracle可以启动多个线程执行操作，从而能够充分利用硬件资源。
• 分区：使用分区可以单独管理表和索引的子集。

一、什么是分区

分区是以透明方式将逻辑表和索引划分为多个独立的、较小的部分。

分区功能的优点：

提高性能：在某些环境中，可以对单个分区或分区子集执行SQL查询命令，从而加快执行命令的速度。
提高可用性：一个分区的可用性不会影响另一个分区的可用性。
提高可维护性：按分区插入、更新、删除、重建和重组数据，可以提高加载数据和归档日志操作的效率。

二、什么情况下使用分区功能

1. 大于2GB的表
2. 含有1000万条记录以上的表
3. 将会含有大量数据的表（在初始时就对表应用分区功能，比出现了问题后再应用分区功能简单得多）
4. 将行拆分后可利于并行操作（如插入、检索、删除、备份与恢复）的表
5. 含有需要定期归档日志或删除部分的表

三、创建分区表

分区策略

分区类型	描述
范围	根据日期、数值或字符创建分区
列表	根据列表值（如省份和地区代码）创建分区
散列	在没有明显分区键的情况下，以均分方式创建分区
组合	组合使用多种分区方式
间隔	当新分区键值超出现存最大范围时，通过自动分配新分区扩展范围分区
引用	根据父表列为子表创建分区
虚拟列分区	在虚拟列上创建分区
系统	根据插入列的应用程序创建分区

1. 按范围分区：

--将分区键设置为number类型
create table s_sales
(
sales_amt number,
d_date_id number	--日期用数值型
)
partition by range (d_date_id)	--定义基于范围的分区键
(
partition p_2012 values less than (20130101),	--values less than 定义范围分区的上边界，范围分区的第一个分区没有下边界
partition p_2013 values less than (20140101),
partition p_max values less than (maxvalue)		--maxvalue 子句创建分区表的最高分区
);

如果不设置maxvalue分区，当插入数据不再在任何范围内时，系统就会显示下列错误提示：ORA-14400: inserted partition key does not map to any partition
在Oracle 11g 或更高版本，可使用间隔分区策略，当插入数据超出范围时Oracle会自动添加分区。
查看分区表：user_part_tables
查看分区表信息：user_tab_partitions

--将分区键设置为timestamp类型
create table s_sales
(
sales_amt number,
d_date_dtt date	--时间用日期型
)
partition by range (d_date_dtt)	--定义基于范围的分区键
(
partition p_2012 values less than (to_date('20130101','dd-mm-yyyy')),	--values less than 定义范围分区的上边界，范围分区的第一个分区没有下边界
partition p_2013 values less than (to_date('20140101','dd-mm-yyyy')),
partition p_max values less than (maxvalue)		--maxvalue 子句创建分区表的最高分区
);

2. 使用表空间存储分区：

--创建表
create table f_sales
(
sales_amt number,
d_date_id number
)
tablespace p1_tbsp	--表f_sales的默认永久表空间
--设置分区
partition by range (d_date_id)
(
partition p_2012 values less than (20130101) tablespace p1_tbsp,   --values less than 定义范围分区的上边界，范围分区的第一个分区没有下边界
partition p_2013 values less than (20140101) tablespace p2_tbsp,
partition p_max values less than (maxvalue) tablespace p3_tbsp     --maxvalue 子句创建分区表的最高分区
);

将分区存储到独立表空间的优点是可以单独备份和恢复分区（备份表空间）。
**注：**当为分区设置表空间时，也可以设置其他存储功能。
下面的例子对表空间使用了PCTFREE,PCTUSED,NOLOGGING子句

--创建表
create table f_sales
(
sales_amt number,
d_date_id number
)
tablespace p1_tbsp	--表f_sales的默认永久表空间
--设置分区
partition by range (d_date_id)
(
partition p_2012 values less than (20130101) tablespace p1_tbsp pctfree 5 pctused 90 nologging,   --values less than 定义范围分区的上边界，范围分区的第一个分区没有下边界
partition p_2013 values less than (20140101) tablespace p2_tbsp pctfree 5 pctused 90 nologging,
partition p_max values less than (maxvalue) tablespace p3_tbsp pctfree 5 pctused 90 nologging    --maxvalue 子句创建分区表的最高分区
);

pctfree 这个参数定义了一个块保留空间的百分比，保留空间是为了将来可能发生的更新操作，因为更新可能增大被更新行占用的空间，如果此时该块没有可利用空间，那么只有发生row migrate了，从而会降低I/O性能。换句话说，就是当一个块的利用率达到(1-pctfree)的时候，oracle就将该块从freelist中移除，不再向该块插入数据。所以说pctfree是控制什么时候将块从freelist中移除的。

pctused 这个参数控制一个块什么时候被重新启用来插入数据，例如当一个块达到(1-pctfree)利用率的时候，oracle停止向该块插入数据，同时从freelist移除该块，但是后来发生一些删除操作，使得该块的利用率下降，当该块的利用率降到pctused以下的时候该块就被重新启用来插入数据了，也即是将该块重新加入到freelist列表中，所以说pctused是控制什么时候将一个块重新加入freelist的。

举个例子：比如一个块的pctfree定为10%，pctused定为40%（oracle默认设置）。那么一个块的使用率达到90%（即1-10%）的时候，oracle将该块从freelist中移除，停止使用该块来插入数据（但可更新）。后来该块上发生了一些删除操作，使得该块的利用率下降，当使用率下降到40%以下的时候，oracle重新将该块加入freelist，可用于新的插入。

3. 根据列表值分区：

对无序和非关系数据使用列表分区策略的效果更好。列如，有一个大型表并想要根据州编码对其进行分区，可以使用CREATE TABLE 的PARTITION BY LIST子句。

create table f_sales
(
sales_amt number,
d_date_id number,
state_code varchar2(3)
)
partition by list (state_code)
(
partition reg_west values ('AZ','CA','CO','MT','OR','ID','UI','NV'),
partition reg_mid values ('IA','KS','MI','MN','MO','NE','OH','ND'),
partition reg_def values (default)
);

4. 散列分区（平均划分分区）：

用序列为表添加代理主键，并根据唯一主键以平均方式划分分区。
使用散列分区可以根据内部算法，以平均方式划分分区。你无法控制散列算法和划分分区的方式，只能设置分区的数量，而Oracle会根据散列键列均分数据。

--创建含有两个分区的表，每个分区都存储在它本身的表空间中
create table f_sales
(
sales_id number primary key,
sales_amt number
)
partition by hash (sales_id)
partition 2 store in(pi_tbsp,p2_tbsp);

create table f_sales
(
sales_id number primary key,
sales_amt number
)
partition by hash (sales_id)
(
partition p1 tablespace p1_tbsp,
partition p2 tablespace p2_tbsp
);

5. 组合使用多种分区方法：

oracle允许使用多种策略划分分区。例如，有一个表像根据范围为其划分分区，还想根据地区列表为每个分区划分子分区，即范围列表分区策略。

create table f_sales
(
sales_amt number,
state_code varchar2(3),
d_date_id number
)
partition by range (d_date_id)
subpartition by list (state_code)
(
partition p2011 values less than (20120101)
	(
	subpartition p1_north values ('ID','OR'),
	subpartition p1_south values ('AZ','MM')
	),
partition p2012 values less than (20130101)
	(
	subpartition p1_north values ('ID','OR'),
	subpartition p1_south values ('AZ','MM')
	)
);

oracle 11g之后，可以使用下列分区策略：

• 范围散列：
• 范围列表：
• 范围范围：
• 列表范围：
• 列表散列：
• 列表列表：
• 散列散列：

6. 根据需要创建分区（间隔分区）：

Oracle11g以上版本可以设置Oracle为范围分区表自动增加分区，该功能称为间隔分区。当插入数据超过范围分区表的最大边际值时，Oracle会自动创建新分区，新分区是根据设置的间隔创建的。
（1）根据日期添加年间隔分区

create table f_sales
(
sales_amt number,
d_date_ddt date
)
--根据年在Oracle范围顶部自动添加分区
partition by range(d_date_ddt) interval(numtoyminterval(1,'YEAR')) store in (p1_tbsp,p2_tbsp,p3_tbsp)
(
partition p1 values less than (to_date('01-01-2013','dd-mm-yyyy')) tablespace p1_tbsp
);

使用间隔分区，只能在表中设置一个键列，而且必须是date或number类型，因为间隔分区策略是对键列值应用算术运算划分分区的。键列不能为NULL。
可以重命名生成的分区名称
SQL> alter table f_sales rename partition sys_p3334 to p2;
（2）根据日期增加星期间隔分区

create table f_sales
(
sales_amt number,
d_date_ddt date
)
--根据星期在Oracle范围顶部自动添加分区
partition by range(d_date_ddt) interval(numtodsinterval(7,'day')) store in (p1_tbsp,p2_tbsp,p3_tbsp)
(
partition p1 values less than (to_date('01-01-2013','dd-mm-yyyy')) tablespace p1_tbsp
);

（3）根据数值增加日间隔分区

create table f_sales
(
sales_amt number,
d_date_id number
)
--根据数值日期在Oracle范围顶部自动添加分区
partition by range (d_date_id) interval(1)
(
partition p1 values less than (20120101)
);

7. 根据父表分区：

直接看例子

--创建表orders
create table orders
(
order_id number,
order_date date,
constraint order_pk primary key(order_id)
)
partition by range(order_date)
(
partition p11 values less than (to_date('20130101','dd-mm-yyyy')),   --values less than 定义范围分区的上边界，范围分区的第一个分区没有下边界
partition p12 values less than (to_date('20140101','dd-mm-yyyy')),
partition p_max values less than (maxvalue)     --maxvalue 子句创建分区表的最高分区
);

创建子表order_items，通过外键约束为它创建分区

create table order_items
(
line_id number,
order_id number not null,
sku number,
quantity number,
constraint order_items_pk primary key(line_id,order_id),
constraint order_items_fk1 foregin key(order_id) references orders
)
partition by reference (order_items_fk1)
(
partition c11,
partition c12,
partition cmax
);

8. 虚拟列分区：

例子：创建表emp，其中含有虚拟列commission，根据该虚拟列创建了范围分区

create table emp
(
emp_id number,
salary number,
comm_pct number,
commission generated always as (salary*comm_pct)
)
partition by range(commission)
(
partition p1 values less than (1000),
partition p2 values less than (2000),
partition p3 values less than (maxvalue)
);

9. 赋予应用控制分区的能力(系统分区)：

create table apps
(
id number,
app_amt number
)
partition by system
(
partition p1,
partition p2,
partition p3
);

当向这个表中插入数据时，必须制定分区。