柯南君：Oracle分区技术之如何支撑大数据操作？
前段时间，看了罗女士（ 资深技术顾问 - Oracle 中国 顾问咨询部）关于《大批量数据处理技术的演讲》视频，感觉受益良多，结合多年的知识积累，柯南君给大家分享一下：
交流内容：
一、Oracle的分区技术
（一）分区技术内容
1. 什么是分区？
分区就是将一个非常大的table或者index 按照某一列的值，分解为更小的，易于管理的逻辑片段---分区。将表或者索引分区不会影响SQL语句以及DML（见备注）语句，就和使用非分区表一样，每个分区拥有自己的segment（见备注），因为，DDL（见备注）能够将比较大的任务分解为更小的颗粒。分区表只有定义信息，只有每个存放数据的分区才有各自的segment。就好象拥有多个相同列名，列类型的一个大的视图。
-
大数据对象（表，索引）被分成小物理段； 当分区表建立时，记录基于分区字段值被存储到相应的分区； 分区字段值可以修改（row movement enable）； 分区可以存储在不同的表空间； 分区可以有不同的物理存储参数； 分区可以支持IOT表，对象表，LOB字段，varrays等；
备注：
- 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）插入实验数据：
Sql代码
- 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）下面查询一下全部数据，然后查询各个分区数据，代码一起写：
Java代码
- select * from graderecord;
- select * from graderecord partition(bujige);
- select * from graderecord partition(jige);
-
select * from graderecord partition(youxiu);
全部数据如下：
不及格数据如下：
及格数据如下：
优秀数据如下：
【范围分区特点】：
① 最早、最经典的分区算法
② Range分区通过对分区字段值的范围进行分区
③ Range分区特别适合于按时间周期进行数据的存储。日、周、月、年等。
④ 数据管理能力强
⑤ 数据迁移
⑥ 数据备份
⑦ 数据交换
⑧ 范围分区的数据可能不均匀
⑨ 范围分区与记录值相关，实施难度和可维护性相对较差
B.hash (散列分区)
【案例分析】：
散列分区是根据字段的hash值进行均匀分布，尽可能的实现各分区所散列的数据相等。
还是刚才那个表，只不过把范围分区改换为散列分区，语法如下（删除表之后重建）：
Sql代码
- 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) 插入实验数据，与范围分区实验插入的数据相同。
然后查询分区数据：
Sql代码
- 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.列表分区【案例分析】：
列表分区明确指定了根据某字段的某个具体值进行分区，而不是像范围分区那样根据字段的值范围来划分的。
Sql代码
- 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')
-
)
以上根据宿舍来进行列表分区，插入与范围分区实验相同的数据，做查询如下：
Sql代码
- 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.组合分区（范围-散列分区，范围-列表分区） 【案例分析】：
首先讲范围-散列分区。先声明一下：列表分区不支持多列，但是范围分区和哈希分区支持多列。
代码如下：
Sql代码
- 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划分散列分区，当数据量大的时候散列分区则趋于“平均”。
插入数据：
Sql代码
- 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);
查询如下：
Sql代码
- 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：
说明：当数据量越来越大时，哈希分区的分区表中数据越来越趋于平衡。
下面讲范围-列表分区
范围-列表分区有两种创立方式，先说说没有模板的创建方式，这个表我要重建：
Sql代码
- 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')
- )
-
)
插入实验数据：
Sql代码
- 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');
查询结果如下：
Sql代码
-
select * from MobileMessage;
分区p1查询结果如下：
分区p2查询结果如下：
子分区xiaxun2查询结果如下：
备注：
① 说明：范围分区 range(A,B)的分区法则，范围分区都是 values less than(A,B)的，通常情况下以A为准，如果小于A的不用考虑B，直接插进去，如果等于A那么考虑B，要是满足B的话也插进去。
另一种范围-列表分区，包含模板的（比较繁琐，但是更加精确，处理海量存储数据十分必要）：
Sql代码
- 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的数据：
Sql代码
-
select * from MobileMessage partition(p_0701_010);
查询结果：
查询子分区p_0701_010_sub4的数据：
Sql代码
-
select * from MobileMessage subpartition(p_0701_010_sub4);
查询结果如下：
查询分区p_0706_011的数据：
Sql代码
-
select * from MobileMessage partition(p_0706_011);
查询结果如下：
查询子分区p_0706_011_sub21的数据：
Sql代码
-
select * from MobileMessage subpartition(p_0706_011_sub21);
查询结果如下：
下面讲讲分区的维护操作：
(1)分裂分区，以第一个范围分区为例：
Sql代码
- alter table graderecord split partition jige at(75)
-
into(partition keyi,partition lianghao);
把分区及格分裂为两个分区：可以和良好。
(2)合并分区，以第一个范围分区为例：
Sql代码
- alter table graderecord merge partitions keyi,lianghao
-
into partition jige;
把可以和良好两个分区合并为及格。
(3)添加分区，由于在范围分区上添加分区要求添加的分区范围大于原有分区最大值，但原有分区最大值已经为maxvalue，故本处以第二个散列分区为例：
Sql代码
-
alter table graderecord add partition p4;
给散列分区例子又增加了一个分区p4 。
(4)删除分区，语法：
Sql代码
-
alter table table_name drop partition partition_name;
(5)截断分区，清空分区中的数据
Sql代码
-
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=yes2) 迁移表空间技术的作用
① 业务系统数据向数据仓库系统的迁移
② 对业务系统和数据仓库系统的数据进行定期归档
③ 数据仓库向数据集市的数据迁移
④ 数据对外发布
⑤ 按表空间进行时间点的数据恢复(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, …）
数据高可用性
本站文章为和通数据库网友分享或者投稿，欢迎任何形式的转载，但请务必注明出处. 同时文章内容如有侵犯了您的权益，请联系QQ：970679559，我们会在尽快处理。
-
alter table table_name truncate partition partition_name;
-
alter table table_name drop partition partition_name;
-
alter table graderecord add partition p4;
-
select * from MobileMessage subpartition(p_0706_011_sub21);
-
select * from MobileMessage partition(p_0706_011);
-
select * from MobileMessage subpartition(p_0701_010_sub4);
-
select * from MobileMessage partition(p_0701_010);
-
select * from MobileMessage;
① DML（data manipulation language）：
它们是SELECT、UPDATE、INSERT、DELETE，就象它的名字一样，这4条命令是用来对数据库里的数据进行操作的语言;
段（segment）是一种在数据库中消耗物理存储空间的任何实体（一个段可能存在于多个数据文件中，因为物理的数据文件
是组成逻辑表空间的基本物理存储单位）
2.分区的好处？
性能 - Select 和 DML操作只访问指定分区
- 并行DML操作
- Patition - wise Join
可管理性：数据删除，数据备份 - 历史数据清除
- 提高备份性能
- 指定分区的数据维护操作
可用性 - 将故障局限在分区中
- 缩短恢复时间
分区目标优先级 - 高性能->数据维护能力->实施难度->高可用性（故障屏蔽能力）
③ 如何实施分区？
A . Range Partitioning（范围分区）<喎?http://www.Bkjia.com/kf/ware/vc/" target="_blank" class="keylink">vcD4KPHA+CjxzdHJvbmc+ICAgICChvrC4wP231s72ob+jujwvc3Ryb25nPjwvcD4KPHA+CjwvcD4KPHA+CiAgICAgICAgICAgvs3Kx7j5vt3K/b7dv+Kx7dbQxLPSu9fWts61xCYjMjA1NDA7tcS3ts6nwLS7rrfWt9bH+KOswP3I56O6PC9wPgoKCiAgIFNxbLT6wusgIDxpbWcgY2xhc3M9"star" src="http://www.2cto.com/uploadfile/Collfiles/20141114/2014111409193437.png" alt="收藏代码">