태터데스크 관리자

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

태터데스크 메시지

저장하였습니다.

'오라클/SQL 튜닝'에 해당되는 글 11건

  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)
  5. 2010.11.29 오라클 실행 계획을 보는 새로운 방법 (10)
  6. 2010.11.25 조인 순서에 대한 간단한 테스트 (3)
  7. 2010.11.04 조인 순서 제어하기 2 (2)
  8. 2010.10.25 조인 순서 제어하기 (3)
  9. 2010.10.21 비주얼 SQL 튜닝? (6)
  10. 2010.10.11 Batching NLJ에 대한 오라클 매뉴얼의 설명

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


오라클 실행 계획을 보는 새로운 방법

오라클/SQL 튜닝 2010.11.29 19:04
대부분의 오라클 클라이언트가 아래와 같은 포맷으로 실행 계획을 보여줍니다.

하지만 다음과 같은 진짜 트리 모양으로 실행 계획을 보는 것이 더 편할 때도 있습니다.

아래 블로그 포스트를 보면 트리 모양으로 표현한 실행 계획의 가독성이 때로는 훨씬 높다는 것을 알 수 있습니다. (영어 해석 귀찮으신 분들은 그림만 보시면 됩니다. ^^)

흔히 온라인 쿼리는 Left Deep 트리, 배치 쿼리는 Right Deep 트리 형태를 따르는 경우가 많으며, 인라인 뷰를 사용하는 경우에는 Bushy 트리로 유도할 수 있다는 식의 설명을 보는 경우가 있는데 위의 글에서 소개한 것과 같이 실행 계획 자체를 트리 모양으로 볼 수 있으면 좀 더 명확하게 이해할 수가 있겠군요.

클라이언트 툴들이 좀 더 똑똑하고 비주얼해지면 좋겠습니다. 아니면 그런 툴들이 이미 있는데 제가 잘 모를 수도 있겠습니다.

PS) 아래 오동규님의 블로그 포스트를 같이 참조하시면 이해하는데 더 도움이 되겠습니다.

저작자 표시
신고
Trackbacks 17 : Comments 10
  1. salvationism 2010.11.30 10:56 신고 Modify/Delete Reply

    긴 실행계획의 경우 화면상의 제약이 심할거 같은데 그것만 극복하면..
    해상도가 좋은 노트북이 필수가 될수도.. ㅎㅎ

    • 욱짜 2010.11.30 11:02 신고 Modify/Delete

      화면상의 제약을 극복하고 보기에도 편한 UI 디자인도 필요하겠는데요. 아무리 좋은 표현 방식도 실제로 사용하기에 불편하면 의미가 없을 것 같아요.

  2. 마농 2010.11.30 15:20 신고 Modify/Delete Reply

    토드에 있는 기능이네요.
    Explain Plan 탭에서 마우스 오른쪽 버튼 클릭 Display Mode > Graphic 선택

  3. 욱짜 2010.11.30 18:19 신고 Modify/Delete Reply

    비슷한 화면들이 Toad와 EM(Enteprise Manager)의 Tuning Pack 화면에서도 제공되고 있군요. 오렌지는 어떤지 모르겠습니다.

    사용자 편의성과 가독성을 갖추면 실행 순서를 이해하는데 좋은 도구가 될 수 있겠습니다.

    실행 순서대로 트리 노드를 따라가면서 하이라이트를 해주는 기능(애니메이션) 같은 것이 있으면 특히 교육용으로 좋겠어요.

  4. 양용성 2010.12.10 10:29 신고 Modify/Delete Reply

    SQLGate 에서 오라클,DB2 용 Visual Plan 을 제작중에 있습니다.

    SSMS 에서와 유사한 형태의 왼쪽에서 오른쪽으로 보는 방식과 탑다운 방식의 플랜을 제작중입니다.

    조만간 공개하겠습니다

  5. hellower 2010.12.23 17:54 신고 Modify/Delete Reply

    이미 마농님이 말씀하셨는데,
    toad 이 이미 구현되어 있으며 animation 기능까지 있답니다.

  6. 양용성 2011.01.25 17:50 Modify/Delete Reply

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

  7. Yates33Ada 2011.11.11 11:10 Modify/Delete Reply

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

Write a comment


조인 순서에 대한 간단한 테스트

오라클/SQL 튜닝 2010.11.25 17:35
조인 순서, 즉 누가 드라이빙 테이블이 되어야 하느냐에 대한 간단하면서도 재미있는 테스트를 소개합니다.

아래와 같이 마스터(T1) - 디테일(T2) 관계를 가지는 테이블이 있습니다.

SQL> select * from v$version where rownum = 1;

BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production


SQL> create table t1(
  2  	     c1 number,
  3  	     c2 number,
  4  	     constraint t1_pk primary key (c1)
  5  );

Table created.

SQL> create table t2(
  2  	     c1 number,
  3  	     c2 number,
  4  	     c3 number,
  5  	     constraint t2_pk primary key (c1, c2),
  6  	     constraint t2_fk foreign key (c1) references t1(c1)
  7  );

Table created.

SQL> create index t2_n1 on t2(c3);

Index created.
마스터 T1은 10건입니다. 그리고 디테일 T2는 10,000건입니다.
 
SQL> insert into t1
  2  select
  3  	     level,
  4  	     level
  5  from
  6  	     dual
  7  connect by level <= 10
  8  ;

10 rows created.

SQL> insert into t2
  2  select
  3  	     mod(level,10)+1,
  4  	     level,
  5  	     mod(level,1000)
  6  from
  7  	     dual
  8  connect by level <= 10000
  9  ;

10000 rows created.

SQL> exec dbms_stats.gather_table_stats(user, 't1');

PL/SQL procedure successfully completed.

SQL> exec dbms_stats.gather_table_stats(user, 't2');

PL/SQL procedure successfully completed.
마스터 T1은 10건이고, C3 = 1 에 해당하는 디테일 T2도 10건입니다.
SQL> select count(*) from t1;

  COUNT(*)
----------
        10

SQL> select count(*) from t2 where c3 = 1;

  COUNT(*)
----------
        10
여기서 질문! 두 테이블을 NL 조인으로 조인하는 경우 드라이빙 테이블은 무엇이 되어야 할까요?
  • 테이블 T1은 크기가 작으므로 T1이 드라이빙이 되어야 한다.
  • 테이블 T2가 필터링이 좋으므로(10000건 중 10건) T2가 드라이빙이 되어야 한다.
  • 둘다 실제 건수는 10건이므로 전혀 무관하다.
아래 결과를 보기전에 잠깐 생각을 해보시기 바랍니다...

아래에 결과가 있습니다.

SQL> select /*+ gather_plan_statistics
  2  		     leading(t1) use_nl(t2) index(t2) index(t2 t2(c3)) */
  3  	     t1.c1, t1.c2, t2.c2, t2.c3
  4  from
  5  	     t1, t2
  6  where
  7  	     t1.c1 = t2.c1
  8  	     and t2.c3 = 1
  9  ;


         C1         C2         C2         C3
---------- ---------- ---------- ----------
         2          2          1          1
         2          2       1001          1
         2          2       2001          1
         2          2       3001          1
         2          2       4001          1
         2          2       5001          1
         2          2       8001          1
         2          2       9001          1
         2          2       6001          1
         2          2       7001          1

10 rows selected.

-----------------------------------------------------------------------------------
| Id  | Operation                    | Name  | Starts | E-Rows | A-Rows | Buffers |
-----------------------------------------------------------------------------------
|   0 | SELECT STATEMENT             |       |      1 |        |     10 |     118 |
|   1 |  NESTED LOOPS                |       |      1 |        |     10 |     118 |
|   2 |   NESTED LOOPS               |       |      1 |     10 |    100 |      18 |
|   3 |    TABLE ACCESS FULL         | T1    |      1 |     10 |     10 |       8 |
|*  4 |    INDEX RANGE SCAN          | T2_N1 |     10 |     10 |    100 |      10 |
|*  5 |   TABLE ACCESS BY INDEX ROWID| T2    |    100 |      1 |     10 |     100 |
-----------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   4 - access("T2"."C3"=1)
   5 - filter("T1"."C1"="T2"."C1")


SQL> select /*+ gather_plan_statistics
  2  		     leading(t1) use_nl(t2) index(t2 t2(c1, c2)) */
  3  	     t1.c1, t1.c2, t2.c2, t2.c3
  4  from
  5  	     t1, t2
  6  where
  7  	     t1.c1 = t2.c1
  8  	     and t2.c3 = 1
  9  ;

        C1         C2         C2         C3
---------- ---------- ---------- ----------
         2          2          1          1
         2          2       1001          1
         2          2       2001          1
         2          2       3001          1
         2          2       4001          1
         2          2       5001          1
         2          2       6001          1
         2          2       7001          1
         2          2       8001          1
         2          2       9001          1

10 rows selected.


