自定义CLOB字段连接函数处理Oracle查询

项目里面有个查询语句，在 A schema 上跑出来的结果是300+万条数据，直接使用 JDBCTemplate.query() + RowMapper的方式逐条处理，速度相当慢。数据大致如下：

处理的逻辑是：按照PERSON_ID，VISIT_OCCURRENCE_ID，CATEGORY 分组，用逗号分隔 CONCEPT_ID保存到一个CLOB 字段，同理对待 EVENT_ID。这些在java层处理速度慢，我估计应该是 round-trip 太多导致的时间非常长吧。

后来换一种思路，就是将查询出来的数据插入到一个临时表，然后通过数据库的 GROUP BY 子句实现，由于有些 PERSON的记录太多，导致 LISTAGG 拼接 CONCEPT_ID 的串都超过了 4000 的限制，不得不使用 XMLAGG。就单个用户 ID：138 ，他的SQL如下（T_CLINICAL_SUM就是前面说的临时表）：

SELECT *
FROM
  (SELECT X.*,
    ROW_NUMBER() over (PARTITION BY X.person_id ORDER BY ROWNUM) ORDINAL
  FROM
    (SELECT PERSON_ID,
      VISIT_OCCURRENCE_ID,
      CATEGORY,
      RTRIM(XMLAGG(XMLELEMENT(E, CONCEPT_ID, ',')).EXTRACT('//text()').GetClobVal(),',') CONCEPTIDS,
      RTRIM(XMLAGG(XMLELEMENT(E, CONCEPT_ID
      || '_'
      || EVENT_ID, ',')).EXTRACT('//text()').GetClobVal(),',') EVENTIDS
    FROM T_CLINICAL_SUM
    GROUP BY PERSON_ID,
      VISIT_OCCURRENCE_ID,
      CATEGORY
    ) X
  ) Y
WHERE Y.ORDINAL <= 20

处理结果如下：

这果然快了很多，三五分钟就能出来。

后来切换到另一个 B 的 SCHEMA， 问题来了，这次查询出来的记录有近 900w 条数据，使用同样的程序跑了两个多小时没结果不说，数据库直接报内存不够了：

也许是 XMLAGG 内部不断地构造XML 结构，比较耗内存。Google 了下，提出了定义一个 IS TABLE OF的 TYPE 类型，和自定义一个函数，把 GROUP BY 后的字段连接成一个 CLOB 字段返回。

TYPE 的定义：

create or replace TYPE t_clob_tab AS TABLE OF VARCHAR2(4000);

自定义函数为：

create or replace FUNCTION tab_to_string (p_clob_tab  IN  t_clob_tab,
                                          p_delimiter     IN  VARCHAR2 DEFAULT ',') RETURN clob IS
  l_string     clob;
BEGIN
  FOR i IN p_clob_tab.FIRST .. p_clob_tab.LAST LOOP
    IF i != p_clob_tab.FIRST THEN
      l_string := l_string || p_delimiter;
    END IF;
    l_string := l_string || p_clob_tab(i);
  END LOOP;
  RETURN l_string;
END tab_to_string;

应用 TYPE 和 自定义函数后的 SQL ：

SELECT * FROM
  (SELECT X.*,
    ROW_NUMBER() over (PARTITION BY X.person_id ORDER BY ROWNUM) ORDINAL
  FROM
    (SELECT PERSON_ID,
      VISIT_OCCURRENCE_ID,
      CATEGORY,
      tab_to_string(CAST(COLLECT(TO_CHAR(CONCEPT_ID)) AS t_clob_tab)) AS CONCEPTIDS,
      tab_to_string(CAST(COLLECT(CONCEPT_ID || '_' || EVENT_ID) AS t_clob_tab)) AS EVENTIDS
    FROM T_CLINICAL_SUM
    GROUP BY PERSON_ID,
      VISIT_OCCURRENCE_ID,
      CATEGORY
    ) X
  ) Y
WHERE Y.ORDINAL <= 20

这个 SQL 执行后，近 900w 的数据跑出来在 20 分钟的样子，还是能接受的。