分区索引失效&这样的索引是否应该存在在线等!

muou2008 2009-07-22 12:12:32

-- Create table
create table T_LOG_AUTOKEYTRACE
(
LOGDATE DATE not null,
PARTID CHAR(4) not null,
CALLERNO VARCHAR2(20),
ACCEPTPHONE VARCHAR2(20),
CALLEENO VARCHAR2(20) not null,
CITYID VARCHAR2(10),
CITYCODE VARCHAR2(5) default '00' not null,
USERGRADE NUMBER(4),
USERTYPE NUMBER(4),
OPERATETRACE VARCHAR2(30),
OPERATERESULT NUMBER(4),
PRESSDATE DATE,
KEYTRACE VARCHAR2(10),
IVRDESC VARCHAR2(10),
EXITDATE DATE
)
partition by range (PARTID)
(
partition P01_1 values less than ('0111')
tablespace SERVICE_LOG_DAT
pctfree 5
pctused 80
initrans 1
maxtrans 255
storage
(
initial 4M
next 1M
minextents 1
maxextents unlimited
pctincrease 0
),
partition P01_2 values less than ('0121')
tablespace SERVICE_LOG_DAT
pctfree 5
pctused 80
initrans 1
maxtrans 255
storage
(
initial 4M
next 1M
minextents 1
maxextents unlimited
pctincrease 0
),
partition P01_3 values less than ('0132')
tablespace SERVICE_LOG_DAT
pctfree 5
pctused 80
initrans 1
maxtrans 255
storage
(
initial 4M
next 1M
minextents 1
maxextents unlimited
pctincrease 0
),
partition P02_1 values less than ('0211')
tablespace SERVICE_LOG_DAT
pctfree 5
pctused 80
initrans 1
maxtrans 255
storage
(
initial 4M
next 1M
minextents 1
maxextents unlimited
pctincrease 0
),
partition P02_2 values less than ('0221')
tablespace SERVICE_LOG_DAT
pctfree 5
pctused 80
initrans 1
maxtrans 255
storage
(
initial 4M
next 1M
minextents 1
maxextents unlimited
pctincrease 0
),
partition P02_3 values less than ('0232')
tablespace SERVICE_LOG_DAT
pctfree 5
pctused 80
initrans 1
maxtrans 255
storage
(
initial 4M
next 1M
minextents 1
maxextents unlimited
pctincrease 0
),
partition P03_1 values less than ('0311')
tablespace SERVICE_LOG_DAT
pctfree 5
pctused 80
initrans 1
maxtrans 255
storage
(
initial 4M
next 1M
minextents 1
maxextents unlimited
pctincrease 0
),
partition P03_2 values less than ('0321')
tablespace SERVICE_LOG_DAT
pctfree 5
pctused 80
initrans 1
maxtrans 255
storage
(
initial 4M
next 1M
minextents 1
maxextents unlimited
pctincrease 0
),
partition P03_3 values less than ('0332')
tablespace SERVICE_LOG_DAT
pctfree 5
pctused 80
initrans 1
maxtrans 255
storage
(
initial 4M
next 1M
minextents 1
maxextents unlimited
pctincrease 0
),
........

partition P12_1 values less than ('1211')
tablespace SERVICE_LOG_DAT
pctfree 5
pctused 80
initrans 1
maxtrans 255
storage
(
initial 4M
next 1M
minextents 1
maxextents unlimited
pctincrease 0
),
partition P12_2 values less than ('1221')
tablespace SERVICE_LOG_DAT
pctfree 5
pctused 80
initrans 1
maxtrans 255
storage
(
initial 4M
next 1M
minextents 1
maxextents unlimited
pctincrease 0
),
partition P12_3 values less than (MAXVALUE)
tablespace SERVICE_LOG_DAT
pctfree 5
pctused 80
initrans 1
maxtrans 255
storage
(
initial 4M
next 1M
minextents 1
maxextents unlimited
pctincrease 0
)
);
-- Create/Recreate indexes
create index IDX_AUTOKEYTRACE on T_LOG_AUTOKEYTRACE (LOGDATE)
tablespace SERVICE_MAIN_DAT
pctfree 10
initrans 2
maxtrans 255
storage
(
initial 1M
next 1M
minextents 1
maxextents unlimited
pctincrease 0
);
问题一：已经发现了2次莫名奇妙的索引失效，导致数据无法插入。想找出导致索引失效的原因，请高手分析下分区表索引失效的原因都有那些,特别是些细小的原因。
问题二：此表2000多万数据量，大概每分钟有100条数据插入。此索引的维护会不会代价很高。
谢谢！

...全文