ANBOB™

开发的提一需求要把当前唯一约束从从三个列改为两列，发现实际改动还没那么容易记录一下。

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就会相对多些

打赏

微信扫一扫，打赏作者吧～