柯南君 ：Oracle 分区技术 之 怎样支撑大数据操作？

       

        前段时间。看了罗女士（ 资深技术顾问 - Oracle 中国 顾问咨询部）关于《大批量数据处理技术的演讲》视频。感觉受益良多，结合多年的知识积累，柯南君给大家分享一下：

      交流内容：     

      一、Oracle的分区技术

         （一）分区技术内容

            1. 什么是分区？

              分区就是将一个很大的table或者index 依照某一列的值。分解为更小的，易于管理的逻辑片段---分区。

将表或者索引分区不会影响SQL语句以及DML（见备注）语句，就和使用非分区表一样，每一个分区拥有自己的segment（见备注）。由于，DDL（见备注）可以将比較大的任务分解为更小的颗粒。分区表仅仅有定义信息，仅仅有每一个存放数据的分区才有各自的segment。就好象拥有多个同样列名。列类型的一个大的视图。

•  大数据对象（表，索引）被分成小物理段；
•  当分区表建立时。记录基于分区字段值被存储到对应的分区。
•  分区字段值能够改动（row movement enable）。
•  分区能够存储在不同的表空间；
•  分区能够有不同的物理存储參数。
•  分区能够支持IOT表，对象表。LOB字段，varrays等。

             备注：

                    ①  DML（data manipulation language）： 
                          它们是SELECT、UPDATE、INSERT、DELETE，就象它的名字一样，这4条命令是用来对数据库里的数据进行操作的语言;                          

                     ② segment（data manipulation language）： 

                           段（segment）是一种在数据库中消耗物理存储空间的不论什么实体（一个段可能存在于多个数据文件里，由于物理的数据文件
 是组成逻辑表空间的基本物理存储单位）

             2.分区的优点？

• 性能

              - Select 和 DML操作仅仅訪问指定分区

              - 并行DML操作

              - Patition - wise Join

• 可管理性：数据删除，数据备份

             - 历史数据清除

             - 提高备份性能

             - 指定分区的数据维护操作

• 可用性

              - 将故障局限在分区中

              - 缩短恢复时间

• 分区目标优先级

             - 高性能->数据维护能力->实施难度->高可用性（故障屏蔽能力）   

               ③  怎样实施分区？

         

  A .  Range Partitioning（范围分区）

     【案例分析】：

           就是依据数据库表中某一字段的值的范围来划分分区。比如：

create table graderecord
  (
  sno varchar2(10),
  sname varchar2(20),
  dormitory varchar2(3),
  grade int
)
partition by range(grade)
(
  partition bujige values less than(60),      --不及格
  partition jige values less than(85),        --及格
  partition youxiu values less than(maxvalue) --优秀
)

    备注：

         ① 分区字段：grade

         ② values less than 必须是确定值

     ③ 每一个分区能够单独指定物理属性 比如：partition bujige values less than(60) tablespace data0

         ④ 说明：数据中有空值，Oracle机制会自己主动将其规划到maxvalue的分区中。

    

    1）插入实验数据：

insert into graderecord values('511601','魁','229',92);
insert into graderecord values('511602','凯','229',62);
insert into graderecord values('511603','东','229',26);
insert into graderecord values('511604','亮','228',77);
insert into graderecord values('511605','敬','228',47);
insert into graderecord(sno,sname,dormitory) values('511606','峰','228');
insert into graderecord values('511607','明','240',90);
insert into graderecord values('511608','楠','240',100);
insert into graderecord values('511609','涛','240',67);
insert into graderecord values('511610','博','240',75);
insert into graderecord values('511611','铮','240',60);

   2）以下查询一下所有数据，然后查询各个分区数据。代码一起写：

select * from graderecord;
select * from graderecord partition(bujige);
select * from graderecord partition(jige);
select * from graderecord partition(youxiu);

   所有数据例如以下：

   

 

   不及格数据例如以下：

   

 

  及格数据例如以下：

    

 

    优秀数据例如以下：

    

    【范围分区特点】：

      ① 最早、最经典的分区算法
      ② Range分区通过对分区字段值的范围进行分区
     ③ Range分区特别适合于按时间周期进行数据的存储。日、周、月、年等。

     ④ 数据管理能力强
     ⑤ 数据迁移
    ⑥ 数据备份
    ⑦ 数据交换
    ⑧ 范围分区的数据可能不均匀
    ⑨ 范围分区与记录值相关。实施难度和可维护性相对较差

B.hash (散列分区)

       【案例分析】：
        散列分区是依据字段的hash值进行均匀分布。尽可能的实现各分区所散列的数据相等。

还是刚才那个表，仅仅只是把范围分区改换为散列分区。语法例如以下（删除表之后重建）：

create table graderecord
(
  sno varchar2(10),
  sname varchar2(20),
  dormitory varchar2(3),
  grade int
)
partition by hash(sno)
(
  partition p1,
  partition p2,
  partition p3
);

 

  备注：

      ① 说明：散列分区即为哈希分区。Oracle採用哈希码技术分区，详细分区怎样由Oracle说的算，也可能我下一次搜索就不是这个数据了。

  

  1)    插入实验数据，与范围分区实验插入的数据同样。

     然后查询分区数据：

select * from graderecord partition(p1);
select * from graderecord partition(p2);
select * from graderecord partition(p3);

   p1分区的数据：

   

 

   p2分区的数据：

   

 

  p3分区的数据：

  

   【HASH分区特点】：

  •基于分区字段的HASH值，自己主动将记录插入到指定分区。

  •分区数通常是2的幂

  •易于实施

 •整体性能最佳

 •适合于静态数据

 •HASH分区适合于数据的均匀存储

 •HASH分区特别适合于PDML和partition-wise joins。

 •支持 (hash) local indexes

 •9i不支持 (hash)global indexes

 •10g 支持(hash)global indexes HASH分区

 •数据管理能力弱

 •HASH分区对数据值无法控制

