详解分区表上的索引问题

今天在做分区切割后查询时,发现索引失效!看到一篇好的文章!共享学习一下![@more@]

详解分区表上的索引问题 
QUESTION:
分区表在管理上的确非常方便，在性能方面的表现也不错。
但是就是有一点不是很理想：删除分区和TRUNCATE分区表中数据，会造成主键和全局索引的失效。如果数据表非常大，索引的重建也是需要花很长时间的。
针对这个问题，有没有合适的解决办法呢?

完整的方案：
最后再整理一下，请大家给指正指正 
1. 分区表的用途和优势：

a 、增强可用性：如果表的一个分区由于系统故障而不能使用，表的其余好的分区仍然可以使用； 
b 、减少关闭时间：如果系统故障只影响表的一部分分区，那么只有这部分分区需要修复，故能比整个大表修复花的时间更少； 
c 、维护轻松：如果需要重建表，独立管理每个分区比管理单个大表要轻松得多； 
d 、均衡I/O:可以把表的不同分区分配到不同的磁盘来平衡I/O改善性能； 
e 、改善性能：对大表的查询、增加、修改等操作可以分解到表的不同分区来并行执行，可使运行速度更快； 
f 、分区对用户透明，最终用户感觉不到分区的存在。

2. 使用过程中存在的问题：
删除分区和TRUNCATE分区表中数据，会造成主键和全局索引的失效。如果数据表非常大，索引的重建也是需要花很长时间的。

3. 解决办法：

首先根据应用尽量将索引修改为分区索引（个别索引可能不能修改），删除主键也修改为唯一分区索引。
经过测试表明，如果分区字段选择合理，使用分区索引的效率比全局索引要高一些，而主键和唯一分区索引的效率大体相当，因此这样的优化还是非常值得的，如果由于优化导致个别应用效率下降，也可以通过应用的调整进行优化。
需要特别注意的几个方面：
a. 如果个别索引不适合使用分区索引，在执行删除分区的操作时最好增加update global indexes子句，示例如下：alter table xxx drop partition yyy update global indexes ，以保证全局索引同步更新，避免对应用造成影响。
b. 分区的创建和删除等维护操作最好采用手工的方式在数据库相对比较空闲的时段进行，特别是分区的删除操作，由于需要释放磁盘空间并同步更新索引，容易产生一些意外。
c. 在分区表上创建的唯一索引必须包含分区字段，否则会提示错误(ORA-14039)，这一点也需要特别注意。
d. 在分区表上增加或者拆分分区时分区索引会同步进行更新，不需要进行索引重建和分析操作，如有必要可以动态创建分区，以满足应用的需要。

