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

本文永久地址
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,
       HE.EMPID,
       ESS.AR_DATE_STR,
       GET_GLOBAL_NAME(ESS.STATUS_CODE, 'zh') STATUS_CODE,
       GET_GLOBAL_NAME(ESS.ITEM_NO, 'zh') ITEM_NO,
       ESS.REMARK,
       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,
       HE.EMPID,
       HE.LOCAL_NAME,
       ESS.ITEM_NO ITEM_NO_NO,
       ESS.PERSON_ID,
       coalesce(department.org_name_local, info.org_name_local, '') DEPT_NAME,
       g1.content 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,
       HE.EMPID,
       ESS.AR_DATE_STR,
       g2.content STATUS_CODE,
       g3.content ITEM_NO,
       ESS.REMARK,
       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', '14015951', '141445', '141443', '190000514')
   AND HE.DEPTNO in (select deptno from t1)
 ORDER BY ESS.AR_DATE_STR ASC, ESS.CREATE_DATE DESC, HE.DEPTNO, HE.EMPID