c.列表分区

     【案例分析】：

     列表分区明白指定了依据某字段的某个详细值进行分区，而不是像范围分区那样依据字段的值范围来划分的。

create table graderecord
(
  sno varchar2(10),
  sname varchar2(20),
  dormitory varchar2(3),
  grade int
)
partition by list(dormitory)
(
  partition d229 values('229'),
  partition d228 values('228'),
  partition d240 values('240')
)

 以上依据宿舍来进行列表分区，插入与范围分区实验同样的数据，做查询例如以下：

select * from graderecord partition(d229);
select * from graderecord partition(d228);
select * from graderecord partition(d240);

 d229分区所得数据例如以下：

  

 

d228分区所得数据例如以下：

  

 

d240分区所得数据例如以下：

  

【列表分区特点】：

•List分区通过对分区字段的离散值进行分区。

•List分区是不排序的，并且分区之间没有关联关系

•List分区适合于对数据离散值进行控制。

•List分区仅仅支持单个字段。

•List分区具有与范围分区相似的优缺点

–数据管理能力强

–List分区的数据可能不均匀

–List分区与记录值相关，实施难度和可维护性相对较差

d.组合分区（范围-散列分区，范围-列表分区）

      【案例分析】：

       首先讲范围-散列分区。先声明一下：列表分区不支持多列。可是范围分区和哈希分区支持多列。

      代码例如以下：

create table graderecord
(
  sno varchar2(10),
  sname varchar2(20),
  dormitory varchar2(3),
  grade int
)
partition by range(grade)
subpartition by hash(sno,sname)
(
  partition p1 values less than(75)
            (
               subpartition sp1,subpartition sp2
            ),
  partition p2 values less than(maxvalue)
            (
               subpartition sp3,subpartition sp4
            )
);

 注：以grade划分范围，然后以sno和sname划分散列分区，当数据量大的时候散列分区则趋于“平均”。

插入数据：

insert into graderecord values('511601','魁','229',92);
insert into graderecord values('511602','凯','229',62);
insert into graderecord values('511603','东','229',26);
insert into graderecord values('511604','亮','228',77);
insert into graderecord values('511605','敬','228',47);
insert into graderecord(sno,sname,dormitory) values('511606','峰','228');
insert into graderecord values('511607','明','240',90);
insert into graderecord values('511608','楠','240',100);
insert into graderecord values('511609','涛','240',67);
insert into graderecord values('511610','博','240',75);
insert into graderecord values('511611','铮','240',60);
insert into graderecord values('511612','狸','244',72);
insert into graderecord values('511613','杰','244',88);
insert into graderecord values('511614','萎','244',19);
insert into graderecord values('511615','猥','244',65);
insert into graderecord values('511616','丹','244',59);
insert into graderecord values('511617','靳','244',95);

 查询例如以下：

select * from graderecord partition(p1);
select * from graderecord partition(p2);
select * from graderecord subpartition(sp1);
select * from graderecord subpartition(sp2);
select * from graderecord subpartition(sp3);
select * from graderecord subpartition(sp4);

 分区p1数据例如以下，本例中75分下面：

   

 

分区p2数据例如以下。本例中75分之上包含75分：

   

 

子分区sp1：

    

 

子分区sp2：

   

 

子分区sp3：

   

 

子分区sp4：

   

 

说明：当数据量越来越大时，哈希分区的分区表中数据越来越趋于平衡。

 

以下讲范围-列表分区

范围-列表分区有两种创立方式，先说说没有模板的创建方式，这个表我要重建：

create table MobileMessage
(
 ACCT_MONTH VARCHAR2(6), -- 帐期 格式：年月 YYYYMM
 AREA_NO VARCHAR2(10), -- 地域号码
 DAY_ID VARCHAR2(2), -- 本月中的第几天 格式 DD
 SUBSCRBID VARCHAR2(20), -- 用户标识
 SVCNUM VARCHAR2(30) -- 手机号码
)
partition by range(ACCT_MONTH,AREA_NO) subpartition by list(DAY_ID)
(
  partition p1 values less than('200705','012')
  (
    subpartition shangxun1 values('01','02','03','04','05','06','07','08','09','10'),
    subpartition zhongxun1 values('11','12','13','14','15','16','17','18','19','20'),
    subpartition xiaxun1 values('21','22','23','24','25','26','27','28','29','30','31')
  ),
  partition p2 values less than('200709','014')
  (
    subpartition shangxun2 values('01','02','03','04','05','06','07','08','09','10'),
    subpartition zhongxun2 values('11','12','13','14','15','16','17','18','19','20'),
    subpartition xiaxun2 values('21','22','23','24','25','26','27','28','29','30','31')
  ),
  partition p3 values less than('200801','016')
  (
    subpartition shangxun3 values('01','02','03','04','05','06','07','08','09','10'),
    subpartition zhongxun3 values('11','12','13','14','15','16','17','18','19','20'),
    subpartition xiaxun3 values('21','22','23','24','25','26','27','28','29','30','31')
  )
)

 插入实验数据：

insert into MobileMessage values('200701','010','04','ghk001','13800000000');
insert into MobileMessage values('200702','015','12','myx001','13633330000');
insert into MobileMessage values('200703','015','24','hjd001','13300000000');
insert into MobileMessage values('200704','010','04','ghk001','13800000000');
insert into MobileMessage values('200705','010','04','ghk001','13800000000');
insert into MobileMessage values('200705','011','18','sxl001','13222000000');
insert into MobileMessage values('200706','011','21','sxl001','13222000000');
insert into MobileMessage values('200706','012','11','tgg001','13800044400');
insert into MobileMessage values('200707','010','04','ghk001','13800000000');
insert into MobileMessage values('200708','012','24','tgg001','13800044400');
insert into MobileMessage values('200709','014','29','zjj001','13100000000');
insert into MobileMessage values('200710','014','29','zjj001','13100000000');
insert into MobileMessage values('200711','014','29','zjj001','13100000000');
insert into MobileMessage values('200711','013','30','wgc001','13444000000');
insert into MobileMessage values('200712','013','30','wgc001','13444000000');
insert into MobileMessage values('200712','010','30','ghk001','13800000000');
insert into MobileMessage values('200801','015','22','myx001','13633330000');

 查询结果例如以下：