-----------------------------------------------------------------------------------
| Id  | Operation                    | Name  | Starts | E-Rows | A-Rows | Buffers |
-----------------------------------------------------------------------------------
|   0 | SELECT STATEMENT             |       |      1 |        |     10 |     278 |
|   1 |  NESTED LOOPS                |       |      1 |        |     10 |     278 |
|   2 |   NESTED LOOPS               |       |      1 |     10 |  10000 |      57 |
|   3 |    TABLE ACCESS FULL         | T1    |      1 |     10 |     10 |       8 |
|*  4 |    INDEX RANGE SCAN          | T2_PK |     10 |   1000 |  10000 |      49 |
|*  5 |   TABLE ACCESS BY INDEX ROWID| T2    |  10000 |      1 |     10 |     221 |
-----------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   4 - access("T1"."C1"="T2"."C1")
   5 - filter("T2"."C3"=1)


SQL> select /*+ gather_plan_statistics
  2  		     leading(t2) use_nl(t1) index(t2) */
  3  	     t1.c1, t1.c2, t2.c2, t2.c3
  4  from
  5  	     t1, t2
  6  where
  7  	     t1.c1 = t2.c1
  8  	     and t2.c3 = 1
  9  ;

        C1         C2         C2         C3
---------- ---------- ---------- ----------
         2          2          1          1
         2          2       1001          1
         2          2       2001          1
         2          2       3001          1
         2          2       4001          1
         2          2       5001          1
         2          2       8001          1
         2          2       9001          1
         2          2       6001          1
         2          2       7001          1

10 rows selected.


------------------------------------------------------------------------------------
| Id  | Operation                     | Name  | Starts | E-Rows | A-Rows | Buffers |
------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT              |       |      1 |        |     10 |      27 |
|   1 |  NESTED LOOPS                 |       |      1 |        |     10 |      27 |
|   2 |   NESTED LOOPS                |       |      1 |     10 |     10 |      17 |
|   3 |    TABLE ACCESS BY INDEX ROWID| T2    |      1 |     10 |     10 |      13 |
|*  4 |     INDEX RANGE SCAN          | T2_N1 |      1 |     10 |     10 |       3 |
|*  5 |    INDEX UNIQUE SCAN          | T1_PK |     10 |      1 |     10 |       4 |
|   6 |   TABLE ACCESS BY INDEX ROWID | T1    |     10 |      1 |     10 |      10 |
------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   4 - access("T2"."C3"=1)
   5 - access("T1"."C1"="T2"."C1")

예상하신대론가요?

해석은 각자의 몫! @_@

저작자 표시
신고
Trackbacks 5 : Comments 3
  1. 조용중 2010.11.26 01:53 신고 Modify/Delete Reply

    정답을 볼려고 끝까지 내려봤는데 "해석은 각자의 몫"...읽기만하다가 오랜만에 '타의적'으로 생각볼수 있어서 보람있었습니다. ㅎㅎㅎ

    • 욱짜 2010.11.26 11:09 신고 Modify/Delete

      해석에 어려움은 없으셨죠?

      곧 좀 더 재밌는 테스트로 이 주제를 또 다루겠습니다.

  2. tohappy 2011.01.05 15:37 Modify/Delete Reply

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

Write a comment


조인 순서 제어하기 2

오라클/SQL 튜닝 2010.11.04 15:16
아래 포스트에서 조인 순서 제어에 대한 짤막한 이야기를 한 바 있습니다. 이번에는 서브 쿼리가 포함된 조금 더 복잡한 예를 보겠습니다.

우선 아래와 같이 마스터 테이블 M1과 디테일 테이블 D1, D2, D3를 만듭니다.

create table m
as
select level as c1, level as c2
from dual connect by level <= 1000;

create table d1
as
select mod(level,1000) as c1, level as c2
from dual connect by level <= 10000;


create table d2
as
select mod(level,1000) as c1, level as c2
from dual connect by level <= 20000;

create table d3
as
select mod(level,1000) as c1, level as c2
from dual connect by level <= 30000;

create index m_n1 on m(c1);
create index m_n2 on m(c2);
create index d1_n1 on d1(c1);
create index d2_n1 on d2(c1);
create index d3_n1 on d3(c1);

@gather m
@gather d1
@gather d2
@gather d3
M, D1, D3를 조인하고, D2를 서브쿼리를 이용해 필터(NO_UNNEST 힌트를 통해 강제로 Unnesting을 막음)하는 쿼리가 있습니다. 우리가 원하는 것은 { M -> D1 -> D2 -> D3 }의 순서로(이것이 최상의 조인 순서로 가정하고) 만드는 것입니다.

1. PUSH_SUBQ 힌트를 이용해 Subquery를 Push하면 아래와 같이 { M -> D2 -> D1 -> D3 }의 조인 순서를 따릅니다.

explain plan for
select /*+ use_nl(d1 d3) index(d1) index(d3) */
	*
