解决数据库的索引乱码问题，先删除外键约束，再删除主键约束及其索引

根据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);

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/12778571/viewspace-694789/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/12778571/viewspace-694789/