|
前段时间，看了罗女士（ 资深技术顾问 - 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 可管理性：数据删除，数据备份 - 历史数据清除 - 提高备份性能 - 指定分区的数据维护操作 可用性 - 将故障局限在分区中 - 缩短恢复时间 分区目标优先级 - 高性能->数据维护能力->实施难度->高可用性（故障屏蔽能力） ③ 如何实施分区？ file:///C:\Users\ADMINI~1\AppData\Local\Temp\ksohtml\wps7B9E.tmp.png A . Range Partitioning（范围分区）< 32喎�"http://www.2cto.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="收藏代码"> 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); 全部数据如下： file:///C:\Users\ADMINI~1\AppData\Local\Temp\ksohtml\wpsCE11.tmp.png 不及格数据如下： file:///C:\Users\ADMINI~1\AppData\Local\Temp\ksohtml\wpsCE12.tmp.png 及格数据如下： file:///C:\Users\ADMINI~1\AppData\Local\Temp\ksohtml\wpsCE23.tmp.png 优秀数据如下： file:///C:\Users\ADMINI~1\AppData\Local\Temp\ksohtml\wpsCE24.tmp.png 【范围分区特点】： ① 最早、最经典的分区算法
② 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) 插入实验数据，与范围分区实验插入的数据相同。 2) 然后查询分区数据： Sql代码 select * from graderecord partition(p1); select * from graderecord partition(p2); select * from graderecord partition(p3); p1分区的数据： file:///C:\Users\ADMINI~1\AppData\Local\Temp\ksohtml\wpsCE25.tmp.png p2分区的数据： file:///C:\Users\ADMINI~1\AppData\Local\Temp\ksohtml\wpsCE35.tmp.png p3分区的数据： file:///C:\Users\ADMINI~1\AppData\Local\Temp\ksohtml\wpsCE36.tmp.png 【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分区所得数据如下： file:///C:\Users\ADMINI~1\AppData\Local\Temp\ksohtml\wpsCE37.tmp.png d228分区所得数据如下： file:///C:\Users\ADMINI~1\AppData\Local\Temp\ksohtml\wpsCE38.tmp.png d240分区所得数据如下： file:///C:\Users\ADMINI~1\AppData\Local\Temp\ksohtml\wpsCE39.tmp.png 【列表分区特点】： ?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分以下： file:///C:\Users\ADMINI~1\AppData\Local\Temp\ksohtml\wpsCE4A.tmp.png 分区p2数据如下，本例中75分之上包括75分： file:///C:\Users\ADMINI~1\AppData\Local\Temp\ksohtml\wpsCE4B.tmp.png 子分区sp1： file:///C:\Users\ADMINI~1\AppData\Local\Temp\ksohtml\wpsCE4C.tmp.png 子分区sp2： file:///C:\Users\ADMINI~1\AppData\Local\Temp\ksohtml\wpsCE4D.tmp.png 子分区sp3： file:///C:\Users\ADMINI~1\AppData\Local\Temp\ksohtml\wpsCE4E.tmp.png 子分区sp4： file:///C:\Users\ADMINI~1\AppData\Local\Temp\ksohtml\wpsCE5F.tmp.png 说明：当数据量越来越大时，哈希分区的分区表中数据越来越趋于平衡。 下面讲范围-列表分区 范围-列表分区有两种创立方式，先说说没有模板的创建方式，这个表我要重建： 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; file:///C:\Users\ADMINI~1\AppData\Local\Temp\ksohtml\wpsCE60.tmp.png 分区p1查询结果如下： file:///C:\Users\ADMINI~1\AppData\Local\Temp\ksohtml\wpsCE70.tmp.png 分区p2查询结果如下：file:///C:\Users\ADMINI~1\AppData\Local\Temp\ksohtml\wpsCE71.tmp.png 子分区xiaxun2查询结果如下： file:///C:\Users\ADMINI~1\AppData\Local\Temp\ksohtml\wpsCE72.tmp.png 备注： ① 说明：范围分区 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); 查询结果： file:///C:\Users\ADMINI~1\AppData\Local\Temp\ksohtml\wpsCE73.tmp.png 查询子分区p_0701_010_sub4的数据： Sql代码 select * from MobileMessage subpartition(p_0701_010_sub4); 查询结果如下： file:///C:\Users\ADMINI~1\AppData\Local\Temp\ksohtml\wpsCE84.tmp.png 查询分区p_0706_011的数据： Sql代码 select * from MobileMessage partition(p_0706_011); 查询结果如下： file:///C:\Users\ADMINI~1\AppData\Local\Temp\ksohtml\wpsCE85.tmp.png 查询子分区p_0706_011_sub21的数据： Sql代码 select * from MobileMessage subpartition(p_0706_011_sub21); 查询结果如下： file:///C:\Users\ADMINI~1\AppData\Local\Temp\ksohtml\wpsCE86.tmp.png 下面讲讲分区的维护操作： (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）分区索引
file:///C:\Users\ADMINI~1\AppData\Local\Temp\ksohtml\wpsCE87.tmp.png
file:///C:\Users\ADMINI~1\AppData\Local\Temp\ksohtml\wpsCE88.tmp.png
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 ）都可以进行分区消除,前提是查询的条件中包含索引分区键，它们都支持惟一性（只要局部非前缀索引包含分区键列)。
局部索引与表的分区数一致，如果新增一个分区，新增加的分区局部索引也会自动创建。全局索引则不行（即需要重建全局索引）。
-下面通过实验来说明索引的分区消除
③ Global Prefixed index ④ 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）分区选择的策略 file:///C:\Users\ADMINI~1\AppData\Local\Temp\ksohtml\wpsCE98.tmp.png 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）“滚动窗口”操作 - 大量数据高速装载 file:///C:\Users\ADMINI~1\AppData\Local\Temp\ksohtml\wpsCE99.tmp.pngfile:///C:\Users\ADMINI~1\AppData\Local\Temp\ksohtml\wpsCEAA.tmp.png 分区索引字典 DBA_PART_INDEXES 分区索引的概要统计信息，可以得知每个表上有哪些分区索引，分区索引的类新(local/global,)
Dba_ind_partitions每个分区索引的分区级统计信息
Dba_indexesminusdba_part_indexes，可以得到每个表上有哪些非分区索引 2.Oracle的分区的交换功能 1）交换功能分类 ① 通过交换数据段，实现分区和非分区表的数据交换。以及子分区和分区表的数据交换
② 非常快捷的数据移动方式。特别是没有validation和索引维护操作时
③ Local 索引自动维护
④ Global索引必须重建2) 分区交换的应用--- 全文检索 file:///C:\Users\ADMINI~1\AppData\Local\Temp\ksohtml\wpsCEAB.tmp.png 第一步：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) file:///C:\Users\ADMINI~1\AppData\Local\Temp\ksohtml\wpsCEAC.tmp.png 备注： 第一步：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, …）
数据高可用性
| 
发表于 2014-11-15 09:39:32