3. 创建分区表的实例：
CREATE TABLE EDU.TJ_RESULT_PARTITION 
(
ID NUMBER(8) NOT NULL,
MSG_ID NUMBER(8) NOT NULL,
AINSERVICEID VARCHAR2(10) NOT NULL,
STATE NUMBER(1) DEFAULT 0 NOT NULL,
MSGMODE NUMBER(1) NULL,
SERVICEID VARCHAR2(10) NOT NULL,
SRCTERMID VARCHAR2(22) NOT NULL,
DESCTERMID VARCHAR2(22) NOT NULL,
FEETERMINALID VARCHAR2(22) NOT NULL,
SRC_MOBILE VARCHAR2(11) DEFAULT '0' NOT NULL,
SRC_ACCOUNTID NUMBER(8) DEFAULT 0 NOT NULL,
SRC_PERSONID NUMBER(8) DEFAULT 0 NOT NULL,
SRC_ORGID NUMBER(6) DEFAULT 0 NOT NULL,
VALIDTIME DATE NULL,
ATTIME DATE NULL,
FINISHDATE DATE DEFAULT sysdate NOT NULL
)
TABLESPACE EDUCATION
NOLOGGING
PCTFREE 10
PCTUSED 40
INITRANS 10
MAXTRANS 255
STORAGE(PCTINCREASE 0
FREELISTS 5
FREELIST GROUPS 2
BUFFER_POOL KEEP)
NOPARALLEL
NOCACHE
PARTITION BY RANGE(FINISHDATE)
(
PARTITION PARTITION_200605 VALUES LESS THAN (TO_DATE(' 2006-06-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN'))
TABLESPACE EDUSPACE,
PARTITION PARTITION_200606 VALUES LESS THAN (TO_DATE(' 2006-07-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN'))
TABLESPACE EDUSPACE,
PARTITION PARTITION_200607 VALUES LESS THAN (TO_DATE(' 2006-08-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN'))
TABLESPACE EDUSPACE,
PARTITION PARTITION_200608 VALUES LESS THAN (TO_DATE(' 2006-09-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN'))
TABLESPACE EDUSPACE,
PARTITION PARTITION_200609 VALUES LESS THAN (TO_DATE(' 2006-10-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN'))
TABLESPACE EDUSPACE,
PARTITION PARTITION_200610 VALUES LESS THAN (TO_DATE(' 2006-11-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN'))
TABLESPACE EDUSPACE,
PARTITION PARTITION_200611 VALUES LESS THAN (TO_DATE(' 2006-12-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN'))
TABLESPACE EDUSPACE,
PARTITION PARTITION_200612 VALUES LESS THAN (TO_DATE(' 2007-01-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN'))
LOGGING)
/
4. 创建唯一分区索引的实例：
CREATE UNIQUE INDEX EDU.PARTITION_PRIMARY 
ON EDU.TJ_RESULT_PARTITION
(ID, FINISHDATE)
LOGGING
LOCAL ( 
PARTITION PARTITION_200605
LOGGING
NOCOMPRESS, 
PARTITION PARTITION_200606
LOGGING
NOCOMPRESS, 
PARTITION PARTITION_200607
LOGGING
NOCOMPRESS, 
PARTITION PARTITION_200608
LOGGING
NOCOMPRESS, 
PARTITION PARTITION_200609
LOGGING
NOCOMPRESS, 
PARTITION PARTITION_200610
LOGGING
NOCOMPRESS, 
PARTITION PARTITION_200611
LOGGING
NOCOMPRESS, 
PARTITION PARTITION_200612
LOGGING
NOCOMPRESS
)
5. 创建普通分区索引的实例：
CREATE INDEX EDU.PARTITION_FINISHDATE 
ON EDU.TJ_RESULT_PARTITION
(FINISHDATE)
LOGGING
LOCAL ( 
PARTITION PARTITION_200605
LOGGING
NOCOMPRESS, 
PARTITION PARTITION_200606
LOGGING
NOCOMPRESS, 
PARTITION PARTITION_200607
LOGGING
NOCOMPRESS, 
PARTITION PARTITION_200608
LOGGING
NOCOMPRESS, 
PARTITION PARTITION_200609
LOGGING
NOCOMPRESS, 
PARTITION PARTITION_200610
LOGGING
NOCOMPRESS, 
PARTITION PARTITION_200611
LOGGING
NOCOMPRESS, 
PARTITION PARTITION_200612
LOGGING
NOCOMPRESS
)
6. 分区维护实例：
a. 删除分区：
ALTER TABLE EDU.TJ_RESULT_PARTITION DROP PARTITION PARTITION_200610;
b. 增加分区：
ALTER TABLE EDU.TJ_RESULT_PARTITION ADD PARTITION PARTITION_200701 VALUES LESS THAN 
(TO_DATE(' 2007-02-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')) 
LOGGING NOCOMPRESS;
c. 拆分分区：
ALTER TABLE EDU.TJ_RESULT_PARTITION 
SPLIT PARTITiON PARTITION_200608 AT (TO_DATE('2006-08-15','YYYY-MM-DD'))
INTO (partition PARTITION_20060801 , partition PARTITION_20060802)

oracle分区表学习及应用

-- Create table(创建分区表)
create table BILL_MONTHFEE_ZERO
(
SERV_ID NUMBER(20) not null,
BILLING_CYCLE_MONTH NUMBER(6) not null,
DATE_TYPE NUMBER(1),
ACC_NBR VARCHAR2(80)
)
partition by range (BILLING_CYCLE_MONTH)
(partition p_200407 values less than (200407)
tablespace TS_ZIKEN
storage(initial 100k next 100k minextents 1 maxextents unlimited pctincrease 0),
partition p_200408 values less than (200408)
tablespace TS_ZIKEN
storage(initial 100k next 100k minextents 1 maxextents unlimited pctincrease 0))
;
create index idx_bill_monthfee_zero_idx01 on bill_monthfee_zero(billing_cycle_month)
tablespace TS_ZIKEN_idx
storage(initial 100k next 100k minextents 1 maxextents unlimited pctincrease 0) nologging;
grant all on bill_monthfee_zero to dxsq_dev;

