详解分区表上的索引问题Word文件下载.docx
《详解分区表上的索引问题Word文件下载.docx》由会员分享,可在线阅读,更多相关《详解分区表上的索引问题Word文件下载.docx(10页珍藏版)》请在冰豆网上搜索。
f、分区对用户透明,最终用户感觉不到分区的存在。
2.使用过程中存在的问题:
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:
PARTITIONPARTITION_200607VALUESLESSTHAN(TO_DATE('
2006-08-0100:
PARTITIONPARTITION_200608VALUESLESSTHAN(TO_DATE('
2006-09-0100:
PARTITIONPARTITION_200609VALUESLESSTHAN(TO_DATE('
2006-10-0100:
PARTITIONPARTITION_200610VALUESLESSTHAN(TO_DATE('
2006-11-0100:
PARTITIONPARTITION_200611VALUESLESSTHAN(TO_DATE('
2006-12-0100:
PARTITIONPARTITION_200612VALUESLESSTHAN(TO_DATE('
2007-01-0100:
LOGGING)
/
4.创建唯一分区索引的实例:
CREATEUNIQUEINDEXEDU.PARTITION_PRIMARY
ONEDU.TJ_RESULT_PARTITION
(ID,FINISHDATE)
LOGGING
LOCAL(
PARTITIONPARTITION_200605
NOCOMPRESS,
PARTITIONPARTITION_200606
PARTITIONPARTITION_200607
PARTITIONPARTITION_200608
PARTITIONPARTITION_200609
PARTITIONPARTITION_200610
PARTITIONPARTITION_200611
PARTITIONPARTITION_200612
NOCOMPRESS
5.创建普通分区索引的实例:
CREATEINDEXEDU.PARTITION_FINISHDATE
(FINISHDATE)
6.分区维护实例:
a.删除分区:
ALTERTABLEEDU.TJ_RESULT_PARTITIONDROPPARTITIONPARTITION_200610;
b.增加分区:
ALTERTABLEEDU.TJ_RESULT_PARTITIONADDPARTITIONPARTITION_200701VALUESLESSTHAN
(TO_DATE('
2007-02-0100:
))
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)
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'
partitionH_LK200809valueslessthan('
20080930'
partitionH_LK200810valueslessthan(MAXVALUE)
tablespaceRPT_TBS_H)