from
	m, d1, d3
where
	m.c1 = d1.c1
	and m.c1 = d3.c1
	and exists (select /*+ no_unnest index(d2) push_subq */ null from d2 where m.c1 = d2.c1 and d2.c2 between 1 and 1000)
	and m.c2 between 1 and 100
	and d1.c1 = 1
;

TPACK@ukja1120> select * from table(dbms_xplan.display(format=>'basic'));

--------------------------------------------------
| Id  | Operation                        | Name  |
--------------------------------------------------
|   0 | SELECT STATEMENT                 |       |
|   1 |  NESTED LOOPS                    |       |
|   2 |   NESTED LOOPS                   |       |
|   3 |    NESTED LOOPS                  |       |
|   4 |     TABLE ACCESS BY INDEX ROWID  | M     |
|   5 |      INDEX RANGE SCAN            | M_N1  |
|   6 |       TABLE ACCESS BY INDEX ROWID| D2    |
|   7 |        INDEX RANGE SCAN          | D2_N1 |
|   8 |     TABLE ACCESS BY INDEX ROWID  | D1    |
|   9 |      INDEX RANGE SCAN            | D1_N1 |
|  10 |    INDEX RANGE SCAN              | D3_N1 |
|  11 |   TABLE ACCESS BY INDEX ROWID    | D3    |
--------------------------------------------------
2. NO_PUSH_SUBQ 힌트를 이용해 Subquery Pushing을 못하게 하면 { M -> D1 -> D3 -> D2 }의 조인 순서를 따릅니다.
explain plan for
select /*+ use_nl(d1 d3) index(d1) index(d3) */
	*
from
	m, d1, d3
where
	m.c1 = d1.c1
	and m.c1 = d3.c1
	and exists (select /*+ no_unnest index(d2) no_push_subq */ null from d2 where m.c1 = d2.c1 and d2.c2 between 1 and 1000)
	and m.c2 between 1 and 100
	and d1.c1 = 200
;

-------------------------------------------------
| Id  | Operation                       | Name  |
-------------------------------------------------
|   0 | SELECT STATEMENT                |       |
|   1 |  FILTER                         |       |
|   2 |   NESTED LOOPS                  |       |
|   3 |    NESTED LOOPS                 |       |
|   4 |     NESTED LOOPS                |       |
|   5 |      TABLE ACCESS BY INDEX ROWID| M     |
|   6 |       INDEX RANGE SCAN          | M_N1  |
|   7 |      TABLE ACCESS BY INDEX ROWID| D1    |
|   8 |       INDEX RANGE SCAN          | D1_N1 |
|   9 |     INDEX RANGE SCAN            | D3_N1 |
|  10 |    TABLE ACCESS BY INDEX ROWID  | D3    |
|  11 |   TABLE ACCESS BY INDEX ROWID   | D2    |
|  12 |    INDEX RANGE SCAN             | D2_N1 |
-------------------------------------------------
3. 흐음... 다음과 같이 QB_NAME 힌트와 LEADING 힌트를 사용하면? QB_NAME 힌트로 제어가 힘들어보입니다.
explain plan for
select /*+ use_nl(d1 d3) index(d1) index(d3) leading(m d1 @sq d3 ) */
	*
from
	m, d1, d3
where
	m.c1 = d1.c1
	and m.c1 = d3.c1
	and exists (select /*+ qb_name(sq) no_unnest index(d2) no_push_subq */ null from d2 where m.c1 = d2.c1 and d2.c2 between 1 and 1000)
	and m.c2 between 1 and 100
	and d1.c1 = 200
;

-------------------------------------------------
| Id  | Operation                       | Name  |
-------------------------------------------------
|   0 | SELECT STATEMENT                |       |
|   1 |  FILTER                         |       |
|   2 |   NESTED LOOPS                  |       |
|   3 |    NESTED LOOPS                 |       |
|   4 |     NESTED LOOPS                |       |
|   5 |      TABLE ACCESS BY INDEX ROWID| M     |
|   6 |       INDEX RANGE SCAN          | M_N1  |
|   7 |      TABLE ACCESS BY INDEX ROWID| D1    |
|   8 |       INDEX RANGE SCAN          | D1_N1 |
|   9 |     INDEX RANGE SCAN            | D3_N1 |
|  10 |    TABLE ACCESS BY INDEX ROWID  | D3    |
|  11 |   TABLE ACCESS BY INDEX ROWID   | D2    |
|  12 |    INDEX RANGE SCAN             | D2_N1 |
-------------------------------------------------
4. 이 때 이전 글 조인 순서 제어하기에서 소개한 방법을 쓸 수 있습니다. 아래와 같이 Subquery 내에서 WHERE m.c1 + 0*d1.c1 = d2.c1 조건을 사용하면 D1이 항상 D2의 선행이 되어야 합니다. 그리고 PUSH_SUBQ 힌트에 의해 D3 보다는 앞으로 가게 됩니다. 따라서 { M -> D1 -> D2 -> D3 }의 순서로 액세스하게 할 수 있습니다.
explain plan for
select /*+ use_nl(d1 d3) index(d1) index(d3) */
	*
