꿈꾸는 개발자, DBA 커뮤니티 구루비

1. 인라인 뷰 활용

인라인 뷰 활용
1:M 조인 & 그룹핑 은 M을 그룹핑 후 조인이 유리
Bad


select min(t2.상품명) 상품명, sum(t1.판매수량) 판매수량, sum(t1.판매금액) 판매금액
  from 일별상품판매 t1, 상품 t2
 where t1.판매일자 between '20090101' and '20091231'
   and t1.상품코드 = t2.상품코드
 group by t2.상품코드
 
Call Count CPU Time Elapsed Time Disk Query Current Rows 
---- ---- ------- --------- ---- ---- ---- ---- 
Parse 1 0.000 0.000 0 0 0 0 
Execute 1 0.000 0.000 0 0 0 0
Fetch 101 5.109 13.805 52744 782160 0 1000 
---- ---- ------- --------- ---- ---- ---- ----
Total 103 5.109 13.805 52744 782160 0 1000 

Rows Row Source Operation
----- ---------------------------------------------------
1000 SORT GROUP BY (cr=782160 pr=52744 pw=0 time=13804391 us) 
365000 NESTED LOOPS (cr=782160 pr=52744 pw=0 time=2734163731004 us) 
365000 TABLE ACCESS FULL 일별상품판매 (cr=52158 pr=51800 pw=0 time=456175026878 us) 
365000 TABLE ACCESS BY INDEX ROWID 상품 (cr=730002 pr=944 pw=0 time=872397482545 us) 
365000 INDEX UNIQUE SCAN 상품_PK (cr=365002 pr=4 pw=0 time=416615350685 us) 
-- 일별상품판매 테이블의 365,000 개 레코드 마다 상품 테이블과 조인 시도
-- 730,002개의 블록 I/O 발생, 13.8초 소요

Good


select t2.상품명, t1.판매수량, t1.판매금액
  from (select 상품코드, sum(판매수량) 판매수량, sum(판매금액) 판매금액
          from 일별상품판매
	 where 판매일자 between '20090101' and '20091231'
	 group by 상품코드) t1, 상품 t2
 where t1.상품코드 = t2.상품코드 
 
Call Count CPU Time Elapsed Time Disk Query Current Rows
--- ----- -------- --------- ---- ---- ----- ----
Parse 1 0.000 0.000 0 0 0 0
Execute 1 0.000 0.000 0 0 0 0
Fetch 101 1.422 5.540 51339 54259 0 1000
--- ----- -------- --------- ---- ---- ----- ----
Total 103 1.422 5.540 51339 54259 0

1000 Rows Row Source Operation 
---- --------------------------------------------------- 
1000 NESTED LOOPS (cr=54259 pr=51339 pw=0 time=5540320 us) 
1000 VIEW (cr=52158 pr=51339 pw=0 time=5531294 us) 
1000 SORT GROUP BY (cr=52158 pr=51339 pw=0 time=5530293 us) 
365000 TABLE ACCESS FULL 일별상품판매 (cr=52158 pr=51339 pw=0 time=2920041 us) 
1000 TABLE ACCESS BY INDEX ROWID 상품 (cr=2101 pr=0 pw=0 time=8337 us) 
1000 INDEX UNIQUE SCAN 상품_PK (cr=1101 pr=0 pw=0 time=3747 us) 
-- 일별상품판매 상품코드별 그룹핑 : 1,000 건
-- 상품 테이블과 조인도 1,000 건 수행
-- 2,101개의 블록 I/O 발생, 5.5초 소요

2. 배타적 관계의 조인

어떤 엔터티가 두 개 이상의 다른 엔터티의 합집합과 관계(Relationship)를 갖는 것 : 상호배타적(Exclusive OR)관계
작업지시 테이블 - (개통신청 및 장애접수 테이블)

작업지시 테이블 구현
1. 개통신청번호, 장애접수번호 컬럼 별도 관리


[예제] Oracle 
select /*+ ordered use_nl(b) use_nl(c) */ a.작업일련번호, a.작업자ID
     , a.작업상태코드 , nvl(b.고객번호, c.고객번호) 고객번호 , nvl(b.주소, c.주소) 주소, ...... 
  from 작업지시 a, 개통신청 b, 장애접수 c
 where a.방문예정일시 = :방문예정일시
   and b.개통신청번호(+) = a.개통신청번호 
   and c.장애접수번호(+) = a.장애접수번호

