数据库查询效率问题优化

这是一查询数据库表的ＳＱＬ源码，请教擅长者帮忙优化一下ｉｎ下的语句
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哈哈~

这一次不知行不行，你测试一下吧：
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)

没有测试环境，我是ORACLE 下将IN 换成了EXISTS的写法，另外减少了一层IN的过滤，感觉好像有点多余，所以去了一层。如果能明白你的业务逻辑，估计更好优化，这整个语句觉得应该有很大的优化空间，主要是没有测试环境，不好测试。

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 sst.oper_code
FROM sys_specstruct_t tt
WHERE tt.spec_id = specreal.speciality_id
AND EXISTS (SELECT 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
WHERE specreal.speciality_id = tt.spec_id
AND specreal.data_role_sno = sysrole.data_role_sno
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')))

上面错了，重新改了一下：
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 sys_specstruct_t tt
WHERE tt.spec_id = specreal.speciality_id
AND (SELECT st.oper_code
FROM sys_specstruct_t st
WHERE st.oper_code = tt.oper_code
AND EXISTS (SELECT 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
WHERE specreal.speciality_id = tt.spec_id
AND specreal.data_role_sno = sysrole.data_role_sno
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')))

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 sys_specstruct_t tt
WHERE tt.spec_id = specreal.speciality_id
AND (SELECT st.oper_code
FROM sys_specstruct_t st
WHERE st.oper_code = tt.oper_code
AND EXISTS (SELECT 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
WHERE specreal.speciality_id = st.spec_id
AND specreal.data_role_sno = sysrole.data_role_sno
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')))