from
	m, d1, d3
where
	m.c1 = d1.c1
	and m.c1 = d3.c1
	and exists (select /*+ no_unnest index(d2) push_subq */ null from d2 where m.c1 + 0*d1.c1 = d2.c1 and d2.c2 between 1 and 10)
	and m.c2 between 1 and 100
	and d1.c1 = 200
;

--------------------------------------------------
| Id  | Operation                        | Name  |
--------------------------------------------------
|   0 | SELECT STATEMENT                 |       |
|   1 |  NESTED LOOPS                    |       |
|   2 |   NESTED LOOPS                   |       |
|   3 |    NESTED LOOPS                  |       |
|   4 |     TABLE ACCESS BY INDEX ROWID  | M     |
|   5 |      INDEX RANGE SCAN            | M_N1  |
|   6 |     TABLE ACCESS BY INDEX ROWID  | D1    |
|   7 |      INDEX RANGE SCAN            | D1_N1 |
|   8 |       TABLE ACCESS BY INDEX ROWID| D2    |
|   9 |        INDEX RANGE SCAN          | D2_N1 |
|  10 |    INDEX RANGE SCAN              | D3_N1 |
|  11 |   TABLE ACCESS BY INDEX ROWID    | D3    |
--------------------------------------------------
오라클과 같이 힌트가 풍부하지 않은 다른 DBMS에서도 위와 같은 방법을 사용하면 조인 순서를 거의 원하는 대로 제어할 수 있지 않을까요?
저작자 표시
신고

'오라클 > SQL 튜닝' 카테고리의 다른 글

오라클 실행 계획을 보는 새로운 방법  (10) 2010.11.29
조인 순서에 대한 간단한 테스트  (3) 2010.11.25
조인 순서 제어하기 2  (2) 2010.11.04
조인 순서 제어하기  (3) 2010.10.25
비주얼 SQL 튜닝?  (6) 2010.10.21
Trackback 0 : Comments 2
  1. hellower 2010.11.08 16:23 신고 Modify/Delete Reply

    참고로, 서브쿼리를 푼후에
    아래와 같이 하면 query block 과 leading 으로도 조인 순서를 M -> D1 -> D2 -> D3 이렇게 바꿀수 있습니다.

    SELECT /*+ use_nl(d1 d3) index(d1) index(d3) leading(@SEL$5DA710D3 m@sel$1 d1@sel$1 d2@sel$2 d3@sel$1) */
    *
    FROM
    m, d1, d3
    WHERE
    m.c1 = d1.c1
    AND m.c1 = d3.c1
    AND EXISTS (SELECT /*+ unnest index(d2) */ NULL FROM d2 WHERE m.c1 = d2.c1 AND d2.c2 BETWEEN 1 AND 1000)
    AND m.c2 BETWEEN 1 AND 100
    AND d1.c1 = 200
    ;

Write a comment


조인 순서 제어하기

오라클/SQL 튜닝 2010.10.25 14:57
오라클이 제공하는 힌트가 너무 강력해서 가끔 기본적인 것을 지나칠 때가 있습니다. 가끔은 힌트가 없다면 어떻게 실행 계획을 제어할 수 있을까를 고민해보면 재미있는 발견을 할 때가 있습니다.

예를 들어 아래와 같은 5개의 테이블이 있습니다.

create table t1(c1, c2)
as 
select
	 level, level
from dual
connect by level <= 5000;

create table t2(c1, c2)
as 
select
	 level, level
from dual
connect by level <= 4000;

create table t3(c1, c2)
as 
select
	 level, level
from dual
connect by level <= 3000;

create table t4(c1, c2)
as 
select
	 level, level
from dual
connect by level <= 2000;

create table t5(c1, c2)
as 
select
	 level, level
from dual
connect by level <= 1000;

create index t1_n1 on t1(c1);
create index t2_n1 on t2(c1);
create index t3_n1 on t3(c1);
create index t4_n1 on t4(c1);
create index t5_n1 on t5(c1);

