태터데스크 관리자

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

태터데스크 메시지

저장하였습니다.

'2010/12'에 해당되는 글 4건

  1. 2010.12.09 Set-Based Approach (17)
  2. 2010.12.07 Missing Value 채우기와 Row를 Column으로 변환하기 - Partition Outer Join과 listagg 함수의 조합의 예
  3. 2010.12.03 Column의 값을 Row로 변환하기 (1)
  4. 2010.12.01 Recursive Subquery Factoring의 한가지 활용 예 (5)

Set-Based Approach

오라클/SQL 튜닝 2010.12.09 18:23
SQL을 효과적으로 작성하는 방법은 집합적 사고(Set-Based Approach)를 사용하는 것이라고 하죠.

오늘 어떤 책을 보다가 아주 재미있는 예제를 봤습니다. 아래 문제를 손쉽게 푸신다면 집합적 사고에 능숙하다고 할 수 있겠습니다.

1. 아래와 같은 테이블 ORDERS가 있습니다.

create table orders(
    member_no     number, -- 회원번호
    order_no        number, -- 주문번호
    order_date     date -- 주문일자
);
2. 아래 질문을 집합적 사고에 기반한 SQL 문으로 작성해 보세요.
  • 100번 회원의 주문 간격의 평균값은 무엇입니까?
즉, 2010년 12월 1일, 2010년 12월 10일, 2010년 12월 15일 이렇게 세번 주문했다면 이렇게 되겠죠?
  • 2010년 12월 10일 - 2010년 12월 1일 = 10일
  • 2010년 12월 15일 - 2010년 12월 10일 = 5일
  • 따라서 평균 = (10 + 5) / 2 = 7.5일
자신만의 SQL 문장을 잠시 생각해보세요.

...

...

...

...

만일 다음과 같은 문장을 생각했다면 SQL 작성에는 능숙할지 모르겠지만 집합적 사고에는 실패하신 겁니다.

select 
    avg(trunc(order_date) - trunc(prev_order_date))  avg_order_between  -- (현재일자 - 이전일자)의 평균