select * from MobileMessage;

   

 

分区p1查询结果例如以下：

 

 

分区p2查询结果例如以下：

 

 

子分区xiaxun2查询结果例如以下：

 

 备注：

   ① 说明：范围分区 range(A,B)的分区法则。范围分区都是 values less than(A,B)的。通常情况下以A为准。假设小于A的不用考虑B。直接插进去，假设等于A那么考虑B。要是满足B的话也插进去。

 

还有一种范围-列表分区。包括模板的（比較繁琐，可是更加精确，处理海量存储数据十分必要）：

create table MobileMessage
(
 ACCT_MONTH VARCHAR2(6), -- 帐期 格式：年月 YYYYMM
 AREA_NO VARCHAR2(10), -- 地域号码
 DAY_ID VARCHAR2(2), -- 本月中的第几天 格式 DD
 SUBSCRBID VARCHAR2(20), -- 用户标识
 SVCNUM VARCHAR2(30) -- 手机号码
)
partition by range(ACCT_MONTH,AREA_NO) subpartition by list(DAY_ID)
subpartition template
(
 subpartition sub1 values('01'),subpartition sub2 values('02'),
 subpartition sub3 values('03'),subpartition sub4 values('04'),
 subpartition sub5 values('05'),subpartition sub6 values('06'),
 subpartition sub7 values('07'),subpartition sub8 values('08'),
 subpartition sub9 values('09'),subpartition sub10 values('10'),
 subpartition sub11 values('11'),subpartition sub12 values('12'),
 subpartition sub13 values('13'),subpartition sub14 values('14'),
 subpartition sub15 values('15'),subpartition sub16 values('16'),
 subpartition sub17 values('17'),subpartition sub18 values('18'),
 subpartition sub19 values('19'),subpartition sub20 values('20'),
 subpartition sub21 values('21'),subpartition sub22 values('22'),
 subpartition sub23 values('23'),subpartition sub24 values('24'),
 subpartition sub25 values('25'),subpartition sub26 values('26'),
 subpartition sub27 values('27'),subpartition sub28 values('28'),
 subpartition sub29 values('29'),subpartition sub30 values('30'),
 subpartition sub31 values('31')
)
(
  partition p_0701_010 values less than('200701','011'),
  partition p_0701_011 values less than('200701','012'),
  partition p_0701_012 values less than('200701','013'),
  partition p_0701_013 values less than('200701','014'),
  partition p_0701_014 values less than('200701','015'),
  partition p_0701_015 values less than('200701','016'),
  partition p_0702_010 values less than('200702','011'),
  partition p_0702_011 values less than('200702','012'),
  partition p_0702_012 values less than('200702','013'),
  partition p_0702_013 values less than('200702','014'),
  partition p_0702_014 values less than('200702','015'),
  partition p_0702_015 values less than('200702','016'),
  partition p_0703_010 values less than('200703','011'),
  partition p_0703_011 values less than('200703','012'),
  partition p_0703_012 values less than('200703','013'),
  partition p_0703_013 values less than('200703','014'),
  partition p_0703_014 values less than('200703','015'),
  partition p_0703_015 values less than('200703','016'),
  partition p_0704_010 values less than('200704','011'),
  partition p_0704_011 values less than('200704','012'),
  partition p_0704_012 values less than('200704','013'),
  partition p_0704_013 values less than('200704','014'),
  partition p_0704_014 values less than('200704','015'),
  partition p_0704_015 values less than('200704','016'),
  partition p_0705_010 values less than('200705','011'),
  partition p_0705_011 values less than('200705','012'),
  partition p_0705_012 values less than('200705','013'),
  partition p_0705_013 values less than('200705','014'),
  partition p_0705_014 values less than('200705','015'),
  partition p_0705_015 values less than('200705','016'),
  partition p_0706_010 values less than('200706','011'),
  partition p_0706_011 values less than('200706','012'),
  partition p_0706_012 values less than('200706','013'),
  partition p_0706_013 values less than('200706','014'),
  partition p_0706_014 values less than('200706','015'),
  partition p_0706_015 values less than('200706','016'),
  partition p_0707_010 values less than('200707','011'),
  partition p_0707_011 values less than('200707','012'),
  partition p_0707_012 values less than('200707','013'),
  partition p_0707_013 values less than('200707','014'),
  partition p_0707_014 values less than('200707','015'),
  partition p_0707_015 values less than('200707','016'),
  partition p_0708_010 values less than('200708','011'),
  partition p_0708_011 values less than('200708','012'),
  partition p_0708_012 values less than('200708','013'),
  partition p_0708_013 values less than('200708','014'),
  partition p_0708_014 values less than('200708','015'),
  partition p_0708_015 values less than('200708','016'),
  partition p_0709_010 values less than('200709','011'),
  partition p_0709_011 values less than('200709','012'),
  partition p_0709_012 values less than('200709','013'),
  partition p_0709_013 values less than('200709','014'),
  partition p_0709_014 values less than('200709','015'),
  partition p_0709_015 values less than('200709','016'),
  partition p_0710_010 values less than('200710','011'),
  partition p_0710_011 values less than('200710','012'),
  partition p_0710_012 values less than('200710','013'),
  partition p_0710_013 values less than('200710','014'),
  partition p_0710_014 values less than('200710','015'),
  partition p_0710_015 values less than('200710','016'),
  partition p_0711_010 values less than('200711','011'),
  partition p_0711_011 values less than('200711','012'),
  partition p_0711_012 values less than('200711','013'),
  partition p_0711_013 values less than('200711','014'),
  partition p_0711_014 values less than('200711','015'),
  partition p_0711_015 values less than('200711','016'),
  partition p_0712_010 values less than('200712','011'),
  partition p_0712_011 values less than('200712','012'),
  partition p_0712_012 values less than('200712','013'),
  partition p_0712_013 values less than('200712','014'),
  partition p_0712_014 values less than('200712','015'),
  partition p_0712_015 values less than('200712','016'),
  partition p_0801_010 values less than('200801','011'),
  partition p_0801_011 values less than('200801','012'),
  partition p_0801_012 values less than('200801','013'),
  partition p_0801_013 values less than('200801','014'),
  partition p_0801_014 values less than('200801','015'),
  partition p_0801_015 values less than('200801','016'),
  partition p_other values less than(maxvalue, maxvalue)
);

 这个是带有模板子分区的。模板子分区具体到月中的天。