[예제] SQL Server 
select a.작업일련번호, a.작업자ID, a.작업상태코드 , isnull(b.고객번호, c.고객번호) 고객번호 , isnull(b.주소, c.주소) 주소, ...... 
  from 작업지시 a left outer join 개통신청 b on b.개통신청번호 = a.개통신청번호 
                  left outer join 장애접수 c on c.장애접수번호 = a.장애접수번호
 where a.방문예정일시 = :방문예정일시
option(force order, loop join)

1. 작업구분과 접수번호 칼럼을 두고, 작업구분('1', '2')에 따라 접수번호 컬럼에 해당 값 관리


select x.작업일련번호, x.작업자ID, x.작업상태코드, y.고객번호, y.주소, ...... 
  from 작업지시 x, 개통신청 y
 where x.방문예정일시 = :방문예정일시 
   and x.작업구분 = '1' 
   and y.개통신청번호 = x.접수번호 
 union all 
select x.작업일련번호, x.작업자ID, x.작업상태코드, y.고객번호, y.주소, ...... 
  from 작업지시 x, 장애접수 y
 where x.방문예정일시 = :방문예정일시 
   and x.작업구분 = '2' 
   and y.장애접수번호 = x.접수번호 

-- 인덱스[작업구분 + 방문예정일시] 사용시 GOOD
-- 인덱스[방문예정일시 + 작업구분] 사용시 인덱스 스캔범위 중복 발생
-- 인덱스[방문예정일시] 사용시 '작업구분' 필터링을 위한 Random 테이블 액세스 발생


[예제] Oracle

select /*+ ordered use_nl(b) use_nl(c) */ a.작업일련번호, a.작업자ID
     , a.작업상태코드 , nvl(b.고객번호, c.고객번호) 고객번호 , nvl(b.주소, c.주소) 주소, ...... 
  from 작업지시 a, 개통신청 b, 장애접수 c
 where a.방문예정일시 = :방문예정일시 
   and b.개통신청번호(+) = decode(a.작업구분, '1', a.접수번호) 
   and c.장애접수번호(+) = decode(a.작업구분, '2', a.접수번호) 
   
[예제] SQL Server
select a.작업일련번호, a.작업자ID, a.작업상태코드 , isnull(b.고객번호, c.고객번호) 고객번호 , isnull(b.주소, c.주소) 주소, ...... 
  from 작업지시 a left outer join 개통신청 b on b.개통신청번호 = (case when a.작업구분 = '1' then a.접수번호 end)
                  left outer join 장애접수 c on c.장애접수번호 = (case when a.작업구분 = '2' then a.접수번호 end)
 where a.방문예정일시 = :방문예정일시
option(force order, loop join) 

-- 비효율 해소

3. 부등호 조인

업무에 따라 between, like, 부등호 같은 연산자 조인 필요
누적 매출(running total) 구하기


-- 윈도우(분석) 함수 이용
select 지점, 판매월, 매출 
     , sum(매출) over (partition by 지점 order by 판매월 range between unbounded preceding and current row) 누적매출
  from 월별지점매출 

-- 부등호 조인 적용
select t1.지점, t1.판매월, min(t1.매출) 매출, sum(t2.매출) 누적매출
  from 월별지점매출 t1, 월별지점매출 t2 where t2.지점 = t1.지점 
   and t2.판매월 <= t1.판매월 
 group by t1.지점, t1.판매월 
 order by t1.지점, t1.판매월;

4. Between 조인

가. 선분이력이란?

시작시점과 종료시점을 함께 관리하는 것, 가장 마지막 이력의 종료일자는 항상 '99991231' 이어야 함

선분형태로 관리하면 쿼리가 간단해짐


select 고객번호, 연체금액, 연체개월수 from 고객별연체금액 where 고객번호 = '123' and '20040815' between b.시작일자 and b.종료일자 ;

점형태로 관리하면 쿼리가 복잡해짐


select 고객번호, 연체금액, 연체개월수 from 고객별연체금액 a 
where 고객번호 = '123' and 연체변경일자 = (select max(연체변경일자) 
                                             from 고객별연체금액
					    where 고객번호 = a.고객번호 and 변경일자 <= '20040815') ;

