컬럼값을 레코드로 변환하기

거의 2년만에 새로운 글을 올리게 되어 기분이 남다르다. ^^;

나는 지난달 MySQL에 대한 튜닝 요청이 있어서 약 3주간 지원을 다녀온 적이 있다.

오라클이 아니라서 살짝 MySQL 책을 사서 맛만 좀 보고 지원을 하게되었다.

사실 직접 가서 확인해보니, MySQL로 2TB에 달하는 대용량 데이터를 운영하고 있는 것이 놀라울 정도였다. 전체 데이터는 약 2TB, 가장 큰 테이블 하나가 약 200GB 였다. MySQL의 다른 면을 보는 듯 했다.

튜닝이 필요한 SQL을 전달 받고서 점검을 수행하던 중에 매우 특이한 SQL을 발견했다.

SQL문장 하나가 4만라인... 헉~~

도저히 이해가 되지 않아 가만히 들어다 보니, 응용프로그램에서 SQL을 동적으로 생성하여 실행한 SQL처럼 보였다.

대략 아래와 같은 패턴이었다.

SELECT ...
FROM   (
            SELECT 1 AS CNT FROM DUAL
            UNION ALL
            SELECT 1 AS CNT FROM DUAL
            UNION ALL
            SELECT 1 AS CNT FROM DUAL
            UNION ALL
            SELECT 1 AS CNT FROM DUAL
            .....
            UNION ALL
            SELECT 1 AS CNT FROM DUAL
          )

위와 같은 SQL이 생성 되어진 이유는 다음과 같았다.

1. 다음과 같이 테이블A 가 존재하는데

2. 테이블에 데이터가 입력되는 형태는 아래와 같이 생성시간과 생성유형 컬럼에 버티컬바(‘|’)를 구분 값으로 하여 쌍으로 입력되며, 생성개수 컬럼에는 쌍의 개수가 입력되어 진다.

일련번호	생성시간	생성유형	생성개수
1	2015010100|2015010101|2015010102|…	080101|080101|090102|…	40
2	2015010200|2015010201|2015010202|…	090101|080101|090102|…	45
3	2015010300|2015010301|2015010302|…	080101|090101|090102|…	80
4	2015010400|2015010401|2015010402|…	080101|080101|080102|…	30
5	2015010500|2015010501|2015010502|…	070101|080101|090102|…	109

3. 이렇게 입력된 데이터를 생성시간의 일자, 생성유형별로 그룹핑하여 개수를 구해야 하는데 이를 구하기 위해서 프로그램을 작성하여 한 건씩 패치하여 SQL문장을 생성 및 실행하게 된다.

String SQL = “”;
while(rs.next())
{
   String[] createTime = rs.getString(1).split("|");
   String[] createType = rs.getString(2).split("|");
   int createCnt = rs.getInt(3);
   for(int i = 0; i < createCnt; i++)
   {
      SQL += “select ‘“ + createDate[I] + “_” + createType[I] + “’ as d, ‘1’ as cnt from dual union all”;

}

}

rs.close();

/* to-do */

SQL 문장 실행…

위와 같이 SQL 문장을 만들게 되니 4만 라인의 SQL이 생성된 것이다.

이러한 로직에는 어떤 문제점이 있을까?

우선 첫 번째로 불필요한 fetch를 수행하게 되고,

두 번째 불필요한 network 부하를 발생하게 된다. 그러므로 해당 업무의 성능이 저하될 수 밖에 없다.

그럼 이런 문제점들을 어떻게 해결할 수 있을까? 답은 간단하다. 그냥 SQL로 처리하는 것이다.

위의 데이터에서 우리는 각 레코드를 응용프로그램에서 fetch하지 않고 생성일자, 생성유형별 개수를 한방의 SQL로 추출할 수 있는 중요한 두 가지를 발견할 수 있다.

첫 번째 ‘|’로 구분되는 생성시간과 생성유형의 각 값의 길이가 고정되어 있다는 것과

두 번째 생성개수 컬럼에 의해 몇 개가 생성되어 있는지 알 수 있다는 것이다.

이렇게 두 가지만 알면 ‘|’로 구분된 각 컬럼의 데이터를 레코드로 변환할 수 있다.
실제로 테스트를 수행해 보자

