分区表尽量不要建主键

原地址：http://blog.itpub.net/16396821/viewspace-679681/

   今天有个项目要做升级，此项目用的是公司新产品，处于研发阶段，数据库设计文档不全，因为需要手动对比一下升级前后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>