这样的分区模式仅仅要建立了分区就会自己主动创建子分区的。

插入上面不带模板分区实验同样的数据，随机查询分区数据：

 

查询分区p_0701_010的数据：

select * from MobileMessage partition(p_0701_010);

 查询结果：

 

查询子分区p_0701_010_sub4的数据：

select * from MobileMessage subpartition(p_0701_010_sub4);

 查询结果例如以下：

 

查询分区p_0706_011的数据：

select * from MobileMessage partition(p_0706_011);

 查询结果例如以下：

 

查询子分区p_0706_011_sub21的数据：

select * from MobileMessage subpartition(p_0706_011_sub21);

 查询结果例如以下：

 

以下讲讲分区的维护操作：

(1)分裂分区，以第一个范围分区为例：

alter table graderecord split partition jige at(75)
      into(partition keyi,partition lianghao);

 把分区及格分裂为两个分区：能够和良好。

 

(2)合并分区。以第一个范围分区为例：

alter table graderecord merge partitions keyi,lianghao
into partition jige;

 把能够和良好两个分区合并为及格。

 

(3)加入分区。因为在范围分区上加入分区要求加入的分区范围大于原有分区最大值，但原有分区最大值已经为maxvalue。故本处以第二个散列分区为例：

alter table graderecord add partition p4;

 给散列分区样例又添加了一个分区p4 。

 

(4)删除分区，语法：

alter table table_name drop partition partition_name;

 

(5)截断分区，清空分区中的数据

alter table table_name truncate partition partition_name;

 备注：

      ① 说明：对待分区的操作相同能够对待子分区，效果一样。删除一个分区会同一时候删除其下的子分区。

合并多个分区也会把他们的子分区自己主动合并。分裂分区时注意分裂点。

 另外不带模板子分区和带有模板子分区的分区表操作的差别：带有子分区模板的分区表在加入分区时候自己主动加入子分区。不带模板子分区的分区表没有这个功能；带有子分区模板的分区表在更改分区时仅仅需更改分区，不带模板子分区的分区表在更改分区时一定注意连同子分区一起更改。

【复合分区特点】：

    •Oracle支持的Composite分区：

    • Range-Hash,Range-List

    •既适合于历史数据。又适合于数据均匀分布

    •与范围分区一样提供高可用性和管理性

    •更好的PDML和partition-wisejoins性能

    •实现粒度更细的操作

    •支持复合 local indexes

    •不支持复合composite global indexes？

         3）分区索引

         

         

        4）分区表索引的分类：
            ① Local Prefixed index

                 局部前缀索引（local prefixed index）：在这些索引中，分区键在索引定义的前几列上。比如，一个表在名为LOAD_DATE 的列上进行区间分区，

                                                                                           该表上的局部前缀索引就是採用LOAD_DATE作为其索引列列表中的第一列。

            ② Local Non-prefiexed index

                局部非前缀索引（local nonprefixed index）：这些索引不以分区键作为其列列表的前几列。索引可能包括分区键列，也可能不包括。

                注意： 这两类索引（Local Prefixed index 与 Local Non-prefiexed index ）都能够进行分区消除,前提是查询的条件中包括索引分区键，它们都支持惟一性（仅仅要局部非前缀索引包括分区键列)。
                           局部索引与表的分区数一致，假设新增一个分区，新添加的分区局部索引也会自己主动创建。全局索引则不行（即须要重建全局索引）。

               【案例分析】：

               --以下通过实验来说明索引的分区消除

