书签分享收藏举报版权申诉 / 14

立即下载加入VIP,免费下载

当前位置：首页 > 初中教育 > 学科竞赛 > 分区表技术手册.docx

分区表技术手册.docx

文档编号：9685806
上传时间：2023-02-05
格式：DOCX
页数：14
大小：20.83KB

分区表技术手册.docx

《分区表技术手册.docx》由会员分享，可在线阅读，更多相关《分区表技术手册.docx（14页珍藏版）》请在冰豆网上搜索。

分区表技术手册.docx

分区表技术手册

公司内部

分区表技术手册

主题：

分区表技术手册

项目名称：

公司内部

撰写单位：

海鼎

作者：

林杨

版本号：

0.1使用说明（此内容将不被打印）：

请点击菜单“文件|属性…”修改有关文档属性；

请按下工具栏“更新文档信息”，或按下快捷键++，系统自动更新所有文档属性到文档中。

文档名（链接）

一、分区表理论知识

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分区中。

如按照时间划分，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分区也需要指定列的值，其分区值必须明确指定，该分区列只能有一个，不能像range或者hash分区那样同时指定多个列做为分区依赖列，但它的单个分区对应值可以是多个。

　　在分区时必须确定分区列可能存在的值，一旦插入的列值不在分区范围内，则插入/更新就会失败，因此通常建议使用list分区时，要创建一个default分区存储那些不在指定范围内的记录，类似range分区中的maxvalue分区。

在根据某字段，如城市代码分区时，可以指定default，把非分区规则的数据，全部放到这个default分区。

createtablecustaddr

idvarchar2（15）notnull,

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：

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））;

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'）;

默认是按主键的方式进行重定义，如果没有主键，则需要设定参数为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'）;

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，例：

分区的范围条件为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）

vmsgvarchar2（1000）;

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权限

=add_months（last_day（trunc（vdate））+1,2）;

selectmax（to_date（substr（partition_name,2）,'yyyymm'））

fromdba_tab_partitions

wheretable_name=R.TABLE_NAME;

ifvnext<=vmaxthen

（time,oper,modulename,type,content）

（sysdate,'添加分区','添加'||R.TABLE_NAME||'分区',101,'未到添加时间，不需添加。

=to_char（vnext,'yyyymm'）;

IFR.partitioning_type='RANGE'THEN

='altertable'||R.TABLE_NAME||'addpartitionp'||vnum||'valueslessthan

（to_date（'''||VNum||''',''yyyymm''））tablespace'||R.TABLESPACE_NAME||'';

ELSIFR.partitioning_type='LIST'THEN

='altertable'||R.TABLE_NAME||'addpartitionp'||vnum||'values

（'''||VNum||'''）tablespace'||R.TABLESPACE_NAME||'';

executeimmediatevcmd;

（time,oper,modulename,type,content）

（sysdate,'添加分区','添加分区',101,'添加'||R.TABLE_NAME||'分区结束'）;

='添加'||R.TABLE_NAME||'分区出错，错误号:

'||SQLCODE||'错误信息：

（time,oper,modulename,type,content）

（sysdate,'添加'||R.TABLE_NAME||'分区','添加分区出错',304,substrb（vmsg,1,254））;

四、分区表的其他操作

添加新的分区有2中情况：

（1）原分区里边界是maxvalue或者default。

这种情况下，我们需要把边界分区drop掉，加上新分区后，在添加上新的分区。

或者采用split，对边界分区进行拆分。

（2）没有边界分区的。

这种情况下，直接添加分区就可以了。

给SALES表添加一个P200306分区:

ALTERTABLESALESADDPARTITIONP200306VALUESLESSTHAN（TO_DATE（'2003-06-01','YYYY-MM-DD'））;

ALTERTABLESALESDROPPARTITIONP200306;

截断某个分区是指删除某个分区中的数据，并不会删除分区，也不会删除其它分区中的数据。

ALTERTABLESALESTRUNCATEPARTITIONP200306;

相邻的分区可以merge为一个分区，新分区的下边界为原来边界值较低的分区，上边界为原来边界值较高的分区：

ALTERTABLESALESMERGEPARTITIONSP200305,P200306INTOPARTITIONP200306;

拆分分区将一个分区拆分两个新分区，拆分后原来分区不再存在。

注意不能对HASH类型的分区进行拆分。

ALTERTABLESALESSBLITPARTITIONP200306AT（TO_DATE（'2003-05-15','YYYY-MM-DD'））INTO（PARTITIONP20030601,PARTITIONP20030602）;

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

版权申诉

版权申诉 word格式文档无特别注明外均可编辑修改；预览文档经过压缩，下载后原文更清晰！ 立即下载

配套讲稿：: 如PPT文件的首页显示word图标，表示该PPT已包含配套word讲稿。双击word图标可打开word文档。
特殊限制：: 部分文档作品中含有的国旗、国徽等图片，仅作为作品整体效果示例展示，禁止商用。设计者仅对作品中独创性部分享有著作权。
关键词：: 分区表技术手册

冰豆网所有资源均是用户自行上传分享，仅供网友学习交流，未经上传用户书面授权，请勿作他用。

关于本文

本文标题：分区表技术手册.docx
链接地址：https://www.bdocx.com/doc/9685806.html

相关资源更多

相关搜索

分区表 技术手册

关于我们 - 网站声明 - 网站地图 - 资源地图 - 友情链接 - 网站客服 - 联系我们

copyright@ 2008-2022 冰点文档网站版权所有

经营许可证编号:鄂ICP备2022015515号-1

收起

展开