第13章 分区

一、目标

　　了解分区的作用

　　了解表分区、索引分区的种类

　　知道要在哪些场景下使用哪些分区

二、带着问题阅读

　　1、分区有什么作用

　　2、有哪些表分区，简述这些分区的作用，并了解什么时候用此分区以及创建分区的语句

　　3、表分区总结（统筹2点中的各个分区使用的场景）

　　4、了解索引分区：本地索引和全局索引原理，以及哪些场景使用，以及创建这些索引分区的语句

　　5、在OLTP场景下的全局索引

　　6、分区和性能

　　7、扩展：分区的增删改查SQL语句

三、根据以上，整理笔记

　　1、分区的作用：

　　　　（1）提高可用性；将数据分区之后，各个部分的数据在物理上就隔离了，一部分的数据损坏不会影响其他分区的数据，你依旧可以查询其他分区的数据（SQL语句要带上未损坏的分区信息），分区机制从两个方面提高了可用性：

　　　　　　·优化器可以跳过对某些分区的访问

　　　　　　·数据库出现错误时的停机时间会减少，因为只需要对坏的分区恢复就可以了

　　　　（2）减少管理负担：体现了一种分而治之的思想，将一个大对象分成一个小对象，再对一个小对象进行维护，更容易，速度更快，占用的资源也更少。

　　　　（3）增强语句性能：当涉及到并发处理的时候，并发度根据分区来确定。

　　　　　　当对表进行查询时：

　　　　　　　　·可略过不必要的分区

　　　　　　　　·并行查询

　　2、有哪些表分区，简述这些分区的作用，并了解什么时候用此分区以及创建分区的语句

　　　　注：（a）表分区要在表创建的时候就应该要创建，如果没有创建，那么在以后也不能创建（可以用其他间接的方法），表分区创建完后，可以增加、合并、删除分区。

　　　　　　（b）oracle企业版才支持分区，所以在安装oracle的时候，要选择分区，我就是安装时是标准版，后来更改oracle特性，玩来玩去，玩坏了。稳重起见，还是安装企业版

　　　　（1）区间分区：当使用数字、日期类型的字段作为分区字段的时候，并且表达“小于某个点&大于某个点”的时候，就可以使用区间分区将数据按值来进行分区，区间分区的缺点就是对增量数据需要写个定时器，定时地去创建分区。分区的SQL语句如下：

　　　　　　create table range_example(
　　　　　　　　range_key_column date not null,
　　　　　　　　data varchar(2)
　　　　　　)
　　　　　　partition by range(range_key_column)
　　　　　　(
　　　　　　　　partition part_1 values less than(to_date('01-01-2014','dd-mm-yyyy')),　　--小于2014年1月1号并且大于  的所有数据
　　　　　　　　partition part_2 values less than(to_date('01-01-2015','dd-mm-yyyy')),　　--小于2015年1月1日并且大于等于2014年1月1日的所有数据
　　　　　　　　partition part_3 values less than(MAXVALUE)
　　　　　　);
　　　　　　对此字段进行区间分区；分区名称应当有意义

　　　　（2） 散列分区：仅仅是将大对象分解成小对象的时候，使用散列分区。数据被分到哪个分区上，因此你不会知道插入的数据会存在哪个分区上，完全是取决于hash函数，分区数最好是2的幂次方，分区的SQL语句如下：

　　　　　　create table hash_example(hash_key_column date,
　　　　　　　　date varchar2(20))
　　　　　　partition by hash(hash_key_column)
　　　　　　(partition part_1 tablespace p1,　　--这里是否要创建一个表空间？
　　　　　　partition part_2 tablespace p2); 

　　　　（3）列表分区：跟区间分区很像，但是分区区间的分区字段是数字、date等表示区间的。而列表分区是根据某个地市、型号等不能表示为区间的。列表分区的SQL语句如下：

    　　create table list_example(
　　        state_cd    varchar2(2),
 　　       data    varchar2(20)
　　    )
　　    partition by list(state_cd)
　　   　　(partition part_1 values('zj','js','sh'),
　　       partition part_2 values('gz','yn')
　　    );　

