【DM版本】: 8
【操作系统】：linux
【CPU】:
【问题描述】：
加上注释 /+no_use_cvt_var */ 执行计划中SSEK2没有了，反而执行更快。 为什么执行计划中会有 ssek2，是什么原理？
执行SQL如下
SELECT
r.id AS region_id,
r.region_no,
r.region_name,
h.id house_id ,
h.address,
h.house_type,
CASE
WHEN h.construct_area > 0 THEN h.construct_area
ELSE NVL(h.pre_construct_area, 0)
END AS construct_area,
h.deposit_no AS deposit_no,
h.mix_sign,
h.mix_house_prop,
CASE
WHEN h.mix_sign = 'Y' THEN m.deposit_names
ELSE h.deposit_name
END AS deposit_name,
CASE
WHEN h.mix_sign = 'Y' THEN m.house_props
ELSE h.house_prop
END AS house_prop,
h.due_amount AS due_amount,
s.owner_names owner_name,
s.cert_nos cert_no,
h.transmit_state,
h.busi_node,
s.owner_no,
s.contract_No,
msa.balance,
h.deposit_sign,
CASE
WHEN h.deposit_sign = 'X' THEN '1010'
WHEN h.deposit_sign = 'Y' THEN '1012'
ELSE '1011'
END AS busiType,
CASE
WHEN to_number(b.build_stage) > 1 THEN 'Y'
ELSE 'N'
END AS allowDep,
h.tags
FROM
BVRFIS_YT23_0104.mf_region r
INNER JOIN BVRFIS_YT23_0104.mf_build b ON
b.region_id = r.id
INNER JOIN BVRFIS_YT23_0104.mf_house h ON
b.id = h.build_id AND h.deposit_Sign IN ('X') AND h.busi_node = '0' AND h.node_status = '3' AND h.VERSION_STATUS = 0
LEFT JOIN BVRFIS_YT23_0104.mf_ownership s ON
s.VERSION_STATUS = 0
AND s.main_owner = 'Y'
AND s.busi_node = '0'
AND s.node_status = '3'
AND h.id = s.house_id
LEFT JOIN (
SELECT
mix.house_id,
listagg(dm.deposit_name, ',') WITHIN GROUP (
ORDER BY dm.deposit_name) deposit_names,
listagg(mix.house_prop, ',') WITHIN GROUP (
ORDER BY mix.house_prop) AS house_props
FROM
BVRFIS_YT23_0104.mf_mix_house mix
LEFT JOIN BVRFIS_YT23_0104.mf_dep_mode dm ON
dm.deposit_no = mix.deposit_no
AND dm.VERSION_STATUS = 0
WHERE
mix.VERSION_STATUS = 0
GROUP BY
mix.house_id ) m ON
m.house_id = h.id
LEFT JOIN BVRFIS_YT23_0104.MF_SUB_ACCOUNT msa ON
h.id = msa.key_id
AND msa.VERSION_STATUS = 0
AND msa.account_type = '10'
AND msa.status = '0'
AND (msa.fund_Type = '02'
OR (msa.fund_type = '03'
AND msa.is_auto_close = 'Y'))
WHERE
r.busi_node = '0'
AND r.node_status = '3'
AND r.VERSION_STATUS = 0
AND b.VERSION_STATUS = 0
AND b.busi_node = '0'
AND b.node_status = '3'
AND r.division_code LIKE '3706%'
LIMIT 0,
10

执行慢的计划。3.6秒 ssek2

执行快的计划，0.6秒，执行计划house表没有ssek2