【DM版本】:8
【操作系统】：centos7
【CPU】:x86
【问题描述】*：
语句：
SELECT
e.locationkey ,
e.physical_subsystem_key ,
e.SUBSYSTEMKEY ,
fn_alarm_desc('[DataPointValue]', t.param_list) ValueDesc,
t.alarm_id ,
t.sourcetime ,
t.description ,
t.severity ,
t.type ,
t.state ,
t.entitykey ,
t.comments ,
t.asset_name ,
t.operator_name ,
MMS_STATE_TYPE ,
t.close_time
from
alarm t, --6065921 分区表 SOURCETIME月分区
entity e --1056718
where
t.entitykey =e.pkey
and locationkey in (84, 184)
and e.SUBSYSTEMKEY in (3, 5, 8, 14, 32, 40, 45, 49, 53, 75, 76, 77, 158, 292, 300, 334, 342, 349, 351)
and t.severity in (1, 2, 3)
and t.SOURCETIME >= '2023-08-23 17:38:42'
AND t.SOURCETIME < '2023-09-23 17:38:42'
and
(
t.description like '%开门%'
or fn_alarm_desc('[DataPointValue]', t.param_list) like '%开门%'
)
order by
sourcetime desc
limit 10000;

执行计划：

1 #NSET2: [2725, 10000, 626]
2 #PRJT2: [2725, 10000, 626]; exp_num(16), is_atom(FALSE)
3 #SORT3: [2725, 10000, 626]; key_num(1), is_distinct(FALSE), top_flag(1), is_adaptive(0)
4 #UNION FOR OR2: [2716, 43351, 626]; key_num(2), outer_join(-)
5 #HASH2 INNER JOIN: [1353, 21675, 626]; KEY_NUM(1); KEY(E.PKEY=T.ENTITYKEY) KEY_NULL_EQU(0)
6 #HASH RIGHT SEMI JOIN2: [24, 21676, 132]; n_keys(1) KEY(DMTEMPVIEW_889222948.colname=E.SUBSYSTEMKEY) KEY_NULL_EQU(0)
7 #CONST VALUE LIST: [1, 19, 30]; row_num(19), col_num(1),
8 #NEST LOOP INDEX JOIN2: [24, 21676, 132]
9 #CONST VALUE LIST: [1, 2, 30]; row_num(2), col_num(1),
10 #BLKUP2: [24, 10838, 132]; INDX_ENTITY_LOCATIONKEY(E)
11 #SSEK2: [24, 10838, 132]; scan_type(ASC), INDX_ENTITY_LOCATIONKEY(ENTITY as E), scan_range[DMTEMPVIEW_889222946.colname,DMTEMPVIEW_889222946.colname]
12 #HASH RIGHT SEMI JOIN2: [1316, 60299, 494]; n_keys(1) KEY(DMTEMPVIEW_889222945.colname=T.SEVERITY) KEY_NULL_EQU(0)
13 #CONST VALUE LIST: [1, 3, 30]; row_num(3), col_num(1),
14 #PARALLEL: [1308, 60356, 494]; scan_type(GE_L), key_num(0, 1, 1), simple(0)
15 #SLCT2: [1308, 60356, 494]; exp11 > 0
16 #SLCT2: [1308, 6065921, 494]; [(T.SOURCETIME >= var6 AND T.SOURCETIME < var7)]
17 #CSCN2: [1308, 6065921, 494]; INDEX33560009(ALARM as T)
18 #HASH2 INNER JOIN: [1353, 21675, 626]; KEY_NUM(1); KEY(E.PKEY=T.ENTITYKEY) KEY_NULL_EQU(0)
19 #HASH RIGHT SEMI JOIN2: [24, 21676, 132]; n_keys(1) KEY(DMTEMPVIEW_889222949.colname=E.SUBSYSTEMKEY) KEY_NULL_EQU(0)
20 #CONST VALUE LIST: [1, 19, 30]; row_num(19), col_num(1),
21 #NEST LOOP INDEX JOIN2: [24, 21676, 132]
22 #CONST VALUE LIST: [1, 2, 30]; row_num(2), col_num(1),
23 #BLKUP2: [24, 10838, 132]; INDX_ENTITY_LOCATIONKEY(E)
24 #SSEK2: [24, 10838, 132]; scan_type(ASC), INDX_ENTITY_LOCATIONKEY(ENTITY as E), scan_range[DMTEMPVIEW_889222946.colname,DMTEMPVIEW_889222946.colname]
25 #HASH RIGHT SEMI JOIN2: [1316, 60299, 494]; n_keys(1) KEY(DMTEMPVIEW_889222947.colname=T.SEVERITY) KEY_NULL_EQU(0)
26 #CONST VALUE LIST: [1, 3, 30]; row_num(3), col_num(1),
27 #PARALLEL: [1308, 60356, 494]; scan_type(GE_L), key_num(0, 1, 1), simple(0)
28 #SLCT2: [1308, 60356, 494]; exp11 LIKE '%开门%'
29 #SLCT2: [1308, 6065921, 494]; [(T.SOURCETIME >= var8 AND T.SOURCETIME < var9)]
30 #CSCN2: [1308, 6065921, 494]; INDEX33560009(ALARM as T)