　　　　(4)间隔分区：R11g后对区间分区进行了拓展，可以根据增量数据自动创建分区。常用的有每日建分区，每月分区，每年分区。但是区间分区名称是自动生成的，因此也要写个触发器将分区重命名为有意义的分区名，以便更好的使用，这也是唯一麻烦的地方。SQL语句如下：

　　　　create table audit_trail(
　　　　    ts timestamp,
　　　　    data varchar2(30)
　　　　)
　　    partition by range(ts)
　　    interval (numtoyminterval(1,'month'))
　　    store in(users)　　　　--表空间可以不写表示为默认表空间，即users
　　　　(
　　　　    partition p0 values less than(to_date('01-01-1900','dd-mm-yyyy'))
　　　　);
以上是按月建分区，按天建分区语句：
INTERVAL (numtodsinterval(1,'day'))
按年建分区语句：
INTERVAL (NUMTOYMINTERVAL (1,'YEAR'))

 

　　　　(5)引用分区：R11g以及以上版本，用于处理父／子对等分区问题，即我们以某种方式对子表分区，使得子表的各个分区与父表相对应的分区也是一对一的关系。引用分区是在子表上操作，例如以下SQL语句，可以看到在父表上创建分区（order_date）又以order#关联子表和父表（以此将父表中的分区信息传递到了子表中），以后对父表分区的操作都会同步到子表的分区中（一一对应）包括SQL语句中的行移动、删除分区、增加分区等。引用分区在数据仓库尤为有用【那么能直接越过父表，删除子表分区？】：

Create table orders(        --父表
    Order# number primary key    ,    --关联自表的外键
    Order_date date,    --分区字段
    Data varchar2(30)
)
enable row movement –-开启行移动
partition by range(order_date)
(partition part_2015 values less than (to_date('2015-01-01','yyyy-mm-dd')),
partition part_2016 values less than (to_date('2016-01-01','yyyy-mm-dd'))
);