exec dbms_stats.gather_table_stats(user, 't1');
exec dbms_stats.gather_table_stats(user, 't2');
exec dbms_stats.gather_table_stats(user, 't3');
exec dbms_stats.gather_table_stats(user, 't4');
exec dbms_stats.gather_table_stats(user, 't5');
테이블 T1, T2, T3, T4, T4를 조인하는 쿼리에서 조인 순서는 어떻게 될까요? 테이블의 데이터 분포를 보면 T1(c1 between 1 and 10 조건 때문에)이 드라이빙 테이블이 되고 그 다음은 T5(1000건), T4(2000건), T3(3000건), T2(4000건)이 됨을 알 수 있습니다.
explain plan for
select 
	*
from
	t1, t2, t3, t5, t4
where
	t1.c1 = t2.c1
	and t1.c1 = t3.c1
	and t1.c1 = t4.c1
	and t1.c1 = t5.c1
	and t1.c2 between 1 and 10
;

--------------------------------------
| Id  | Operation             | Name |
--------------------------------------
|   0 | SELECT STATEMENT      |      |
|*  1 |  HASH JOIN            |      |
|*  2 |   HASH JOIN           |      |
|*  3 |    HASH JOIN          |      |
|*  4 |     HASH JOIN         |      |
|*  5 |      TABLE ACCESS FULL| T1   |
|   6 |      TABLE ACCESS FULL| T5   |
|   7 |     TABLE ACCESS FULL | T4   |
|   8 |    TABLE ACCESS FULL  | T3   |
|   9 |   TABLE ACCESS FULL   | T2   |
--------------------------------------
만일 조인 순서를 T1 -> T2 -> T3 -> T4 -> T5로 만들고 싶다면? 아래와 같이 ORDERED 힌트를 사용하면 되겠죠? 또는 LEADING(t1 t2 t3 t4 t5) 힌트를 사용해도 됩니다.
explain plan for
select /*+ ordered */
	*
from
	t1, t2, t3, t5, t4
where
	t1.c1 = t2.c1
	and t1.c1 = t3.c1
	and t1.c1 = t4.c1
	and t1.c1 = t5.c1
	and t1.c2 between 1 and 10
;

--------------------------------------
| Id  | Operation             | Name |
--------------------------------------
|   0 | SELECT STATEMENT      |      |
|*  1 |  HASH JOIN            |      |
|*  2 |   HASH JOIN           |      |
|*  3 |    HASH JOIN          |      |
|*  4 |     HASH JOIN         |      |
|*  5 |      TABLE ACCESS FULL| T1   |
|   6 |      TABLE ACCESS FULL| T2   |
|   7 |     TABLE ACCESS FULL | T3   |
|   8 |    TABLE ACCESS FULL  | T4   |
|   9 |   TABLE ACCESS FULL   | T5   |
--------------------------------------
만일 힌트를 사용하지 않고(그리고 RBO가 아닌 CBO라고 하면), 조인 순서가 항상 위와 같이 나오게 하고 싶다면 어떻게 해야될까요? 힌트에 익숙해지다보면 이런 간단한 질문에 대한 답이 선뜻 나오지 않는 경우가 많습니다.

가장 전형적인 방법은 아래와 같이 t1.c1 = t3.c1 조인 조건을 t1.c1 + 0*t2.c1 = t3.c1 같은 형태로 사용하는 것입니다. t3.c1에서 조인이 이루어지려면 t2.c1 값을 알아야 하므로 반드시 T2 -> T3 순서로 조인이 됩니다. 비슷한 원리로 조건절을 만들어가면 됩니다.

explain plan for
select 
	*
from
	t1, t2, t3, t4, t5
where
	t1.c1 = t2.c1
	and t1.c1 + 0*t2.c1 = t3.c1  -- t2.c1 값을 알아야 t3.c1 조인 가능
	and t1.c1 + 0*t3.c1= t4.c1  -- t3.c1 값을 알아야 t4.c1 조인 가능
	and t1.c1 + 0*t4.c1 = t5.c1  -- t4.c1 값을 알아야 t5.c1 조인 가능
	and t1.c2 between 1 and 10
;

--------------------------------------
| Id  | Operation             | Name |
--------------------------------------
|   0 | SELECT STATEMENT      |      |
|*  1 |  HASH JOIN            |      |
|*  2 |   HASH JOIN           |      |
|*  3 |    HASH JOIN          |      |
|*  4 |     HASH JOIN         |      |
|*  5 |      TABLE ACCESS FULL| T1   |
|   6 |      TABLE ACCESS FULL| T2   |
|   7 |     TABLE ACCESS FULL | T3   |
|   8 |    TABLE ACCESS FULL  | T4   |
|   9 |   TABLE ACCESS FULL   | T5   |
--------------------------------------
위의 방법을 응용하면 조인 순서를 자유롭게 제어할 수 있습니다. 예를 들어 조인 순서를 T1 -> T2 -> T4 -> T3 -> T5(T4가 T3보다 먼저 조인되게)로 만들려면 어떻게 해야할까요?
explain plan for
select 
	*