--增加分区表

alter table BILL_MONTHFEE_ZERO add Partition p_200409
values less than (200409) tablespace ts_ziken;

--删除一分区
alter table part_tbl drop Partition part_tbl_08;

--将一个分区分为两个分区
alter table bill_monthfee_zero split Partition p_200409 at (200409)
into (Partition p_200409_1 tablespace ts_ziken,
Partition p_200409_2 tablespace ts_ziken_idx); 

--合并分区
ALTER TABLE bill_monthfee_zero
MERGE PARTITIONS p_200408, p_200409 INTO PARTITION p_all

--将分区改名
alter table bill_monthfee_zero rename Partition p_200408 to p_fee_200408

--将分区改表空间
alter table bill_monthfee_zero move Partition p_200409
tablespace ts_ziken_01 nologging

--查询特定分区
select count(*) from BILL_MONTHFEE_ZERO partition (p_200407);

--添加数据
insert into bill_monthfee_zero select * from bill_monthfee_zero partition (p_200407)

--分区表的导出
userid=dxsq/teledoone@jndxsq154
buffer=102400
tables=bill_monthfee:P_200401,
file=E:exp_paraexp_dxsq_tables.dmp
log=E:exp_paraexp_dxsq_tables.log


技巧：
删除表中一个字段：
alter table bill_monthfee_zero set unused column date_type;
添加一个字段：alter table bill_monthfee_zero add date_type number(1);

今天在删除一个oralce分区表中的一个分区时直接把分区删了,而没有重新建立这个表的索引,造成应用出问题了,以后要切记这个问题.
其实造成这个问题的发生就是我对oracle的分区表太不了解了.
错误1.在建立这个分区表时,这个表的所有索引全部建成global的了,就是整个表的索引,如果建成每个分区一个索引,也就是local的也不会出现今天的问题
错误2.删除分区表时没有加入更新索引的语句,造成所有索引unusable,我的语句是alter table hs_app_visitlog drop Partition HS_APP_VISITLOG_0701;
这里如果写成alter table HS_APP_VISITLOG drop Partition HS_APP_VISITLOG_0701 update global indexes;也没事了
没办法,错误已经造成,只好把这个表的所有index rebuild了
alter index HSIDX_VISITLOG_VERSION rebuild;
rebuild的时间是漫长的....
另外查询某个表的索引状态可以用下面这个语句:
select index_name,status from user_indexes where table_name='HS_APP_VISITLOG';
结果中valid是有效的,unusable未使用的.
以后要切记了!

原来一个表的分区，由于忘了加上新的月份的分区，在已经有一部分数据存入MAX区域时
强行重建分区，结果在向这个表插入新数据时，提示：
索引'USER.TABLE_AAA_IDX4'或这类索引的分区处于不可用状态
查询索引状态（也可通过EMC管理终端查看库-》方案-》用户-》索引）：
select index_name, status from user_indexes;
发现此索引状态为 N/A， 原来分区的索引不能通过用户字典查看，
需要用DBA的数据字典查看，用sys用户登陆，查询：
select index_name,partition_name,status from dba_ind_partitions order by status;
原来是4月份的新插分的数据的索引失效了，针对4月份分区进行重建索引
alter index TABLE_AAA_IDX4 rebuild partition TABLE_AAA_200704;
重建后再次查询，索引已经生效，插入新数据正常。

create index WWWW1 on H_LINK_INFO1 (DATE_ID)
global partition by range(DATE_ID)
(partition H_LK200803 values less than ('20080331')
tablespace RPT_TBS_H,
partition H_LK200806 values less than ('20080630')
tablespace RPT_TBS_H,
partition H_LK200809 values less than ('20080930')
tablespace RPT_TBS_H,
partition H_LK200810 values less than (MAXVALUE)
tablespace RPT_TBS_H)