SQL BOOSTER 이어지는 이야기 .02

SQL BOOSTER 에 이어지는 이야기들입니다.~! 
SQL BOOSTER 를 보신 분들께 좀 더 도움을 드리고자 추가로 작성한 내용입니다.

www.aladin.co.kr/shop/wproduct.aspx?ItemId=216383877

SQL BOOSTER

설명의 편의상 반말체로 작성한 점 양해바랍니다.  pdf 파일도 첨부드리니 다운 받아 보셔도 됩니다.

 

<LISTAGG & 테이블의 인덱스 확인하기>

 

오늘은 정말 짧은 글을 하나 올립니다. 앞으로 이어질 글에, 인덱스를 확인해야 하는 경우들이 있기 때문에 짧지만 LISTAGG와 같이 설명하고 넘어가려고 합니다. 

오라클 11g에는 LISTAGG함수가 있다. LISTAGG는 여러 로우를 하나로 만드는 유용한 기능이다. 쉽게 말에 여러 건의 데이터를 콤마로 결합해 한 건으로 보여주기 위해 사용한다. [그림 2-1]을 본다면 이해가 쉽다.

 

그림 2-1

현업의 요구 사항 중에는 [그림 2-1]의 우측과 같이 여러 건의 로우를 콤마를 이용해 한 건으로 보여주기를 원하는 경우가 있다. 이때, LISTAGG를 사용하면 비교적 쉽게 요구 사항을 해결할 수 있다. 하지만 많은 데이터를 LISTAGG로 처리하면 성능적인 이슈가 있을 수 있다. 로우를 모두 끌어 올려 컬럼으로 변경하는 것이기 때문에, 일반적인 데이터 조회보다 성능 부하가 있을 수 밖에 없다.

필자의 경우, 현업에서 여러 건을 콤마로 구분해 한 건으로 보여주고자 하는 요구 사항은 최대한 들어주지 못하는 쪽으로 협의해왔다. 꼭 필요한 경우라면, 건수에 제한을 두고 성능까지 검토한 후에 요구사항을 받아들였다. LISTAGG를 익히기 전에 성능적인 이슈가 있음을 알고 제한적으로 사용해야 함을 꼭 명심해주기 바란다.

C_BAS_CD 테이블을 BAS_CD_DV(기준코드구분) 별로, 기준코드(BAS_CD) 리스트를 한 컬럼으로 보여주는 SQL을 작성해보자. 아래와 같다.

[SQL-1] BAS_CD_DV별 BAS_CD 리스트를 한 컬럼으로 표현
SELECT  T1.BAS_CD_DV
        ,LISTAGG(T1.BAS_CD,',') WITHIN GROUP(ORDER BY T1.BAS_CD) IND_COLS
FROM    C_BAS_CD T1
WHERE   T1.LNG_CD = 'KO'
GROUP BY T1.BAS_CD_DV;

위 SQL을 실행하면 아래와 같은 결과가 나온다.

[결과-1] BAS_CD_DV별 BAS_CD 리스트를 한 컬럼으로 표현
BAS_CD_DV	          BAS_CD_LST
=========	          ==========
CUS_GD		A,B
GND_TP		FEMLE,MALE
ITM_TP		CLOTHES,COOK,ELEC,PC
...

LISTAGG를 사용하려면 GROUP BY를 같이 사용해야 한다. SQL BOOSTER의 GROUP BY 부분을 읽었다면 전혀 어렵지 않을 것이다. SUM이나 MAX가 사용될 집계함수 부분만 LISTAGG로 대체하면 된다. LISTAGG에는 WITHIN GROUP절도 같이 사용해야 한다. 아래를 참고하기 바란다.

- LISTAGG(결합할 컬럼명, 구분자) WITHIN GROUP(ORDER BY 정렬기준)

 

이번에는 코드와 코드명을 결합해 코드명까지 보여주도록 LISTAGG를 변형해보자.

[SQL-2] BAS_CD_DV별 BAS_CD, BAS_CD_NM 리스트를 한 컬럼으로 표현

