今天有个项目要做升级，此项目用的是公司新产品，处于研发阶段，数据库设计文档不全，因为需要手动对比一下升级前后2个版本的ddl，发现新版本的ddl中有些分区表建的是主键。如:create table ALARM_RESULT
(
ALARM_RESULT_ID VARCHAR2(50) not null,
TIME NUMBER(20)
)tablespace test_DEFAULT
partition by range (TIME)
(
partition W values less than (5)
tablespace test_DEFAULT,partition P_OTHER values less than (MAXVALUE)
tablespace test_DEFAULT);
此问题已反馈研发，建议取消主键，建唯一性本地索引。分区表尽量不要建主键，因为建主键的同时会建一个唯一性的全局索引，在drop分区表时如果不指定update global indexes则
会使索引失效，导致数据无法入库。
如果非要建主键，要2种方法：
1.应用上drop 分区表时显示指定update global indexes，
2.将主键上的索引建成本地索引
上述2种方法虽然可以实现，但效果都不好。因为当数据量超大时维护索引也是很大的开销。将主键建成本地索引的方法也比较受限。
将主键建成本地索引方法:
建一个包含(分区列、主键列)的唯一性复合索引，然后在这个索引上建主键
create unique index idx_ALARM_RESULT_ID on ALARM_RESULT (ALARM_RESULT_ID,TIME) local; #一定要包含分区列否则会报ORA-14039: 分区列必须构成 UNIQUE 索引的关键字列子集
alter table ALARM_RESULT add primary key (ALARM_RESULT_ID,TIME);
测试分区表建主键导致数据无法入库过程：
create table ALARM_RESULT
(
ALARM_RESULT_ID VARCHAR2(50) not null,
TIME NUMBER(20)
)tablespace test_DEFAULT
partition by range (TIME)
(
partition W values less than (5)
tablespace test_DEFAULT,partition P_OTHER values less than (MAXVALUE)
tablespace test);
alter table ALARM_RESULT add primary key (ALARM_RESULT_ID) using index tablespace test_INDEX;
1.查看索引状态
SQL> select table_name,index_name,status from user_indexes;
TABLE_NAME INDEX_NAME STATUS
—————————— —————————— ——–
ALARM_RESULT SYS_C003358 VALID
SQL>
2.查看各个分区的数据
SQL> select ALARM_RESULT_ID,time from ALARM_RESULT PARTITION(w);
ALARM_RESULT_ID TIME
————————————————– ———————
1 1
SQL>
SQL> select ALARM_RESULT_ID,time from ALARM_RESULT PARTITION(p_other);
ALARM_RESULT_ID TIME
————————————————– ———————
2 10
SQL>
3.drop分区
SQL> ALTER TABLE ALARM_RESULT DROP PARTITION w;
Table altered
SQL>
4.查看索引状态
SQL> select table_name,index_name,status from user_indexes;
TABLE_NAME INDEX_NAME STATUS
—————————— —————————— ——–
ALARM_RESULT SYS_C003358 UNUSABLE
SQL>
5.向表中插入数据时报错
SQL> insert into ALARM_RESULT(ALARM_RESULT_ID,time)values(3,15);
insert into ALARM_RESULT(ALARM_RESULT_ID,time)values(3,15)
ORA-01502: 索引'owner.SYS_C003358'或这类索引的分区处于不可用状态
SQL>
6.此时如果重建一下索引，索引状态会恢复为可用状态，数据也可插入。
SQL> alter index SYS_C003358 rebuild;
Index altered
SQL> select table_name,index_name,status from user_indexes;
TABLE_NAME INDEX_NAME STATUS
—————————— —————————— ——–
ALARM_RESULT SYS_C003358 VALID
SQL> insert into ALARM_RESULT(ALARM_RESULT_ID,time)values(3,15);
1 row inserted
SQL> commit;
Commit complete
SQL>