关于Oracle表分区.docx
《关于Oracle表分区.docx》由会员分享，可在线阅读，更多相关《关于Oracle表分区.docx（27页珍藏版）》请在冰豆网上搜索。
关于Oracle表分区
关于Oracle表分区
关于Oracle表分区（转）
在ORACLE里如果遇到特别大的表，可以使用分区的表来改变其应用程序的性能。
同事的分区表总结，转载一下。
1.1分区表PARTITIONtable
在ORACLE里如果遇到特别大的表，可以使用分区的表来改变其应用程序的性能。
1.1.1分区表的建立：
某公司的每年产生巨大的销售记录，DBA向公司建议每季度的数据放在一个分区内，以下示范的是该公司1999年的数据（假设每月产生30M的数据），操作如下：
范围分区表：
CREATETABLEsales
（invoice_noNUMBER,
...
sale_dateDATENOTNULL）
PARTITIONBYRANGE（sale_date）
（PARTITIONsales1999_q1
VALUESLESSTHAN（TO_DATE（‘1999-04-01’,’YYYY-MM-DD’）
TABLESPACEts_sale1999q1,
PARTITIONsales1999_q2
VALUESLESSTHAN（TO_DATE（‘1999-07-01’,’YYYY-MM-DD’）
TABLESPACEts_sale1999q2,
PARTITIONsales1999_q3
VALUESLESSTHAN（TO_DATE（‘1999-10-01’,’YYYY-MM-DD’）
TABLESPACEts_sale1999q3,
PARTITIONsales1999_q4
VALUESLESSTHAN（TO_DATE（‘2000-01-01’,’YYYY-MM-DD’）
TABLESPACEts_sale1999q4）;
--valueslessthan（maxvalue）
列表分区表：
createtableemp（
empnonumber（4）,
enamevarchar2（30）,
locationvarchar2（30））
partitionbylist（location）
（partitionp1values（'北京'）,
partitionp2values（'上海','天津','重庆'）,
partitionp3values（'广东','福建'）
partitionp0values（default）
）;
哈希分区：
createtableemp（
empnonumber（4）,
enamevarchar2（30）,
salnumber）
partitionbyhash（empno）
partitions8
storein（emp1,emp2,emp3,emp4,emp5,emp6,emp7,emp8）;
组合分区：
范围哈希组合分区：
createtableemp（
empnonumber（4）,
enamevarchar2（30）,
hiredatedate）
partitionbyrange（hiredate）
subpartitionbyhash（empno）
subpartitions2
（partitione1valueslessthan（to_date（'20020501','YYYYMMDD'））,
partitione2valueslessthan（to_date（'20021001','YYYYMMDD'））,
partitione3valueslessthan（maxvalue））;
范围列表组合分区：
CREATETABLEcustomers_part（
customer_idNUMBER（6）,
cust_first_nameVARCHAR2（20）,
cust_last_nameVARCHAR2（20）,
nls_territoryVARCHAR2（30）,
credit_limitNUMBER（9,2））
PARTITIONBYRANGE（credit_limit）
SUBPARTITIONBYLIST（nls_territory）
SUBPARTITIONTEMPLATE
（SUBPARTITIONeastVALUES（'CHINA','JAPAN','INDIA','THAILAND'）,
SUBPARTITIONwestVALUES（'AMERICA','GERMANY','ITALY','SWITZERLAND'）,
SUBPARTITIONotherVALUES（DEFAULT））
（PARTITIONp1VALUESLESSTHAN（1000）,
PARTITIONp2VALUESLESSTHAN（2500）,
PARTITIONp3VALUESLESSTHAN（MAXVALUE））;
createtablet1（id1number,id2number）
partitionbyrange（id1）subpartitionbylist（id2）
（partitionp11valueslessthan（11）
（subpartitionsubp1values
（1））
）;
索引分区：
CREATEINDEXmonth_ixONsales（sales_month）
GLOBALPARTITIONBYRANGE（sales_month）
（PARTITIONpm1_ixVALUESLESSTHAN
（2）
PARTITIONpm12_ixVALUESLESSTHAN（MAXVALUE））;
1.1.2分区表的维护：
增加分区：
ALTERTABLEsalesADDPARTITIONsales2000_q1
VALUESLESSTHAN（TO_DATE（‘2000-04-01’,’YYYY-MM-DD’）
TABLESPACEts_sale2000q1;
如果已有maxvalue分区，不能增加分区，可以采取分裂分区的办法增加分区！
删除分区：
ALTERTABLEsalesDROPPARTIONsales1999_q1;
截短分区:
altertablesalestruncatepartitonsales1999_q2;
合并分区：
altertablesalesmergepartitonssales1999_q2,sales1999_q3intosales1999_q23;
alterindexind_t2rebuildpartitionp123parallel2;
分裂分区：
ALTERTABLEsales
SPLITPARTITONsales1999_q4
ATTO_DATE（‘1999-11-01’,’YYYY-MM-DD’）
INTO（partitionsales1999_q4_p1,partitionsales1999_q4_p2）;
altertablet2splitpartitionp123values（1,2）into（partitionp12,partitionp3）;
交换分区:
altertablexexchangepartitionp0withtablebsvcbusrundatald;
访问指定分区：
select*fromsalespartition（sales1999_q2）
EXPORT指定分区：
expsales/sales_passwordtables=sales:
sales1999_q1
file=sales1999_q1.dmp
IMPORT指定分区：
impsales/sales_passwordFILE=sales1999_q1.dmp
TABLES=（sales:
sales1999_q1）IGNORE=y
查看分区信息：
user_tab_partitions,user_segments
注：
若分区表跨不同表空间，做导出、导入时目标数据库必须预建这些表空间。
分表区各区所在表空间在做导入时目标数据库一定要预建这些表空间！
这些表空间不一定是用户的默认表空间，只要存在即可。
如果有一个不存在，就会报错！
默认时，对分区表的许多表维护操作会使全局索引不可用，标记成UNUSABLE。
那么就必须重建整个全局索引或其全部分区。
如果已被分区，Oracle允许在用于维护操作的ALTERTABLE语句中指定UPDATEGLOBALINDEXES来重载这个默认特性，指定这个子句也就告诉Oracle当它执行维护操作的DDL语句时更新全局索引，这提供了如下好处：
1.在操作基础表的同时更新全局索引这就不需要后来单独地重建全局索引；
2.因为没有被标记成UNUSABLE，所以全局索引的可用性更高了，甚至正在执行分区的DDL语句时仍然可用索引来访问表中的其他分区,避免了查询所有失效的全局索引的名字以便重建它们；
另外在指定UPDATEGLOBALINDEXES之前还要考虑如下性能因素:
1.因为要更新事先被标记成UNUSABLE的索引，所以分区的DDL语句要执行更长时间，当然这要与先不更新索引而执行DDL然后再重建索引所花的时间做个比较，一个适用的规则是如果分区的大小小于表的大小的5%，则更新索引更快一点；
2.DROPTRUNCATE和EXCHANGE操作也不那么快了，同样这必须与先执行DDL然后再重建所有全局索引所花的时间做个比较；
3.要登记对索引的更新并产生重做记录和撤消记录，重建整个索引时可选择NOLOGGING；
4.重建整个索引产生一个更有效的索引，因为这更利于使用空间，再者重建索引时允许修改存储选项。
注意分区索引结构表不支持UPDATEGLOBALINDEXES子句。
1.1.3普通表变为分区表
将已存在数据的普通表转变为分区表，没有办法通过修改属性的方式直接转化为分区表，必须通过重建的方式进行转变，一般可以有三种方法，视不同场景使用：
用例：
方法一：
利用原表重建分区表。
CREATETABLET（IDNUMBERPRIMARYKEY,TIMEDATE）;
INSERTINTOT
SELECTROWNUM,SYSDATE-ROWNUMFROMDBA_OBJECTSWHEREROWNUM<=5000;
COMMIT;
CREATETABLET_NEW（ID,TIME）PARTITIONBYRANGE（TIME）
（PARTITIONP1VALUESLESSTHAN（TO_DATE（'2000-1-1','YYYY-MM-DD'））,
PARTITIONP2VALUESLESSTHAN（TO_DATE（'2002-1-1','YYYY-MM-DD'））,
PARTITIONP3VALUESLESSTHAN（TO_DATE（'2005-1-1','YYYY-MM-DD'））,
PARTITIONP4VALUESLESSTHAN（MAXVALUE））
ASSELECTID,TIMEFROMT;
RENAMETTOT_OLD;
RENAMET_NEWTOT;
SELECTCOUNT（*）FROMT;
COUNT（*）
----------
5000
SELECTCOUNT（*）FROMTPARTITION（P1）;
COUNT（*）
----------
2946
SELECTCOUNT（*）FROMTPARTITION（P2）;
COUNT（*）
----------
731
SELECTCOUNT（*）FROMTPARTITION（P3）;
COUNT（*）
----------
1096
优点：
方法简单易用，由于采用DDL语句，不会产生UNDO，且只产生少量REDO，效率相对较高，而且建表完成后数据已经在分布到各个分区中了。
不足：
对于数据的一致性方面还需要额外的考虑。
由于几乎没有办法通过手工锁定T表的方式保证一致性，在执行CREATETABLE语句和RENAMET_NEWTOT语句直接的修改可能会丢失，如果要保证一致性，需要在执行完语句后对数据进行检查，而这个代价是比较大的。
另外在执行两个RENAME语句之间执行的对T的访问会失败。
适用于修改不频繁的表，在闲时进行操作，表的数据量不宜太大。
方法二：
使用交换分区的方法。
Droptablet;
CREATETABLET（IDNUMBERPRIMARYKEY,TIMEDATE）;
INSERTINTOT
SELECTROWNUM,SYSDATE-ROWNUMFROMDBA_OBJECTSWHEREROWNUM<=5000;
COMMIT;
CREATETABLET_NEW（IDNUMBERPRIMARYKEY,TIMEDATE）PARTITIONBYRANGE（TIME）
（PARTITIONP1VALUESLESSTHAN（TO_DATE（'2005-9-1','YYYY-MM-DD'））,
PARTITIONP2VALUESLESSTHAN（MAXVALUE））;
ALTERTABLET_NEWEXCHANGEPARTITIONP1WITHTABLET;
RENAMETTOT_OLD;
RENAMET_NEWTOT;
优点：
只是对数据字典中分区和表的定义进行了修改，没有数据的修改或复制，效率最高。
如果对数据在分区中的分布没有进一步要求的话，实现比较简单。
在执行完RENAME操作后，可以检查T_OLD中是否存在数据，如果存在的话，直接将这些数据插入到T中，可以保证对T插入的操作不会丢失。
不足：
仍然存在一致性问题，交换分区之后RENAMET_NEWTOT之前，查询、更新和删除会出现错误或访问不到数据。
如果要求数据分布到多个分区中，则需要进行分区的SPLIT操作，会增加操作的复杂度，效率也会降低。
适用于包含大数据量的表转到分区表中的一个分区的操作。
应尽量在闲时进行操作。
方法三：
Oracle9i以上版本，利用在线重定义功能
Droptablet;
CREATETABLET（IDNUMBERPRIMARYKEY,TIMEDATE）;
INSERTINTOT
SELECTROWNUM,SYSDATE-ROWNUMFROMDBA_OBJECTSWHEREROWNUM<=5000;
COMMIT;
EXECDBMS_REDEFINITION.CAN_REDEF_TABLE（USER,'T'）;
PL/SQL过程已成功完成。
CREATETABLET_NEW（IDNUMBERPRIMARYKEY,TIMEDATE）PARTITIONBYRANGE（TIME）
（PARTITIONP1VALUESLESSTHAN（TO_DATE（'2004-7-1','YYYY-MM-DD'））,
PARTITIONP2VALUESLESSTHAN（TO_DATE（'2005-1-1','YYYY-MM-DD'））,
PARTITIONP3VALUESLESSTHAN（TO_DATE（'2005-7-1','YYYY-MM-DD'））,
PARTITIONP4VALUESLESSTHAN（MAXVALUE））;
表已创建。
EXECDBMS_REDEFINITION.START_REDEF_TABLE（USER,'T','T_NEW'）;
PL/SQL过程已成功完成。
EXECDBMS_REDEFINITION.FINISH_REDEF_TABLE（USER,'T','T_NEW'）;
PL/SQL过程已成功完成。
SELECTCOUNT（*）FROMT;
COUNT（*）
----------
5000
SELECTCOUNT（*）FROMTPARTITION（P3）;
COUNT（*）
----------
1096
优点：
保证数据的一致性，在大部分时间内，表T都可以正常进行DML操作。
只在切换的瞬间锁表，具有很高的可用性。
这种方法具有很强的灵活性，对各种不同的需要都能满足。
而且，可以在切换前进行相应的授权并建立各种约束，可以做到切换完成后不再需要任何额外的管理操作。
不足：
实现上比上面两种略显复杂。
适用于各种情况。
这里只给出了在线重定义表的一个最简单的例子，详细的描述和例子可以参考下面两篇文章。
Oracle的在线重定义表功能：
Oracle的在线重定义表功能
（二）：
XSB:
把一个已存在数据的大表改成分区表：
第一种（表不是太大）：
1.把原表改名：
renamexsb1toxsb2;
2.创建分区表：
CREATETABLExsb1
PARTITIONBYLIST（c_test）
（PARTITIONxsb1_p1VALUES
（1）,
PARTITIONxsb1_p2VALUES
（2）,
PARTITIONxsb1_p0VALUES（default））
nologgingASSELECT*FROMxsb2;
3.将原表上的触发器、主键、索引等应用到分区表上；
4.删除原表：
droptablexsb2;
第二种（表很大）：
1.创建分区表：
CREATETABLExPARTITIONBYLIST（c_test）[range（）]
（PARTITIONp0VALUES[lessthan]
（1）tablespacetbs1,
PARTITIONp2VALUES
（2）tablespacetbs1,
PARTITIONxsb1_p0VALUES（[maxvalue]default））
ASSELECT*FROMxsb2[where1=2];
2.交换分区altertablexexchangepartitionp0withtablebsvcbusrundatald;
3.原表改名altertablebsvcbusrundataldrenametox0;
4.新表改名altertablexrenametobsvcbusrundatald;
5.删除原表droptablex0;
6.创建新表触发器和索引createindexind_busrundata_lponbsvcbusrundatald（。
。
。
）localtablespacetbs_brd_ind;
或者：
1.规划原大表中数据分区的界限，原则上将原表中近期少量数据复制至另一表；
2.暂停原大表中的相关触发器；
3.删除原大表中近期数据；
4.改名原大表名称；
5.创建分区表；
6.交换分区；
7.重建相关索引及触发器（先删除之再重建）.
参考脚本：
selectcount（*）fromt1whererecdate>sysdate-2
createtablex2nologgingasselect*fromt1whererecdate>trunc（sysdate-2）
altertrigertrg_t1disable
deletet1whererecdate>sysdate-2
commit
renamet1tox1
createtablet1[nologging]partitio
升级会员 