태터데스크 관리자

도움말
닫기
적용하기   첫페이지 만들기

태터데스크 메시지

저장하였습니다.

Column의 값을 Row로 변환하기

오라클/SQL 튜닝 2010.12.03 11:40
가장 과격한 형태의 비정규화 중 하나가 여러 개의 로우로 저장해야할 값들을 하나의 컬럼 값으로 병합(Concatenation)해서 저장하는 것입니다. 가령 이렇게 저장되어 있어야할 데이터를
        C1
----------
        11
         2
       345
        89
      3334
        12
이렇게 저장하는 거죠.
11,2,345,89,3334,12
이유야 어쨌든, 이렇게 저장된 데이터를 SQL에서 사용하기는 매우 어렵습니다. SQL은 기본적으로 하나의 데이터가 하나의 로우에 저장되어 있다는 전제하에서 가장 이상적으로 동작하기 때문입니다.

아마 데이터를 위와 같이 저장한 것은 이런 쿼리 패턴을 염두에 두었기 때문일 것입니다.

select ... from where id in (11,2,345,89,3334,12);
즉, IN 을 이용한 동적인 쿼리(Dynamic Query)를 생성해서 사용하게 됩니다. 과감한 비정규화를 수행한 대신 하드 파스의 부담을 안게 됩니다. 그리고 집합으로서 처리하는 것이 불가능하기 때문에 비슷한 모양의 쿼리를 반복적으로 수행하게 됩니다. 데이터 정합성이나 기타 모델링 관점에서의 고려는 물론 이미 배제된 상태구요.

위와 같이 저장된 데이터(11,2,345,89,3334,12)를 정상적인 SQL을 통해서 사용하려면 1) 값을 "," 기준으로 분리한 다음 2) 그 값들을 로우의 형태로 바꾸어 주어야 합니다. 이때 보편적으로 사용되는 방법이 피봇 테이블(대용량데이터베이스 책에서는 COPY_T 라는 이름으로 사용)을 사용해서 값을 여러 개 복사해서 로우로 만든 다음 각 로우에서 값을 추출하는 방법입니다. 다음과 같이 하는거죠.

Row#1 : 11,2,345,89,3334,12 --> 11 추출
Row#2 : 11,2,345,89,3334,12 --> 2 추출
Row#3 : 11,2,345,89,3334,12 --> 345 추출
Row#4 : 11,2,345,89,3334,12 --> 89 추출
Row#5 : 11,2,345,89,3334,12 --> 3334 추출
Row#6 : 11,2,345,89,3334,12 --> 12 추출
위의 개념을 SQL 코드로 구현해보면 다음과 같습니다. 우선 다음과 같이 테이블을 만듭니다. 테이블 T2가 비정규화가 감행된 형태로 들어가있죠. 테이블 T1과 T2를 조인하는 것이 우리의 목표입니다.
create table t1(c1 number);

insert into t1 values(11);
insert into t1 values(2);
insert into t1 values(345);
insert into t1 values(89);
insert into t1 values(3334);
insert into t1 values(12);

create table t2(id_array	varchar2(4000));
insert into t2 values('11,2,345');
insert into t2 values('89,3334,12');

우선 다음과 같이 PIVOT 테이블을 이용해 데이터를 카피하면서 SUBSTR을 통해 "," 문자가 첫번째에 오는 경우만을 추출합니다.
SQL> var b1 varchar2(100);
SQL> exec :b1 := ','||'11,2,345,89,3334,12';

PL/SQL procedure successfully completed.

SQL> select
  2  	     str,
  3  	     bi,
  4  	     ei,
  5  	     substr(x.str, bi+1, decode(ei,-1,length(x.str), ei-bi)) as id
  6  from
  7  	     (select
  8  		     rnum,
  9  		     substr(:b1, rnum) as str,
 10  		     instr(substr(:b1, rnum), ',') as bi,
 11  		     instr(substr(:b1, rnum), ',', 2)-1 as ei
 12  	     from
 13  		     (select level as rnum from dual
 14  		     connect by level <= 100) pivot
 15  	     ) x
 16  where
 17  	     x.bi = 1
 18  ;

STR                                    BI         EI ID
------------------------------ ---------- ---------- ----------
,11,2,345,89,3334,12                    1          3 11
,2,345,89,3334,12                       1          2 2
,345,89,3334,12                         1          4 345
,89,3334,12                             1          3 89
,3334,12                                1          5 3334
,12                                     1         -1 12
위의 쿼리에서 얻는 BI(Begin Index), EI(End Index) 값을 이용해서 다음과 같이 T1에서 해당하는 값을 구할 수 있습니다.
SQL> select
  2  	     *
  3  from
  4  	     t1,
  5  	     (select
  6  		     substr(x.str, bi+1, decode(ei,-1,length(x.str), ei-bi)) as id
  7  	     from
  8  		     (select
  9  			     rnum,
 10  			     substr(:b1, rnum) as str,
 11  			     instr(substr(:b1, rnum), ',') as bi,
 12  			     instr(substr(:b1, rnum), ',', 2)-1 as ei
 13  		     from
 14  			     (select level as rnum from dual
 15  			     connect by level <= 100) pivot
 16  		     ) x
 17  	     where
 18  		     x.bi = 1
 19  	     ) y
 20  where
 21  	     t1.c1 = y.id
 22  ;

        C1 ID
