1. 인라인 뷰 활용

대부분 조인은 1:M 관계인 테이블끼리의 조인이다. 조인 결과는 M쪽 집합과 같은 단위가 되는데, 이를 다시 1쪽 집합 단위로 그룹핑해야 한다면 M쪽 집합을 먼저 1쪽 단위로 그룹핑하고 나서 조인하는 것이 유리하다. 조인 횟수를 줄여주기 때문인데, 그런 처리를 위해 인라인 뷰를 사용할 수 있다. 2009년도 상품별 판매수량과 판매금액을 집계하는 아래 쿼리를 예로 들어보자.

Row Source Operation을 분석해 보면, 일별상품판매 테이블로부터 읽힌 365,000개 레코드마다 상품 테이블과 조인을 시도했다. 조인 과정에서 730,002개의 블록 I/O가 발생했고, 총 소요시간은 13.8초다. 아래 처럼 상품코드별로 먼저 집계하고서 조인하도록 바꾸고 다시 수행해 보자.

상품코드별로 먼저 집계한 결과건수가 1,000건이므로 상품 테이블과 조인도 1,000번만 발생했다. 조인 과정에서 발생한 블록 I/O는 2,101개에 불과하고 수행시간도 5.5초 밖에 걸리지 않았다.

2. 배타적 관계의 조인

어떤 엔터티가 두 개 이상의 다른 엔터티의 합집합과 관계(Relationship)를 갖는 것을 ‘상호배타적(Exclusive OR) 관계’라고 한다. [그림 Ⅲ-4-31]에서 작업지시 테이블과 개통신청 및 장애접수 테이블과의 관계가 여기에 해당하며, 관계선에 표시된 아크(Arc)를 확인하기 바란다.

ERD에 표현된 업무를 간단히 설명하면, 고객으로부터 개통이나 장애처리 요청을 받으면 작업기사에게 작업지시서를 발행한다. 한 작업자에게만 작업지시를 내릴 때가 많지만, 작업 내용에 따라서는 여러 작업자가 필요할 수도 있다. 또한, 여러 작업자가 동시에 출동하는가 하면, 시간 간격을 두고 따로 출동하기도 한다. ERD에 다 표현하진 않았지만 개통신청과 장애접수는 관리하는 속성이 상당히 달라 별도의 테이블로 설계했다. 반면, 작업지시는 개통신청이든 장애접수든 거의 같은 속성을 관리하므로 한 테이블로 설계했다. 한 테이블로 통합하더라도 개통신청이나 장애접수 중 어느 것과 관계를 갖는지 구분할 수 있어야 한다. [그림 Ⅲ-4-31]과 같은 데이터 모델을 실제 데이터베이스로 구현할 때, 작업지시 테이블에는 아래 두 가지 방법 중 하나를 사용한다.

① 개통신청번호, 장애접수번호 두 칼럼을 따로 두고, 레코드별로 둘 중 하나의 칼럼에만 값을 입력한다. ② 작업구분과 접수번호 칼럼을 두고, 작업구분이 ‘1’일 때는 개통신청번호를 입력하고 ‘2’일 때는 장애접수번호를 입력한다.

①번처럼 설계할 때는 아래와 같이 Outer 조인으로 간단하게 쿼리를 작성할 수 있다.

②번처럼 설계했을 때는 약간의 고민이 필요한데, 가장 쉽게 생각할 수 있는 방법은 아래와 같이 union all을 이용하는 것이다.

union all을 중심으로 쿼리를 위아래 두 번 수행했지만 만약 [작업구분+방문예정일시] 순으로 구성된 인덱스를 이용한다면 읽는 범위에 중복은 없다. 하지만 [방문예정일시+작업구분] 순으로 구성된 인덱스를 이용할 때는 인덱스 스캔범위에 중복이 생기고, [방문예정일시]만으로 구성된 인덱스를 이용한다면 작업구분을 필터링하기 위한 테이블 Random 액세스까지 중복해서 발생할 것이다. 그럴 때는 아래와 같이 쿼리함으로써 중복 액세스에 의한 비효율을 해소할 수 있다.

3. 부등호 조인

우리는 늘 ‘=’ 연산자를 이용한 조인에만 익숙해져 있지만 업무에 따라서는 between, like, 부등호 같은 연산자로 조인해야 할 때도 있다.

예를 들어, [그림 Ⅲ-4-32] 좌측과 같은 월별지점매출 테이블이 있다고 하자. 이 데이터를 이용해 [그림 Ⅲ-4-32] 우측과 같은 형태의 누적매출을 구해보자. 각 지점별로 판매월과 함께 증가하는 누적매출(running total)을 구하려는 것이다.

윈도우 함수(Oracle에서는 분석 함수(Analytic Function)라고 함)를 이용하면 아래와 같이 간단히 원하는 결과를 얻을 수 있다.

