分区表技术手册.docx
《分区表技术手册.docx》由会员分享，可在线阅读，更多相关《分区表技术手册.docx（14页珍藏版）》请在冰豆网上搜索。
分区表技术手册
公司内部
分区表技术手册
主题：
分区表技术手册
项目名称：
公司内部
撰写单位：
海鼎
作者：
林杨
版本号：
0.1使用说明（此内容将不被打印）：
请点击菜单“文件|属性…”修改有关文档属性；
请按下工具栏“更新文档信息”，或按下快捷键++，系统自动更新所有文档属性到文档中。
日期：
2011-08-24
审核
功能
姓名
部门
签名
日期
文档历史
版本
修改原因
修改人
基于版本
日期
0.1
创建
林杨
2011-08-24
参考文档
编号
文档名（链接）
概述
一、分区表理论知识
Oracle的分区表可以包括多个分区，每个分区都是一个独立的段（SEGMENT），可以存放到不同的表空间中。
查询时可以通过查询表来访问各个分区中的数据，也可以通过在查询时直接指定分区的方法来进行查询。
在oracle10g中最多支持：
1024k-1个分区。
分区提供以下优点：
（1）由于将数据分散到各个分区中，减少了数据损坏的可能性；
（2）可以对单独的分区进行备份和恢复；
（3）可以将分区映射到不同的物理磁盘上，来分散IO；
（4）提高可管理性、可用性和性能。
Oracle10g提供了以下几种分区类型：
（1）范围分区（range）；
（2）哈希分区（hash）；
（3）列表分区（list）；
（4）范围－哈希复合分区（range-hash）；
（5）范围－列表复合分区（range-list）。
这里主要介绍range分区和list分区：
Range分区：
以列的值的范围来做为分区的划分条件，将记录存放到列值所在的range分区中。
如按照时间划分，2010年1月的数据放到a分区，2月的数据放到b分区，在创建的时候，需要指定基于的列，以及分区的范围值。
在按时间分区时，如果某些记录暂无法预测范围，可以创建maxvalue分区（不是必须创建的），所有不在指定范围内的记录都会被存储到maxvalue所在分区中。
同时可以为每个分区指定表空间（不指定则为当前用户的默认表空间），各分区的表空间可以相同也可以不同。
如：
createtablepdba（idnumber,timedate）partitionbyrange（time）
（
partitionp201010valueslessthan（to_date（'2010-10-1','yyyy-mm-dd'））tablespacetablespace01,
partitionp201011valueslessthan（to_date（'2010-11-1','yyyy-mm-dd'））tablespacetablespace01,
partitionp201012valueslessthan（to_date（'2010-12-1','yyyy-mm-dd'））tablespacetablespace02,
partitionpothervalueslessthan（maxvalue）tablespacetablespace03
）
List分区：
List分区也需要指定列的值，其分区值必须明确指定，该分区列只能有一个，不能像range或者hash分区那样同时指定多个列做为分区依赖列，但它的单个分区对应值可以是多个。
在分区时必须确定分区列可能存在的值，一旦插入的列值不在分区范围内，则插入/更新就会失败，因此通常建议使用list分区时，要创建一个default分区存储那些不在指定范围内的记录，类似range分区中的maxvalue分区。
在根据某字段，如城市代码分区时，可以指定default，把非分区规则的数据，全部放到这个default分区。
如：
createtablecustaddr
（
idvarchar2（15）notnull,
SETTLENONUMBER
）
partitionbylist（SETTLENO）
（partitionP201010values（'201010'）,
partitionP201011values（'201011'）,
partitionP201012values（'201012'）,
partitionp_othervalues（default）
）
2、普通表转分区表方法
将普通表转换成分区表有4种方法：
1.Export/importmethod
导入导出方法
2.Insertwithasubquerymethod
插入法
3.Partitionexchangemethod
交换分区法
4.DBMS_REDEFINITION
在线重定义
由于我们需要改造的表都是数据量非常大的，且为了便于操作和将业务影响减小到最低，所以建议采用导入导出法或在线重定义。
下面重点介绍这两种方法：
Export/importmethod：
1）导出表:
expuser/password@servertables=tablenamefile=exp.dmp
2）删除原表:
droptabletablename;
3）重建分区表:
CREATETABLEtablename（
idNUMBER（10）,
create_dateDATE,
nameVARCHAR2（100）
）
PARTITIONBYRANGE（create_date）
（PARTITIONp200501VALUESLESSTHAN（TO_DATE（'01/01/2005','DD/MM/YYYY'））,
PARTITIONp200502VALUESLESSTHAN（TO_DATE（'01/02/2005','DD/MM/YYYY'））,
PARTITIONpotherVALUESLESSTHAN（MAXVALUE））;
4）导入表:
impuser/password@serverfile=exp.dmpignore=y
（a.如果导入的用户相同，可以将原表先RENAMETO另一个表名，将分区表建好后导入，核查无误后再将原表删除；
b.如果导入的用户不同，则可先不删除原表，直接导入另一用户下：
impuser/password@servertables=tablenamefile=exp.dmpfromuser=hd40touser=hdreportignore=y
这样可以与原表数据核查无误后，再将原表删除，将新建表赋权给hd40用户。
）
5）最后检查下数据是否一致，检查约束和索引，如没有则需重建。
检查索引状态：
selectindex_name,table_name,statusfromuser_indexes;
DBMS_REDEFINITION：
ORACLE9i开始提供了在线重定义的功能，在线重定义可以保证数据的一致性，在大部分时间内，表都可以正常进行DML操作。
只在切换的瞬间锁表，具有很高的可用性。
这种方法具有很强的灵活性，对各种不同的需要都能满足。
而且，可以在切换前进行相应的授权并建立各种约束，可以做到切换完成后不再需要任何额外的管理操作。
在线重定义需要注意的几个限制条件：
必须准备两倍表大小的空间；
必须在同一用户下操作，且不能用sys用户。
在线重定义的大致操作流程如下：
（1）创建基础表A，如果存在，就不需要操作。
（2）创建临时的分区表B。
（3）开始重定义，将基表A的数据导入临时分区表B。
（4）结束重定义，此时在数据库的数据字典里，已经将2个表进行了交换。
即此时基表A成了分区表，我们创建的临时分区表B成了普通表。
此时我们可以删除我们创建的临时表B。
它已经是普通表。
示例：
1.我们要改造的基本表：
CREATETABLEunpar_table（
idNUMBER（10）,
create_dateDATE,
nameVARCHAR2（100）
）;
ALTERTABLEunpar_tableADD（
CONSTRAINTunpar_table_pkPRIMARYKEY（id）
）;
CREATEINDEXcreate_date_indONunpar_table（create_date）;
2.收集表的统计信息
EXECDBMS_STATS.gather_table_stats（USER,'unpar_table',cascade=>TRUE）;
3.创建临时分区表
CREATETABLEpar_table（
idNUMBER（10）,
create_dateDATE,
nameVARCHAR2（100）
）
PARTITIONBYRANGE（create_date）
（PARTITIONp200501VALUESLESSTHAN（TO_DATE（'01/01/2005','DD/MM/YYYY'））,
PARTITIONp200502VALUESLESSTHAN（TO_DATE（'01/02/2005','DD/MM/YYYY'））,
PARTITIONpotherVALUESLESSTHAN（MAXVALUE））;
4.进行重定义操作
4.1检查重定义的合理性
EXECDbms_Redefinition.can_redef_table（USER,'unpar_table'）;
（
PS:
默认是按主键的方式进行重定义，如果没有主键，则需要设定参数为2，按照rowid重定义
EXECDbms_Redefinition.can_redef_table（USER,'unpar_table',2）;
）
4.2如果4.1没有问题，开始重定义，这个过程可能需要比较长的时间。
EXECDBMS_REDEFINITION.start_redef_table（USER,'unpar_table','par_table'）;
（
ROWID方式：
EXECDBMS_REDEFINITION.start_redef_table（USER,'unpar_table','par_table',null,2）;
）
这一步操作结束后，数据就已经同步到这个临时的分区表里来了。
4.3同步新表，这是可选的操作
Execdbms_redefinition.sync_interim_table（USER,'unpar_table','par_table'）;
4.4创建主键和索引。
ALTERTABLEpar_tableADD（
CONSTRAINTunpar_table_pk2PRIMARYKEY（id）
）;
CREATEINDEXcreate_date_ind2ONpar_table（create_date）;
4.5收集新表的统计信息
EXECDBMS_STATS.gather_table_stats（USER,'par_table',cascade=>TRUE）;
4.6结束重定义
Execdbms_redefinition.finish_redef_table（USER,'unpar_table','par_table'）;
此时基表unpar_table和临时分区表par_table已经进行了交换。
此时临时分区表par_table成了普通表，我们的基表unpar_table成了分区表。
我们在重定义的时候，基表unpar_table是可以进行DML操作的。
只有在2个表进行切换的时候会有短暂的锁表。
5.删除临时表
DROPTABLEpar_table;
6.主键和索引重命名
ALTERTABLEunpar_tableRENAMECONSTRAINTunpar_table_pk2TOunpar_table_pk;
ALTERINDEXcreate_date_ind2RENAMETOcreate_date_ind;
三、创建存储过程自动新建分区
为了不每次都手工去增加分区，所以可以将新建分区的动作写进存储过程，通过JOB每月自动调用。
（此过程仅指定但不维护表空间，需人工定期维护表空间避免表空间不足）
标准示例：
分区的命名规则为pyyyymm，例：
p201108。
分区的范围条件为yyyymm01，例：
valueslessthan（TO_DATE（'2011-08-1','YYYY-MM-DD'））
即：
p201108存放2011-07的数据
分区的列表条件为yyyymm，例：
values（'201108'）
每次提前两个月创建新分区和新数据文件，
即：
2011-05创建p201108，里面存放2011-07的数据
createorreplaceprocedureAUTO_ADD_PARTITION（pidatedate）
IS
vmsgvarchar2（1000）;
vdatedate;
vmaxdate;--目前最大分区日期
vnextdate;--要创建的分区日期
vnumvarchar2（10）;--日期转分区号
vcmdvarchar2（500）;
CURSORCISselectDISTINCTtab.TABLE_NAME,tab.TABLESPACE_NAME,part.partitioning_type
fromdba_tab_partitionstab,DBA_PART_TABLESpart
wheretab.table_name=part.table_name
andtab.table_owner=part.owner
andtable_owner<>'SYS'
andtable_owner<>'SYSTEM';
--此处要注意当前用户是否有表dba_tab_partitions和DBA_PART_TABLES的select权限
begin
vdate:
=pidate;
vnext:
=add_months（last_day（trunc（vdate））+1,2）;
FORRINCLOOP
begin
selectmax（to_date（substr（partition_name,2）,'yyyymm'））
intovmax
fromdba_tab_partitions
wheretable_name=R.TABLE_NAME;
ifvnext<=vmaxthen
insertintolog
（time,oper,modulename,type,content）
values
（sysdate,'添加分区','添加'||R.TABLE_NAME||'分区',101,'未到添加时间，不需添加。
'）;
commit;
else
vnum:
=to_char（vnext,'yyyymm'）;
IFR.partitioning_type='RANGE'THEN
vcmd:
='altertable'||R.TABLE_NAME||'addpartitionp'||vnum||'valueslessthan
（to_date（'''||VNum||''',''yyyymm''））tablespace'||R.TABLESPACE_NAME||'';
ELSIFR.partitioning_type='LIST'THEN
vcmd:
='altertable'||R.TABLE_NAME||'addpartitionp'||vnum||'values
（'''||VNum||'''）tablespace'||R.TABLESPACE_NAME||'';
ENDIF;
executeimmediatevcmd;
insertintolog
（time,oper,modulename,type,content）
values
（sysdate,'添加分区','添加分区',101,'添加'||R.TABLE_NAME||'分区结束'）;
commit;
endif;
exception
whenothersthen
begin
rollback;
vmsg:
='添加'||R.TABLE_NAME||'分区出错，错误号:
'||SQLCODE||'错误信息：
'||SQLERRM;
insertintolog
（time,oper,modulename,type,content）
values
（sysdate,'添加'||R.TABLE_NAME||'分区','添加分区出错',304,substrb（vmsg,1,254））;
commit;
end;
end;
ENDLOOP;
end;
四、分区表的其他操作
1.添加分区
添加新的分区有2中情况：
（1）原分区里边界是maxvalue或者default。
这种情况下，我们需要把边界分区drop掉，加上新分区后，在添加上新的分区。
或者采用split，对边界分区进行拆分。
（2）没有边界分区的。
这种情况下，直接添加分区就可以了。
给SALES表添加一个P200306分区:
ALTERTABLESALESADDPARTITIONP200306VALUESLESSTHAN（TO_DATE（'2003-06-01','YYYY-MM-DD'））;
2.删除分区
ALTERTABLESALESDROPPARTITIONP200306;
3.截断分区
截断某个分区是指删除某个分区中的数据，并不会删除分区，也不会删除其它分区中的数据。
ALTERTABLESALESTRUNCATEPARTITIONP200306;
4.合并分区
相邻的分区可以merge为一个分区，新分区的下边界为原来边界值较低的分区，上边界为原来边界值较高的分区：
ALTERTABLESALESMERGEPARTITIONSP200305,P200306INTOPARTITIONP200306;
5.拆分分区
拆分分区将一个分区拆分两个新分区，拆分后原来分区不再存在。
注意不能对HASH类型的分区进行拆分。
ALTERTABLESALESSBLITPARTITIONP200306AT（TO_DATE（'2003-05-15','YYYY-MM-DD'））INTO（PARTITIONP20030601,PARTITIONP20030602）;
6.移动分区
altertabletablenamemovepartitionP200306tablespacetablespacename;
7.重命名表分区
ALTERTABLESALESRENAMEPARTITIONP200206TOP200306;
注意，在对分区做了除重命名之外的动作后，都需要检查索引状态是否有效，如果失效则需要重建。
检查索引状态：
selectindex_name,table_name,statusfromuser_indexes;
五、分区表的相关查询
如果不指定分区查询，oracle也会自动根据查询条件进行分区筛选。
指定分区查询
Select*fromtablepartition（P201108）;
显示所有分区表的信息：
select*fromDBA_PART_TABLES
显示当前用户可访问的所有分区表信息:
select*fromALL_PART_TABLES
显示当前用户所有分区表的信息：
select*fromUSER_PART_TABLES
显示表分区信息显示数据库所有分区表的详细分区信息：
select*fromDBA_TAB_PARTITIONS
显示当前用户可访问的所有分区表的详细分区信息：
select*fromALL_TAB_PARTITIONS
显示当前用户所有分区表的详细分区信息：
select*fromUSER_TAB_PARTITIONS
升级会员 