선분형태 단점
- 이력이 추가될 때 마다 기존 최종 이력의 종료일자도 같이 변경 필요 (DML 부하)
- 일반적인 PK [ 마스터 키 + 종료일자 + 시작일자 ] 가 이력 변경시 같이 변경 됨 (RDBMS 설계 사상)
- 개체 무결성을 완벽히 보장하기 어려움 (가장 큰 단점)

나. 선분이력 기본 조회 패턴

과거, 현재, 미래 임의 시점 조회


select 연체개월수, 연체금액 from 고객별연체금액 where 고객번호 = :cust_num and :dt between 시작일자 and 종료일자

현재 시점


select 연체개월수, 연체금액 from 고객별연체금액 where 고객번호 = :cust_num and 종료일자 = '99991231'

현재 시점 (선분이력 테이블에 정보를 미리 입력 허용시)


-- 고객별 연체변경이력을 지금 등록 하지만, 유효 시작일자가 내일 일때
Oracle
select 연체개월수, 연체금액 from 고객별연체금액
 where 고객번호 = :cust_num and to_char(sysdate, 'yyyymmdd') between 시작일자 and 종료일자
 
 SQL Server
select 연체개월수, 연체금액 from 고객별연체금액 
where 고객번호 = :cust_num and convert(varchar(8), getdate(), 112) between 시작일자 and 종료일자

다. 선분이력 조인

과거/현재/미래의 임의 시점 조회


select c.고객번호, c.고객명, c1.고객등급, c2.전화번호 from 고객 c, 고객등급변경이력 c1, 전화번호변경이력 c2 
 where c.고객번호 = :cust_num and c1.고객번호 = c.고객번호 and c2.고객번호 = c.고객번호
   and :dt between c1.시작일자 and c1.종료일자 and :dt between c2.시작일자 and c2.종료일자

현재 시점


select c.고객번호, c.고객명, c1.고객등급, c2.전화번호 from 고객 c, 고객등급변경이력 c1, 전화번호변경이력 c2
 where c.고객번호 = :cust_num and c1.고객번호 = c.고객번호 and c2.고객번호 = c.고객번호 
   and c1.종료일자 = '99991231' and c2.종료일자 = '99991231'

현재 시점 (선분이력 테이블에 정보를 미리 입력 허용시)


Oracle
select c.고객번호, c.고객명, c1.고객등급, c2.전화번호 
  from 고객 c, 고객등급변경이력 c1, 전화번호변경이력 c2
 where c.고객번호 = :cust_num and c1.고객번호 = c.고객번호 
   and c2.고객번호 = c.고객번호 
   and to_char(sysdate, 'yyyymmdd') between c1.시작일자 and c1.종료일자
   and to_char(sysdate, 'yyyymmdd') between c2.시작일자 and c2.종료일자 
   
SQL Server
and convert(varchar(8), getdate(), 112) between c1.시작일자 and c1.종료일자 and convert(varchar(8), getdate(), 112) between c2.시작일자 and c2.종료일자

라. Between 조인

일별종목거래및시세와 같은 일별 거래 테이블로부터 읽히는 미지의 거래일자 시점으로 선분이력을 조회

주식시장, 과거 20년, 당일 최고가로 장을 마친 종목 조회


select a.거래일자, a.종목코드, b.종목한글명, b.종목영문명, b.상장주식수 , a.시가, a.종가, a.체결건수, a.체결수량, a.거래대금 
  from 일별종목거래및시세 a, 종목이력 b 
 where a.거래일자 between to_char(add_months(sysdate, -20*12), 'yyyymmdd') 
   and to_char(sysdate-1, 'yyyymmdd')
   and a.종가 = a.최고가 
   and b.종목코드 = a.종목코드 
   and a.거래일자 between b.시작일자 and b.종료일자

현재(=최종) 시점의 종목명이 아닌 거래가 일어난 그 시점의 종목명 조회

현재(=최종) 시점의 종목명과 상장주식수를 출력


