詳解分區(qū)表上的索引問(wèn)題
QUESTION:
分區(qū)表在管理上的確非常方便，在性能方面的表現(xiàn)也不錯(cuò)。
但是就是有一點(diǎn)不是很理想：刪除分區(qū)和TRUNCATE分區(qū)表中數(shù)據(jù)，會(huì)造成主鍵和全局索引的失效。如果數(shù)據(jù)表非常大，索引的重建也是需要花很長(zhǎng)時(shí)間的。
針對(duì)這個(gè)問(wèn)題，有沒(méi)有合適的解決辦法呢?
完整的方案：
最后再整理一下，請(qǐng)大家給指正指正
1. 分區(qū)表的用途和優(yōu)勢(shì)：
a 、增強(qiáng)可用性：如果表的一個(gè)分區(qū)由于系統(tǒng)故障而不能使用，表的其余好的分區(qū)仍然可以使用；
b 、減少關(guān)閉時(shí)間：如果系統(tǒng)故障只影響表的一部分分區(qū)，那么只有這部分分區(qū)需要修復(fù)，故能比整個(gè)大表修復(fù)花的時(shí)間更少；
c 、維護(hù)輕松：如果需要重建表，獨(dú)立管理每個(gè)分區(qū)比管理單個(gè)大表要輕松得多；
d 、均衡I/O:可以把表的不同分區(qū)分配到不同的磁盤(pán)來(lái)平衡I/O改善性能；
e 、改善性能：對(duì)大表的查詢(xún)、增加、修改等操作可以分解到表的不同分區(qū)來(lái)并行執(zhí)行，可使運(yùn)行速度更快；
f 、分區(qū)對(duì)用戶(hù)透明，最終用戶(hù)感覺(jué)不到分區(qū)的存在。
2. 使用過(guò)程中存在的問(wèn)題：
刪除分區(qū)和TRUNCATE分區(qū)表中數(shù)據(jù)，會(huì)造成主鍵和全局索引的失效。如果數(shù)據(jù)表非常大，索引的重建也是需要花很長(zhǎng)時(shí)間的。
3. 解決辦法：
首先根據(jù)應(yīng)用盡量將索引修改為分區(qū)索引（個(gè)別索引可能不能修改），刪除主鍵也修改為唯一分區(qū)索引。
經(jīng)過(guò)測(cè)試表明，如果分區(qū)字段選擇合理，使用分區(qū)索引的效率比全局索引要高一些，而主鍵和唯一分區(qū)索引的效率大體相當(dāng)，因此這樣的優(yōu)化還是非常值得的，如果由于優(yōu)化導(dǎo)致個(gè)別應(yīng)用效率下降，也可以通過(guò)應(yīng)用的調(diào)整進(jìn)行優(yōu)化。
需要特別注意的幾個(gè)方面：
a. 如果個(gè)別索引不適合使用分區(qū)索引，在執(zhí)行刪除分區(qū)的操作時(shí)最好增加update global indexes子句，示例如下：alter table xxx drop partition yyy update global indexes ，以保證全局索引同步更新，避免對(duì)應(yīng)用造成影響。
b. 分區(qū)的創(chuàng)建和刪除等維護(hù)操作最好采用手工的方式在數(shù)據(jù)庫(kù)相對(duì)比較空閑的時(shí)段進(jìn)行，特別是分區(qū)的刪除操作，由于需要釋放磁盤(pán)空間并同步更新索引，容易產(chǎn)生一些意外。
c. 在分區(qū)表上創(chuàng)建的唯一索引必須包含分區(qū)字段，否則會(huì)提示錯(cuò)誤(ORA-14039)，這一點(diǎn)也需要特別注意。
d. 在分區(qū)表上增加或者拆分分區(qū)時(shí)分區(qū)索引會(huì)同步進(jìn)行更新，不需要進(jìn)行索引重建和分析操作，如有必要可以動(dòng)態(tài)創(chuàng)建分區(qū)，以滿(mǎn)足應(yīng)用的需要。
3. 創(chuàng)建分區(qū)表的實(shí)例：
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. 創(chuàng)建唯一分區(qū)索引的實(shí)例：
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. 創(chuàng)建普通分區(qū)索引的實(shí)例：
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. 分區(qū)維護(hù)實(shí)例：
a. 刪除分區(qū)：
ALTER TABLE EDU.TJ_RESULT_PARTITION DROP PARTITION PARTITION_200610;
b. 增加分區(qū)：
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. 拆分分區(qū)：
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分區(qū)表學(xué)習(xí)及應(yīng)用
-- Create table(創(chuàng)建分區(qū)表)
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;
--增加分區(qū)表
alter table BILL_MONTHFEE_ZERO add Partition p_200409
values less than (200409) tablespace ts_ziken;
--刪除一分區(qū)
alter table part_tbl drop Partition part_tbl_08;
--將一個(gè)分區(qū)分為兩個(gè)分區(qū)
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);
--合并分區(qū)
ALTER TABLE bill_monthfee_zero
MERGE PARTITIONS p_200408, p_200409 INTO PARTITION p_all
--將分區(qū)改名
alter table bill_monthfee_zero rename Partition p_200408 to p_fee_200408
--將分區(qū)改表空間
alter table bill_monthfee_zero move Partition p_200409
tablespace ts_ziken_01 nologging
--查詢(xún)特定分區(qū)
select count(*) from BILL_MONTHFEE_ZERO partition (p_200407);
--添加數(shù)據(jù)
insert into bill_monthfee_zero select * from bill_monthfee_zero partition (p_200407)
--分區(qū)表的導(dǎo)出
userid=dxsq/teledoone@jndxsq154buffer=102400
tables=bill_monthfee:P_200401,
file=E:\exp_para\exp_dxsq_tables.dmp
log=E:\exp_para\exp_dxsq_tables.log
技巧：
刪除表中一個(gè)字段：
alter table bill_monthfee_zero set unused column date_type;
添加一個(gè)字段：alter table bill_monthfee_zero add date_type number(1);
今天在刪除一個(gè)oralce分區(qū)表中的一個(gè)分區(qū)時(shí)直接把分區(qū)刪了,而沒(méi)有重新建立這個(gè)表的索引,造成應(yīng)用出問(wèn)題了,以后要切記這個(gè)問(wèn)題.
其實(shí)造成這個(gè)問(wèn)題的發(fā)生就是我對(duì)oracle的分區(qū)表太不了解了.
錯(cuò)誤1.在建立這個(gè)分區(qū)表時(shí),這個(gè)表的所有索引全部建成global的了,就是整個(gè)表的索引,如果建成每個(gè)分區(qū)一個(gè)索引,也就是local的也不會(huì)出現(xiàn)今天的問(wèn)題
錯(cuò)誤2.刪除分區(qū)表時(shí)沒(méi)有加入更新索引的語(yǔ)句,造成所有索引unusable,我的語(yǔ)句是alter table hs_app_visitlog drop Partition HS_APP_VISITLOG_0701;
這里如果寫(xiě)成alter table HS_APP_VISITLOG drop Partition HS_APP_VISITLOG_0701 update global indexes;也沒(méi)事了
沒(méi)辦法,錯(cuò)誤已經(jīng)造成,只好把這個(gè)表的所有index rebuild了
alter index HSIDX_VISITLOG_VERSION rebuild;
rebuild的時(shí)間是漫長(zhǎng)的....
另外查詢(xún)某個(gè)表的索引狀態(tài)可以用下面這個(gè)語(yǔ)句:
select index_name,status from user_indexes where table_name='HS_APP_VISITLOG';
結(jié)果中valid是有效的,unusable未使用的.
以后要切記了!
原來(lái)一個(gè)表的分區(qū)，由于忘了加上新的月份的分區(qū)，在已經(jīng)有一部分?jǐn)?shù)據(jù)存入MAX區(qū)域時(shí)
強(qiáng)行重建分區(qū)，結(jié)果在向這個(gè)表插入新數(shù)據(jù)時(shí)，提示：
索引'USER.TABLE_AAA_IDX4'或這類(lèi)索引的分區(qū)處于不可用狀態(tài)
查詢(xún)索引狀態(tài)（也可通過(guò)EMC管理終端查看庫(kù)-》方案-》用戶(hù)-》索引）：
select index_name, status from user_indexes;
發(fā)現(xiàn)此索引狀態(tài)為 N/A， 原來(lái)分區(qū)的索引不能通過(guò)用戶(hù)字典查看，
需要用DBA的數(shù)據(jù)字典查看，用sys用戶(hù)登陸，查詢(xún)：
select index_name,partition_name,status from dba_ind_partitions order by status;
原來(lái)是4月份的新插分的數(shù)據(jù)的索引失效了，針對(duì)4月份分區(qū)進(jìn)行重建索引
alter index TABLE_AAA_IDX4 rebuild partition TABLE_AAA_200704;
重建后再次查詢(xún)，索引已經(jīng)生效，插入新數(shù)據(jù)正常。
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)