from
	t1, t2, t3, t4, t5
where
	t1.c1 = t2.c1
	{ 여기에 어떤 조건이 들어가야 아래의 실행 계획이 나올까요? }
	and t1.c2 between 1 and 10
;

--------------------------------------
| Id  | Operation             | Name |
--------------------------------------
|   0 | SELECT STATEMENT      |      |
|*  1 |  HASH JOIN            |      |
|*  2 |   HASH JOIN           |      |
|*  3 |    HASH JOIN          |      |
|*  4 |     HASH JOIN         |      |
|*  5 |      TABLE ACCESS FULL| T1   |
|   6 |      TABLE ACCESS FULL| T2   |
|   7 |     TABLE ACCESS FULL | T4   |
|   8 |    TABLE ACCESS FULL  | T3   |
|   9 |   TABLE ACCESS FULL   | T5   |
--------------------------------------
정답은 이미 공개된 것이나 마찬가지이기 때문에 여기에 적지는 않겠습니다. 혹시 잠깐 시간내서 도전해보고 싶은 분이 있으면 댓글로 남기셔도 좋겠습니다.

그래도 역시 힌트가 편하긴 편합니다!

저작자 표시
신고

'오라클 > SQL 튜닝' 카테고리의 다른 글

조인 순서에 대한 간단한 테스트  (3) 2010.11.25
조인 순서 제어하기 2  (2) 2010.11.04
조인 순서 제어하기  (3) 2010.10.25
비주얼 SQL 튜닝?  (6) 2010.10.21
Batching NLJ에 대한 오라클 매뉴얼의 설명  (0) 2010.10.11
Trackback 0 : Comments 3
  1. salvation 2010.10.27 00:48 신고 Modify/Delete Reply

    재미있는 글 잘 보았습니다. ^^
    앞으로 올라올 새로운 포스팅을 기대하며..

  2. 드림걸 2010.11.02 11:05 신고 Modify/Delete Reply

    항상 힌트로 제어할려고난 했는데 재밌는 글 읽고갑니다^^

    • 욱짜 2010.11.02 11:09 신고 Modify/Delete

      힌트로는 제어하기 힘든 경우에 위의 방법을 쓸 수 있습니다. 조만간 간단한 사례를 포스팅하겠습니다.

Write a comment


비주얼 SQL 튜닝?

오라클/SQL 튜닝 2010.10.21 13:49
복잡한 SQL 튜닝을 어떻게 하면 손쉽게, 정확하게 자동화할 것인가는 이 업계의 오랜 숙원이자 도전과제입니다. 오라클이 제공하는 자동 SQL 튜닝(Automatic SQL Tuning) 기능조차도 외면받는 걸 보면 이것이 얼마나 어려운 일인지 알 수 있습니다.

Dan Tow의 저서 [SQL Tuning]을 보면 SQL 문장을 비주얼한 다이어그램으로 표현해서 최적의 실행 계획을 찾는 방법을 기술하고 있습니다. 대단히 매력적인 방법이고, 실제로 쿼리 튜닝을 할 때는 우리 머리 속에서는 이런 일이 발생합니다. 이런 개념을 GUI로 구현한 툴이 있어서 소개합니다.

DB Optimizer라는 툴인데요, 국내에 정식 공급되는지는 모르겠습니다. 하여간 이 툴이 제공하는 VST(Visual SQL Tuning) 기능을 보면 SQL 다이어그램 기반의 튜닝 기법을 구현하기 위해 대단한 노력을 기울이고 있음을 알 수 있습니다.

간단한 예를 들어, 아래와 같은 쿼리가 있다고 가정하면

select distinct * from foo.a, foo.c, foo.d, foo.g
WHERE a.planted_date = to_date('02/10/2008','dd/mm/yyyy')
AND a.pears = 'D'
AND a.green_beans = '1'
AND a.planted_date = c.planted_date
AND a.pears = c.pears
AND a.zuchinis = c.zuchinis
AND a.brocoli = c.brocoli
AND a.planted_date = d.planted_date
AND a.pears = d.pears
AND a.harvest_size = d.harvest_size
AND c.oranges = d.oranges
AND c.apples = d.apples
AND (d.lemons = 0 OR d.lemons IS NULL)
AND a.planted_date = g.planted_date
AND a.pears = g.pears
AND a.harvest_size = g.harvest_size
AND c.oranges = g.oranges
AND c.apples = g.apples
AND (g.lemons = 0 OR g.lemons IS NULL)
and a.zuchinis='0236'
ORDER BY a.zuchinis, a.brocoli;
위의 쿼리를 다이어그램으로 표현하면 아래 그림과 같습니다.

