解決資料庫的索引亂碼問題，先刪除外來鍵約束，再刪除主鍵約束及其索引

根據Oracle的AWR、ADDM發現的一個SQL語句：
SQL語句：
select distinct m.*
  from ING_mmee_info m
 start with m.mmee_id in
            (select distinct m.mmee_id
               from ING_PERSON_EMPLOYEE   h,
                    ING_PERSON_ROLE       r,
                    ING_mmee_info        m,
                    ING_PERSON_ROLE_ASGMT hr,
                    ING_role_mmee        rm
              where h.employee_id = hr.EMPLOYEE_ID
                and hr.role_id = r.role_id
                and r.role_id = rm.role_id
                and rm.mmee_id = m.mmee_id
                and m.bsflag = '0'
                and h.employee_id = '89474644FEDE10A8E0430A15081210A8'
                and r.locked_if = '0'
                and sysdate between rm.start_date and
                    rm.start_date + rm.valid_dates
             union (select distinct m.mmee_id
                     from ING_PERSON_EMPLOYEE       h,
                          ING_PERSON_POSITION       p,
                          ING_PERSON_ROLE           r,
                          ING_mmee_info            m,
                          ING_PERSON_POSITION_ASGMT hp,
                          ING_POSITION_ROLE_ASGMT  pr,
                          ING_role_mmee            rm
                    where h.employee_id = hp.EMPLOYEE_ID
                      and hp.POSITION_ID = p.POSITION_CODE_ID
                      and p.POSITION_CODE_ID = pr.position_id
                      and pr.role_id = r.role_id
                      and r.role_id = rm.role_id
                      and rm.mmee_id = m.mmee_id
                      and h.employee_id = '89474644FEDE10A8E0430A15081210A8'
                      and p.locked_if = '0'
                      and r.locked_if = '0'
                      and m.bsflag = '0'
                      and sysdate between rm.start_date and
                          rm.start_date + rm.valid_dates)
             union (select distinct m.mmee_id
                     from ING_PERSON_EMPLOYEE h,
                          ING_PERSON_mmee     hm,
                          ING_mmee_info      m
                    where hm.mmee_id = m.mmee_id
                      and hm.employee_id = h.employee_id
                      and m.bsflag = '0'
                      and h.employee_id = '89474644FEDE10A8E0430A15081210A8'
                      and sysdate between hm.start_date and
                          hm.start_date + hm.valid_dates))
connect by prior m.upmmee_id = m.mmee_id
 order by m.mmee_order, m.mmee_id

在PLSQL Developer中檢視該SQL的執行計劃：

結果顯示兩個個亂碼：
 

檢視亂碼索引的表：
select * from user_indexes t
where t.table_name in(
'ING_PERSON_EMPLOYEE',
'ING_PERSON_ROLE',
'ING_mmee_INFO',
'ING_PERSON_ROLE_ASGMT',
'ING_ROLE_mmee',
'ING_PERSON_POSITION',
'ING_PERSON_POSITION_ASGMT',
'ING_POSITION_ROLE_ASGMT'
)

 
根據檢視結果，下面的2個表的主鍵索引存在亂碼。
ING_PERSON_POSITION和ING_PERSON_POSITION_ASGMT 有亂碼
這兩個表的主鍵約束為中文字元，在遷移資料時很容易出現對應的索引亂碼。

1、這個ING_PERSON_POSITION_ASGMT表的主鍵沒有對應其他表的外來鍵：
下面的方法解決：
刪除主鍵及對應索引：
ALTER TABLE ING_PERSON_POSITION_ASGMT  drop  CONSTRAINT XPK人員崗位分配表 drop  index
建立主鍵：
ALTER TABLE  ING_PERSON_POSITION_ASGMT add  CONSTRAINT PK_POSITION_ASGMT_ID  PRIMARY KEY (POSITION_ASGMT_ID);

 

2、ING_PERSON_POSITION有其他表對應的外來鍵。
--1 查詢主鍵名所對應  其他表的外來鍵：
select * from user_constraints t
where t.r_constraint_name='XPK崗位資訊表'

對應的外來鍵的表 ING_PERSON_POSITION_MAP ，外來鍵約束名：  FK_ING_PERSON_POSITION_MAP2

--2 刪除外來鍵約束
alter table ING_PERSON_POSITION_MAP
  drop constraint FK_ING_PERSON_POSITION_MAP2
 
--3 刪除主鍵及其索引
ALTER TABLE ING_PERSON_POSITION drop  CONSTRAINT XPK崗位資訊表 drop index

--4 增加主鍵
ALTER TABLE  ING_PERSON_POSITION add  CONSTRAINT PK_POSITION_CODE_ID  PRIMARY KEY (POSITION_CODE_ID);

--5 增加外來鍵
alter table ING_PERSON_POSITION_MAP
  add constraint FK_ING_PERSON_POSITION_MAP2 foreign key (DEVICE_REC_ID)
  references ING_PERSON_POSITION (POSITION_CODE_ID);