SQL튜닝 - 자주쓰는 고급조인기법 정리(인라인뷰를 활용한 조인횟수 줄이기 , 베타적 관계 테이블 조인, 누적합계조인, 선분이력, 점이력)

인라인뷰를 활용한 조인횟수 줄이기

 

조인의 결과는 대부분 M 쪽 집합과 같은 단위가 됩니다. 만약 조인 결과를 1쪽집합으로 다시 그룹핑해야한다면 M쪽 집합을 1쪽 단위로 먼저 그룹핑하고나서 조인하는것이 성능상 더 유리합니다. 조인 횟수가 줄어들기 때문입니다. 인라인 뷰를 사용해 이를 구현할 수 있습니다.

 

-- T1 일별 상품판매
-- T2 상품
-- 2009년도 상품별 판매수량과 판매금액을 집계하는 쿼리

SELECT  MIN(T2.T_NAME) T_NAME, SUM(T1.T_COUNT) T_COUNT, SUM(T1.T_AMOUNT) T_AMOUNT
FROM T1, T2
WHERE T1.T_DATE BETWEEN '20090101' AND '20091230'
AND T1.PCODE = T2.P_CODE
GROUP BY T2.P_CODE

 

위 쿼리를 인라인뷰를 통해 상품코드별로 먼저 집계한 후 조인하도록하면 다음과 같습니다.

 

SELECT T2.T_NAME, T1.T_COUNT, T1.T_AMOUNT
FROM (
    SELECT SUM(T1.T_COUNT) T_COUNT, SUM(T1.T_AMOUNT) T_AMOUNT
    FROM T1
    WHERE T_DATE BETWEEN '20090101' AND '20091230'
    GROUP BY P_CODE

)T1, T2
WHERE T1.P_CODE = T2.P_CODE

 

베타적 관계의 조인

 

한 엔터티가 두 개 이상의 다른 엔터티의 합집합인것을 상호 배타적 관계라고 합니다. 예를 들어 어떤 작업이 개통작업과 장애수리 두가지가 있을때 작업내역테이블은 '개통작업'과 '장애수리' 두 테이블의 합집합입니다.

 

'개통작업'과 '장애수리'는 작업이라는 합집합이 있지만 관리하는 별개속성이 있어 별도의 테이블로 설계합니다. 반면 작업은 개통작업과 장애수리의 동일한 속성을 관리하므로 한 테이블로 설계합니다. 이때 추후 세 태이블을 한 테이블로 통합하더라도 개통작업과 장애수리 중 어느것과 관계를 갖는지 구분할 수 있어야합니다.

 

이런 베타적 관계의 데이터 모델을 실제로 구현할때 두 가지 방법중 하나를 사용합니다.

1. 합집합 테이블에 두 베타적 테이블과의 연결 칼럼을 따로두고 실제 관계에 따라 둘 중 하나의 칼럼에만 값을 입력합니다.
2. 하나의 연결 칼럼에 실제 관계에 따라 서로 다른 값으로 입력합니다. 작업테이블의 작업구분 칼럼에 '개통작업'이면 1, '장애수리'면 2 로 입력합니다.

1번의 경우, OUTER 조인으로 전체 조인데이터를 조회할 수 있습니다.

 

SELECT /*+ordered use_nl(b) use_nl(c)*/
a.작업일련번호, a.작업일자id, a.작업상태코드
, nvl(b.고객번호, c.고객번호) 고객번호
, nvl(b.주소, c.주소) 주소
FROM 작업지시 a, 개통신청 b, 장애접수 c
where a.방문예정일시 Between :방문예정일시1 and :방문예정일시2
And b.개통신청번호(+) = a.개통신청번호
and C.장애접수번호(+) = a.장애접수번호

 

2번의 경우, union all을 이용해 전체 조인데이터를 조회할 수 있습니다.

 

SELECT
a.작업일련번호, a.작업일자id, a.작업상태코드
, a.고객번호, a.주소
FROM 작업지시 a, 개통신청 b
where a.방문예정일시 Between :방문예정일시1 and :방문예정일시2
and a.작업구분 = '1'
And b.개통신청번호 = a.개통신청번호
union all
a.작업일련번호, a.작업일자id, a.작업상태코드
, a.고객번호, a.주소
FROM 작업지시 a, 장애접수 b
where a.방문예정일시 Between :방문예정일시1 and :방문예정일시2
and a.작업구분 = '2'
And b.장애접수번호 = a.개통신청번호

 

2번의 경우, 만약 인덱스가 [방문예정일시 + 작업구분] 순으로 생성돼있다면 인덱스 스캔범위에 중복이 발생합니다. 다음과 같이 쿼리를 변경함으로써 중복 엑세스에 의한 비효율을 해소할 수 있습니다.

 

SELECT
FROM
WHERE A.방문예정일시 BETWEEN :방문예정일시1 AND :방문예정일시2
AND B.개통신청번호(+) = DECODE(A.작업구분, '1', A.접수번호)
AND C.개통신청번호(+) = DECODE(A.작업구분, '2', A.접수번호)

 

부등호 조인

 

업무에 따라 부등호 연산자로 조인해야할 때가 있습니다. 아래는 부등호 조인을 이용해 각 달의 매출과 누적매출을 구하는 쿼리입니다.

 

SELECT t1.지점, T1.판매월, min(t1.매출), sum(t2.매출) AS 누적매출
FROM 월별지점매출 T1, 월별지점매출 T2
WHERE T2.지점 = T1.지점
AND T2.판매월 <= T1.판매월
GROUP BY T1.지점, T2.판매월
ORDER BY T1.지점, T2.판매월

 

