태터데스크 관리자

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

태터데스크 메시지

저장하였습니다.

'Partition Outer Join'에 해당되는 글 1건

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

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

티스토리 툴바