Oracle 字串彙總 (String Aggregation) 三大技術方案效能實測

測試環境與資料

DROP TABLE t_collect PURGE;
CREATE TABLE t_collect AS
SELECT MOD(ROWNUM, 30) AS flag,
       LPAD(DBMS_RANDOM.STRING('l', 3), 4, ' ') AS val
FROM dual CONNECT BY ROWNUM <= 10000;

• 10,000 筆測試資料，30 組 flag，每組約 333 筆。
• 使用 SAMPLE(10) 抽樣 10% 加速測試 (實際生產移除)。

方案一：COLLECT 函數 + 自訂類型 (Oracle 10g+)

-- 需預先建立集合類型
CREATE OR REPLACE TYPE my_tk_str_tab_type IS TABLE OF VARCHAR2(100);

-- 輔助封裝函數
CREATE OR REPLACE PACKAGE my_tk AS
    FUNCTION f_list2str(p_list my_tk_str_tab_type,
                        p_separator VARCHAR2 DEFAULT ',',
                        p_sort INTEGER DEFAULT 1) RETURN VARCHAR2;
    FUNCTION f_sort_list(p_list my_tk_str_tab_type) RETURN my_tk_str_tab_type;
END my_tk;
/

-- 查詢
SELECT flag,
       my_tk.f_list2str(CAST(COLLECT(TRIM(val)) AS my_tk_str_tab_type)) AS ename
FROM t_collect SAMPLE(10)
GROUP BY flag
ORDER BY 1;

• COLLECT 將群組內值聚合為嵌套表。
• 需 CAST 轉型為預定義集合類型。
• 封裝函數處理排序、分隔符串連。

方案二：SYS_CONNECT_BY_PATH + 分析函數

SELECT t1.flag,
       SUBSTR(MAX(SYS_CONNECT_BY_PATH(t1.val, ',')), 2) AS q
FROM (
    SELECT a.flag,
           TRIM(a.val) AS val,
           ROW_NUMBER() OVER (PARTITION BY a.flag ORDER BY a.val) AS rn
    FROM t_collect SAMPLE(10) a
) t1
START WITH t1.rn = 1
CONNECT BY t1.flag = PRIOR t1.flag
       AND t1.rn - 1 = PRIOR t1.rn
GROUP BY t1.flag
ORDER BY 1;

• 利用階層查詢串連字串，SYS_CONNECT_BY_PATH 累積路徑。
• ROW_NUMBER 產生序號模擬遞迴鏈結。
• MAX 取最長路徑 (完整串連)，SUBSTR(...,2) 移除前導分隔符。

方案三：使用者自訂彙總函數 (UDF, Oracle 9i+)

-- 1. 定義物件型別實作 ODCIAggregate 介面
CREATE OR REPLACE TYPE t_string_agg AS OBJECT (
    g_string VARCHAR2(32767),
    STATIC FUNCTION ODCIAggregateInitialize(sctx IN OUT t_string_agg) RETURN NUMBER,
    MEMBER FUNCTION ODCIAggregateIterate(self IN OUT t_string_agg, value IN VARCHAR2) RETURN NUMBER,
    MEMBER FUNCTION ODCIAggregateTerminate(self IN t_string_agg, returnValue OUT VARCHAR2, flags IN NUMBER) RETURN NUMBER,
    MEMBER FUNCTION ODCIAggregateMerge(self IN OUT t_string_agg, ctx2 IN t_string_agg) RETURN NUMBER
);
/

CREATE OR REPLACE TYPE BODY t_string_agg IS
    STATIC FUNCTION ODCIAggregateInitialize(sctx IN OUT t_string_agg) RETURN NUMBER IS
    BEGIN
        sctx := t_string_agg(NULL);
        RETURN ODCIConst.Success;
    END;

    MEMBER FUNCTION ODCIAggregateIterate(self IN OUT t_string_agg, value IN VARCHAR2) RETURN NUMBER IS
    BEGIN
        SELF.g_string := SELF.g_string || ',' || value;
        RETURN ODCIConst.Success;
    END;

    MEMBER FUNCTION ODCIAggregateTerminate(self IN t_string_agg, returnValue OUT VARCHAR2, flags IN NUMBER) RETURN NUMBER IS
    BEGIN
        returnValue := RTRIM(LTRIM(SELF.g_string, ','), ',');
        RETURN ODCIConst.Success;
    END;

    MEMBER FUNCTION ODCIAggregateMerge(self IN OUT t_string_agg, ctx2 IN t_string_agg) RETURN NUMBER IS
    BEGIN
        SELF.g_string := SELF.g_string || ',' || ctx2.g_string;
        RETURN ODCIConst.Success;
    END;
END;
/

-- 2. 建立彙總函數
CREATE OR REPLACE FUNCTION string_agg(p_input VARCHAR2) RETURN VARCHAR2
PARALLEL_ENABLE AGGREGATE USING t_string_agg;
/

-- 3. 使用
SELECT flag, string_agg(TRIM(val)) AS ename
FROM t_collect SAMPLE(10)
GROUP BY flag
ORDER BY 1;

• 實作 ODCIAggregate 介面四方法：Initialize, Iterate, Terminate, Merge。
• PARALLEL_ENABLE 支援並行查詢。
• 語法最簡潔，接近標準 SQL 想像。

效能測試結果 (10,000 筆資料，秒)

結論與建議

1. COLLECT 方案效能最佳，隨資料量線性增長，適合大量資料彙總。
2. SYS_CONNECT_BY_PATH 效能最差，隨資料量指數級惡化 (階層查詢開銷大)，不建議生產環境大量資料使用。
3. UDF 方案次之，語法優雅、可維護性高，中等資料量可接受；大量資料 (100%) 耗時明顯高於 COLLECT。
4. Oracle 11g R2+ 可直接使用內建 LISTAGG 函數 (未測試)，語法最簡單、效能通常優於 UDF，為現代首選。

補充：LISTAGG 範例：SELECT flag, LISTAGG(TRIM(val), ',') WITHIN GROUP (ORDER BY val) FROM t_collect GROUP BY flag;