今天朋友丢来一个SQL,叫我帮忙优化一下.受过落总真传,我瞄了几眼就知道咋回事了
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36
| 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.
附上那俩函数
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109
| CREATE OR REPLACE FUNCTION "GET_GLOBAL_NAME" (IN_code_NO IN VARCHAR2,in_language VARCHAR2) RETURN VARCHAR2 IS v_name VARCHAR2 (100); 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); 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秒,交差了
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59
| 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
|