조인하지 않고 윈도우 함수를 이용해 동일한 결과를 얻을 수 있습니다.

 

SELECT 지점, 판매월, 매출,
SUM(매출) OVER (PARTITION BY 지점 ORDER BY 판매월 RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW)
 AS 누적 매출
FROM 월별지점매출

 

BETWEEN 조인

 

선분이력

 

하나의 테이블에 시작시점과 종료시점을 함께 관리하는 것을 '선분이력' 모델이라고 합니다. 선분이력 모델의 가장 마지막 이력의 종료일은 항상 '99991231' 혹은 시간까지 고려해 '99991231235959' 로 입력해두어야합니다. 선분이력을 사용하면 쿼리가 간단해집니다. 일반적인 점이력 모델에서 특정시점의 이력을 구하기 위해선 서브쿼리를 이용해야 합니다.

 

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

 

반면, 선분이력을 관리하는 테이블이라면 간단한 조건을 통해 동일한 결과를 구할 수 있습니다.

 

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

 

선분이력의 단점은 이력이 추가 될때마다 기존 최종 이력의 종료일시도 같이 변경해줘야하는 불편함과 이에 동반되는 DML부하입니다. 또한, 일반적인 PK인덱스 구성이 [마스터키 + 종료일자 + 시작일자] 순인데 이 경우 조회조건이 바뀔때마다 PK 인덱스가 변경되야하기 때문에 설계 사상과 맞지않습니다.

 

선분이력 기본조회 패턴

 

SELECT 연체개월수, 연체금액
FROM 고객별연체금액
WHERE 고객번호 = :cust_nm
AND :dt between 시작일자 and 종료일자

 

현재 시점을 조회할 때는 상수를 이용해 등치조건으로 검색하는 것이 성능상 유리합니다.

 

SELECT 연체개월수, 연체금액
FROM 고객별연체금액
WHERE 고객번호 = :cust_num
AND 종료일자 = '99991231'

 

선분이력 테이블의 최근시점에 종료정보를 미리 입력해 두는 경우 현재시간을 기준으로 조회해야합니다.

 

SELECT 연체개월수, 연체금액
FROM 고객별연체금액
WHERE 고객번호 = :cust_num
AND TO_CHAR(SYSDATE, 'YYYYMMDD') BETWEEN 시작일자 AND 종료일자

 

선분이력 조인

 

 

2개 이상의 선분이력을 다음과 같은 방식으로 조인할 수 있습니다.

 

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.시작일자

 

BETWEEN 조인

 

선분이력 조건이 상수가 아닌 조인할 테이블의 특정 시점으 기준으로 조회회야할 때는 BETWEEN 조인을 이용합니다.

다음은 주식시장에서 과거 20년 동안 최고가로 장을 마친 종목을 조회하는 쿼리입니다. '일별종목거래및시세' 테이블의 거래일자가 속하는 종목이력을 BETWEEN 조인을 통해 조회합니다.

 

SELECT A.시가, A.종가, A.최고가, B.종목명, B.상장주식수
FROM 일별종목거래및시세 A, 종목이력 B
WHERE A.거래일자 BETWEEN TO_CHAR(ADD_MONTH(SYSDATE, -20*12),'YYYYMMDD') 
AND TO_CHAR(SYSDATE -1, 'YYYYMMDD')
AND A.종가 = A.최고가
AND B.종목코드 = A.종목코드
AND A.거래이자 BETWEEN B.시작일자 AND B.종료일자

 

ROWID 활용

 

데이터 변경이 발생할때마다 변경이자와 함께 새로운 이력 레코드를 쌓는 방식을 '점이력 모델' 이라합니다. 아래의 쿼리는 점이력 모델에서 특정시점에 가장 가까운 이력을 찾는 것으로 서브쿼리를 활용합니다.

 

SELECT *
FROM 고객 A, 고객별연체이력 B
WHERE A.가입회사 = 'C70'
AND B.고객번호 = A.고객번호
AND B.변경일자 = (
  SELECT MAX(변경일자)
  FROM 고객별연체이력
  WHERE 고객번호 = A.고객번호
  AND 변경일자 <= a.서비스만료일
)

 

위 쿼리는 고객별 연체이력을 두 번 엑세스 하는 비효율이 있습니다. ROWID를 이용해 조인하는 튜닝기법을 이용해 엑세스를 최소화할 수 있습니다.

 

SELECT *
FROM 고객 A, 고객별연체이력 B
WHERE A.가입회사 = 'C70'
AND B.고객번호 = A.고객번호
AND B.ROWID = (
  SELECT ROWID
  FROM 고객별연체이력
  WHERE 고객번호 = A.고객번호
  AND 변경일자 <= A.서비스만료일
  AND ROWNUM <= 1
)

 

쿼리상 동일 테이블을 두 번 참조했지만, 실행계획 상에는 한 번만 조인한 것과 일량이 같습니다. 위 쿼리가 제대로 동작하기 위해서는 인덱스가 반드시 [고객번호 + 변경일자] 순으로 구성돼있어야 합니다. 인덱스 구성이 달라지면 쿼리 결과도 달라질 수 있습니다. FIRST ROW(MIN/MAX) 알고리즘이 옵티마이저에 의해 동작하면 위와 같은 기법을 적용하지 않는것이 좋습니다.