今天朋友丢来一个SQL,叫我帮忙优化一下.受过落总真传,我瞄了几眼就知道咋回事了
1 | SELECT ESS.PK_NO, |
我让他先执行一下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
109CREATE 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秒,交差了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
59with 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