---------- ----------
        11 11
         2 2
       345 345
        89 89
      3334 3334
        12 12
위의 방법을 테이블 T2에 대해 적용하면 다음과 같이 테이블 T1과 테이블 T2를 조인할 수 있습니다.
SQL> select
  2  	     *
  3  from
  4  	     t1,
  5  	     (select
  6  		     substr(x.str, bi+1, decode(ei,-1,length(x.str), ei-bi)) as id
  7  	     from
  8  		     (select
  9  			     rnum,
 10  			     substr(t2.id, rnum) as str,
 11  			     instr(substr(t2.id, rnum), ',') as bi,
 12  			     instr(substr(t2.id, rnum), ',', 2)-1 as ei
 13  		     from
 14  			     (select level as rnum from dual
 15  			     connect by level <= 100) pivot,
 16  			     (select ','||id_array as id from t2) t2
 17  		     ) x
 18  	     where
 19  		     x.bi = 1
 20  	     ) y
 21  where
 22  	     t1.c1 = y.id
 23  ;

        C1 ID
---------- ----------
        11 11
         2 2
       345 345
        89 89
      3334 3334
        12 12
PIVOT 테이블을 이용해서 데이터를 카피하는 방식은 일단 이해하기가 어렵고, 둘째 테이블 T2를 여러번 읽어야 하기 때문에 성능 면에서도 유리하다고 보기 어렵습니다.

그래서 개인적으로는 아래와 같은 방법이 더 좋다고 생각합니다. 아래와 같은 방법이란 테이블 펑션을 이용하는 것입니다.

아래와 같이 문자열을 받아서 로우 형태로 리터하는 테이블 펑션을 만듭니다.

SQL> create or replace type varchar2_array is table of varchar2(100);
  2  /

Type created.

SQL> -- table function
SQL> create or replace function get_id(p_list in varchar2)
  2  return varchar2_array
  3  pipelined
  4  is
  5  	     v_temp	     varchar2(2000);
  6  	     bi 		     number := 0;
  7  	     ei 		     number := 0;
  8  
  9  begin
 10  	     if substr(p_list, 1, 1) <> ',' then
 11  		     v_temp := ',' || p_list;
 12  	     else
 13  		     v_temp := p_list;
 14  	     end if;
 15  
 16  	     loop
 17  		     bi := instr(p_list, ',', ei+1, 1);
 18  		     ei := instr(p_list, ',', ei+1, 2)-1;
 19  
 20  		     if ei > 0 then
 21  			     pipe row(substr(v_temp, bi+1, ei-bi));
 22  		     else
 23  			     pipe row(substr(v_temp, bi+1));
 24  		     end if;
 25  
 26  		     exit when ei < 0;
 27  	     end loop
 28  
 29  	     return;
 30  
 31  end get_id;
 32  /

Function created.
결과는 아래와 같습니다.
SQL> select * from table(get_id(:b1));

COLUMN_VALUE
--------------------------------------------------------------------------------
11
2
345
89
3334
12
위에서 만든 테이블 펑션을 아래와 같이 사용하면 테이블 T1과 테이블 T2를 조인할 수 있습니다. PIVOT 테이블을 사용한 것과 동일한 결과를 얻을 수 있죠.
SQL> select
  2  	     *
  3  from
  4  	     t1,
  5  	     (select
  6  		     column_value as id
  7  	      from
  8  		     t2, table(get_id(','||t2.id_array)) -- 조인 방식 유의
  9  	     ) y
 10  where
 11  	     t1.c1 = y.id
 12  ;

        C1 ID
---------- ----------
        11 11
         2 2
       345 345
        89 89
      3334 3334
        12 12
이외에도 여러 가지 방법이 있을텐데요. 결국 무리한 비정규화의 결과는 무리한 SQL 문장이 되는 셈입니다.

우리가 멋진 SQL 문장이다, 화려한 SQL 문장이다라고 할때는, 사실은 그 이면에는 잘못된 모델링으로 인해 발생한 불필요하게 복잡한 SQL 문장인 경우도 있다는 말이 됩니다. 소프트웨어 공학에서는 "데이터 구조가 정교하면 코드가 간단해지고, 데이터 구조가 단순하면 코드가 복잡해진다"라고 하죠.

가장 중요한 것은 모델링, 그 다음이 SQL 문장이라고 볼 수 있겠습니다.

저작자 표시
신고
Trackbacks 2 : Comment 1
  1. extremedb 2010.12.03 17:43 신고 Modify/Delete Reply

    마지막의 통찰이 가슴에 와 닿습니다. 좋은글 감사드립니다.

Write a comment

티스토리 툴바