数据库查询效率问题优化

Question

这是一查询数据库表的ＳＱＬ源码，请教擅长者帮忙优化一下ｉｎ下的语句
select distinct (specreal.speciality_id)
from om_datarole_spec_rela_t specreal,
om_funcrole_relation_t funreal,
OM_SYS_ROLE_T sysrole,
OM_ROLE_PERSON_RELA_T sysreal,
OM_DATAROLE_DISTRICT_RELA_T DISRELA
WHERE specreal.data_role_sno = sysrole.data_role_sno
and sysrole.fun_role_sno = funreal.fun_role_sno
and funreal.f_id = 'B'
AND SYSROLE.DATA_ROLE_SNO = DISRELA.DATA_ROLE_SNO
and sysrole.sys_role_sno = sysreal.sys_role_sno
and sysreal.f_employee_id = '00003502'
and specreal.speciality_id [color=red]in/color))

[b]问题补充：[/b]
这是在Oracle环境下的，业务逻辑可以对应字段！多谢。
[b]问题补充：[/b]
谢了，第一此执行速度于优化前速度，第二次执行却快于优化前第二次执行很多？我再看一下，下午确认一下，多谢了O(∩_∩)O哈哈~

Answer 1

这一次不知行不行，你测试一下吧：
SELECT DISTINCT (specreal.speciality_id)
FROM om_datarole_spec_rela_t specreal,
om_funcrole_relation_t funreal,
om_sys_role_t sysrole,
om_role_person_rela_t sysreal,
om_datarole_district_rela_t disrela
WHERE specreal.data_role_sno = sysrole.data_role_sno
AND sysrole.fun_role_sno = funreal.fun_role_sno
AND funreal.f_id = 'B'
AND sysrole.data_role_sno = disrela.data_role_sno
AND sysrole.sys_role_sno = sysreal.sys_role_sno
AND sysreal.f_employee_id = '00003502'
AND EXISTS (SELECT tt.spec_id
FROM om_datarole_spec_rela_t specreal1,
om_funcrole_relation_t funreal,
om_sys_role_t sysrole,
om_role_person_rela_t sysreal,
sys_specstruct_t tt
WHERE tt.spec_id = specreal.speciality_id
AND sysrole.fun_role_sno = funreal.fun_role_sno
AND funreal.f_id = 'BAD'
AND sysrole.sys_role_sno = sysreal.sys_role_sno
AND sysreal.f_employee_id = '0012'
AND specreal1.speciality_id = tt.spec_id
AND specreal1.data_role_sno = sysrole.data_role_sno)