详解分区表上的索引问题.docx
《详解分区表上的索引问题.docx》由会员分享，可在线阅读，更多相关《详解分区表上的索引问题.docx（10页珍藏版）》请在冰豆网上搜索。
详解分区表上的索引问题
详解分区表上的索引问题
详解分区表上的索引问题
QUESTION:
分区表在管理上的确非常方便，在性能方面的表现也不错。
但是就是有一点不是很理想：
删除分区和TRUNCATE分区表中数据，会造成主键和全局索引的失效。
如果数据表非常大，索引的重建也是需要花很长时间的。
针对这个问题，有没有合适的解决办法呢?
完整的方案：
最后再整理一下，请大家给指正指正
1.分区表的用途和优势：
a、增强可用性：
如果表的一个分区由于系统故障而不能使用，表的其余好的分区仍然可以使用；
b、减少关闭时间：
如果系统故障只影响表的一部分分区，那么只有这部分分区需要修复，故能比整个大表修复花的时间更少；
c、维护轻松：
如果需要重建表，独立管理每个分区比管理单个大表要轻松得多；
d、均衡I/O:
可以把表的不同分区分配到不同的磁盘来平衡I/O改善性能；
e、改善性能：
对大表的查询、增加、修改等操作可以分解到表的不同分区来并行执行，可使运行速度更快；
f、分区对用户透明，最终用户感觉不到分区的存在。
2.使用过程中存在的问题：
删除分区和TRUNCATE分区表中数据，会造成主键和全局索引的失效。
如果数据表非常大，索引的重建也是需要花很长时间的。
3.解决办法：
首先根据应用尽量将索引修改为分区索引（个别索引可能不能修改），删除主键也修改为唯一分区索引。
经过测试表明，如果分区字段选择合理，使用分区索引的效率比全局索引要高一些，而主键和唯一分区索引的效率大体相当，因此这样的优化还是非常值得的，如果由于优化导致个别应用效率下降，也可以通过应用的调整进行优化。
需要特别注意的几个方面：
a.如果个别索引不适合使用分区索引，在执行删除分区的操作时最好增加updateglobalindexes子句，示例如下：
altertablexxxdroppartitionyyyupdateglobalindexes，以保证全局索引同步更新，避免对应用造成影响。
b.分区的创建和删除等维护操作最好采用手工的方式在数据库相对比较空闲的时段进行，特别是分区的删除操作，由于需要释放磁盘空间并同步更新索引，容易产生一些意外。
c.在分区表上创建的唯一索引必须包含分区字段，否则会提示错误（ORA-14039），这一点也需要特别注意。
d.在分区表上增加或者拆分分区时分区索引会同步进行更新，不需要进行索引重建和分析操作，如有必要可以动态创建分区，以满足应用的需要。
3.创建分区表的实例：
CREATETABLEEDU.TJ_RESULT_PARTITION
（
IDNUMBER（8）NOTNULL,
MSG_IDNUMBER（8）NOTNULL,
AINSERVICEIDVARCHAR2（10）NOTNULL,
STATENUMBER
（1）DEFAULT0NOTNULL,
MSGMODENUMBER
（1）NULL,
SERVICEIDVARCHAR2（10）NOTNULL,
SRCTERMIDVARCHAR2（22）NOTNULL,
DESCTERMIDVARCHAR2（22）NOTNULL,
FEETERMINALIDVARCHAR2（22）NOTNULL,
SRC_MOBILEVARCHAR2（11）DEFAULT'0'NOTNULL,
SRC_ACCOUNTIDNUMBER（8）DEFAULT0NOTNULL,
SRC_PERSONIDNUMBER（8）DEFAULT0NOTNULL,
SRC_ORGIDNUMBER（6）DEFAULT0NOTNULL,
VALIDTIMEDATENULL,
ATTIMEDATENULL,
FINISHDATEDATEDEFAULTsysdateNOTNULL
）
TABLESPACEEDUCATION
NOLOGGING
PCTFREE10
PCTUSED40
INITRANS10
MAXTRANS255
STORAGE（PCTINCREASE0
FREELISTS5
FREELISTGROUPS2
BUFFER_POOLKEEP）
NOPARALLEL
NOCACHE
PARTITIONBYRANGE（FINISHDATE）
（
PARTITIONPARTITION_200605VALUESLESSTHAN（TO_DATE（'2006-06-0100:
00:
00','SYYYY-MM-DDHH24:
MI:
SS','NLS_CALENDAR=GREGORIAN'））
TABLESPACEEDUSPACE,
PARTITIONPARTITION_200606VALUESLESSTHAN（TO_DATE（'2006-07-0100:
00:
00','SYYYY-MM-DDHH24:
MI:
SS','NLS_CALENDAR=GREGORIAN'））
TABLESPACEEDUSPACE,
PARTITIONPARTITION_200607VALUESLESSTHAN（TO_DATE（'2006-08-0100:
00:
00','SYYYY-MM-DDHH24:
MI:
SS','NLS_CALENDAR=GREGORIAN'））
TABLESPACEEDUSPACE,
PARTITIONPARTITION_200608VALUESLESSTHAN（TO_DATE（'2006-09-0100:
00:
00','SYYYY-MM-DDHH24:
MI:
SS','NLS_CALENDAR=GREGORIAN'））
TABLESPACEEDUSPACE,
PARTITIONPARTITION_200609VALUESLESSTHAN（TO_DATE（'2006-10-0100:
00:
00','SYYYY-MM-DDHH24:
MI:
SS','NLS_CALENDAR=GREGORIAN'））
TABLESPACEEDUSPACE,
PARTITIONPARTITION_200610VALUESLESSTHAN（TO_DATE（'2006-11-0100:
00:
00','SYYYY-MM-DDHH24:
MI:
SS','NLS_CALENDAR=GREGORIAN'））
TABLESPACEEDUSPACE,
PARTITIONPARTITION_200611VALUESLESSTHAN（TO_DATE（'2006-12-0100:
00:
00','SYYYY-MM-DDHH24:
MI:
SS','NLS_CALENDAR=GREGORIAN'））
TABLESPACEEDUSPACE,
PARTITIONPARTITION_200612VALUESLESSTHAN（TO_DATE（'2007-01-0100:
00:
00','SYYYY-MM-DDHH24:
MI:
SS','NLS_CALENDAR=GREGORIAN'））
LOGGING）
/
4.创建唯一分区索引的实例：
CREATEUNIQUEINDEXEDU.PARTITION_PRIMARY
ONEDU.TJ_RESULT_PARTITION
（ID,FINISHDATE）
LOGGING
LOCAL（
PARTITIONPARTITION_200605
LOGGING
NOCOMPRESS,
PARTITIONPARTITION_200606
LOGGING
NOCOMPRESS,
PARTITIONPARTITION_200607
LOGGING
NOCOMPRESS,
PARTITIONPARTITION_200608
LOGGING
NOCOMPRESS,
PARTITIONPARTITION_200609
LOGGING
NOCOMPRESS,
PARTITIONPARTITION_200610
LOGGING
NOCOMPRESS,
PARTITIONPARTITION_200611
LOGGING
NOCOMPRESS,
PARTITIONPARTITION_200612
LOGGING
NOCOMPRESS
）
5.创建普通分区索引的实例：
CREATEINDEXEDU.PARTITION_FINISHDATE
ONEDU.TJ_RESULT_PARTITION
（FINISHDATE）
LOGGING
LOCAL（
PARTITIONPARTITION_200605
LOGGING
NOCOMPRESS,
PARTITIONPARTITION_200606
LOGGING
NOCOMPRESS,
PARTITIONPARTITION_200607
LOGGING
NOCOMPRESS,
PARTITIONPARTITION_200608
LOGGING
NOCOMPRESS,
PARTITIONPARTITION_200609
LOGGING
NOCOMPRESS,
PARTITIONPARTITION_200610
LOGGING
NOCOMPRESS,
PARTITIONPARTITION_200611
LOGGING
NOCOMPRESS,
PARTITIONPARTITION_200612
LOGGING
NOCOMPRESS
）
6.分区维护实例：
a.删除分区：
ALTERTABLEEDU.TJ_RESULT_PARTITIONDROPPARTITIONPARTITION_200610;
b.增加分区：
ALTERTABLEEDU.TJ_RESULT_PARTITIONADDPARTITIONPARTITION_200701VALUESLESSTHAN
（TO_DATE（'2007-02-0100:
00:
00','SYYYY-MM-DDHH24:
MI:
SS','NLS_CALENDAR=GREGORIAN'））
LOGGINGNOCOMPRESS;
c.拆分分区：
ALTERTABLEEDU.TJ_RESULT_PARTITION
SPLITPARTITiONPARTITION_200608AT（TO_DATE（'2006-08-15','YYYY-MM-DD'））
INTO（partitionPARTITION_20060801,partitionPARTITION_20060802）
oracle分区表学习及应用
--Createtable（创建分区表）
createtableBILL_MONTHFEE_ZERO
（
SERV_IDNUMBER（20）notnull,
BILLING_CYCLE_MONTHNUMBER（6）notnull,
DATE_TYPENUMBER
（1）,
ACC_NBRVARCHAR2（80）
）
partitionbyrange（BILLING_CYCLE_MONTH）
（partitionp_200407valueslessthan（200407）
tablespaceTS_ZIKEN
storage（initial100knext100kminextents1maxextentsunlimitedpctincrease0）,
partitionp_200408valueslessthan（200408）
tablespaceTS_ZIKEN
storage（initial100knext100kminextents1maxextentsunlimitedpctincrease0））
;
createindexidx_bill_monthfee_zero_idx01onbill_monthfee_zero（billing_cycle_month）
tablespaceTS_ZIKEN_idx
storage（initial100knext100kminextents1maxextentsunlimitedpctincrease0）nologging;
grantallonbill_monthfee_zerotodxsq_dev;
--增加分区表
altertableBILL_MONTHFEE_ZEROaddPartitionp_200409
valueslessthan（200409）tablespacets_ziken;
--删除一分区
altertablepart_tbldropPartitionpart_tbl_08;
--将一个分区分为两个分区
altertablebill_monthfee_zerosplitPartitionp_200409at（200409）
into（Partitionp_200409_1tablespacets_ziken,
Partitionp_200409_2tablespacets_ziken_idx）;
--合并分区
ALTERTABLEbill_monthfee_zero
MERGEPARTITIONSp_200408,p_200409INTOPARTITIONp_all
--将分区改名
altertablebill_monthfee_zerorenamePartitionp_200408top_fee_200408
--将分区改表空间
altertablebill_monthfee_zeromovePartitionp_200409
tablespacets_ziken_01nologging
--查询特定分区
selectcount（*）fromBILL_MONTHFEE_ZEROpartition（p_200407）;
--添加数据
insertintobill_monthfee_zeroselect*frombill_monthfee_zeropartition（p_200407）
--分区表的导出
userid=dxsq/teledoone@jndxsq154
buffer=102400
tables=bill_monthfee:
P_200401,
file=E:
\exp_para\exp_dxsq_tables.dmp
log=E:
\exp_para\exp_dxsq_tables.log
技巧：
删除表中一个字段：
altertablebill_monthfee_zerosetunusedcolumndate_type;
添加一个字段：
altertablebill_monthfee_zeroadddate_typenumber
（1）;
今天在删除一个oralce分区表中的一个分区时直接把分区删了,而没有重新建立这个表的索引,造成应用出问题了,以后要切记这个问题.
其实造成这个问题的发生就是我对oracle的分区表太不了解了.
错误1.在建立这个分区表时,这个表的所有索引全部建成global的了,就是整个表的索引,如果建成每个分区一个索引,也就是local的也不会出现今天的问题
错误2.删除分区表时没有加入更新索引的语句,造成所有索引unusable,我的语句是altertablehs_app_visitlogdropPartitionHS_APP_VISITLOG_0701;
这里如果写成altertableHS_APP_VISITLOGdropPartitionHS_APP_VISITLOG_0701updateglobalindexes;也没事了
没办法,错误已经造成,只好把这个表的所有indexrebuild了
alterindexHSIDX_VISITLOG_VERSIONrebuild;
rebuild的时间是漫长的....
另外查询某个表的索引状态可以用下面这个语句:
selectindex_name,statusfromuser_indexeswheretable_name='HS_APP_VISITLOG';
结果中valid是有效的,unusable未使用的.
以后要切记了!
原来一个表的分区，由于忘了加上新的月份的分区，在已经有一部分数据存入MAX区域时
强行重建分区，结果在向这个表插入新数据时，提示：
索引'USER.TABLE_AAA_IDX4'或这类索引的分区处于不可用状态
查询索引状态（也可通过EMC管理终端查看库-》方案-》用户-》索引）：
selectindex_name,statusfromuser_indexes;
发现此索引状态为N/A，原来分区的索引不能通过用户字典查看，
需要用DBA的数据字典查看，用sys用户登陆，查询：
selectindex_name,partition_name,statusfromdba_ind_partitionsorderbystatus;
原来是4月份的新插分的数据的索引失效了，针对4月份分区进行重建索引
alterindexTABLE_AAA_IDX4rebuildpartitionTABLE_AAA_200704;
重建后再次查询，索引已经生效，插入新数据正常。
createindexWWWW1onH_LINK_INFO1（DATE_ID）
globalpartitionbyrange（DATE_ID）
（partitionH_LK200803valueslessthan（'20080331'）
tablespaceRPT_TBS_H,
partitionH_LK200806valueslessthan（'20080630'）
tablespaceRPT_TBS_H,
partitionH_LK200809valueslessthan（'20080930'）
tablespaceRPT_TBS_H,
partitionH_LK200810valueslessthan（MAXVALUE）
tablespaceRPT_TBS_H）
升级会员 