만약 윈도우 함수가 지원되지 않는 DBMS를 사용하고 있다면 아래와 같이 부등호 조인을 이용해 같은 결과를 얻을 수 있다.

4. Between 조인

Between 조인을 설명하기에 앞서 선분이력의 개념부터 살펴보자.

가. 선분이력이란?

예를 들어 고객별연체금액 변경이력을 관리할 때 이력의 시작시점만을 관리하는 것을 ‘점이력’ 모델이라고 하고, 시작시점과 종료시점을 함께 관리하는 것을 ‘선분이력’ 모델이라고 한다. 선분이력 모델에서 가장 마지막 이력의 종료일자는 항상 ‘99991231’(시간까지 관리할 때는 ‘99991231235959’)로 입력해 두어야 한다. [표 Ⅲ-4-3]을 참조하기 바란다.

[그림 Ⅲ-4-33]은 [표 Ⅲ-4-3]에 있는 123번 고객에 대한 3개의 선분이력 레코드를 일직선 상에 펼쳐서 그려본 것이다.

이력을 이처럼 선분형태로 관리하면 무엇보다 쿼리가 간단해진다는 것이 가장 큰 장점이다. 예를 들어, 123번 고객의 2004년 8월 15일 시점 이력을 조회하고자 할 때 아래 처럼 between 조인을 이용해 간편하게 조회할 수 있다.

데이터를 일반적인 점이력으로 관리할 때 아래 처럼 서브쿼리를 이용해 복잡하게 쿼리하던 것과 비교해 보기 바란다.

쿼리가 간단하면 아무래도 성능상 유리하지만 선분이력에 장점만 있는 것은 아니다. 우선 이력이 추가될 때마다 기존 최종 이력의 종료일자(또는 종료일시)도 같이 변경해 주어야 하는 불편함과, 이 때문에 생기는 DML 부하를 고려해야 한다. PK를 어떻게 구성하느냐에 따라 다르지만 성능을 고려해 일반적으로 [ 마스터 키 + 종료일자 + 시작일자 ] 순으로 구성하곤 하는데, 이럴 경우 이력을 변경할 때마다 PK 값을 변경하는 셈이어서 RDBMS 설계 사상에 맞지 않다는 지적을 받곤 한다. 무엇보다, 개체 무결성을 완벽히 보장하기 어렵다는 것이 가장 큰 단점이다. 선분이력 모델과 관련해 많은 이슈들이 존재하지만 더 깊이 설명하지는 않겠다.

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

조금 전 선분이력 조회를 간단히 살펴보았는데, 선분이력에 자주 사?함한 선분이력 조회 방법에 대해서는 잠시 후에 다루며, 여기서는 단일 테이블 조회만을? 계속 참조하기 바란다. 가장 기본적인 패턴으로 과거, 현재, 미래 임의 시점을 모두 조회할 수 있도록 하려면 아래 처럼 쿼리를 작성하면 된다. 예를 들어, 2004년 8월 15일자 데이터를 조회하려면 :dt 변수에 ‘20040815’을 입력하면 된다.

현재 시점을 조회할 때는 ‘99991231’ 상수 조건을 이용해 아래와 같이 ‘=’ 조건으로 검색하는 것이 성능상 유리하다.

론 맨 마지막 이력 레코드는 종료일에 ‘99991231’을 넣는다는 약속을 전제로 하며, 선분이력이 갖는 이점을 제대로 활용하려면 꼭 그렇게 값을 넣어야만 한다. 주의할 것은, 선분이력 테이블에 정보를 미리 입력해 두는 경우가 종종 있고 그럴 때는 현재 시점을 위와 같은 식으로 조회해선 안 된다. 예를 들어, 고객별 연체변경이력을 지금 등록하지만 그 정보의 유효 시작일자가 내일일 수 있다. 그럴 때는 현재이력을 아래와 같이 조회해야 한다.

다. 선분이력 조인

단일 선분이력을 조회하는 기본 패턴을 살펴보았다. 지금부터는 2개 이상의 선분이력을 조인하는 경우를 살펴보자.

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

[그림 Ⅲ-4-34]와 같이 고객등급과 전화번호 변경이력을 관리하는 두 선분이력 테이블이 있다고 하자.

고객과 두 선분이력 테이블을 조인해서 2004년8월 15일 시점 데이터를 조회할 때는 아래와 같이 쿼리하면 된다. 물론 :dt 변수에는 ‘20040815’(시작일자, 종료일자가 문자열 칼럼일 때)를 입력한다.

2) 현재 시점 조회