SELECT  T1.BAS_CD_DV
        ,(SELECT A.BAS_CD_DV_NM FROM C_BAS_CD_DV A WHERE A.BAS_CD_DV = T1.BAS_CD_DV) BAS_CD_DV_NM
        ,LISTAGG('['||T1.BAS_CD||']'||T1.BAS_CD_NM,',') WITHIN GROUP(ORDER BY T1.BAS_CD) BAS_CD_LST
FROM    C_BAS_CD T1
WHERE   T1.LNG_CD = 'KO'
GROUP BY T1.BAS_CD_DV;

위 SQL의 결과는 아래와 같다.

[결과-2] BAS_CD_DV별 BAS_CD, BAS_CD_NM 리스트를 한 컬럼으로 표현
BAS_CD_DV     BAS_CD_DV_NM           BAS_CD_LST
=========     =============          ===========
CUS_GD	    고객등급	       [A]A,[B]B
GND_TP	    성별구분	       [FEMLE]여성,[MALE]남성
ITM_TP	    아이템유형	       [CLOTHES]옷,[COOK]주방도구,[ELEC]가전제품,[PC]컴퓨터
LNG_CD	    언어코드	       [CN]중국어,[EN]영어,[KO]한국어
ORD_ST	    주문상태	       [COMP]완료,[WAIT]대기
PAY_TP	    지불유형	       [BANK]계좌이체,[CARD]카드

여기까지 위의 SQL 들을 직접 입력하고 실행해봤다면, LISTAGG의 사용에는 문제가 없으리라 생각된다. LISTAGG 외에도, 유사한 XMLLAG도 있으니 찾아보기 바란다. 처음에도 이야기 했듯이 성능적인 부분을 고려해 제한적으로 사용하기 바란다.

 

 

사실, LISTAGG에 대해서는 쓰고 싶지 않았다. 프로젝트 여기 저기에서, 무분별하게 LISTAGG가 사용되게 될까 걱정되기 때문이다. 그럼에도 불구하고 LISTAGG에 대해 짤막하게 다룬 이유는, 아래의 인덱스 리스트를 조회하는 SQL을 보여주기 위해서다. 필자가 자주 사용하는 SQL이다.

[SQL-3] 인덱스 리스트 조회하기
SELECT  T1.INDEX_OWNER ,T1.TABLE_NAME ,T1.INDEX_NAME
        ,LISTAGG(T1.COLUMN_NAME,',') WITHIN GROUP(ORDER BY T1.COLUMN_POSITION) IND_COLS
FROM    ALL_IND_COLUMNS T1
        ,ALL_INDEXES T2
WHERE   T1.TABLE_NAME = 'T_ORD_BIG'
AND     T1.INDEX_NAME = T2.INDEX_NAME
AND     T1.TABLE_NAME = T2.TABLE_NAME
AND     T1.INDEX_OWNER = T2.OWNER
GROUP BY T1.INDEX_OWNER ,T1.TABLE_NAME ,T1.INDEX_NAME
ORDER BY T1.INDEX_OWNER ,T1.TABLE_NAME ,T1.INDEX_NAME;

 

위 SQL을 실행하면 아래와 같이, T_ORD_BIG 테이블의 인데스 현황을 보기 좋게 조회할 수 있다.

[결과-3] 인덱스 리스트 조회하기
INDEX_OWNER     TABLE_NAME    INDEX_NAME          IND_COLS             
============    ==========    =============       =====================
ORA_SQL_TEST    T_ORD_BIG	X_T_ORD_BIG_1	ORD_YMD	             
ORA_SQL_TEST    T_ORD_BIG	X_T_ORD_BIG_2	RNO	                 
ORA_SQL_TEST    T_ORD_BIG	X_T_ORD_BIG_3	ORD_YMD,CUS_ID	     
ORA_SQL_TEST    T_ORD_BIG	X_T_ORD_BIG_4       CUS_ID,ORD_YMD,ORD_ST
ORA_SQL_TEST    T_ORD_BIG	X_T_ORD_BIG_TEST    ORD_SEQ	

빠르게 튜닝을 하려면, 조회하는 테이블의 인덱스들을 빨리 확인해야 한다. 그때, 유용하게 사용하는 스크립트다.

 

오늘 준비한 글은 여기까지입니다. 읽어주셔서 감사합니다.!