select a.거래일자, a.종목코드, b.종목한글명, b.종목영문명, b.상장주식수 , a.시가, a.종가, a.체결건수, a.체결수량, a.거래대금 
  from 일별종목거래및시세 a, 종목이력 b 
 where a.거래일자 between to_char(add_months(sysdate, -20*12), 'yyyymmdd') and to_char(sysdate-1, 'yyyymmdd')
   and a.종가 = a.최고가 and b.종목코드 = a.종목코드 
   and to_char(sysdate, 'yyyymmdd') between b.시작일자 and b.종료일자

5. ROWID 활용

'점이력' : 데이터 변경이 발생할 때마다 변경일자와 함께 새로운 이력 레코드를 쌓는 방식

일반적인 조회 방법


select a.고객명, a.거주지역, a.주소, a.연락처, b.연체금액, b.연체개월수
  from 고객 a, 고객별연체이력 b where a.가입회사 = 'C70'
   and b.고객번호 = a.고객번호 and b.변경일자 = (select max(변경일자) from 고객별연체이력
                                                  where 고객번호 = a.고객번호 and 변경일자 <= a.서비스만료일)
						  
Execution Plan
------------------------------------------------------------- 
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=845 Card=10 Bytes=600)
1 0 TABLE ACCESS (BY INDEX ROWID) OF '고객별연체이력' (Cost=2 Card=1 Bytes=19) 
2 1 NESTED LOOPS (Cost=845 Card=10 Bytes=600)
3 2 TABLE ACCESS (BY INDEX ROWID) OF '고객' (Cost=825 Card=10 Bytes=410) 
4 3 INDEX (RANGE SCAN) OF '고객_IDX01' (NON-UNIQUE) (Cost=25 Card=10)
5 2 INDEX (RANGE SCAN) OF '고객별연체이력_IDX01' (NON-UNIQUE) (Cost=1 Card=1) 
6 5 SORT (AGGREGATE) (Card=1 Bytes=13) 7 6 FIRST ROW (Cost=2 Card=5K Bytes=63K)
8 7 INDEX (RANGE SCAN (MIN/MAX)) OF '고객별연체이력_IDX01' (NON-UNIQUE) (... ) 

-- 고객별연체이력 두번 액세스
-- INDEX RANGE SCAN(MIN/MAX) 발생

ROWID 이용한 튜닝


select /*+ ordered use_nl(b) rowid(b) */ a.고객명, a.거주지역, a.주소, a.연락처, b.연체금액, b.연체개월수
  from 고객 a, 고객별연체이력 b
 where a.가입회사 = 'C70' and b.rowid = (select /*+ index(c 고객별연체이력_idx01) */ rowid
                                           from 고객별연체이력 c 
					  where c.고객번호 = a.고객번호 and c.변경일자 <= a.서비스만료일 and rownum <= 1) 
					 
Eecution Plan ------------------------------------------------------------- 
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=835 Card=100K Bytes=5M)
1 0 NESTED LOOPS (Cost=835 Card=100K Bytes=5M) 
2 1 TABLE ACCESS (BY INDEX ROWID) OF '고객' (Cost=825 Card=10 Bytes=410)
3 2 INDEX (RANGE SCAN) OF '고객_IDX01' (NON-UNIQUE) (Cost=25 Card=10)
4 1 TABLE ACCESS (BY USER ROWID) OF '고객별연체이력' (Cost=1 Card=10K Bytes=137K) 
5 4 COUNT (STOPKEY) 6 5 INDEX (RANGE SCAN) OF '고객별연체이력_IDX01' (NON-UNIQUE) (Cost=2 Card=5K... ) 

-- 일반적인 NL Join과 같은 프로세스 진행 : Outer 인덱스 → Outer 테이블 → Inner 인덱스 → Inner 테이블
-- 고객별연체이력_IDX01 [ 고객번호 + 변경일자] 인덱스 필요
-- ROWID 튜닝은 성능이 아주 중요한 경우에 관리하에 적용 되어야 함

SQL Server 는 Oracle 의 ROWID 를 이용한 테이블 액세스를 지원하지 않는다.

4.고급 조인 기법 (by openlsw) [2013.11.23]

1. 인라인 뷰 활용

2. 배타적 관계의 조인

3. 부등호 조인

4. Between 조인

가. 선분이력이란?

나. 선분이력 기본 조회 패턴

다. 선분이력 조인

라. Between 조인

5. ROWID 활용