<strong>     </strong>--创建一个分区表
      CREATE TABLE partitioned_table ( a int, b int, data char(20))
      PARTITION BY RANGE (a)
      ( PARTITION part_1 VALUES LESS THAN(2) tablespace gcomm,      --以a字段进行分区，小于等于2的存在分区1。小于等于3的存在分区2
      PARTITION part_2 VALUES LESS THAN(3) tablespace gmapdata )
      --创建一个本地前缀索引   
      create index local_prefixed on partitioned_table (a,b) local;
      --创建一个本地非前缀索引
      create index local_nonprefixed on partitioned_table (b) local;
      --向表中插入数据
      insert into partitioned_table select mod(rownum-1,2)+1, rownum, 'x' from all_objects;
      --分析表
      begin dbms_stats.gather_table_stats ( user,'PARTITIONED_TABLE',cascade=>TRUE );end;

      --以sys用户登录后 将gmapdata表空间置为离线
      alter tablespace gmapdata offline;--分区2的数据包含其索引等都被置为离线状态

     <p>      select * from partitioned_table where a = 1 and b = 1;
       A        B     DATA
      ----  ------ --------------------
       1       1        x
     --将之前的plan_table表的数据清除
     delete from plan_table;
     --生成统计信息
     explain plan for select * from partitioned_table where a = 1 and b = 1;
     --查看统计信息结果
     select * from table(dbms_xplan.display);
     PLAN_TABLE_OUTPUT
     --------------------------------------------------------------------------------
     Plan hash value: 1622054381
     --------------------------------------------------------------------------------
     | Id  | Operation                                                  | Name                       | Rows  | Bytes | Pstart| Pstop
     --------------------------------------------------------------------------------
     |   0 | SELECT STATEMENT                                  |                                 |     1   |    28 |    
     |   1 |  PARTITION RANGE SINGLE                        |                                 |     1   |    28 |    1      1
     |   2 |   TABLE ACCESS BY LOCAL INDEX ROWID  | PARTITIONED_TABLE  |     1   |    28 |    1      1
     |*  3 |    INDEX RANGE SCAN                              | LOCAL_PREFIXED       |     1   |         |    1      1
    --------------------------------------------------------------------------------
    Predicate Information (identified by operation id):
    ---------------------------------------------------
   3 - access("A"=1 AND "B"=1)
   注：能够进行查询。能够通过本地前缀索引将分区2消除 由于分区2採用的表空间为gmapdata，而这个表空间在上述已将其离线，通过本地前缀索引在查询的时候将分区2消除，仅仅在第一个分区进行查询，因此该查询能够成功查询。

<u>   再看以下的一个查询： </u> 
 select * from partitioned_table where b = 1; 
<span style="color: red;">提示：ora-00376:此时无法读取文件11 </span>
<span style="color: red;">      ora-01110：数据文件11：‘D:\ORACE|PRODUCT\10.2.0\ORADATA\FGISDBGMAPDATA.DBF’</span>
  delete from plan_table;
  explain plan for select * from partitioned_table where b = 1;
  select * from table(dbms_xplan.display);
  PLAN_TABLE_OUTPUT
  --------------------------------------------------------------------------------
  Plan hash value: 440752652
  --------------------------------------------------------------------------------
 | Id  | Operation                                                      | Name                        | Rows  | Bytes |  Pstart| Pstop
 --------------------------------------------------------------------------------
 |   0 | SELECT STATEMENT                                   |                                     |     1 |    28 |   
 |   1 |  PARTITION RANGE ALL                              |                                      |     1 |    28 |    1       2
 |   2 |   TABLE ACCESS BY LOCAL INDEX ROWID   | PARTITIONED_TABLE      |     1 |    28 |    1       2
 |*  3 |    INDEX RANGE SCAN                               | LOCAL_NONPREFIXED     |     1 |        |    1       2
 --------------------------------------------------------------------------------
 Predicate Information (identified by operation id):
 ---------------------------------------------------
   3 - access("B"=1)
   注：当查询谓词仅仅有b。即採用非前缀索引，并且查询的条件中又不含分区键a，因此在查询时无法将分区2消除。导致在查询分区2时提示数据文件不在。

<span style="font-family: Helvetica, Tahoma, Arial, sans-serif;">将本地前缀索引删掉后：  </span><span style="font-family: Helvetica, Tahoma, Arial, sans-serif;"> </span></p><p>drop index local_prefixed;
select * from partitioned_table where a = 1 and b = 1;
A        B     DATA
----  ------ --------------------
1       1        x
delete from plan_table;
explain plan for select * from partitioned_table where a = 1 and b = 1;
select * from table(dbms_xplan.display);
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
Plan hash value: 904532382
--------------------------------------------------------------------------------
| Id  | Operation                          | Name              | Rows  | Bytes | Pstart| Pstop 
--------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                   |                   |     1 |    28 |
|   1 |  PARTITION RANGE SINGLE            |                   |     1 |    28 |   1       1
|*  2 |   TABLE ACCESS BY LOCAL INDEX ROWID| PARTITIONED_TABLE |     1 |    28 |   1       1
|*  3 |    INDEX RANGE SCAN                | LOCAL_NONPREFIXED |     1 |       |   1       1
--------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
   2 - filter("A"=1)
   3 - access("B"=1)
   
    注：本地前缀索引删除后。採用本地非前缀索引进行如上查询也能够成功。可见本地非前缀索引也能够进行消除分区，主要取决于谓词。
该表利用a字段
进行分区。因此主要谓词中有a字段的查询。就能够成功查询。
<strong>
</strong></p><p></p><p><strong><span style="font-size:10px;">-局部索引和惟一约束介绍：</span></strong>
</p><p>CREATE TABLE partitioned
 ( load_date date, id int, constraint partitioned_pk primary key(id) )
 PARTITION BY RANGE (load_date)
 ( PARTITION part_1 VALUES LESS THAN( to_date('01/01/2000','dd/mm/yyyy') ) ,
   PARTITION part_2 VALUES LESS THAN( to_date('01/01/2001','dd/mm/yyyy') ))

select segment_name, partition_name, segment_type from user_segments where segment_name like 'PARTITIONED%';

SEGMENT_NAME              PARTITION_NAME       SEGMENT_TYPE
--------------------  ---------------------  ------------------
PARTITIONED               PART_1                TABLE PARTITION
PARTITIONED               PART_2                TABLE PARTITION
PARTITIONED_PK                                  INDEX
<span style="color: red;">注：PARTITIONED_PK 索引没有进行分区。因此能够保证唯一性</span><br style="color: red;" />
</p><p><strong>删掉表又一次创建并建立一个本地索引后在创建一个唯一索引</strong> 
</p><p>drop table partitioned
CREATE TABLE partitioned ( timestamp date, id int)
 PARTITION BY RANGE (timestamp)
 (PARTITION part_1 VALUES LESS THAN( to_date('01-1-2000','dd-mm-yyyy') ) ,
  PARTITION part_2 VALUES LESS THAN( to_date('01-1-2001','dd-mm-yyyy') )
 )

