[Oracle] 고급 조인(JOIN) 튜닝(인라인 뷰 활용 / 배타적 관계의 조인 / 부등호 조인 / Between 조인 / ROWID 활용)

고급 조인 기법

1. 인라인 뷰(In-line View) 활용
2. 배타적 관계의 조인
3. 부등호 조인
4. Between 조인
5. ROWID 활용

---

1. 인라인 뷰(In-line View) 활용

• 인라인 뷰(In-line View)
  • 조인하기 전에 인라인 뷰 안에서 미리 데이터를 집계(Group By)하거나 필터링하여 조인 대상 건수를 줄이는 기법입니다.
  • 조인 발생 횟수를 줄이기 위해 집계 함수를 인라인 뷰로 감싸는 튜닝입니다.
• 원리: 집계 후 조인
  • 조인한 뒤에 GROUP BY를 하면 조인 횟수가 늘어나지만, 조인 전에 미리 집계하면 조인 횟수 자체를 물리적으로 줄일 수 있습니다.
• 동작 순서
  1. 인라인 뷰(FROM 절 서브쿼리) 내에서 대상 테이블을 스캔합니다.
  2. 뷰 안에서 GROUP BY를 수행하여 결과 건수를 대폭 축소(예: 100만 건 →100건)합니다.
  3. 축소된 결과 집합을 메인 테이블과 조인합니다.
  4. 최종 결과 출력

활용예시

활용 전 / 활용 후

• 왼쪽(일반 조인): 월별고객카드사용실적(B) 테이블의 모든 행에 대해 매출지역코드(A) 테이블과 조인을 시도합니다. 만약 실적 데이터가 1,000만 건이면 조인도 1,000만 번이 일어납니다.
  1. 월별고객카드사용실적(B) 테이블을 스캔합니다. (대량 데이터)
  2. 읽어온 각 레코드마다 매출지역코드(A) 테이블의 인덱스를 타고 조인을 수행합니다. (NL 조인 가정 시 수많은 랜덤 액세스 발생)
  3. 조인된 결과 집합을 사용자에게 출력합니다.
• 문제점: 동일한 '서울' 지역이라도 고객 수만큼 반복해서 조인하므로 I/O 낭비가 심함.

 

• 오른쪽(인라인 뷰 튜닝): 실적 데이터를 먼저 매출월, 매출지역코드로 그룹화(집계)합니다. 결과적으로 조인 대상 집합의 크기가 획기적으로 줄어들어(예: 1,000만 건 → 1,000건) 조인 성능이 극대화 됩니다.
  1. (인라인 뷰 내부): 월별고객카드사용실적 테이블을 읽어 GROUP BY를 먼저 수행합니다.
  2. (집계 완료): 지역별로 합산된 유니크한 결과 집합이 메모리(PGA) 또는 임시 공간에 생성됩니다.
  3. (조인): 집계된 결과(B)를 드라이빙(선행)으로 하여 매출지역코드(A) 테이블과 딱 지역 수만큼만 조인합니다.
  4. 최종 합산된 판매금액을 출력합니다.

---

2. 배타적 관계의 조인

• 배타적 관계의 조인
  • 어떤 엔티티가 두 개 이상의 다른 엔티티와 관계를 맺고 있지만, 한 번에 어느 한쪽하고만 관계를 맺는 모델에서의 조인입니다.(예: 접수 테이블이 '개인고객' 혹은 '법인고객' 중 하나만 연결됨)
  • OUTER JOIN 또는 UNION ALL을 활용해 각각의 관계를 따로 조인합니다.
  • NAL 또는 DECODE를 활용해 조인 대상을 동적으로 결정할 때의 인덱스 사용 효율을 묻는
• 동작 순서
  1. 메인 테이블을 먼저 읽습니다.
  2. 구분값(예: 고객구분코드)에 따라 조인 대상을 분기합니다.
  3. UNION ALL 방식 사용 시, 각각의 관계별로 최적화된 인덱스를 타고 조인합니다.
  4. OUTER JOIN 방식 사용 시, 일치하지 않는 쪽은 NULL로 처리하며 결과를 합칩니다.

 

활용예시

• 구분 값(1: 개통, 2: 장애)을 담는 컬럼과, 실제 ID 값을 담는 하나의 컬럼만 사용하는 방식입니다. 논리적으로 깔끔하지만 조인이 까다롭습니다.
• 동작 순서
  1. 작업지시 테이블을 스캔합니다.
  2. 작업일련번호가 '1'인지 '2'인지에 따라 조인할 대상 테이블을 결정해야 합니다.
  3. 보통 UNION ALL을 사용하여 쿼리를 두 번 나누거나, 조인 조건에 DECODE나 CASE문을 사용합니다.
  4. 조건에 맞는 테이블의 정보가 결합됩니다.

-- 예시
ON (A.작업일련번호 = '1' AND A.접수번호 = B.개통신청번호)
	OR (A.작업일련번호 = '2' AND A.접수번호 = C.장애접수번호)

---

3. 부등호(>, <, !=) 조인