전체 로우수가 1,777,110건이고 필터율이 0.674%인 테이블 A를 선행으로 테이블 C를 조인하면 44,106건이 됩니다. A --> C 의 순서로 조인하는 것이 유리할 것이라는 것을 직관적으로 알 수 있습니다(정확한 해석법은 매뉴얼 참조). 이런 방식으로 조인을 이어나가면 아래와 같은 순서가 됩니다.

지나치게 간단한 예이지만, 대단히 흥미롭습니다. 이 정도까지 구현했다는 것이 대단합니다.

DB Optimizer의 SQL Tuning 모듈은 VST 외에도 많은 기능들이 있는데 전체 그림은 아래와 같습니다.

관심있으신 분들은 다운로드받아서 사용해보셔도 좋겠습니다. Dan Tow의 저서 [SQL Tuning]도 기회가 되면 읽어보시구요.

저작자 표시
신고

'오라클 > SQL 튜닝' 카테고리의 다른 글

조인 순서 제어하기 2  (2) 2010.11.04
조인 순서 제어하기  (3) 2010.10.25
비주얼 SQL 튜닝?  (6) 2010.10.21
Batching NLJ에 대한 오라클 매뉴얼의 설명  (0) 2010.10.11
PLAN_HASH_VALUE  (0) 2010.10.06
Trackbacks 2 : Comments 6
  1. proud0 2010.10.22 10:40 신고 Modify/Delete Reply

    제가 알기론 공식 공급되고 있습니다.

    http://www.devgear.co.kr/products/db-optimizer-xe/

    • 욱짜 2010.10.22 19:30 신고 Modify/Delete

      좋은 정보 감사합니다. 최신 버전이 반영되어 있지 않을거로 보니 아직 시장의 반응은 없는 듯 하네요.

  2. 2dong0 2010.10.24 02:02 신고 Modify/Delete Reply

    와 정말 멋진데요~^^ 도스에서 윈도우로 넘어가는 것처럼 혁명 같습니다.^^

    • 욱짜 2010.10.25 13:12 신고 Modify/Delete

      이런 방법론이 확실히 장점이 있는데, 현실적인 문제가 있는거 같습니다. 이 툴을 사용해서 SQL 튜닝을 하려면 SQL Diagram을 작성하고 해석하는 방법을 알아야 하는데 제법 학습이 필요하거든요. 투자가 필요한 셈인데, 그걸 유도하기가 쉽지는 않아 보입니다.

  3. extremedb 2010.10.26 15:22 신고 Modify/Delete Reply

    헐 ~ 이런 툴이 있었네요.
    제가 찾고 있던 종류입니다.
    감사합니다.

    • 욱짜 2010.10.26 15:56 신고 Modify/Delete

      누군가는 이 툴을 보고 눈이 반짝할 줄 알았습니다. ㅎㅎ

      혹시 실제로 사용하시게 되면 기술적인 평가를 해주셔도 좋겠습니다!

Write a comment


Batching NLJ에 대한 오라클 매뉴얼의 설명

오라클/SQL 튜닝 2010.10.11 18:57
Oracle 11g의 Batching NLJ에 의해 물리 I/O(Physical I/O)가 발생할 때 정렬이 깨어지는 것처럼 보이는 현상을 이 포스트에서 소개한 바 있습니다.

오늘 우연히 오라클 매뉴얼(Performance Tuning Guide)에서 Oracle 11g의 Batching NLJ에 대해 설명하고 있는 부분을 발견했습니다.

단순하면서도 명확하게 설명되어 있습니다. 이 설명과 더불어 제가 설명한 힌트와 히든 파라미터를 같이 이해하면 운영 시스템에서 문제가 발생했을 때 효과적으로 대처하실 수 있을 거 같습니다.
저작자 표시
신고

'오라클 > SQL 튜닝' 카테고리의 다른 글

조인 순서 제어하기 2  (2) 2010.11.04
조인 순서 제어하기  (3) 2010.10.25
비주얼 SQL 튜닝?  (6) 2010.10.21
Batching NLJ에 대한 오라클 매뉴얼의 설명  (0) 2010.10.11
PLAN_HASH_VALUE  (0) 2010.10.06
tags : Batching NLJ
Trackback 0 : Comment 0

Write a comment

티스토리 툴바