# [Oracle] 콤마 문자열 파싱 4가지 방식 성능 및 실무 비교 (XMLTABLE vs JSON_TABLE vs CONNECT BY)

1탄 [Oracle] 쉼표(,)로 구분된 문자열을 행(Row)으로 분리해 검색 조건 처리하기 (REGEXP_SUBSTR + CONNECT BY)

 

 

앞서 살펴본 REGEXP_SUBSTR + CONNECT BY LEVEL 방식은 직관적이고 빠르게 작성할 수 있지만, 정규식(RegExp) 연산 비용과 계층형 쿼리의 내부 메모리 오버헤드로 인해 파싱해야 할 데이터 건수가 많거나 동시 요청이 몰릴 때 CPU 점유율이 급증하는 치명적인 단점이 있습니다.

실무에서 수백~수천 개 이상의 대량 문자열을 고속으로 분리해야 할 때 사용할 수 있는 3가지 대표적인 고성능 대안을 비교해 보겠습니다.

---

💡 실무 적용 가이드 및 결론

1. Oracle 11g 이하 환경:

• 성능 이슈가 발생하는 쿼리라면 CONNECT BY LEVEL 대신 XMLTABLE로 전환하세요.

2. Oracle 12c 이상 환경:

• 가독성과 성능이 모두 우수한 JSON_TABLE 방식을 표준으로 채택하는 것을 권장합니다.

3. 파라미터가 수천 건을 넘는 경우:

• SQL 내부에서 콤마를 쪼개기보다는 MyBatis의 <foreach>를 통해 배열/List 형태로 바인딩하거나, 임시 테이블(GTT)을 사용하는 아키텍처 개선을 우선 고려해야 합니다.

---

1. 대안 1: XMLTABLE (Oracle 10g R2+ 지원 / 11g 안정화)

오라클의 XML 파싱 엔진을 활용하는 방식으로, 정규표현식 엔진보다 훨씬 빠르고 CPU 소모량이 적습니다.

📌 쿼리 예시

SELECT TRIM(COLUMN_VALUE) AS GOODS_CODE
FROM XMLTABLE(
    ('"' || REPLACE(:arg_goods, ',', '","') || '"')
);

🔍 동작 원리 및 장점

1. REPLACE(:arg_goods, ',', '","'): 문자열 'A,B,C'를 "A","B","C" 형태의 XML 시퀀스로 변환합니다.
2. XMLTABLE(...): 내장 XQuery 엔진이 이를 행(Row)으로 풀어냅니다.
3. 장점:

• REGEXP 및 CONNECT BY를 전혀 쓰지 않아 처리 속도가 3~5배 이상 빠릅니다.
• 공백(TRIM) 처리만 곁들이면 빈 값 예외도 안정적으로 방어합니다.

---

2. 대안 2: JSON_TABLE (Oracle 12c R1+ 지원)

Oracle 12c 이상을 사용 중이라면 XMLTABLE보다 가볍고 현대적인 JSON_TABLE을 사용하는 것이 가장 권장됩니다.

📌 쿼리 예시

SELECT GOODS_CODE
FROM JSON_TABLE(
    '["' || REPLACE(:arg_goods, ',', '","') || '"]',
    '$[*]' COLUMNS (GOODS_CODE VARCHAR2(100) PATH '$')
);

🔍 동작 원리 및 장점

1. 콤마 구분 문자열을 JSON 배열 형태(["A","B","C"])로 감싸줍니다.
2. JSON_TABLE의 경로 표현식($[*])을 통해 JSON 배열의 각 원소를 일반 RDBMS 컬럼으로 즉시 투영합니다.
3. 장점:

• XML보다 파싱 엔진의 오버헤드가 적어 가장 빠른 연산 속도를 자랑합니다.
• 타입 캐스팅(VARCHAR2(100))을 쿼리 레벨에서 명확히 지정할 수 있어 안정적입니다.

---

3. 대안 3: 사용자 정의 Collection + TABLE() 함수 (PL/SQL)

프로시저나 대규모 배치 작업에서 동일한 문자열 분리 로직이 수없이 호출된다면, C 기반의 내장 SUBSTR + INSTR로 구현된 커스텀 파싱 함수가 최고의 성능을 냅니다.

📌 1) Type 선언

CREATE OR REPLACE TYPE T_VARCHAR2_TABLE AS TABLE OF VARCHAR2(4000);

📌 2) 파싱 함수 정의

CREATE OR REPLACE FUNCTION FN_SPLIT_STRING (
    p_string IN VARCHAR2,
    p_delimiter IN VARCHAR2 DEFAULT ','
) RETURN T_VARCHAR2_TABLE PIPELINED
AS
    v_start NUMBER := 1;
    v_end   NUMBER;
BEGIN
    LOOP
        v_end := INSTR(p_string, p_delimiter, v_start);
        IF v_end > 0 THEN
            PIPE ROW (SUBSTR(p_string, v_start, v_end - v_start));
            v_start := v_end + LENGTH(p_delimiter);
        ELSE
            PIPE ROW (SUBSTR(p_string, v_start));
            EXIT;
        END IF;
    END LOOP;
    RETURN;
END;
/

📌 3) 쿼리 활용

SELECT COLUMN_VALUE AS GOODS_CODE
FROM TABLE(FN_SPLIT_STRING(:arg_goods, ','));

---

4. 4가지 방식 성능 & 실무 특성 비교표

비교 항목	CONNECT BY LEVEL (기존)	XMLTABLE	JSON_TABLE (추천)	PIPELINED FUNCTION
지원 버전	Oracle 9i+	Oracle 10g R2+	Oracle 12c+	Oracle 9i+
처리 성능 (대용량)	⚠️ 느림 (CPU 과점유)	🚀 빠름	⚡ 매우 빠름	⚡ 최상
구현 편의성	높음 (순수 SQL)	보통 (XML 치환 필요)	보통 (JSON 형태 치환)	낮음 (DB Object 생성 필요)
특수문자 이슈	정규식 이스케이프 주의	<, >, & 이스케이프 주의	쌍따옴표(") 이스케이프 주의	없음
주요 추천 환경	데이터 수십 건 이하의 단순 쿼리	Oracle 11g 환경의 대용량 파싱	Oracle 12c+ 현대적 시스템 표준	대규모 배치 / 프로시저 환경

---