create index partitioned_idx on partitioned(id) local;--创建一个本地索引

select segment_name, partition_name, segment_type from user_segments where segment_name like 'PARTITIONED%';

SEGMENT_NAME                PARTITION_NAME                 SEGMENT_TYPE
------------------------ ------------------------------ ------------------
PARTITIONED                 PART_1                         TABLE PARTITION
PARTITIONED                 PART_2                         TABLE PARTITION
PARTITIONED_IDX             PART_1                         INDEX PARTITION
PARTITIONED_IDX             PART_2                         INDEX PARTITION

alter table partitioned add constraint partitioned_pk primary key(id);--在id上添加一个全局索引
提示：此列列表已有索引
<strong>注：分区索引无法 保证唯一性，由于假设要保证分区索引的唯一性。即分区1有id=1。那么分区2中就不能有id=1，而我们假设做了这个限制。往不同
分区进行插数据就会减少分区表的灵活性。
</strong>


</p>

            ③ Global Prefixed index

                

    全局索引使用一种有别于底层表的机制进行分区。
表能够按一个TIMESTAMP 列划分为10 个分区，而这个表上的一个全局索引能够按REGION 列划分
为5 个分区。与局部索引不同，全局索引仅仅有一类。这就是前缀全局索引（prefixed global index）。假设全局索引的索引键未从该索引的分区键開始
。这是不同意的。
这说明，不论用什么属性对索引分区，这些属性都必须是索引键的前几列。


drop table partitioned
CREATE TABLE partitioned ( timestamp date, id int )
 PARTITION BY RANGE (timestamp)
 ( PARTITION part_1 VALUES LESS THAN ( to_date('01-1-2000','dd-mm-yyyy') ) ,
   PARTITION part_2 VALUES LESS THAN ( to_date('01-1-2001','dd-mm-yyyy') )
 )

create index partitioned_index on partitioned(id) GLOBAL
 partition by range(id)
 (partition part_1 values less than(1000),
  partition part_2 values less than (MAXVALUE) --全局索引必须指定最大值，否则会提示：ORA-14021：必须指定全部列的MAXVALUE
 )
注：全局索引有一个要求。即最高分区（最后一个分区）必须有一个值为MAXVALUE 的分区上界。这能够确保底层表中的全部行都能放在这个索引中。

全局索引能够创建一个唯一索引:
alter table partitioned add constraint partitioned_pk primary key(id);--创建唯一索引成功
注：该唯一索引是通过创建的全局索引来保证唯一。能够通过删除其索引的错误来说明
drop index partitioned_index;提示：ora-02429：无法删除用于强制唯一/主键的索引。

--下面样例说明了全局索引必须是前缀的
create index partitioned_index2 on partitioned(timestamp,id) GLOBAL
 partition by range(id)--以id为分区键 那么其索引就必须将id置到最前面
 (partition part_1 values less than(1000),
  partition part_2 values less than (MAXVALUE)
 )
提示：ORA-14038:全局分区索引必须是前缀

数据仓库与全局索引：
数据仓库通常是通过数据的滑入划出进行管理（即旧数据划出，新数据滑入）。
一个分区表中，假设进行分区的增删改操作会造成全局索引失效。因此，
採用何种索引要依据系统的要求。

实验1：分区的滑入滑出导致全局索引失效，局部索引仍有效
--创建分区表  
CREATE TABLE partitioned ( timestamp date, id int )
 PARTITION BY RANGE (timestamp)
 ( PARTITION fy_2004 VALUES LESS THAN ( to_date('01-1-2005','dd-mm-yyyy') ) ,
  PARTITION fy_2005 VALUES LESS THAN ( to_date('01-1-2006','dd-mm-yyyy') )
 )
--对两个分区都插入数据
insert into partitioned partition(fy_2004)
  select to_date('31-12-2004', 'dd-mm-yyyy') - mod(rownum,360),
         object_id
    from all_objects;
         
insert into partitioned partition(fy_2005)
  select to_date('31-12-2005', 'dd-mm-yyyy') - mod(rownum,360),
         object_id
    from all_objects;
--分别创建一个本地索引和全局索引
create index partitioned_idx_local on partitioned(id) LOCAL;
create index partitioned_idx_global on partitioned(timestamp) GLOBAL;
--创建一个新表（用于装载分区划出的数据）
create table fy_2004 (timestamp date, id int); create table fy_2005 (timestamp date, id int); 
create index fy_2004_idx on fy_2004(id);create index fy_2005_idx on fy_2005(id);
--创建一个新表并插入数据
create table fy_2006 ( timestamp date, id int );
insert into fy_2006
  select to_date('31-12-2006', 'dd-mm-yyyy') - mod(rownum,360),
         object_id
    from all_objects;
create index fy_2006_idx on fy_2006(id) nologging;

create table fy_2007 ( timestamp date, id int );
insert into fy_2007
  select to_date('31-12-2007', 'dd-mm-yyyy') - mod(rownum,360),
         object_id
    from all_objects;
create index fy_2007_idx on fy_2007(id) nologging;

--将分区fy_2004的数据放到表fy_2004中，并删除该分区
alter table partitioned exchange partition fy_2004 with table fy_2004 including indexes without validation;
alter table partitioned drop partition fy_2004;
--创建一个新分区。用于装载新数据
alter table partitioned add partition fy_2006 values less than ( to_date('01-12-2007','dd-mm-yyyy') );
alter table partitioned exchange partition fy_2006 with table fy_2006 including indexes without validation;

--最后查看索引的情况
select index_name, status from user_indexes where table_name='PARTITIONED';
1    PARTITIONED_IDX_LOCAL    N/A
2    PARTITIONED_IDX_GLOBAL    UNUSABLE
--发现全局索引已失效

