自定义函数改表关联优化一例

前端之家收集整理的这篇文章主要介绍了自定义函数改表关联优化一例前端之家小编觉得挺不错的,现在分享给大家,也给大家做个参考。

本文永久地址
http://fuxkdb.com/2017/12/28/%E8%87%AA%E5%AE%9A%E4%B9%89%E5%87%BD%E6%95%B0%E6%94%B9%E8%A1%A8%E5%85%B3%E8%81%94%E4%BC%98%E5%8C%96%E4%B8%80%E4%BE%8B/

今天朋友丢来一个sql,叫我帮忙优化一下.受过落总真传,我瞄了几眼就知道咋回事了

SELECT ESS.PK_NO,HE.EMPID,HE.LOCAL_NAME,ESS.ITEM_NO ITEM_NO_NO,ESS.PERSON_ID,GET_DEPT_NAME(HE.DEPTNO,'zh') DEPT_NAME,GET_GLOBAL_NAME(ESS.ITEM_NO,'zh') ITEM_NAME,ESS.AR_DATE_STR,TO_CHAR(ESS.FROM_TIME,'HH24:MI') FROM_TIME,TO_CHAR(ESS.TO_TIME,'HH24:MI') TO_TIME,ESS.QUANTITY,ESS.REMARK,GET_GLOBAL_NAME(ESS.STATUS_CODE,'zh') STATUS_CODE,'zh') ITEM_NO,ESS.LOCK_YN FROM AR_DETAIL_HYOSUNG_JX ESS,HR_EMPLOYEE HE WHERE ESS.PERSON_ID = HE.PERSON_ID AND ESS.PERSON_ID NOT LIKE '111111%' AND ESS.ITEM_NO IN ('141454','14015951','141445','141443','190000514') AND EXISTS (SELECT B1.DEPTID FROM HR_DEPARTMENT B1 WHERE B1.DEPTNO = HE.DEPTNO START WITH B1.DEPTNO in (SELECT HRD.DEPTID FROM HR_DEPARTMENT HRD WHERE HRD.MANAGER_EMP_ID = '11111117') CONNECT BY PRIOR B1.DEPTNO = B1.PARENT_DEPT_NO UNION SELECT AR_SUPERVISOR_INFO.DEPTNO FROM AR_SUPERVISOR_INFO WHERE AR_SUPERVISOR_INFO.DEPTNO = HE.DEPTNO AND AR_SUPERVISOR_INFO.PERSON_ID = '11111117') ORDER BY ESS.AR_DATE_STR ASC,ESS.CREATE_DATE DESC,HE.DEPTNO,HE.EMPID

我让他先执行以下sql 看看几秒,他说6秒
OK再把select 里面那俩函数GET_DEPT_NAME,GET_GLOBAL_NAME 注释掉拍一下,他说1.5秒
那这个sql就是慢在 这俩自定义函数上呗,这个查询每返回一行,这函数就要执行一次
那么函数可以改成 标量,标量可以改成 letf join.

附上那俩函数

CREATE OR REPLACE FUNCTION "GET_GLOBAL_NAME" (IN_code_NO IN VARCHAR2,in_language VARCHAR2) RETURN VARCHAR2 IS v_name VARCHAR2 (100);

/****************************************************************************** NAME : -- GET_GLOBAL_NAME PURPOSE : -- 依据传入项目O和语言参数查找国际化名称 IMPUT : -- IN_code_NO code_no,in_language 语言 OUTPUT : -- none Author : -- hj CreateDate : -- 2012-3-2 ******************************************************************************/

BEGIN BEGIN SELECT a.CONTENT INTO v_name FROM sy_global_name a WHERE a.no = IN_code_NO AND a.LANGUAGE = in_language ;
   EXCEPTION
      WHEN NO_DATA_FOUND
      THEN
         v_name := '';
      WHEN OTHERS
      THEN
         RAISE;
   END;

   RETURN v_name;
