使用wm_concat导致的性能问题及解决 wm_concat去重及字符超过4000太长 解决

sql类似如下
select
a.ID as id,
a.STUDENT as student,
WM_CONCAT(SOURCE_ID) as sourceId
from ML_LEARN_SITU a left join ML_LEARN_SITU_REL_SOURCE b on a.ID=b.SITU_ID
where
LESSON_CODE=#{lessonCode,jdbcType=VARCHAR}
group by a.id,a.STUDENT

这里要按名称分组将其他三个值都拼接起来，返回的都是clob类型，导致查询非常慢
而且占用相当大的表空间。。。请教大神，这要如何优化？a.c1等拼接的值很容易超过
4000。。。

解决
select
a.ID as id,
a.STUDENT as student,
LISTAGG(SOURCE_ID,’,’) WITHIN group(ORDER BY SOURCE_ID) as sourceId
from ML_LEARN_SITU a left join ML_LEARN_SITU_REL_SOURCE b on a.ID=b.SITU_ID
where
LESSON_CODE=#{lessonCode,jdbcType=VARCHAR}
group by a.id,a.STUDENT

listAGG 去重
select COUNT(1) as cnt, WM_CONCAT(sourceId) as sourceId
from (select COUNT(1), WM_CONCAT(distinct SOURCE_ID) as sourceId
from ML_LEARN_SITU a
left join ML_LEARN_SITU_REL_SOURCE b on a.id = b.SI