from (
    select 
          order_date,   -- 현재 주문일자
          lag(order_date, 1, order_date) over (partition by member_no order by order_date
              as prev_order_date  -- 이전 주문일자
    from
        orders
    where
        member_no = 100
);
사실 위의 문장은 집합적 사고에 조금 실패한 정도가 아니라, 완벽하게 프로시저적인 방법(한단계 한단계씩 실행하는 방식)입니다.

그렇다면 완벽한 집합적 사고에 의해 작성된 SQL 문장은 무엇일까요? 책에서 제시한 정답은 다음과 같습니다.

...

...

...

...

select
    (max(trunc(order_date)) - min(trunc(order_date))) / count(*) as avg_order_between
from
    orders
where
    member_no = 100
;
어떠신가요? 집합적 사고에 성공하셨나요?
저작자 표시
신고
Trackbacks 9 : Comments 17
  1. maceo 2010.12.09 20:58 신고 Modify/Delete Reply

    혹시 어떤 책을 보고 계신지 알 수 있을까요???

  2. 욱짜 2010.12.10 09:38 신고 Modify/Delete Reply

    Pro Oracle SQL이라는 신간입니다.
    http://www.amazon.com/gp/product/1430232285/ref=s9_simh_gw_p14_d3_i1?pf_rd_m=ATVPDKIKX0DER&pf_rd_s=center-2&pf_rd_r=18643QB18H4MJRK75RPQ&pf_rd_t=101&pf_rd_p=470938631&pf_rd_i=507846
    책 자체는 비추입니다. ㅎㅎ

  3. 조용중 2010.12.10 11:00 신고 Modify/Delete Reply

    어라 결과가 틀린데...라는 생각에 내가 뭘 잘못했나라며 자책하길 10여분...
    2010년 12월 10일 - 2010년 12월 1일 = 10일 인데,
    2010년 12월 15일 - 2010년 12월 10일 = 5일 이 된 연산에 문제 있었습니다.
    위에가 9일인것같습니다만...^^;
    물론 프로시저적인 방법이 떠올랐고, 집합적 해답도 이해하는데 한참 걸렸네요...
    항상 좋은 글 감사합니다.

    • 욱짜 2010.12.10 11:23 신고 Modify/Delete

      ㅎㅎ 그렇군요!

      책의 예제를 보고 소개하면 많은 분들이 머리를 딱 칠거 같은 느낌이 들었습니다.

  4. 한현규 2010.12.14 15:47 신고 Modify/Delete Reply

    근데 count(*) - 1 로 나누는 게 맞을 것 같은데요.

    앗. count(*)가 1이면 0이되네요.

    그럼.. decode(count(*), 1, 1, count(*)-1) 로 나눠야할 것 같은데요. 어떻게 생각하세요 ?

  5. ukdissertationwritinghelp 2011.01.25 15:00 Modify/Delete Reply

    관리자의 승인을 기다리고 있는 댓글입니다

  6. ukdissertationwritinghelp 2011.01.25 15:00 Modify/Delete Reply

    관리자의 승인을 기다리고 있는 댓글입니다

  7. term paper assignment 2011.04.09 18:54 Modify/Delete Reply

    관리자의 승인을 기다리고 있는 댓글입니다

  8. 1 2011.04.22 09:37 Modify/Delete Reply

    관리자의 승인을 기다리고 있는 댓글입니다

  9. 생각하게 하는 글 2011.08.10 20:45 Modify/Delete Reply

    관리자의 승인을 기다리고 있는 댓글입니다

  10. TOP 1 Oli Sintetik Mobil-Motor Indonesia 2011.10.13 11:59 Modify/Delete Reply

    관리자의 승인을 기다리고 있는 댓글입니다

  11. Assignment 2011.12.28 18:21 Modify/Delete Reply

    관리자의 승인을 기다리고 있는 댓글입니다

  12. details here 2012.05.10 05:10 Modify/Delete Reply

    관리자의 승인을 기다리고 있는 댓글입니다

  13. nhà hàng cưới 2012.07.13 15:30 Modify/Delete Reply

    관리자의 승인을 기다리고 있는 댓글입니다

  14. 장우성 2012.10.06 14:30 Modify/Delete Reply

    관리자의 승인을 기다리고 있는 댓글입니다

  15. cong ty du lich tphcm 2012.12.24 11:09 Modify/Delete Reply

    관리자의 승인을 기다리고 있는 댓글입니다

Write a comment


Missing Value 채우기와 Row를 Column으로 변환하기 - Partition Outer Join과 listagg 함수의 조합의 예

오라클/SQL 튜닝 2010.12.07 14:32
데이터 모델링을 하다보면 비정규화의 유혹에 빠지는 경우가 있습니다. 이유는 여러가지가 있겠지만, 대표적인 한가지 이유는 우리의 데이터 인식법과 관계형 데이터베이스의 표현 양식에서 차이가 있다는 것입니다. 가령 특정 테이블에 있는 인덱스별로 컬럼 목록을 보고 싶다면, 우리 눈에는 이렇게 보는 것이 가장 편합니다.
T1_N1		C1, C2, C3
T1_N2		C3
T1_N3		C2, C1
하지만 관계형 데이터베이스는 정규화 법칙에 의해 다음과 같이 데이터를 저장하게 됩니다. 보기가 어렵죠.
T1_N1		C1
T1_N1		C2
T1_N1		C3
T1_N2		C3
T1_N3		C2
T1_N3		C1
정규화된 형태에서 데이터를 추출하면 우리가 데이터를 인식하는 방법과 달라서 대단히 불편하게 느끼게 됩니다. 이런 이유 때문에 비정규화의 유혹이 생길 수 있습니다. (엄격하게 말하면 위의 예는 제 1 정규형에 해당합니다)

비정규화란 "우리가 데이터를 인식하는 방식 그대로" 표현하고자 하는 욕구를 의미한다고 할 수 있습니다. 비정규화를 하면 쿼리가 간단해지는 것도 이런 이유 때문입니다. 더 정확하게 말하면 우리 눈에 간단해보이는거죠. 물론 성능을 위한 적절한 비정규화는 삶의 양념같은 역할을 하기도 합니다. 너무 빡빡한 삶은 재미가 없잖아요? ^^;

다행스러운 것은 오라클이 제공하는 (비교적 최신의) 기능들을 잘 이용하면 관계형으로 저장된 데이터를 비교적 간단하게 우리가 원하는 비정규화된 형태로 추출할 수 있다는 것입니다.

간단한 예제를 볼텐데요, 우선 다음과 같이 테이블 T1과 총 6개의 인덱스(Primary Key 포함)을 만듭니다.

create table t1(
	c1 number primary key,
	c2 number,
	c3 number,
	c4 number
);

create index t1_n1 on t1(c1, c2);
create index t1_n2 on t1(c2, c3);
create index t1_n3 on t1(c3);
create index t1_n4 on t1(c3, c1, c2);
create index t1_n5 on t1(c1, c2, c4);
우리의 목적은 다음과 같은 표 형태로 인덱스별, 컬럼별 순서와 Unique 여부를 보는 것입니다. U가 붙으면 Unique이고, T1_N4의 경우에는 (C3, C1, C2)로 이루어져 있다는 의미입니다.
Index_name			 C1   C2   C3   C4
-----------          --   --   --   --
SYS_C0016792         1U
T1_N1                1    2
T1_N2                     1    2
T1_N3                          1
T1_N4                2    3    1
T1_N5                1    2         3
위의 같은 표현 양식에는 일반적으로 표현하기 힘든 두 가지 쿼리 패턴이 숨어 있습니다.
  • Missing Value 채우기: 인덱스 T1_N1의 경우를 보면 컬럼 (C1, C2)로 구성되어 있습니다. 하지만 전체 컬럼은 (C1, C2, C3, C4)로 이루어져 있죠. 이때 C3, C4가 Missing Value가 됩니다. Missing Value까지 다 포함해야 위와 같은 형태의 결과를 얻을 수 있습니다.
  • Row 값을 Column으로 변환하기: 인덱스 T1_N1의 경우를 보면 (C1(1), C2(2), C3(X), C4(X)) 로 구성됩니다. Missing Value 채우기를 통해 데이터를 만들었다고 하더라도 이 데이터들을 Row로 되어 있기 때문에 컬럼 형태로 변환해야 합니다.
위의 두 가지 요구 사항은 매우 보편적으로 발생합니다. 최신 버전의 오라클에서는 Partition Outer JoinListagg 함수를 이용하면 비교적 간단하게 구현할 수 있습니다. 그렇지 않다면 좀 더 수고스러운 과정을 거쳐야할 것이구요.

우선 Missing Value를 채우는 가장 보편적인 방법은 값을 중복해서 복사하는 것입니다. 가령 다음과 같이 인덱스별, 컬럼별로 모든 조합이 다 나오도록 중복된 데이터 셋을 만들면 됩니다.

INDEX_NAME           COLUMN_NAME
-------------------- --------------------
SYS_C0016792         C1
SYS_C0016792         C2
SYS_C0016792         C3
SYS_C0016792         C4
T1_N1                C1
T1_N1                C2
T1_N1                C3
T1_N1                C4
T1_N2                C1
T1_N2                C2
T1_N2                C3
T1_N2                C4
T1_N3                C1
T1_N3                C2
T1_N3                C3
T1_N3                C4
T1_N4                C1
T1_N4                C2
T1_N4                C3
T1_N4                C4
T1_N5                C1
T1_N5                C2
T1_N5                C3
T1_N5                C4
위와 같은 형태의 데이터를 만드는 가장 보편적인 방법은 복사를 위한 피봇(Pivot) 테이블을 이용하는 것입니다. 아래와 같이 DBA_TAB_COLUMNS 뷰를 피봇 테이블로 활용할 수 있습니다. DBA_TAB_COLUMNS와 DBA_INDEXES 뷰는 정상적인 조인이 불가능합니다. DBA_INDEXES 뷰에는 컬럼 정보가 없기 때문입니다. 따라서 Cartesian Join이 발생하게 되고 그로 인해 인덱스별로 컬럼 수만큼 복사가 발생하게 됩니다.
-- pivot table
select 
	i.index_name,
	i.uniqueness,
	tc.column_id,
	tc.column_name
from
	dba_tab_columns tc,
	dba_indexes i
where
	tc.owner = 'TPACK'
	and tc.table_name = 'T1'
	and tc.owner = i.owner
	and tc.table_name = i.table_name  
        -- 컬럼에 대한 조인 필터 없음 ==> Cartesian Join
order by
	i.index_name, tc.column_id
;

-- 그 덕분에 다음과 같이 복사가 이루어짐
INDEX_NAME           UNIQUENES  COLUMN_ID COLUMN_NAME
-------------------- --------- ---------- --------------------
SYS_C0016792         UNIQUE             1 C1
SYS_C0016792         UNIQUE             2 C2
SYS_C0016792         UNIQUE             3 C3
SYS_C0016792         UNIQUE             4 C4
T1_N1                NONUNIQUE          1 C1
T1_N1                NONUNIQUE          2 C2
T1_N1                NONUNIQUE          3 C3
T1_N1                NONUNIQUE          4 C4
T1_N2                NONUNIQUE          1 C1
T1_N2                NONUNIQUE          2 C2
T1_N2                NONUNIQUE          3 C3
T1_N2                NONUNIQUE          4 C4
T1_N3                NONUNIQUE          1 C1
T1_N3                NONUNIQUE          2 C2
T1_N3                NONUNIQUE          3 C3
T1_N3                NONUNIQUE          4 C4
T1_N4                NONUNIQUE          1 C1
T1_N4                NONUNIQUE          2 C2
T1_N4                NONUNIQUE          3 C3
T1_N4                NONUNIQUE          4 C4
T1_N5                NONUNIQUE          1 C1
T1_N5                NONUNIQUE          2 C2
T1_N5                NONUNIQUE          3 C3
T1_N5                NONUNIQUE          4 C4
Missing Value를 채운 후, 그 데이터를 DBA_IND_COLUMNS 뷰와 조인하면 우리가 원하는 완전한 형태의 정보를 얻을 수 있습니다.
select
	c.index_name,
	decode(ic.column_position,null,'',
		decode(c.uniqueness,'UNIQUE','U','')) as is_unique,
	c.column_name,
	c.column_id,
	ic.column_position
from
	(
	select 
		i.table_owner,
		i.table_name,
		i.index_name,
		i.uniqueness,
		tc.column_id,
		tc.column_name
	from
		dba_tab_columns tc,
		dba_indexes i
	where
		tc.owner = 'TPACK'
		and tc.table_name = 'T1'
		and tc.owner = i.owner
		and tc.table_name = i.table_name
	order by
		i.index_name, tc.column_id
	) c,
	dba_ind_columns ic
where
	c.table_owner = ic.table_owner(+)
	and c.table_name = ic.table_name(+)
	and c.index_name = ic.index_name(+)
	and c.column_name = ic.column_name(+)
order by
	c.index_name, c.column_id
;

INDEX_NAME           I COLUMN_NAME           COLUMN_ID COLUMN_POSITION
-------------------- - -------------------- ---------- ---------------
SYS_C0016792         U C1                            1               1
SYS_C0016792           C2                            2
SYS_C0016792           C3                            3
SYS_C0016792           C4                            4
T1_N1                  C1                            1               1
T1_N1                  C2                            2               2
T1_N1                  C3                            3
T1_N1                  C4                            4
T1_N2                  C1                            1
T1_N2                  C2                            2               1
T1_N2                  C3                            3               2
T1_N2                  C4                            4
T1_N3                  C1                            1
T1_N3                  C2                            2
T1_N3                  C3                            3               1
T1_N3                  C4                            4
T1_N4                  C1                            1               2
T1_N4                  C2                            2               3
T1_N4                  C3                            3               1
T1_N4                  C4                            4
T1_N5                  C1                            1               1
T1_N5                  C2                            2               2
T1_N5                  C3                            3
T1_N5                  C4                            4               3
이제 남은 일은 Row로 존재하는 데이터를 Column 형태로 표현하는 것입니다. 아래와 같이 DECODE 함수와 MAX 함수를 조합하면 완전한 형태의 피보팅(Pivoting)이 이루어집니다.
-- pivoting and decode
col col_list format a50
with x as (
	select
		c.index_name,
		decode(ic.column_position,null,'',
			decode(c.uniqueness,'UNIQUE','U','')) as is_unique,
		c.column_name,
		c.column_id,
		ic.column_position
	from
		(
		select 
			i.table_owner,
			i.table_name,
			i.index_name,
			i.uniqueness,
			tc.column_id,
			tc.column_name
		from
			dba_tab_columns tc,
			dba_indexes i
		where
			tc.owner = 'TPACK'
			and tc.table_name = 'T1'
			and tc.owner = i.owner
			and tc.table_name = i.table_name
		order by
			i.index_name, tc.column_id
		) c,
		dba_ind_columns ic
	where
		c.table_owner = ic.table_owner(+)
		and c.table_name = ic.table_name(+)
		and c.index_name = ic.index_name(+)
		and c.column_name = ic.column_name(+)
	order by
		c.index_name, c.column_id
)
select
	x.index_name,
	rpad(max(decode(x.column_id,1,nvl(x.column_position||'',' ')||x.is_unique)),5,' ')||
	rpad(max(decode(x.column_id,2,nvl(x.column_position||'',' ')||x.is_unique)),5,' ')||
	rpad(max(decode(x.column_id,3,nvl(x.column_position||'',' ')||x.is_unique)),5,' ')||
	rpad(max(decode(x.column_id,4,nvl(x.column_position||'',' ')||x.is_unique)),5,' ')
		as col_list
from 
	x
group by 
	index_name
;


INDEX_NAME           COL_LIST
-------------------- --------------------------------------------------
SYS_C0016792         1U
T1_N1                1    2
T1_N2                     1    2
T1_N3                          1
T1_N4                2    3    1
T1_N5                1    2         3
Oracle 11gR2에서는 LISTAGG 함수를 이용하면 훨씬 간단한 방법으로 구현할 수 있습니다. 그 이전 버전이라면 XMLAGG 함수를 사용할 수 있을 것입니다.
-- pivoting and listagg
col col_list format a50
with x as (
	select
		c.index_name,
		decode(ic.column_position,null,'',
			decode(c.uniqueness,'UNIQUE','U','')) as is_unique,
		c.column_name,
		c.column_id,
		ic.column_position
	from
		(
		select 
			i.table_owner,
			i.table_name,
			i.index_name,
			i.uniqueness,
			tc.column_id,
			tc.column_name
		from
			dba_tab_columns tc,
			dba_indexes i
		where
			tc.owner = 'TPACK'
			and tc.table_name = 'T1'
			and tc.owner = i.owner
			and tc.table_name = i.table_name
		order by
			i.index_name, tc.column_id
		) c,
		dba_ind_columns ic
	where
		c.table_owner = ic.table_owner(+)
		and c.table_name = ic.table_name(+)
		and c.index_name = ic.index_name(+)
		and c.column_name = ic.column_name(+)
	order by
		c.index_name, c.column_id
)
select
	x.index_name,
	listagg(rpad(nvl(x.column_position||'',' ')||x.is_unique, 5, ' '), '') 
		within group (order by x.column_id) 
		as col_list
from 
	x
group by 
	x.index_name
;

INDEX_NAME           COL_LIST
-------------------- --------------------------------------------------
SYS_C0016792         1U
T1_N1                1    2
T1_N2                     1    2
T1_N3                          1
T1_N4                2    3    1
T1_N5                1    2         3
Partition Outer Join을 사용하면 Cartesian Join을 사용할 필요없이 훨씬 효율적으로 Missing Value를 채울 수 있습니다.

아래 결과를 보면 DBA_TAB_COLUMNS 뷰와 DBA_IND_COLUMNS 뷰를 Outer Join하는 것만으로 Missing Value를 채울 수가 없다는 것을 알 수 있습니다.

-- what is partition outer join?
select
	ic.index_name,
	tc.column_name,
	ic.column_position
from
	(select
		column_id,
		column_name
	from 
		dba_tab_columns
	where
		owner = 'TPACK'
		and table_name = 'T1'
	) tc
	left outer join
	(select 
		index_name,
		column_position,
		column_name
	from
		dba_ind_columns
	where
		table_owner = 'TPACK'
		and table_name = 'T1'
	) ic
	on (tc.column_name = ic.column_name)
;

INDEX_NAME           COLUMN_NAME          COLUMN_POSITION
-------------------- -------------------- ---------------
SYS_C0016793         C1                                 1
T1_N1                C1                                 1
T1_N1                C2                                 2
T1_N2                C2                                 1
T1_N2                C3                                 2
T1_N3                C3                                 1
T1_N4                C3                                 1
T1_N4                C1                                 2
T1_N4                C2                                 3
T1_N5                C1                                 1
T1_N5                C2                                 2
T1_N5                C4                                 3
하지만 다음과 같이 Partition Outer Join을 사용하면 DBA_IND_COLUMNS.INDEX_NAME 별로 그룹핑해서 Outer Join을 수행합니다. 따라서 Missing Value를 완벽하게 채울 수 있습니다.
select
	ic.index_name,
	tc.column_name,
	ic.column_position
from
	(select
		column_id,
		column_name
	from 
		dba_tab_columns
	where
		owner = 'TPACK'
		and table_name = 'T1'
	) tc
	left outer join
	(select 
		index_name,
		column_position,
		column_name
	from
		dba_ind_columns
	where
		table_owner = 'TPACK'
		and table_name = 'T1'
	) ic
	partition by (ic.index_name)   -- DBA_IND_COLUMNS.INDEX_NAME 별로 그룹핑해서 아우터 조인
	on (tc.column_name = ic.column_name)
;


INDEX_NAME           COLUMN_NAME          COLUMN_POSITION
-------------------- -------------------- ---------------
SYS_C0016793         C1                                 1
SYS_C0016793         C2
SYS_C0016793         C3
SYS_C0016793         C4
T1_N1                C1                                 1
T1_N1                C2                                 2
T1_N1                C3
T1_N1                C4
T1_N2                C1
T1_N2                C2                                 1
T1_N2                C3                                 2
T1_N2                C4
T1_N3                C1
T1_N3                C2
T1_N3                C3                                 1
T1_N3                C4
T1_N4                C1                                 2
T1_N4                C2                                 3
T1_N4                C3                                 1
T1_N4                C4
T1_N5                C1                                 1
T1_N5                C2                                 2
T1_N5                C3
T1_N5                C4                                 3
위에서 얻은 결과를 LISTAGG 함수를 이용하면 원하던 포맷의 결과를 얻을 수 있습니다.
-- partition outer join & listagg
col col_list format a50
with x as (
	select
		i.index_name, 
		tc.column_id,
		ic.column_position,
		tc.column_name,
		decode(ic.column_position,null,'',
			decode(i.uniqueness,'UNIQUE','U','')) as is_unique
	from 
		(
			(
			select
				column_id,
				column_name
			from 
				dba_tab_columns
			where
				owner = 'TPACK'
				and table_name = 'T1'
			) tc
			left outer join
			(select 
				index_name,
				column_position,
				column_name
			from
				dba_ind_columns
			where
				table_owner = 'TPACK'
				and table_name = 'T1'
			) ic
			partition by (ic.index_name) 
			on (tc.column_name = ic.column_name)
			left outer join
			(select
				index_name,
				uniqueness
			from
				dba_indexes
			where
				table_owner = 'TPACK'
				and table_name = 'T1'
			) i
			on (i.index_name = ic.index_name)
		)
	order by
		ic.index_name, tc.column_id
)
select
	x.index_name,
	listagg(rpad(nvl(x.column_position||'',' ')||x.is_unique, 5, ' '), '') 
		within group (order by x.column_id) 
		as col_list
from 
	x
group by
	x.index_name
;

INDEX_NAME           COL_LIST
-------------------- --------------------------------------------------
SYS_C0016792         1U
T1_N1                1    2
T1_N2                     1    2
T1_N3                          1
T1_N4                2    3    1
T1_N5                1    2         3
적다보니 너무 길어졌네요. 오라클이 제공하는 기능을 잘 이용하면 그다지 어렵지 않게(?) 정규화된 관계 모델로부터 우리가 인식하는 형식의 데이터로 표현할 수 있다는 정도의 의도로 봐주시면 되겠습니다.
저작자 표시
신고
Trackbacks 14 : Comment 0

Write a comment


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


Recursive Subquery Factoring의 한가지 활용 예

오라클/SQL 튜닝 2010.12.01 17:46
Oracle 11gR2부터 Recursive Subquery Factoring이라는 기능을 제공합니다. 이 기능을 이용하면 Connect By 구문을 대신할 수 있죠. 아래에 간단한 사용법이 있습니다.
SQL>  select lpad(' ', 2 * level - 2, ' ') || ename as ename
  2        , empno
  3        , mgr
  4        , level
  5     from emp
  6  connect by mgr = prior empno
  7    start with mgr is null
  8  /
SQL> with emps (ename,empno,mgr,lvl) as
  2  ( select ename
  3         , empno
  4         , mgr
  5         , 1
  6      from emp
  7     where mgr is null
  8     union all
  9    select emp.ename
 10         , emp.empno
 11         , emp.mgr
 12         , emps.lvl + 1
 13      from emp
 14           join emps on (emp.mgr = emps.empno)
 15  ) search depth first by empno set a
 16  select lpad(' ', 2 * lvl - 2, ' ') || ename as ename
 17       , empno
 18       , mgr
 19       , lvl
 20    from emps
 21    order by a
 22  /
굳이 Connect By로 잘 사용하고 있었던 것을 왜 다시 Recursive Subquery Factoring을 사용해야 하는지를 고민해보면 딱히 떠오르는 것이 없는데요. 다음과 같은 유형의 쿼리에서 유용하게 사용할 수 있을 것 같습니다.

1. 다음과 같은 두 개의 테이블 T_MATERIAL과 T_COMPOSE가 있습니다. 테이블 T_MATERIAL은 "약"(material_type=Med) 또는 "약의 성분"(material_type=Mat) 데이터를 가지고 있습니다. 테이블 T_COMPOSE는 하나의 약이 어떤 하위 성분과 하위 약으로 이루어져 있는지의 관계를 나타냅니다. 하위 약은 다시 하위 약 또는 하위 성분을 가지므로 계층 구조가 됩니다. 그리고 각 하위 약 또는 하위 성분이 몇 %를 구성하고 있는지의 정보(contain_pct)를 가지고 있습니다.

SQL> create table t_material(
  2  	material_id		number,
  3  	material_name	varchar2(10),
  4  	material_type	varchar2(3) -- Med = medicine, Mat = material
  5  );

Table created.

SQL> 
SQL> create table t_compose (
  2  	medicine_id			number,
  3  	material_id			number,
  4  	contain_pct			number
  5  );

Table created.
이제 다음과 같이 데이터를 생성합니다.
 
SQL> begin
  2  	insert into t_material values(1, 'medicine1', 'Med');
  3  	insert into t_material values(2, 'medicine2', 'Med');
  4  	insert into t_material values(3, 'material1', 'Mat');
  5  	insert into t_material values(4, 'medicine3', 'Med');
  6  	insert into t_material values(5, 'material2', 'Mat');
  7  	insert into t_material values(6, 'medicine4', 'Med');
  8  end;
  9  /

PL/SQL procedure successfully completed.

SQL> 
SQL> begin
  2  	insert into t_compose values(1, 2, 0.3); -- Med
  3  		insert into t_compose values(2, 6, 0.5);
  4  			insert into t_compose values(6, 3, 0.8);
  5  			insert into t_compose values(6, 5, 0.2);
  6  		insert into t_compose values(2, 5, 0.5);
  7  	insert into t_compose values(1, 3, 0.3); -- Mat
  8  	insert into t_compose values(1, 4, 0.2); -- Med
  9  		insert into t_compose values(4, 3, 0.7);
 10  		insert into t_compose values(4, 5, 0.3);
 11  	insert into t_compose values(1, 5, 0.2); -- Mat
 12  end;
 13  /

PL/SQL procedure successfully completed.
1번 약은 (2번 약 30% + 3번 성분 30% + 4번 약 20% + 5번 성분 20%) 으로 이루어져있죠. 2번 약은 (6번 약 50% + 5번 약 50%)로 이루어져 있고, 6번 약은 (3번 성분 80% + 5번 성분 20%)로 이루어져 있습니다. 이런 식으로 계층 구조를 이루고 있습니다.

계층 구조를 지니면서 성분의 함량(contain_pct) 정보가 존재합니다. 여기서 이런 쿼리가 필요해집니다. 1번 약을 구성하는 각 성분의 함량은 어떻게 되는가? 즉, 1번 약을 구성하는 성분인 3번 성분(material1)과 5번 성분(material2)는 각각 몇 %인가?

위와 같은 쿼리가 까다로운 것은 계층 구조를 따라 모든 노드의 값(여기서는 contain_pct)를 알아야하기 때문입니다. 간단하게 계산해보면 3번 성분(material1)의 함량을 구하려면 계층 구조를 따라가면서 0.3*0.5*0.8 + 0.3 + 0.2*0.7 = 0.56 = 56%와 같은 계산이 필요합니다.

Connect By 구문에서는 현재 값과 이전 값(부모 값)만을 알 수 있습니다. 이 한계를 극복하기 위해 나온 것이 SYS_CONNECT_BY_PATH같은 함수죠. 아래와 같이 각 노드의 모든 함량 정보를 얻을 수 있습니다.

SQL> col pholder format a10
SQL> col pct_path format a20
SQL> select
  2  	lpad('-',level,'-') as pholder,
  3  	medicine_id,
  4  	material_id,
  5  	contain_pct,
  6  	sys_connect_by_path(contain_pct,'/') as pct_path
  7  from
  8  	t_compose
  9  connect by medicine_id = prior material_id
 10  start with medicine_id = 1
 11  ;

PHOLDER    MEDICINE_ID MATERIAL_ID CONTAIN_PCT PCT_PATH
---------- ----------- ----------- ----------- --------------------
-                    1           2          .3 /.3
--                   2           5          .5 /.3/.5
--                   2           6          .5 /.3/.5
---                  6           3          .8 /.3/.5/.8
---                  6           5          .2 /.3/.5/.2
-                    1           3          .3 /.3
-                    1           4          .2 /.2
--                   4           3          .7 /.2/.7
--                   4           5          .3 /.2/.3
-                    1           5          .2 /.2

10 rows selected.
위의 값을 실제로 계산하려면 다음과 같이 별도의 함수를 이용한 로직이 필요하게 됩니다.
SQL> create or replace function get_total_pct(pct_path in varchar2)
  2  return number
  3  is
  4  	v_idx1		number := 0;
  5  	v_idx2		number;
  6  	v_temp		number;
  7  	v_total		number := 1;
  8  begin
  9  	v_idx1 := instr(pct_path, '/');
 10  
 11  	loop
 12  
 13  		v_idx2 := instr(pct_path, '/', v_idx1+1);
 14  		if v_idx2 = 0 then
 15  			v_idx2 := length(pct_path)+1;
 16  		end if;
 17  
 18  		v_temp := to_number(substr(pct_path, v_idx1+1, v_idx2-v_idx1-1));
 19  		v_total := v_total * v_temp;
 20  
 21  		v_idx1 := v_idx2;
 22  
 23  		exit when v_idx1 > length(pct_path);
 24  
 25  	end loop;
 26  
 27  	return v_total;
 28  end;
 29  /

Function created.
CONNECT BY 구문과 SYS_CONNECT_BY_PATH 함수, 그리고 위에서 정의한 함수 GET_TOTAL_PCT를 이용하면 다음과 같이 원하는 값을 얻을 수 있습니다.
SQL> with c as (
  2  	select
  3  		material_id,
  4  		get_total_pct(sys_connect_by_path(contain_pct,'/')) as comp_pct
  5  	from
  6  		t_compose
  7  	connect by medicine_id = prior material_id
  8  	start with medicine_id = 1
  9  )
 10  select
 11  	m.material_name,
 12  	sum(c.comp_pct) as total_pct
 13  from
 14  	c,
 15  	t_material m
 16  where
 17  	c.material_id = m.material_id
 18  	and m.material_type = 'Mat'
 19  group by
 20  	m.material_name
 21  ;

MATERIAL_N  TOTAL_PCT
---------- ----------
material1         .56
material2         .44
(음... 더 멋진 방법이 있을 듯... )

Recursive Subquery Factoring에서는 위의 작업을 보다 직관적으로 처리할 수 있습니다. 다음과 같이 부모의 값을 받아서 함량(contain_pct)을 계속 곱해가면 최종 자식 노드의 함량을 알 수 있죠. 그 값을 SUM 하면 함량의 합이 됩니다. 즉, Recursive Subquery Factoring의 장점은 SYS_CONNECT_BY_PATH 같은 함수의 도움을 빌리지 않아도 모든 모드의 값을 이용할 수 있다는 것입니다.

SQL> with recur_mat(comp_pct, material_id)
  2  as (
  3  	select
  4  		contain_pct,
  5  		material_id
  6  	from
  7  		t_compose
  8  	where
  9  		medicine_id = 1
 10  	union all
 11  	select
 12  		p.comp_pct * c.contain_pct,  -- 부모 * 현재 
 13  		c.material_id
 14  	from
 15  		recur_mat p,
 16  		t_compose c
 17  	where
 18  		c.medicine_id = p.material_id
 19  )
 20  select
 21  	m.material_name,
 22  	sum(r.comp_pct) as total_pct
 23  from
 24  	recur_mat r,
 25  	t_material m
 26  where
 27  	r.material_id = m.material_id
 28  	and m.material_type = 'Mat'
 29  group by
 30  	m.material_name
 31  ;

MATERIAL_N  TOTAL_PCT
---------- ----------
material1         .56
material2         .44
아... 설명이 좀 구질구질했는데요. Recursive Subquery Factoring을 이용함으로써 좀 더 작업이 간편해지는 몇 안되는 예제 중 하나라서 올려봅니다.

앞으로 11gR2가 본격적으로 사용되면 활용 예제가 더 많이 소개될 것으로 기대합니다.

저작자 표시
신고
Trackbacks 7 : Comments 5
  1. 욱짜 2010.12.01 21:41 신고 Modify/Delete Reply

    첫번째 버전 쿼리에 비효율이 존재하는군요. 귀엽게 봐주세요.

  2. salvationism 2010.12.02 10:48 신고 Modify/Delete Reply

    오라클 성능 Q&A 책을 받았습니다. 얇을 줄 알았는데 생각보다 책이 두껍네요^^
    대리인 이라는 추천사?가 인상적이 였습니다.
    직접 테스트 하신분과 받아들이는 깊이가 다르겠고, 읽는 독자의 수준마다 받아 들이는 깊이가 다르겠지만
    덕분에 편안하고 쉽게 그리고 재밌게 잘 보겠습니다.
    감사합니다. ^^

    • 욱짜 2010.12.02 10:57 신고 Modify/Delete

      감사합니다. 추천사를 쓰신 분이 워낙 내공이 깊습니다. ^^;

      저도 추천사를 받고 반성하게 되더라구요. 더 잘 적을걸!!!

      책이 두꺼운 이유는 가능한 현장감있게 설명하기 위해 테스트 결과를 많이 넣었기 때문입니다. 보시기에 따라서는 불필요한 데이터를 많이 넣은 것은 아닌가하고 생각하실수도 있는데, 읽을 때 걸림이 없도록 하기 위해 시도한 것이라고 생각해주시면 좋겠습니다.

  3. Ejql 2011.01.28 14:30 Modify/Delete Reply

    관리자의 승인을 기다리고 있는 댓글입니다

  4. more info here 2012.03.31 09:18 Modify/Delete Reply

    관리자의 승인을 기다리고 있는 댓글입니다

Write a comment

티스토리 툴바