create table order_line_items(    --子表
    order# number,
    line# number,
    data varchar2(30),
    constraint c1_pk primary key(order#,line#),
    constraint c1_fk_p foreign key(order#) references orders
)
enable row movement,
partition by reference (c1_fk_p);

 

　　　　（6）间隔引用分区：（5）中的例子是在父表中创建了一个区间分区，如果创建的不是区间分区，而是间隔分区，那么在12c之前会报错，但是12c之后就不会报错。至于这个分区的特性之类的，顾名思义，是间隔分区特性+引用分区特性。SQL语句跟（5）中类似。

　　　　（7）虚拟列分区：如果业务需要以某列的一部分来对数据分区，或者以某几列的组合来分区（原始数据不能明显地以范围或列表进行分区）时，使用虚拟列。虚拟列分区所用的表达式可以是一个复杂的计算、某列中的子串，也可是某些列的组合，计算结果（虚拟列）不会被保存；比如以下SQL语句表达了根据数据字段首字母来判断存入哪个分区：

create table res(
    reservation_code varchar2(30),
    region as
(decode(substr(reservation_code,1,1),'A','NE'
                                    ,'C','NE'
                                    ,'B','SW'
                                    ,'D','NW')
　　)
)
partition by list (region)
(partition p1 values('NE'),
partition p2 values('SW'),
partition p3 values('NW')
);

 

　　　　(8)组合分区：组合分区是区间分区、散列分区、列表分区的组合，这些在不同版本中的组合又所不同，由于现在公司中用的基本都是11g，也没有必要都列出来，11g里面支持三种组合的任意俩俩组合。分区组合用于那些经过某个分区修剪过，但是仍然觉得分区数据量太大想要进一步进行分而治之。比如一个经过区间分区的表，可能某个分区数据量依然很大，此时再采用散列分区继续将数据细分到不同的磁盘上，此时区间分区不再存储数据，而是散列分区（子分区）的逻辑容器，数据实际存储在子分区中，如下图所示。

　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　

　　　　SQL语句：

Create table composite_example(
        Range_key_column date,
        Hash_key_column int,
        Data varchar2(20)
)
partition by range(range_key_column)
subpartition by hash(hash_key_column) subpartitions 2(
partition part_1 values less than(to_date('20150101','yyyymmdd'))
        (subpartition part_1_sub_1,
　　　　　subpartition part_1_sub_2),
partition part_2 values less than (to_date('20160101','yyyymmdd'))
        (subpartition part_2_sub_1,
        subpartition part_2_sub_2)
); 

  　　　　（9）行移动：当在分区中修改分区字段的时候，会出现以下两种情况：

　　　　　　　　·当修改后的字段值仍然在此分区内

　　　　　　　　·当修改后的字段值不再分区内，那么就要进行行移动来保证分区的正确性，这个功能需要SQL语句开启（enable row movement），行移动的原理就是删除一条记录，再插入一条记录，与此同时，也会更新相关的索引。如果你设计的系统会频繁的更新分区键值，造成大量的跨分区移动，那么这是个糟糕的设计。

 　　3、表分区总结

 　　　　如果我门想让数据按照某些值有逻辑的分割，那么区间分区是比较合适的。时序数据就是一个典型的例子，可以将数据按照“销售季度”、“财政年度”、“月份”等。

　　　　如果所有的属性都不能按照某种自然的范围来划分数据，我们可以使用散列分区，比如人口普查，可以选择一个列或者多个列作为分区键，将大对象分解成小对象便于维护。

　　　　列表分区的经典例子包括按州或者区域代码分区。

　　　　间隔分区是对区间分区的一种扩展，这种分区极大地增强了区间分区的功能，有效地降低了系统维护的工作量。这个是11gR1以及以后的版本才支持。

　　　　引用分区可以将父表的分区状况将子表相关联，这样就可以不必再自表中引入父表中的属性。　

　　　　间隔引用分区实现了间隔分区和引用分区的组合，12c以后才能使用。

　　　　如果一行数据中没有任何列可以直接用于分区，但某些列的一部分能够理想的把数据分离开，那么虚拟分区可以满足这个需求。这种分区方法，使得分区更加的灵活。

　　　　如果某些数据逻辑上可以进行区间分区，但是得到的区间分区还是太大，不能有效的管理，此时就可以使用组合分区。

　　　　在使用分区时，应该优先考虑区间分区，因为区间分区的修剪效率高。

　　4、了解索引分区：本地索引和全局索引原理，以及哪些场景使用，以及创建这些索引分区的语句

　　　　oracle不仅可以对表进行分区，也可以对索引进行分区。索引分区分为本地索引，全局分区索引。其中数据仓库中大多数分区使用的都是本地索引。在OLTP系统中，全局索引更为常见。这是某些特性决定的：本地分区索引维护起来更加方便，可以维护指定的某个分区，相对而言全局索引，要重建或者维护全部的索引。那么全局索引到底有什么用呢？

　　　　(1)按表分区的方式对索引分区叫本地索引：每个表分区对应的都有一个索引分区，这个索引分区只会对这个分区进行索引。

　　　　　　本地索引分为：（a）本地前缀索引：分区键在索引定义的前几列；（b）本地非前缀索引：这些索引不以表的分区键作为前几列，甚至不包含分区键。这两者也没有多大区别，本地前缀索引在大多数情况下都能使用分区修建，而非本地非前缀索引则不一定能保证（仅仅是支持）。SQL代码如下，代码中对local_perfixed做了前缀索引，对local_nonprefixed做了非前缀索引：

create table partitioned_table
(
    a int,
    b int,
    data char(20)
)
partition by range(a)
(
    partition part_1 values less than(2) tablespace p1,
    partition part_2 values less than(3) tablespace p2
)
create index local_perfixed on partition_table(a,b) local;
create index local_nonprefixed on partition_table(b) local;

　　　　　　本地索引的唯一缺点就是不能保证索引的唯一性，因为各自的表分区有各自的索引，跨分区的索引就不一定唯一了。如果强行保证唯一性，那么由于保证唯一性，每次维护的时候都要扫描一边其他分区，除此之外，表的插入和更新也会并行化。

　　　　(2)按区间或散列对索引分区叫全局分区索引：一个索引分区可能指向所有的表分区。