END GET_GLOBAL_NAME;


CREATE OR REPLACE FUNCTION "GET_DEPT_NAME"(in_deptno IN VARCHAR2,in_language VARCHAR2) RETURN VARCHAR2 IS out_department VARCHAR2(200);

  /****************************************************************************** NAME : -- GET_DEPT_NAME PURPOSE : -- 依据法人参数得到此部门编号的部门名称 IMPUT : -- in_deptno 部门名称 IN_CPNY_ID 公司D OUTPUT : -- none Author : -- system CreateDate : -- 2011-12-29 14:57:33 UpdateDate : -- 函数更改信息(包括作者、时间、更改内容等) ******************************************************************************/

BEGIN BEGIN IF in_language = 'zh' then SELECT a.org_name_local INTO out_department FROM hr_department a WHERE a.deptno = in_deptno and rownum = 1;
    ELSIF in_language = 'en' then
      SELECT a.org_name_eng INTO out_department FROM hr_department a WHERE a.deptno = in_deptno and rownum = 1;
    else
      SELECT a.org_name_ko INTO out_department FROM hr_department a WHERE a.deptno = in_deptno and rownum = 1;
    end if;
  EXCEPTION
    WHEN NO_DATA_FOUND THEN
      out_department := '0';
  END;

  IF out_department = '0' THEN
    BEGIN IF in_language = 'zh' then SELECT a.org_name_local INTO out_department FROM org_info a WHERE a.deptno = in_deptno and rownum = 1;
      ELSIF in_language = 'en' then
        SELECT a.org_name_eng INTO out_department FROM org_info a WHERE a.deptno = in_deptno and rownum = 1;
      ELSE
        SELECT a.org_name_ko INTO out_department FROM org_info a WHERE a.deptno = in_deptno and rownum = 1;
      END IF;
    EXCEPTION
      WHEN NO_DATA_FOUND THEN
        out_department := '';
    END;
  END IF;
  RETURN out_department;
END get_dept_name;

      SELECT a.org_name_local INTO out_department FROM org_info a WHERE a.deptno = in_deptno and rownum = 1;

最后改完,0.4秒,交差了

with t1 as
 (select deptno from (SELECT B1.DEPTNO DEPTNO FROM HR_DEPARTMENT B1 START WITH B1.DEPTNO in (SELECT HRD.DEPTID FROM HR_DEPARTMENT HRD WHERE HRD.MANAGER_EMP_ID = '11111117') CONNECT BY PRIOR B1.DEPTNO = B1.PARENT_DEPT_NO UNION SELECT AR_SUPERVISOR_INFO.DEPTNO DEPTNO FROM AR_SUPERVISOR_INFO where AR_SUPERVISOR_INFO.PERSON_ID = '11111117')),global as (select a.CONTENT,a.no from sy_global_name a where a.language = 'zh'),department as (select max(org_name_local) org_name_local,deptno from hr_department group by deptno),info as (select max(org_name_local) org_name_local,deptno from org_info group by deptno) SELECT ESS.PK_NO,coalesce(department.org_name_local,info.org_name_local,'') DEPT_NAME,g1.content ITEM_NAME,g2.content STATUS_CODE,g3.content ITEM_NO,ESS.LOCK_YN FROM AR_DETAIL_HYOSUNG_JX ESS inner join HR_EMPLOYEE HE on ESS.PERSON_ID = HE.PERSON_ID left join global g1 on ess.ITEM_NO = g1.no left join global g2 on ESS.STATUS_CODE = g2.no left join global g3 on ESS.ITEM_NO = g3.no left join department on HE.DEPTNO = department.deptno left join info on HE.DEPTNO = info.deptno where ESS.PERSON_ID NOT LIKE '111111%' AND ESS.ITEM_NO IN ('141454','190000514') AND HE.DEPTNO in (select deptno from t1) ORDER BY ESS.AR_DATE_STR ASC,HE.EMPID

猜你在找的Oracle相关文章