Oracle提供了两种关键的可伸缩性功能,通过他们即使处理非常大的数据库也能获得良好的性能,他们是并行机制
和分区
。
- 并行机制:Oracle可以启动多个线程执行操作,从而能够充分利用硬件资源。
- 分区:使用分区可以单独管理表和索引的子集。
一、什么是分区
分区是以透明方式将逻辑表和索引划分为多个独立的、较小的部分。
分区功能的优点:
提高性能
:在某些环境中,可以对单个分区或分区子集执行SQL查询命令,从而加快执行命令的速度。
提高可用性
:一个分区的可用性不会影响另一个分区的可用性。
提高可维护性
:按分区插入、更新、删除、重建和重组数据,可以提高加载数据和归档日志操作的效率。
二、什么情况下使用分区功能
- 大于2GB的表
- 含有1000万条记录以上的表
- 将会含有大量数据的表(在初始时就对表应用分区功能,比出现了问题后再应用分区功能简单得多)
- 将行拆分后可利于并行操作(如插入、检索、删除、备份与恢复)的表
- 含有需要定期归档日志或删除部分的表
三、创建分区表
分区策略
分区类型 | 描述 |
---|---|
范围 | 根据日期、数值或字符创建分区 |
列表 | 根据列表值(如省份和地区代码)创建分区 |
散列 | 在没有明显分区键的情况下,以均分方式创建分区 |
组合 | 组合使用多种分区方式 |
间隔 | 当新分区键值超出现存最大范围时,通过自动分配新分区扩展范围分区 |
引用 | 根据父表列为子表创建分区 |
虚拟列分区 | 在虚拟列上创建分区 |
系统 | 根据插入列的应用程序创建分区 |
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
);
当向这个表中插入数据时,必须制定分区。