普通表转分区表.doc

普通表转分区表普通表转分区表方法将普通表转换成分区表有4种方法：1. Export/import method2. Insert with a subquery method3. Partition exchange method4. DBMS_REDEFINITION具体参考：HowtoPartitionaNon-partitionedTableID1070693.6/tianlesoftware/archive/2011/03/02/6218704.aspx逻辑导出导入这里就不做说明，我们看看其他三种方法。2.1 插入： Insert with a subquery method这种方法就是使用insert 来实现。 当然在创建分区表的时候可以一起插入数据，也可以创建好后在insert 进去。 这种方法采用DDL语句，不产生UNDO，只产生少量REDO，建表完成后数据已经在分布到各个分区中。SQLselectcount(*)fromdba;COUNT(*)-2713235SQLaltersessionsetnls_date_format=yyyy-mm-ddhh24:mi:ss;会话已更改。SQLselecttime_feefromdbawhererownum2.1.1 Oracle 11g的Interval在11g里的Interval创建，这种方法对没有写全的分区会自动创建。 比如我这里只写了1月日期，如果插入的数据有其他月份的，会自动生成对应的分区。/*Formattedon2011/03/0215:41:09(QP5v5.115.810.9015)*/CREATETABLEintervaldavePARTITIONBYRANGE(time_fee)INTERVAL(NUMTOYMINTERVAL(1,MONTH)(PARTITIONpart1VALUESLESSTHAN(TO_DATE(01/12/2010,MM/DD/YYYY)ASSELECTID,TIME_FEEFROMDAVE;SQLselecttable_name,partition_namefromuser_tab_partitionswheretable_name=INTERVALDAVE;TABLE_NAMEPARTITION_NAME-INTERVALDAVEPART1INTERVALDAVESYS_P24INTERVALDAVESYS_P25INTERVALDAVESYS_P26INTERVALDAVESYS_P33INTERVALDAVESYS_P27INTERVALDAVESYS_P282.1.2 Oracle 10g 版本在10g里面，我需要写全所有的分区。sqlcreatetablepdba(id,time)partitionbyrange(time)2(partitionp1valueslessthan(to_date(2010-10-1,yyyy-mm-dd),3partitionp2valueslessthan(to_date(2010-11-1,yyyy-mm-dd),4partitionp3valueslessthan(to_date(2010-12-1,yyyy-mm-dd),5partitionp4valueslessthan(maxvalue)6asselectid,time_feefromdba;表已创建。SQLselecttable_name,partition_namefromuser_tab_partitionswheretable_name=PDBA;TABLE_NAMEPARTITION_NAME-PDBAP1PDBAP2PDBAP3PDBAP4sqlselectcount(*)frompdbapartition(p1);count(*)-1718285sqlselectcount(*)frompdbapartition(p2);count(*)-183667sqlselectcount(*)frompdbapartition(p3);count(*)-188701sqlselectcount(*)frompdbapartition(p4);count(*)-622582sql现在分区表已经建好了，但是表名不一样，需要用rename对表重命名一下：SQLrenamedbatodba_old;表已重命名。SQLrenamepdbatodba;表已重命名。SQLselecttable_name,partition_namefromuser_tab_partitionswheretable_name=DBA;TABLE_NAMEPARTITION_NAME-DBAP1DBAP2DBAP3DBAP42.2 . 交换分区：Partition exchange method这种方法只是对数据字典中分区和表的定义进行了修改，没有数据的修改或复制，效率最高。适用于包含大数据量的表转到分区表中的一个分区的操作。尽量在闲时进行操作。交换分区的操作步骤如下：1. 创建分区表，假设有2个分区，P1，P2.2. 创建表A存放P1规则的数据。3. 创建表B 存放P2规则的数据。4. 用表A 和P1 分区交换。 把表A的数据放到到P1分区5. 用表B 和p2 分区交换。 把表B的数据存放到P2分区。创建分区表：sqlcreatetablep_dba2(idnumber,timedate)3partitionbyrange(time)4(5partitionp1valueslessthan(to_date(2010-09-1,yyyy-mm-dd),6partitionp2valueslessthan(to_date(2010-11-1,yyyy-mm-dd)7);表已创建。注意：我这里只创建了2个分区，没有创建存放其他数据的分区。创建2个分别对应分区的基表：SQLCREATETABLEdba_p1asSELECTid,time_feeFROMdba_oldWHEREtime_feeCREATETABLEdba_p2asSELECTid,time_feeFROMdba_oldWHEREtime_feeTO_DATE(2010-09-1,YYYY-MM-DD);表已创建。SQLselectcount(*)fromdba_p1;COUNT(*)-1536020SQLselectcount(*)fromdba_p2;COUNT(*)-365932SQL讲2个基表与2个分区进行交换：SQLaltertablep_dbaexchangepartitionp1withtabledba_p1;表已更改。SQLaltertablep_dbaexchangepartitionp2withtabledba_p2;表已更改。查询2个分区：SQLselectcount(*)fromp_dbapartition(p1);COUNT(*)-1536020SQLselectcount(*)fromp_dbapartition(p2);COUNT(*)-365932注意：数据和之前的基表一致。查询原来的2个基表：SQLselectcount(*)fromdba_p2;COUNT(*)-0SQLselectcount(*)fromdba_p1;COUNT(*)-0注意： 2个基表的数据变成成0。在这里我们看一个问题，一般情况下，我们在创建分区表的时候，都会有一个其他分区，用来存放不匹配分区规则的数据。 在这个例子中，我只创建了2个分区，没有创建maxvalue分区。 现在我来插入一条不满足规则的数据，看结果：SQLinsertintop_dbavalues(999999,to_date(2012-12-29,yyyy-mm-dd);insertintop_dbavalues(999999,to_date(2012-12-29,yyyy-mm-dd)*第 1 行出现错误:ORA-14400: 插入的分区关键字未映射到任何分区SQLinsertintop_dbavalues(999999,to_date(2009-12-29,yyyy-mm-dd);已创建 1 行。SQLselect*fromp_dbawhereid=999999;IDTIME-999999 29-12月-09SQLaltersessionsetnls_date_format=yyyy-mm-ddhh24:mi:ss;会话已更改。SQLselect*fromp_dbawhereid=999999;IDTIME-9999992009-12-2900:00:00SQL通过这个测试可以清楚，如果插入的数据不满足分区规则，会报ORA-14400错误。2.3 . 使用在线重定义：DBMS_REDEFINITION在线重定义能保证数据的一致性，在大部分时间内，表都可以正常进行DML操作。只在切换的瞬间锁表，具有很高的可用性。这种方法具有很强的灵活性，对各种不同的需要都能满足。而且，可以在切换前进行相应的授权并建立各种约束，可以做到切换完成后不再需要任何额外的管理操作。关于DBMS_REDEFINITION的介绍，参考官方连接：/docs/cd/B19306_01/appdev.102/b14258/d_redefi.htm#CBBFDJBC关于用在线重定义创建分区表，参考：HowToPartitionExistingTableUsingDBMS_RedefinitionID472449.1/tianlesoftware/archive/2011/03/02/6218693.aspx这个功能只在以后的版本才有，在线重定义表具有以下功能：（1）修改表的存储参数；（2）将表转移到其他表空间；（3）增加并行查询选项；（4）增加或删除分区；（5）重建表以减少碎片；（6）将堆表改为索引组织表或相反的操作；（7）增加或删除一个列。使用在线重定义的一些限制条件：（1） There must be enough space to hold two copies of the table.（2） Primary key columns cannot be modified.（3） Tables must have primary keys.（4） Redefinition must be done within the same schema.（5） New columns added cannot be made NOT NULL until after the redefinition operation.（6） Tables cannot contain LONGs, BFILEs or User Defined Types.（7） Clustered tables cannot be redefined.（8） Tables in the SYS or SYSTEM schema cannot be redefined.（9） Tables with materialized view logs or materialized views defined on them cannot be redefined.（10） Horizontal sub setting of data cannot be performed during the redefinition.在Oracle 和 版本下，在线重定义可能会遇到如下bug：Bug7007594-ORA-60012261/tianlesoftware/archive/2011/03/02/6218681.aspx在线重定义的大致操作流程如下：（1）创建基础表A，如果存在，就不需要操作。（2）创建临时的分区表B。（3）开始重定义，将基表A的数据导入临时分区表B。（4）结束重定义，此时在DB的 Name Directory里，已经将2个表进行了交换。即此时基表A成了分区表，我们创建的临时分区表B 成了普通表。此时我们可以删除我们创建的临时表B。它已经是普通表。下面看一个示例：1. 创建基本表和索引sqlconnicd/icd;已连接。sqlcreatetableunpar_table(2idnumber(10)primarykey,3create_datedate4);表已创建。sqlinsertintounpar_tableselectrownum,createdfromdba_objects;已创建72288行。sqlcreateindexcreate_date_indonunpar_table(create_date);索引已创建。sqlcommit;提交完成。2. 收集表的统计信息sqlexecdbms_stats.gather_table_stats(icd,unpar_table,cascade=true);pl/sql 过程已成功完成。3. 创建临时分区表sqlcreatetablepar_table(idnumberprimarykey,timedate)partitionbyrange(time)2(partitionp1valueslessthan(to_date(2004-7-1,yyyy-mm-dd),3partitionp2valueslessthan(to_date(2005-1-1,yyyy-mm-dd),4partitionp3valueslessthan(to_date(2005-7-1,yyyy-mm-dd),5partitionp4valueslessthan(maxvalue);表已创建。4. 进行重定义操作4.1 检查重定义的合理性sqlexecdbms_redefinition.can_redef_table(icd,unpar_table);pl/sql 过程已成功完成。4.2 如4.1 没有问题，开始重定义，这个过程可能要等一会。这里要注意：如果分区表和原表列名相同，可以用如下方式进行：SQLBEGINDBMS_REDEFINITION.start_redef_table(uname=ICD,orig_table=unpar_table,int_table=par_table);END;/如果分区表的列名和原表不一致，那么在开始重定义的时候，需要重新指定映射关系：SQLEXECDBMS_REDEFINITION.START_REDEF_TABLE(ICD,unpar_table,par_table,IDID,create_dateTIME,- 在这里指定新的映射关系DBMS_REDEFINITION.CONS_USE_PK);这一步操作结束后，数据就已经同步到这个临时的分区表里来了。4.3 同步新表，这是可选的操作SQLBEGIN2dbms_redefinition.sync_interim_table(3uname=ICD,4orig_table=unpar_table,5int_table=par_table);6END;7/PL/SQL 过程已成功完成。4.4 创建索引，在线重定义只重定义数据，索引还需要单独建立。sqlcreateindexcreate_date_ind2onpar_table(time);索引已创建。4.5 收集新表的统计信息sqlexecdbms_stats.gather_table_stats(icd,par_table,cascade=true);pl/sql 过程已成功完成。4.6 结束重定义SQLBEGIN2dbms_redefinition.finish_redef_table(3uname=ICD,4orig_table=unpar_table,5int_table=par_table);6END;7/PL/SQL 过程已成功完成。结束重定义的意义：基表unpar_table 和临时分区表par_table 进行了交换。 此时临时分区表par_table成了普通表，我们的基表unpar_table成了分区表。我们在重定义的时候，基表unpar_table是可以进行DML操作的。 只有在2个表进行切换的时候会有短暂的锁表。5. 删除临时表SQLDROPTABLEpar_table;表已删除。6. 索引重命名SQLALTERINDEXcreate_date_ind2RENAMETOcreate_date_ind;索引已更改。7. 验证sqlselectpartitionedfromuser_tableswheretable_name=UNPAR_TABLE;par-yessqlselectpartition_namefromuser_tab_partitionswheretable_name=UNPAR_TABLE;partition_name-p1p2p3p4sqlselectcount(*)fromunpar_table;count(*)-72288sqlselectcount(*)fromunpar_tablepartition(p4);count(*)-72288sql三. 分区表的其他操作3.1 添加新的分区添加新的分区有2中情况：（1）原分区里边界是maxvalue或者default。 这种情况下，我们需要把边界分区drop掉，加上新分区后，在添加上新的分区。 或者采用split，对边界分区进行拆分。（2）没有边界分区的。 这种情况下，直接添加分区就可以了。以边界分区添加新分区示例：（1）分区表和索引的信息如下：SQLcreatetablecustaddr2(3idvarchar2(15byte)notnull,4areacodevarchar2(4byte)5)6partitionbylist(areacode)7(8partitiont_list556values(556)tablespaceicd_service,9partitionp_othervalues(default)tablespaceicd_service10);表已创建。SQLcreateindexix_custaddr_idoncustaddr(id)2local(3partitiont_list556tablespaceicd_service,4partitionp_othertablespaceicd_service5);索引已创建。（2）插入几条测试数据：SQLinsertintocustaddrvalues(1,556);已创建 1 行。SQLinsertintocustaddrvalues(2,551);已创建 1 行。SQLinsertintocustaddrvalues(3,555);已创建 1 行。SQLcommit;提交完成。SQLselect*fromcustaddr;IDAREA-155625513555SQLselect*fromcustaddrpartition(t_list556);IDAREA-1556SQL（3）删除default分区sqlaltertablecustaddrdroppartitionp_other;表已更改。sqlselecttable_name,partition_namefromuser_tab_partitionswheretable_name=CUSTADDR;table_namepartition_name-custaddrt_list556（4）添加新分区SQLaltertablecustaddraddpartitiont_list551values(551)tablespaceicd_service;表已更改。SQLselecttable_name,partition_namefromuser_tab_partitionswheretable_name=CUSTADDR;TABLE_NAMEPARTITION_NAME-CUSTADDRT_LIST556CUSTADDRT_LIST551（5）添加default 分区SQLaltertablecustaddraddpartitionp_othervalues(default)tablespaceicd_service;表已更改。SQLselecttable_name,partition_namefromuser_tab_partitionswheretable_name=CUSTADDR;TABLE_NAMEPARTITION_NAME-CUSTADDRT_LIST556CUSTADDRT_LIST551CUSTADDRP_OTHER（6）对于局部索引，oracle会自动增加一个局部分区索引。验证一下：sqlselectowner,index_name,table_name,partitioning_typefromdba_part_indexeswhereindex_name=ix_custaddr_id;ownerindex_nametable_name-icdix_custaddr_idcustaddrsqlselectindex_owner,index_name,partition_namefromdba_ind_partitionswhereindex_name=ix_custaddr_id;index_ownerindex_namepartition_name-icdix_custaddr_idp_othericdix_custaddr_idt_list551icdix_custaddr_idt_list556分区索引自动创建了。3.2 split 分区拆分在3.1 中，我们说明了可以使用split的方式来添加分区。 这里我们用split方法继续上面的实验。sqlaltertablecustaddrsplitpartitionp_othervalues(552)into(partitiont_list552tablespaceicd_service,partitionp_othertablespaceicd_service);表已更改。-注意这里红色的地方，如果是Range类型的，使用at，List使用Values。SQLselecttable_name,partition_namefromuser_tab_partitionswheretable_name=CUSTADDR;TABLE_NAMEPARTITION_NAME-CUSTADDRT_LIST556CUSTADDRT_LIST551CUSTADDRT_LIST552CUSTADDRP_OTHERSQLselectindex_owner,index_name,partition_namefromdba_ind_partitionswhereindex_name=IX_CUSTADDR_ID;index_ownerindex_namepartition_name-icdix_custaddr_idp_othericdix_custaddr_idt_list551icdix_custaddr_idt_list552icdix_custaddr_idt_list556注意：分区表会自动维护局部分区索引。全局索引会失效，需要进行rebuild。3.3 合并分区Merge相邻的分区可以merge为一个分区，新分区的下边界为原来边界值较低的分区，上边界为原来边界值较高的分区，原先的局部索引相应也会合并，全局索引会失效，需要rebuild。SQLaltertablecustaddrmergepartitionst_list552,p_otherintopartitionp_other;表已更改。SQLselectindex_owner,index_name,partition_namefromdba_ind_partitionswhereindex_name=IX_CUSTADDR_ID;index_ownerindex_namepartition_name-icdix_custaddr_idp_othericdix_custaddr_idt_list551icdix_custaddr_idt_list556SQLselecttable_name,partition_namefromuser_tab_partitionswheretable_name=CUSTADDR;table_namepartition_name-custaddrt_list556custaddrt_list551custaddrp_other3.4 . 移动分区SQLaltertablecustaddrmovepartitionP_OTHERtablespacesystem;表已更改。SQLaltertablecustaddrmovepartitionP_OTHERtablespaceicd_service;表已更改。注意：分区移动会自动维护局部分区索引，oracle不会自动维护全局索引，所以需要我们重新rebuild分区索引，具体需要rebuild哪些索引，可以通过dba_part_indexes,dba_ind_partitions去判断。SQLSelectindex_name,statusFromuser_indexesWheretable_name=CUSTADDR;INDEX_NAMESTATUS-IX_CUSTADDR_IDN/A3.5. Truncate分区SQLselect*fromcustaddrpartition(T_LIST556);IDAREA-1556SQLaltertablecustaddrtruncatepartition(T_LIST556);表被截断。SQLselect*fromcustaddrpartition(T_LIST556);未选定行说明：Truncate相对delete操作很快，数据仓库中的大量数据的批量数据加载可能会有用到；截断分区同样会自动维护局部分区索引，同时会使全局索引unusable,需要重建3.6. Drop分区SQLaltertablecustaddrdroppartitionT_LIST551;表已更改。SQLselecttable_name,partition_namefromuser_tab_partitionswheretable_name=CUSTADDR;TABLE_NAMEPARTITION_NAME-CUSTADDRT_LIST556CUSTADDRP_OTHER同样会自动维护局部分区索引，同时会使全局索引unusable,需要重建四. 分区表的索引分区索引分为本地(local index)索引和全局索引(global index)。局部索引比全局索引容易管理, 而全局索引比较快。与索引有关的表：dba_part_indexes 分区索引的概要统计信息，可以得知每个表上有哪些分区索引，分区索引的类型(local/global)dba_ind_partitions 每个分区索引的分区级统计信息dba_indexes/dba_part_indexes 可以得到每个表上有哪些非分区索引Local索引肯定是分区索引，Global索引可以选择是否分区，如果分区，只能是有前缀的分区索引。分区索引分2类：有前缀(prefix)的分区索引和无前缀(nonprefix)的分区索引:（1）有前缀的分区索引指包含了分区键，并且将其作为引导列的索引。如：createindexi_id_globalonPDBA(id)global-引导列2partitionbyrange(id)-分区键3(partitionp1valueslessthan(200),4partitionp2valueslessthan(maxvalue)5);这里的ID 就是分区键，并且分区键id 也是索引的引导列。（2）无前缀的分区索引的列不是以分区键开头，或者不包含分区键列。如：createindexix_custaddr_local_id_poncustaddr(id)local(partitiont_list556tablespaceicd_service,partitionp_othertablespaceicd_service)这个分区是按照areacode来的。但是索引的引导列是ID。 所以它就是非前缀分区索引。全局分区索引不支持非前缀的分区索引，如果创建，报错如下：SQLcreateindexi_time_globalonPDBA(id)global-索引引导列2partitionbyrange(time)-分区建3(partitionp1valueslessthan(TO_DATE(2010-12-1,YYYY-MM-DD),4partitionp2valueslessthan(maxvalue)5);partitionbyrange(time)*第 2 行出现错误:ORA-14038: GLOBAL 分区索引必须加上前缀4.1 Local 本地索引对于local索引，当表的分区发生变化时，索引的维护由Oracle自动进行。注意事项：（1） 局部索引一定是分区索引，分区键等同于表的分区键。（2） 前缀和非前缀索引都可以支持索引分区消除，前提是查询的条件中包含索引分区键。（3） 局部索引只支持分区内的唯一性，无法支持表上的唯一性，因此如果要用局部索引去给表做唯一性约束，则约束中必须要包括分区键列。（4） 局部分区索引是对单个分区的，每个分区索引只指向一个表分区；全局索引则不然，一个分区索引能指向n个表分区，同时，一个表分区，也可能指向n个索引分区，对分区表中的某个分区做truncate或者move，shrink等，可能