iteye_7630
2009-06-05 09:36 阅读 217
已采纳

数据库查询效率问题优化

这是一查询数据库表的SQL源码,请教擅长者帮忙优化一下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 specreal.speciality_id [color=red]in/color))

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

  • 点赞
  • 写回答
  • 关注问题
  • 收藏
  • 复制链接分享

4条回答 默认 最新

  • 已采纳
    chengxing6666 chengxing6666 2009-06-05 10:30

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

    点赞 评论 复制链接分享
  • chengxing6666 chengxing6666 2009-06-05 10:06

    没有测试环境,我是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')))

    点赞 评论 复制链接分享
  • chengxing6666 chengxing6666 2009-06-05 10:13

    上面错了,重新改了一下:
    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')))

    点赞 评论 复制链接分享
  • chengxing6666 chengxing6666 2009-06-05 10:15

    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')))

    点赞 评论 复制链接分享

相关推荐