一、表分区
- 允许用户将一个表分成多个分区
- 用户可以执行查询,只访问表中的特定分区
- 将不同的分区存储在不同的磁盘,提高访问性能和安全性
- 可以独立地备份和恢复每个分区
二、传统的表分区的类型 (10g及以前,11g也可使用)
(1)范围分区 :
以表中的一个列或一组列的值的范围分区.
create table sales(product_id varchar2(5),
sales_count number(10,2))
partition by range ( 分区列名)
( partition 分区名称1 values less than (范围1),
partition 分区名称2 values less than (范围2),
……
partition 分区名称n values less than (范围n)
) ;
①建立分区以后,我们可以对分区进行修改,如增加一个分区,
alter table 表名 add partition 分区名 values less than (maxvalues);
②查询分区内的信息:
select * from sales partition(分区名);
select * from user_tab_partitions v where v.table_name=’SALES’;
③插入数据 : 只允许插入在分区范围内的数据。
(2)散列分区 Hash分区
- 允许用户对不具有逻辑范围的数据进行分区
- 通过在分区键上执行HASH函数决定存储的分区
- 将数据平均地分布到不同的分区 , 避免某一分区数据量大,某一分区数据量小。
create table sales(product_id varchar2(5),
sales_count number(10,2))
partition by hash ( 分区列名)
( partition 分区名称1 [tablespace tname ],
partition 分区名称2 [tablespace tname ],
……
partition 分区名称n
) ;
(3)列表分区 list :
分区列的值为有限个,能够列举出来;
限定了插入值的去向;
允许用户将不相关的数据组织在一起
create table sales(product_id varchar2(5),
sales_count number(10,2) ,
city vaechar2(10 ))
partition by list ( city)
( partition 西边 values less than ('开封','商丘'),
partition 东边 values less than ('洛阳'),
partition 南边 values less than ('许昌')
partition 北边 values less than ('新乡')
) ;
(4)复合分区 :
①范围分区range+散列分区hash
-- 三个range分区中每一个都拥有四个子分区,子分区按hash进行分区。
create table sales(product_id varchar2(5),
sales_count number(10,2))
partition by range ( 分区列名1)
subpartition by hash (分区列名2) --外层
subpartition 4 --说明hash分区个数(内层)
( partition 分区名称1 values less than (范围1),
partition 分区名称2 values less than (范围2),
……
partition 分区名称n values less than (范围n)
) ;
②列表分区list+散列分区hash
create table sales(product_id varchar2(5),
sales_count number(10,2) ,
city vaechar2(10 ))
partition by list ( city)
subpartition by hash (sales_count)
subpartition 4
( partition 西边 values less than ('开封','商丘'),
partition 东边 values less than ('洛阳'),
partition 南边 values less than ('许昌')
partition 北边 values less than ('新乡')
) ;
三、11g新增的分区
(1)引用分区 reference
引用分区:基于由外键引用的父表的分区的方法,它依赖已有的父表子表的关系,子表通过外键关联到父表,进而继承了父表的分区方式而不需自己创建,子表还继承了父表的维护操作。 (两张表分主副,主键外键的引用关系 ,子继承父)
1,主表是范围分区,子表是引用分区
2,主表是列表分区,子表是引用分区
3,主表是散列分区,子表是引用分区
父:
子:
(2)间隔分区 interval
间隔分区:可以完全自动地根据间隔阈值创建范围分区,它是范围分区的扩展 。 在数据仓库中有广泛的应用。
这个分区是由oracle自动增加的。
create table sale_detail(id number ,
product_id number,
quantity number ,
sales_date date)
partition by range (sales_date)
interval (numtoyminterval(1,'MONTH'))
(
partition p_201803 values less than (to_date('20180306','yyyymmdd'))
);
-- partition 指定了一个初始分区
-- numtoyminterval 关键字 表明每隔一个月增长一个分区 名字由oracle给定
-- 随着用户插入的数据来进行分区,未插入的月份不会分区。
(3)基于虚拟列的分区:
基于虚拟列的分区:把分区建立在某个虚拟列上,即建立在函数或表达式的计算结果上,来完成某种任务。
在建立表的属性中指明虚拟列。
total_price as (price*quantity) virtual
在上图中,oracle根据列 price与列quantity的乘积来计算 该行数据应该插入在哪个表中。
(4)系统分区 system
系统分区:不指定分区列,由ORACLE来完成分区的控制和管理,它没有了范围分区或列表分区的
界限。
create table sales(product_id varchar2(5),
sales_count number(10,2))
partition by system
( partition 分区名称1 ,
partition 分区名称2 ,
……
partition 分区名称n
) ;
四、关于分区的一些操作;
- 删除分区
alter table 表名 drop partition 分区名;
- 截断分区 删除分区中所有记录保留分区结构
alter table 表名 truncate partition 分区名;
- 添加分区 只能在表的最后添加分区
alter table 表名 add partition 分区名 values less than(条件);
- 合并分区 (把两个相邻的分区的数据和范围都进行了合并)
alter table 表名 merge partitions 分区1,分区2 into partition 分区2;
- 拆分分区 (将一个大的分区拆分成两个)
alter table 表名 split partition 分区 2 at (拆分点)into (partition 分区21,partition 分区22);