위 쿼리를 이용해 과거, 현재, 미래 어느 시점이든 조회할 수 있지만, 만약 미래 시점 데이터를 미리 입력하는 예약 기능이 없다면 “현재 시점(즉, 현재 유효한 시점)” 조회는 아래와 같이 ‘=’ 조건으로 만들어 주는 것이 효과적이다. 범위검색 조건이 비효율을 일으키는 원인은 2절에서 자세히 설명하였다.

미래 시점 데이터를 미리 입력하는 예약 기능이 있다면, 현재 시점을 조회할 때 아래와 같이 조회해야 한다.

라. Between 조인

지금까지는 선분이력 조건이 상수였다. 즉, 조회 시점이 정해져 있었다. [그림 Ⅲ-4-35]에서 만약 우측(일별종목거래및시세)과 같은 일별 거래 테이블로부터 읽히는 미지의 거래일자 시점으로 선분이력(종목이력)을 조회할 때는 어떻게 해야 할까? 이때는 between 조인을 이용하면 된다.

아래는 주식시장에서 과거 20년 동안 당일 최고가로 장을 마친(종가=최고가) 종목을 조회하는 쿼리다. [그림 Ⅲ-4-35]의 일별종목거래및시세 테이블로부터 시가, 종가, 거래 데이터를 읽고 그 당시 종목명과 상장주식수는 종목이력으로부터 가져오는데, 조인 연산자가 ‘=’이 아니라 between이라는 점이 특징적이다.

이런 식으로 조회하면 현재(=최종) 시점의 종목명을 가져오는 것이 아니라 [그림 Ⅲ-4-36]에서 보는 것처럼 거래가 일어난 바로 그 시점의 종목명을 읽게 된다.

거래 시점이 아니라 현재(=최종) 시점의 종목명과 상장주식수를 출력하려면 between 조인 대신 아래와 같이 상수 조건으로 입력해야 한다.([그림 Ⅲ-4-37] 참조)

물론 방금 쿼리는 종목 테이블을 종목이력과 통합해 하나로 설계했을 때 사용하는 방식이다. [그림 Ⅲ-4-35]처럼 종목과 종목이력을 따로 설계했을 때는 최종 시점을 위해 종목 테이블과 조인하면 된다.

5. ROWID 활용

선분이력과 대비해, 데이터 변경이 발생할 때마다 변경일자와 함께 새로운 이력 레코드를 쌓는 방식을 ‘점이력’이라고 흔히 말한다.

이력 모델에선 일반적으로 서브쿼리를 이용해 아래와 같이 조회한다. 즉, 찾고자 하는 시점(서비스만료일)보다 앞선 변경일자 중 가장 마지막 레코드를 찾는 것이다.

SQL과 실행계획에서 알 수 있듯이 고객별연체이력을 두 번 액세스하고 있다. 다행스럽게도 옵티마이저가, 서브쿼리 내에서 서비스만료일보다 작은 레코드를 모두 스캔하지 않고 인덱스를 거꾸로 스캔하면서 가장 큰 값 하나만을 찾는 실행계획(7번째 라인 first row, 8번째 라인 min/max)을 수립했다. 만약 위 쿼리가 가장 빈번하게 수행되는 것이어서 단 한 블록 액세스라도 줄여야 하는 상황이라면 ROWID를 이용해 조인하는 아래와 같은 튜닝 기법을 적용해 볼 수 있다.

고객(a)에서 읽은 고객번호로 서브쿼리 쪽 고객별연체이력(c)과 조인하고, 거기서 얻은 rowid 값으로 고객별연체이력(b)을 곧바로 액세스한다. a와 b간에 따로 조인문을 기술하는 것은 불필요하다. 쿼리에 고객별연체이력을 두 번 참조했지만, 실행계획 상에는 한 번만 조인한 것과 일량이 같다. 일반적인 NL Join과 같은 프로세스(Outer 인덱스 → Outer 테이블 → Inner 인덱스 → Inner 테이블)로 진행되는 것에 주목하기 바란다. 위 쿼리가 제대로 작동하려면 고객별연체이력_idx01 인덱스가 반드시 [ 고객번호 + 변경일자 ] 순으로 구성돼 있어야 한다. 혹시라도 인덱스 구성이 변경되면 그때부터 쿼리 결과가 틀려질 수 있음을 반드시 기억해야 한다. first row(min/max) 알고리즘이 작동한다면 일반적으로 그것만으로도 충분한 성능을 내므로 굳이 위와 같은 기법을 적용하지 않는 것이 좋다. 그럼에도, 성능이 아주 중요한 프로그램이어서 어쩔 수 없이 위 방식을 쓰게 될 때는 이들 프로그램 목록을 관리했다가 인덱스 구성 변경 시 확인하는 프로세스를 반드시 거치기 바란다. SQL Server는 Oracle처럼 사용자가 직접 ROWID를 이용해 테이블을 액세스(Table Access By User Rowid)하는 방식을 지원하지 않는다.