开发的提一需求要把当前唯一约束从从三个列改为两列，发现实际改动还没那么容易记录一下。
1,首先没有alter constraint或alter table modify constraint修改列的语法，只能删掉重建
SQL> select constraint_name,table_name,column_name from user_cons_columns where constraint_name='UK_PROJECT_SCORE';
CONSTRAINT_NAME TABLE_NAME COLUMN_NAME
------------------------------ ------------------------------ ------------------------------
UK_PROJECT_SCORE ICME_PROJECT_SCORE IC_CODE
UK_PROJECT_SCORE ICME_PROJECT_SCORE SCORE_ACTIVITY_DATE
UK_PROJECT_SCORE ICME_PROJECT_SCORE SUBJECT_ID
SQL> ALTER TABLE ICME.ICME_PROJECT_SCORE
2 DROP CONSTRAINT UK_PROJECT_SCORE;
Table altered.
SQL> ALTER TABLE ICME.ICME_PROJECT_SCORE ADD (
2 CONSTRAINT UK_PROJECT_SCORE
3 UNIQUE (IC_CODE, SUBJECT_ID)
4 ENABLE VALIDATE);
ALTER TABLE ICME.ICME_PROJECT_SCORE ADD (
ERROR at line 1:
ORA-00955: name is already used by an existing object
SQL> select object_name,object_type from user_objects where object_name='UK_PROJECT_SCORE';
OBJECT_NAME OBJECT_TYPE
----------------------------------------------------------------- -------------------
UK_PROJECT_SCORE INDEX
SQL> select index_name,UNIQUENESS,index_type,status from user_indexes where index_name='UK_PROJECT_SCORE';
INDEX_NAME UNIQUENES INDEX_TYPE STATUS
------------------------------ --------- --------------------------- --------
UK_PROJECT_SCORE UNIQUE NORMAL VALID
SQL> select table_name,column_name from user_ind_columns where index_name='UK_PROJECT_SCORE';
TABLE_NAME COLUMN_NAME
------------------------------ ------------------------------
ICME_PROJECT_SCORE IC_CODE
ICME_PROJECT_SCORE SCORE_ACTIVITY_DATE
ICME_PROJECT_SCORE SUBJECT_ID
Tip:建唯一约束时，默认会自动在列上创建一个唯一索引，通常删除约束时会自动删除索引，但发现并不完全是这样，这次唯一索引就没有删除。
2，唯一索引问题一会儿再处理，先临时改名，创建新唯一约束
SQL> alter index UK_PROJECT_SCORE rename to IDX_ICME_PRO_SC_ICDATSUB;
Index altered.
SQL> ALTER TABLE ICME.ICME_PROJECT_SCORE ADD (
2 CONSTRAINT UK_PROJECT_SCORE
3 UNIQUE (IC_CODE, SUBJECT_ID)
4 ENABLE VALIDATE);
CONSTRAINT UK_PROJECT_SCORE
ERROR at line 2:
ORA-02299: cannot validate (ICME.UK_PROJECT_SCORE) - duplicate keys found
SQL> ALTER TABLE ICME.ICME_PROJECT_SCORE ADD (
2 CONSTRAINT UK_PROJECT_SCORE
3 UNIQUE (IC_CODE, SUBJECT_ID)
4 ENABLE NOVALIDATE);
CONSTRAINT UK_PROJECT_SCORE
ERROR at line 2:
ORA-02299: cannot validate (ICME.UK_PROJECT_SCORE) - duplicate keys found
Tip:无论是否对已存在数据验证这种方法创建时都会失败，就是因为默认唯一索引创建失败的问题，因为原来唯一建立在三个字段，两个字段确实存在不唯一的情况，也和开发的确认可以只对新数据验证
3，解决这个问题就是先创建一个非唯一索引，在创建唯一约束时指定索引并用ENABLE NOVALIDATE(相关可以看http://www.anbob.com/?p=646)，加速索引创建用到了并行,nologing
SQL> alter session enable parallel ddl;
Session altered.
SQL> create index idx_project_score_uk on icme_project_score(ic_code,subject_id) online parallel 8 nologging;
Index created.
SQL> ALTER TABLE ICME.ICME_PROJECT_SCORE ADD (
2 CONSTRAINT UK_PROJECT_SCORE
3 UNIQUE (IC_CODE, SUBJECT_ID) using index idx_project_score_uk
4 ENABLE NOVALIDATE);
Table altered.
tip: 1 seconds
SQL> select index_name,logging,UNIQUENESS,degree,STATUS from user_indexes where index_name='IDX_PROJECT_SCORE_UK';
INDEX_NAME LOG UNIQUENES DEGREE STATUS
------------------------------ --- --------- ---------------------------------------- --------
IDX_PROJECT_SCORE_UK NO NONUNIQUE 8 VALID
SQL> alter index IDX_PROJECT_SCORE_UK logging;
Index altered.
SQL> alter index IDX_PROJECT_SCORE_UK noparallel;
Index altered.
SQL> select index_name,logging,UNIQUENESS,degree,STATUS,generated from user_indexes where index_name='IDX_PROJECT_SCORE_UK';
INDEX_NAME LOG UNIQUENES DEGREE STATUS G
------------------------------ --- --------- ---------------------------------------- -------- -
IDX_PROJECT_SCORE_UK YES NONUNIQUE 1 VALID N
4,再来解决上面唯一索引的问题，唯一索引也有唯一约束的作用，所以现在两个唯一显然冲突不符合业务.no-unique index convert unique index，no way!,只能重建或在线重定义
SQL> drop index IDX_ICME_PRO_SC_ICDATSUB;
Index dropped.
还是原来的方法创建一个普通索引，步骤不再重复
note:
并行创建的索引extents会更多,所以在一致读时IO就会相对多些
Related Posts:
No related posts found!
--转自
当前位置：
论坛首页 »
自由讨论区 »
MySQL, Oracle及数据库讨论区
»
发起投票
技术讨论
加好友
发消息
2014-12-22 11:48:22 |
赞(
操作