1. 먼저 테스트를 위한 테이블을 만들자.

   CREATE TABLE 테스트A
   (

  일련번호 NUMBER(10) PRIMARY KEY,

     생성시간 VARCHAR2(4000) NOT NULL,
     생성유형 VARCHAR2(4000) NOT NULL,
     생성개수 NUMBER(5) NOT NULL
   );

2. 데이터를 편하게 넣기 위해 SEQUENCE를 하나 생성하자.    CREATE SEQUENCE SEQ1 START WITH 1 INCREMENT BY 1 MINVALUE 1 NOCYCLE;

3. 테스트 데이터를 입력하자

INSERT INTO 테스트A VALUES (SEQ1.NEXTVAL, '2015010100|2015010101|2015010102|2015010115', '080101|080101|090102|090102', 4);
INSERT INTO 테스트A VALUES (SEQ1.NEXTVAL, '2015010200|2015010201|2015010202|2015010202|2015010203', '090101|080101|090102|080101|080101', 5);
INSERT INTO 테스트A VALUES (SEQ1.NEXTVAL, '2015010300|2015010301|2015010302|2015010302', '080101|090101|090102|080101', 4);
INSERT INTO 테스트A VALUES (SEQ1.NEXTVAL, '2015010400|2015010401|2015010402', '080101|080101|080102', 3);
INSERT INTO 테스트A VALUES (SEQ1.NEXTVAL, '2015010500|2015010501|2015010502|2015010510|2015010510|2015010510', '070101|080101|090102|090102|070101|080101', 6);
COMMIT;

4. ‘|’로 구분된 값을 레코드로 변환하기 위해서는 데이터 복제방법을 사용해야 한다. 오라클 이라면 CONNECT BY 절을 사용하여 쉽게 데이터 복제를 위한 데이터를 생성할 수 있겠지만, 다른 DBMS에서도 활용할 수 있게 하려면 고전적인 COPY_T 테이블을 생성해야 한다.

CREATE TABLE COPY_T
(
  NO NUMBER(10) NOT NULL PRIMARY KEY
);

INSERT INTO COPY_T

SELECT LEVEL FROM DUAL CONNECT BY LEVEL <= 100;

MySQL 이라면 아래와 같이 COPY_T 테이블에 데이터를 입력할 수 있다.
CREATE TABLE COPY_T
(
  NO INT PRIMARY KEY
);
INSERT INTO COPY_T
SELECT @RN:=@RN+1 AS ROWNUM
FROM (SELECT 1 FROM <AnyTable>) AS T, (SELECT @RN:=0) AS R
LIMIT 500000;

5. 이제 준비가 완료 되었다.
SQL을 작성하여 컬럼을 레코드가 아닌 데이터 값을 레코드로 변환해보자. 원리는 간단하다. 먼저 이야기 했듯이 우리는 각 레코드의 값에 몇 개씩 생성되었는지 이미 개수를 알고 있으므로 COPY_T 테이블을 이용하여 그 개수만큼 레코드를 복제하면 된다.

SELECT  A.생성시간, A.생성유형, T.NO

FROM   테스트A A, COPY_T T
WHERE  T.NO <= A.생성개수
ORDER BY 생성시간, 생성시간, NO;

6. 레코드가 생성개수만큼 복제 되었다. 다음으로 우리가 알고 있는 정보를 사용해야 한다. 생성시간은 10자리마다 ‘|’로 구분되어 있으며, 생성유형은 6자리마다 ‘|’로 구분되어 있다. 이정보를 사용하여 각 레코드별로 해당되는 위치의 값을 잘라서 취해보자.

SELECT  SUBSTR(A.생성시간, (CASE WHEN T.NO = 1 THEN 1 ELSE (T.NO * 10 + T.NO - 11 + 1) END), 10) AS 생성시간

       ,SUBSTR(A.생성유형, (CASE WHEN T.NO = 1 THEN 1 ELSE (T.NO * 6 + T.NO - 7 + 1) END), 6) AS 생성유형, T.NO
FROM   테스트A A, COPY_T T
WHERE  T.NO <= A.생성개수
ORDER BY 생성시간, 생성시간, NO;

이렇게 하면 구분자로 분리된 값을 레코드로 변환하여 다른 테이블과 조인도 수행할 수 있다.

 
다음을 기약하며 이번 포스팅은 여기까지다.