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