오라클에서는 세로로 나열된 정보를 가로로 연결해줄 수 있는 wm_concat()이라는 함수가 존재한다.
가뭄에 단비같은... 기능이라고나 할까? 역시 DB는 오라클인듯.
자 그럼 wm_concat()에 대해 간단히 설명해본다.
아래 쿼리는 하나의 요청에 1:N으로 엮여진 정보를 ,(콤마)를 붙혀 나열하기 위한 쿼리이다.
ex) 박성훈 요청번호 20150904001에 포함된 카테고리 정보 : 리눅스,자바,스프링
SELECT SUBSTR (SYS_CONNECT_BY_PATH (cd_nm , ','), 2)
FROM ( SELECT cd_nm
, ROW_NUMBER() OVER (ORDER BY cd_nm) rnum
, COUNT(*) OVER () cnt
FROM ( SELECT cd_nm
FROM t_cd
WHERE cd IN ('AAA', 'BBB', 'CCC')
)
)
WHERE rnum = cnt
START WITH rnum = 1
CONNECT BY rnum = PRIOR rnum + 1;
쿼리에 대해 간단히 설명하면 먼저 rnum과 cnt를 붙혀준다.
그리고 재귀함수를 통해서 rnum이 1인 것부터 시작하여 SYS_CONNECT_BY_PATH로 텍스트에 ,로 붙혀주게 된다.
그러면 다음과 같은 형태가 된다.
리눅스
리눅스,자바
리눅스,자바,스프링
그 중에서 rnum과 cnt값이 같은 마지막값인 리눅스,자바,스프링 값을 최종적으로 선택하게 된다.
그런데 이 복잡한 쿼리를 wm_concat()을 이용하게 되면 아래와 같이 한방에 해결할 수 있게 된다.
굉장함! (혼자 감탄중..)
SELECT wm_concat(y.cd_nm)
FROM t_catg x, t_cd y
WHERE x.doc_no = aaa.doc_no AND x.catg_cd = y.cd
만약 t_catg에 중복값이 있다면 wm_concat에서 distinct 키워드를 사용하여 중복처리도 가능하다.
SELECT wm_concat(DISTINCT y.cd_nmo)
FROM t_catg x, t_cd y
WHERE x.doc_no = aaa.doc_no AND x.catg_cd = y.cd
AS-IS 쿼리의 경우 메인쿼리를 실행한 후, mybatis 등에서 다시 쿼리 호출을 통해 데이터를 가져왔었다.
이 경우 메인쿼리의 select 카운트에 따라 N번의 IO가 발생하여 성능이 느렸으나, wm_concat()로 변경하게 되면서 훨씬 더 좋은 성능을 얻을 수 있었다.
또한 over()를 통해 order by나 partition by도 복합적으로 사용할 수도 있다.