et信息：

NSET2 1 0% 31 1 2 0 0 0 0 NULL NULL
DLCK 2 0% 30 0 1 0 0 0 0 NULL NULL
CONSTV 3 0% 29 20 2 0 0 0 0 NULL NULL
CONSTV 4 0% 28 26 2 0 0 0 0 NULL NULL
CONSTV 6 0% 26 7 2 0 0 0 0 NULL NULL
CONSTV 6 0% 26 22 2 0 0 0 0 NULL NULL
CONSTV 10 0% 25 13 2 0 0 0 0 NULL NULL
CONSTV 16 0% 24 9 2 0 0 0 0 NULL NULL
UNION_OR2 108 0% 23 4 219 0 0 0 0 NULL NULL
IJI2 400 0% 22 21 151 0 0 0 0 NULL NULL
IJI2 537 0% 21 8 151 0 0 0 0 NULL NULL
PLL 1189 0% 20 14 7629 0 0 0 0 NULL NULL
HRS2 1203 0% 19 19 150 225 0 18 1 NULL NULL
SSEK2 1634 0% 18 24 75 0 0 0 0 NULL NULL
SORT3 1829 0% 17 3 110 40175 0 0 0 NULL NULL
SSEK2 2735 0% 16 11 75 0 0 0 0 NULL NULL
HRS2 3350 0.01% 15 6 150 225 0 18 1 NULL NULL
HRS2 7006 0.01% 14 12 7630 225 0 3 0 NULL NULL
PLL 7316 0.01% 13 27 6763 0 0 0 0 NULL NULL
PRJT2 9734 0.02% 12 2 2 0 0 0 0 NULL NULL
HI3 11045 0.02% 11 5 3997 14639 0 21258 188 NULL NULL
SLCT2 17797 0.03% 10 16 11952 0 0 0 0 NULL NULL
HRS2 30942 0.05% 9 25 6764 225 0 3 0 NULL NULL
HI3 32343 0.06% 8 18 3456 14639 0 21258 188 NULL NULL
BLKUP2 32934 0.06% 7 23 150 0 0 0 0 NULL NULL
SLCT2 45133 0.08% 6 29 11952 0 0 0 0 NULL NULL
BLKUP2 47738 0.08% 5 10 150 0 0 0 0 NULL NULL
SLCT2 115966 0.20% 4 15 7954 0 0 0 0 NULL NULL
CSCN2 2236307 3.93% 3 17 7813 0 0 0 0 NULL NULL
CSCN2 3512134 6.17% 2 30 7813 0 0 0 0 NULL NULL
SLCT2 50845772 89.26% 1 28 7521 0 0 0 0 NULL NULL

请教一下各位大佬，该怎么优化，统计信息已经收集。