某分区表T,按日分区,分区字段为RQ,有个B字段,且建了local索引,但是查询不同的日期段时,有些日期段是用到了B字段的索引,有些没有,查询语句是这么写的:
select * from T where rq>='20111001' and rq<='20111031' and B='abc'; --这个是不走B字段的索引
select * from T where rq>='20110901' and rq<='20110930' and B='abc'; --这个是走B字段的索引
找到问题了,原来是第三方开发人员在修复数据时,采用了exchange
ALTER TABLE T EXCHANGE PARTITION P20111111 WITH TABLE t_tmp;
查一下user_ind_partitions
select partition_name, status from user_ind_partitions where index_name = 'IDX_T_B' and partition_name='P20111111 ';
可以看到status为UNUSABLE
在分析某个分区或者索引时,提示ora-01502错误
set autotrace traceonly exp
select * from T where rq>='20111001' and rq<='20111031' and B='abc';
select * from T where rq>='20110901' and rq<='20110930' and B='abc';