假设强制用其全局索引，会导致无法查询
set autotrace on explain
select /*+ index( partitioned PARTITIONED_IDX_GLOBAL ) */
 count(*)
  from partitioned
 where timestamp between sysdate - 50 and sysdate;
ORA-01502: 索引 'LTTFM.PARTITIONED_IDX_GLOBAL' 或这类索引的分区处于不可用状态

--直接进行查询，则会进行全表扫描
select count(*) from partitioned where timestamp between sysdate-50 and sysdate;

实验2：全局索引失效的解决的方法：
1）可对索引进行重建。
2）直接在进行分区删改的时候 加上更新索引的字句（UPDATE GLOBAL INDEXES）：
--删除、交换分区时可加上索引更新的字句，添加一个分区不用进行更新索引，由于新添加的分区空行
alter table partitioned exchange partition fy_2004 with table fy_2004 including indexes without validation UPDATE GLOBAL INDEXES
alter table partitioned drop partition fy_2004 UPDATE GLOBAL INDEXES

注：假设在对分区进行操作时加上了 UPDATE GLOBAL INDEXES 更新索引的字句。那么全局索引就不会失效。

实验3：比較索引重建和更新索引所占用的资源情况:
begin runStats_pkg.rs_start;end;  
alter table partitioned exchange partition fy_2004 with table fy_2004 including indexes without validation;
alter table partitioned drop partition fy_2004;
alter table partitioned add partition fy_2006 values less than (to_date('01-1-2007','dd-mm-yyyy') );
alter table partitioned exchange partition fy_2006 with table fy_2006 including indexes without validation;
alter index partitioned_idx_global rebuild; --採用索引重建的方法
begin  runStats_pkg.rs_middle;end;

alter table partitioned exchange partition fy_2005 with table fy_2005 including indexes without validation update global indexes;
alter table partitioned drop partition fy_2005 update global indexes;
alter table partitioned add partition fy_2007 values less than ( to_date('01-1-2008','dd-mm-yyyy') );
alter table partitioned exchange partition fy_2007 with table fy_2007 including indexes without validation update global indexes;
begin runStats_pkg.rs_stop;end;  --採用索引更新的方法

输出的结果：
Run1 ran in 936 hsecs
Run2 ran in 1101 hsecs
run 1 ran in 85.01% of the time

实验结果：其结果表明对全局索引进行更新要花更长时间。可是假设说系统不同意中断的话。那么还是应该採取索引更新的方法。

            ④ Non Partition Index

          备注：
             Global索引的分区不同与表分区
             Local索引的分区与表分区同样
             An index is prefixed if it is partitioned on a left prefix of the index columns.
             分区表上的非分区索引等同于Global索引

分区索引字典：

DBA_PART_INDEXES 分区索引的概要统计信息。能够得知每一个表上有哪些分区索引。分区索引的类新(local/global,)
Dba_ind_partitions每一个分区索引的分区级统计信息
Dba_indexesminusdba_part_indexes。能够得到每一个表上有哪些非分区索引

        5）分区选择的策略

  

      6）分区表设计原则

       ①  表的大小：当表的大小超过1.5GB－2GB。或对于OLTP系统，表的记录超过1000万，都应考虑对表进行分区。 
       ②  数据訪问特性：基于表的大部分查询应用，仅仅訪问表中少量的数据。对于这样表进行分区。可充分利用分区排除无关数据查询的特性。

 
       ③  数据维护：按时间段删除成批的数据，比如按月删除历史数据。对于这种表须要考虑进行分区，以满足维护的须要。
       ④  数据备份和恢复： 按时间周期进行表空间的备份时，将分区与表空间建立相应关系。
       ⑤  仅仅读数据：假设一个表中大部分数据都是仅仅读数据。通过对表进行分区，可将仅仅读数据存储在仅仅读表空间中。对于数据库的备份是很故意的。

 
       ⑥  并行数据操作：对于常常运行并行操作（如Parallel Insert,Parallel Update等）的表应考虑进行分区。 
       ⑦  表的可用性：当对表的部分数据可用性要求非常高时。应考虑进行表分区。 

      7）分区表的管理功能

        ①  分区的添加（ADD）
        ②  分区的删除（DROP）
        ③  分区的合并（MERGE）
        ④  分区的清空（TRUNCATE）
        ⑤  分区的交换（EXCHANGE）
        ⑥  分区的压缩（COALESE）
        ⑦  分区的移动（MOVE）
        ⑧  分区的分离（SPLIT）
        ⑨  改动分区的Default  Attribute
              分区的更名（RENAME）

      8）分区索引的管理功能

        ①  分区索引的删除（DROP）
        ②  分区索引的改动（MODIFY）
        ③  分区索引Default Attribute的改动
        ④  分区索引的重建（REBUILD）
        ⑤  分区索引的更名（RENAME）
        ⑥  分区索引的分离（SPLIT）
        ⑦  分区索引的Unusable

      9）“滚动窗体”操作 - 大量数据快速装载

       

 

分区索引字典

DBA_PART_INDEXES 分区索引的概要统计信息，能够得知每一个表上有哪些分区索引，分区索引的类新(local/global,)
Dba_ind_partitions每一个分区索引的分区级统计信息
Dba_indexesminusdba_part_indexes。能够得到每一个表上有哪些非分区索引

 2.Oracle的分区的交换功能     

      1）交换功能分类      

        ①    通过交换数据段，实现分区和非分区表的数据交换。

以及子分区和分区表的数据交换
        ②   很快捷的数据移动方式。

