oracle中的表分区

一、表分区

• 允许用户将一个表分成多个分区
• 用户可以执行查询，只访问表中的特定分区
• 将不同的分区存储在不同的磁盘，提高访问性能和安全性
• 可以独立地备份和恢复每个分区

二、传统的表分区的类型 （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）；