本文永久地址
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