特别是没有validation和索引维护操作时
        ③   Local 索引自己主动维护
        ④   Global索引必须重建 

       2)  分区交换的应用--- 全文检索

        

        第一步：1：00数据的载入
             insert into BF_DXX_stage(SJ,TEXT3) values(to_date('2004.03.02','YYYY.MM.DD'),'大撒反对撒');
       第二步：建立context 索引
            CREATE INDEX IDX_ BF_DXX _STAGE ON BF_DXX_stage(text3)
           INDEXTYPE IS CTXSYS.CONTEXT PARAMETERS
           ('LEXER MYLEXER STORAGE MYSTORE FILTER CTXSYS.NULL_FILTER MEMORY 100M') parallel 4;
       第三步：partition的交换
          alter table BF_DXX exchange partition p2 with table BF_DXX_stage including indexes;

 3.迁移表空间(Transportable Tablespace)技术简单介绍

   1)    

     备注：          

             第一步：exp transport_tablespace=yes
             第二步：FTP 数据文件和dmp文件
             第三步：imp transport_tablespace=yes

   2) 迁移表空间技术的作用

    ①  业务系统数据向数据仓库系统的迁移 
    ② 对业务系统和数据仓库系统的数据进行定期归档 
    ③ 数据仓库向数据集市的数据迁移 
    ④ 数据对外公布 
    ⑤ 按表空间进行时间点的数据恢复(TSPITR) 

   3）迁移表空间技术的长处

     ① 性能大大高于export/import或PL/SQL编写的程序 
     ② 因为Dmp文件仅仅包括表空间的结构信息，因此该技术的真正开销在于数据文件的传输。 
          对源系统的影响很小 
     ③ 仅仅须要将被迁移的表空间设置为仅仅读方式 
     ④ 可同一时候传输索引数据，避免在目的数据库中重建索引

    4）分区交换的应用--- ETL

      ①   在源系统中，将须要抽取的数据以例如以下语句形式。抽取到建立在单独表空间上的中间表中： 
                CREATE TABLE ... AS SELECT …
                INSERT /*+ APPEND */ AS SELECT …
      ②   以TTS方式将中间表的表空间传输到数据仓库之中。

 
               exp transportable_tablespace=Yes …
               FTP 中间表表空间的数据文件
              imp transportable_tablespace=Yes …
     ③  在数据仓库中对中间表进行各种数据归并等清洗工作，并建立须要的各种索引。

 
     ④  通过exchange技术。将中间表数据及索引直接交换到分区表中。 
     ⑤  Alter table <分区表> exchange partition <分区名> with table <中间表> including indexes; 

  5）分区交换的应用---反复记录删除

       ① 问题描写叙述： 在使用SQL*Loader进行数据载入sor_acct_dcc_saamt_c表时，因为操作失误。反复载入，导致分区ETL_LOAD_DATE_0606出现反复记录，也使得两个唯一          索引：IDX_SAACNAMT_C_1，IDX_SAACNAMT_C_2的ETL_LOAD_DATE_0606分区不可用（UNUSABLE）。
      用户在试图又一次创建该分区索引时。出现例如以下错误：
      SQL> alter index IDX_SAACNAMT_C_2 rebuild partition ETL_LOAD_DATE_0606;
      alter index IDX_SAACNAMT_C_2 rebuild partition ETL_LOAD_DATE_0606
     ORA-01452: cannot CREATE UNIQUE INDEX; duplicate keys found 

  ② 在试图删除该分区的反复记录时。又出现例如以下错误：
        SQL> delete from sor_acct_dcc_saamt_c partition(ETL_LOAD_DATE_0606)
        where rowid not  in (select min(rowid) from sor_acct_dcc_saamt_c partition(ETL_LOAD_DATE_0606) group by ETL_LOAD_DATE, CUST_ACCT_NO,          SA_CURR_COD, SA_CURR_IDEN);
*
ORA-01502: index 'GYFX.IDX_SAACNAMT_C_1' or partition of such index is in unusable state

  ③ 简单办法是彻底删除这两个唯一索引，又一次创建。 
      数据量大，时间太长。
     影响系统的可用性。 
     更完备的解决方案
    创建一个与sor_acct_dcc_saamt_c结构一样的暂时表test。 
   SQL> create table test as select * from sor_acct_dcc_saamt_c where 1=2; 
  将sor_acct_dcc_saamt_c表分区ETL_LOAD_DATE_0606数据交换到暂时表test。 
   SQL> alter table sor_acct_dcc_saamt_c exchange partition ETL_LOAD_DATE_0606 with table test;  

  ④ 更完备的解决方案
    删除test中的反复记录  
    delete from test 
where rowid not in (select min(rowid) from test group by ETL_LOAD_DATE, CUST_ACCT_NO, SA_CURR_COD, SA_CURR_IDEN); 
   由于test表没有不论什么索引，可避免上述ORA-01502错误。  
   将暂时表test数据交换回sor_acct_dcc_saamt_c表分区ETL_LOAD_DATE_0606 。  
   alter table sor_acct_dcc_saamt_c exchange partition ETL_LOAD_DATE_0606 with table test; 

⑤ 更完备的解决方案
    又一次创建创建该分区索引IDX_SAACNAMT_C_1，IDX_SAACNAMT_C_2   
   alter index IDX_SAACNAMT_C_1 rebuild partition ETL_LOAD_DATE_0606 tablespace ETL0_R_LOAD_IDX_200606;  
   alter index IDX_SAACNAMT_C_2 rebuild partition ETL_LOAD_DATE_0606 tablespace ETL0_R_LOAD_IDX_200606; 
   此时反复记录已经删除，可避免上述ORA-01452错误 

  3.分区的评估

    1)性能方面
      对应速度
      资源消耗（CPU、内存、I/O）
      性能分析工具的使用：Oracle Trace, Autotrace, TKPROF
    2) 其他方面
      数据迁移能力
      数据备份和恢复
     数据扩展性（Add, Drop, Exchange, Merge, …）
     数据高可用性