• 부등호 조인
  • 등치(=) 조건이 아닌 부등호를 사용한 조인입니다.
  • Hash Join을 사용할 수 없습니다.(해시 함수는 값의 일치를 전제로 하기 때문)
  • 대량 데이터의 부등호 조인 시 Sort Merge Join이 가장 유리
  • NL 조인 시 인덱스 스캔 범위가 넓어지는 비효율을 계산하는 문제
• 원리: 연속된 데이터 스캔(RANGE SCAN)
  • '=' 조건이 아닌 범위 조건을 조인에 사용하는 것으로, Hash Join이 불가능할 때의 대안을 찾는 것이 핵심
• 동작 순서
  1. 선행 테이블의 한 행을 읽습니다.
  2. 후행 테이블 인덱스에서 부등호 조건의 시작점을 찾습니다.
  3. 조건이 만족할 때까지 인덱스 리프 블록을 수평적으로 스캔(RANGE SCAN)하며 조인합니다.
  4. 대량 데이터일 경우, 양쪽을 정렬한 뒤 스캔하는 Sort Merge Join으로 전환합니다.

활용 예시

윈도우 함수 사용 예시'

• 누적 매출을 구하려면, 현재 행(판매월)보다 작거나 같은 모든 이전 달의 데이터를 조인하여 합산해야 합니다.
  1. A.판매월 >= B.판매월 조건을 사용하여 조인합니다.
  2. 1월은 1건, 2월은 2건, .., 12월은 12건과 조인 됩니다. 1년치 데이터를 처리할 때 약 78번의 조인 복제가 발생합니다.
  3. 만약 데이터가 10만 건이라면 조인 결과는 수십억 건으로 늘어나는 성능 재앙이 발생합니다.
• 동작 순서(이미지 우측에서 추천하는 분석함수(SUM() OVER)를 사용하면 조인 없이 단 한 번의 스캔으로 끝납니다.)
  1. (정렬): 지점, 판매월 순으로 데이터를 정렬합니다.
  2. (윈도우 생성): 정렬된 순서대로 데이터를 읽으며 메모리 상에서 윈도우(누적 범위)를 지정합니다.
  3. (연산): 별도의 테이블 복제 없이, 이전까지 합산된 결과에 현재 행의 매출을 더해 나갑니다.
  4. (출력): 단 한 번의 테이블 스캔으로 누적 집계가 완료됩니다.

 

---

4. Between 조인

• Between 조인
  • 조인 조건이 특정 값의 범위(Between)에 있는 경우입니다.(예: 선분이력 테이블 조인)
    • *점이력: 특정 시점만 관리하는 모델
    • *선분이력: 시작점과 종료시점을 함께 관리하는 모델
  • 마스터 테이블의 한 행이 이력 테이블의 여러 행과 겹칠 수 있어 데이터 뻥튀기를 주의
  • '선분 이력' 모델에서 현재 유효한 데이터를 찾기 위한 조인 성능 최적화가 제 5과목의 핵심 난제
• 원리: 시작점과 종료점의 교집합 탐색
  • 특정 시점이나 기간에 포함되는 데이터를 찾는 조인으로, 주로 선분 이력 모델에서 사용
• 동작 순서
  1. 기준 테이블(마스터)의 키 값과 조회 시점을 읽습니다.
  2. 이력 테이블 인덱스에서 (시작일자 <= 조회시점) 범위를 찾습니다.
  3. 그 중 (종료일자 >= 조회시점) 데이터를 필터링합니다.
  4. 데이터 뻥튀기를 방지하기 위해 유효한 1건만 추출하도록 튜닝(ROWID 활용 등)합니다.

 

 

 

 

 

 

---

5. ROWID 활용 조인

• ROWID 활용 조인
  • 인덱스에서 얻은 ROWID를 직접 사용하여 테이블 블록을 찾아가는 방식
  • 한 번 읽은 테이블의 ROWID를 기억해 두었다가, 나중에 조인이나 재조회 시 이 ROWID로 즉시 테이블을 액세스(TABLE ACCESS BY USER ROWID)
  • 조인 횟수가 너무 많을 때, 중간 결과에 ROWID를 포함시켜 최종 단계에서 한 번만 테이블을 읽게 만드는 ROWID 기반 튜닝
• 원리: 직접 주소 액세스
  • 인덱스를 타고 테이블을 가는 것이 아니라, 이미 알고 있는 물리적 주소(ROWID)로 테이블 블록을 한 번에 찾아가는 최강의 성능 기법
• 동작 순서
  1. 첫 번째 쿼리(또는 조인 단계)에서 대상 테이블의 ROWID 컬럼을 결과 집합에 포함시킵니다.
  2. 다음 조인 단계에서 인덱스를 거치지 않고, 전달받은 ROWID를 사용하여 즉시 해당 테이블 블록을 읽습니다.
  3. 인덱스 수직 탐색 과정이 생략되므로 I/O가 극단적으로 줄어듭니다.

 

 

 

 

 

 

 

참조: 멋쟁이사자처럼 - 한 번에 합격라는 SQLP 과정