[SQL] 조인 시 선행 테이블은 어떤 테이블이어야 할까?

조인에 대해서 2개의 테이블이 있을 때 어떤 테이블이 left에 있는게 맞을까?

흔하게 그리고 항상 사용하게 되는 join에 있어서 left에 위치하게 되는 혹은 먼저 나오게 되는 테이블에 대해서 정리해보겠습니다.

즉, 마스터-디테일 구조에서의 driving table을 정하는 기준을 확인해보겠습니다.

---

1. 마스터 테이블, 디테일 테이블이란?

1) 원장성 테이블(master table)

• 업무의 핵심 개체(entity)들로서 주로 업무성격의 주체(Subject, Source)에 대한 정보를 담고 있으며, 부속된 많은 자식 테이블(child table)들을 거느리고 있음
• 주요 엔터티(Entity)를 관리하는 테이블로, 주로 고유 ID(primary key)와 메타데이터를 포함, 다른 테이블의 참조 대상
  • Customer, Product, Employee 등
• 이력 테이블(History Table)도 부속되어 있음
• dw시스템을 구축할 때도 이와 같은 원장성 테이블은 아주 중요한 핵심 테이블(Key Table) 역할을 함
• 현재(가장 최신)의 상태와 속성정보를 유지, 관리
• 속성들도 시간이 흘러도 변하지 않는 불변 속성과 시간의 경과에 따라 상태가 변하는 가변 속성이 존재
  • 불변 속성: "생년월일", "주민번호", "성별"
  • 가변 속성: "건강상태", "주소", "전화번호"

---

2) 거래성 테이블(transactional table, detail table)

• 행위(behavior) 테이블을 말하는 것으로, "카드거래내역 테이블, 입출금내역 테이블" 등이 해당
• Master 테이블과 1:N 관계를 가지며, Master 엔터티와 관련된 세부 데이터를 저장하는 테이블
  • ex) Order, OrderDetails, TransactionHistory 등
• 주로 Master 테이블의 각 엔터티와 관련된 개별 트랜잭션(이벤트)을 기록
• 원칙적으로 거래성 테이블은 등록만 되는 개념이지, 변경되는 개념이 아님
• ex) "카드거래내역 테이블"을 본다면, 카드 승인시에 승인기록이 저장되고, 이를 정정하기 위해서는 변경이 발생하는 것이 아니라, 취소기록을 등록하게 됨
• 어떠한 경우도 한번 등록된 내용을 변경하는 경우는 없음 → 따라서 이력 테이블이 없음
• detail table 주요 특징:
  1. 외래 키: Detail 테이블은 항상 Master 테이블의 Primary Key를 참조
  2. 1:N 관계: Master 테이블의 한 행이 Detail 테이블의 여러 행과 연결
  3. 종속적 데이터: Detail 테이블은 Master 테이블의 데이터 없이는 의미가 없으며, 주로 Master 데이터를 보완

Customer (Master Table)
-------------------------
customer_id | name
-------------------------
1           | Alice
2           | Bob

Order (Detail Table - Transactional)
-------------------------------------
order_id | customer_id | amount
-------------------------------------
101      | 1           | 100
102      | 1           | 200
103      | 2           | 150

 

• Master: Customer
• Detail: Order

 

---

3) 이력성 테이블(history table, detail table)

• 이력성 테이블은 Master 테이블의 각 엔터티와 관련된 변경 이력이나 상태를 저장
• 원장성 테이블의 외부에 동일한 구조를 가지고 있는 외부 이력 테이블(Outer History Table) 형태가 있을 수 있고 원장성 테이블의 내부에 상위키(upper key) 속성 컬럼을 포함시켜 내부순환(recursive) 구조를 가진 내부 이력 테이블(Inner History Table) 형태가 있을 수 있으며, 원장성 테이블이 중요. 상태 정보만을 이력 관리하는 중요 항목 이력 테이블(Hot-Item History Table) 구조로 구성될 수 있음
• 변경된 상태정보를 시작시점~종료시점의 구간으로 하는 선분이력(Line History) 형태로 관리될 수도 있고 변경시점을 기준으로 하는 점 이력(Point History) 형태로 이력은 관리될 수 있습니다. 선분 이력 테이블(Line History Table)은 주로 원장성 테이블에 구축되며 점이력 테이블(Point History Table)은 주로 양면성 테이블에 일반적으로 구축

Employee (Master Table)
-------------------------
employee_id | name
-------------------------
1           | Alice
2           | Bob

EmployeeHistory (Detail Table - History)
-----------------------------------------
history_id | employee_id | role      | effective_date
-----------------------------------------------------
201        | 1           | Manager   | 2024-01-01
202        | 1           | Director  | 2025-01-01
203        | 2           | Engineer  | 2024-05-01

• Master: Employee
• Detail: EmployeeHistory

---

4) 거래성 vs 이력성 테이블

 

• Transactional Table: 실시간 이벤트 데이터를 저장하며, Master 테이블의 특정 엔터티에 종속
• Historical Table: 시간에 따른 Master 테이블 엔터티의 상태 변경 이력을 저장하며, Detail 테이블의 역할 수행

---

2. 조인 시 driving table

1) Driving table과 Driven table

• 선행 테이블(Driving Table) : 조인 시 먼저 액세스 되는 테이블로, WHERE 절로 최대한 데이터를 거를 수 있는 테이블 / 데이터 양이 적은 테이블로 선정
• 후행 테이블(Driven Table): 조인 시 나중에 액세스 되는 테이블 (Driving이 아닌 나머지 테이블)조인을 위한 인덱스가 생성되어 있는 것이 좋음(없다면 Driving Table에서 도출된 결과와 맞는지 매번 FULL TABLE SCAN으로 일일이 비교해야 하기 때문)

---

2) RBO

• 규칙에 의한 순서: RBO는 특정 규칙을 따르며, 종종 테이블의 크기나 인덱스 유무에 관계없이, 조인 순서가 하드코딩된 규칙에 의해 결정
  • 인덱스를 이용한 액세스 방식이 전체 테이블 액세스 방식보다 우선순위가 높음
  • 조인 칼럼에 대한 인덱스가 양쪽 테이블에 모두 존재할 때, 우선순위가 높은 테이블을 선택
  • 조인 칼럼에만 인덱스가 존재하는 경우에는 인덱스가 없는 테이블을 먼저 선택하여 조인 수행
  • 만약 조인 테이블의 우선순위가 동일하지 않다면, FROM 절에 나열된 테이블의 역순으로 수
• 성능 저하: RBO는 테이블 크기나 데이터 분포를 고려하지 않기 때문에, 큰 테이블을 먼저 읽고 작은 테이블을 뒤에 조인할 경우 성능이 저하될 수 있음

3) CBO

• 규칙의 우선순위가 아닌 쿼리를 수행하는데 소요되는 예상 비용을 바탕으로 실행계획을 생성
• 통계정보, DBMS 설정정보, DBMS 버전 등의 차이로 인해 똑같은 SQL문이라도 서로 다른 실행계획이 생성될 수 있음
• Driving Table: CBO에서는 보통 작은 테이블을 먼저 읽도록 최적화, 작은 테이블을 Driving Table로 선택하여, 그 결과를 기반으로 다른 테이블을 필터링하거나 결합(master table)
• Driven Table: Driving Table의 결과와 결합되는 테이블로, 대체로 후속 테이블이며, Driving Table에 의해 필터링(detail table)
  
  1. 두 칼럼 모두 각각 인덱스가 있는 경우
     • 옵티마이저의 판단으로 각 테이블의 통계 정보에 있는 레코드 건수에 따라 driving, driven 결정
  2. right 테이블의 칼럼에만 인덱스가 있는 경우
     • left 테이블의 반복된 풀 스캔을 막기 위해 left 테이블을 driving
  3. left 테이블의 칼럼에만 인덱스가 있는 경우
     • left 테이블이 driving, right이 drivien
  4. 두 테이블 모두 칼럼에 인덱스가 없는 경우
     • 어느 테이블을 driving으로 선택하더라도 driven 테이블의 풀스캔은 발생하기 때문에 스캔되는 레코드 수에 따라 옵티마이저가 적절히 driving 테이블을 선택

 

• 주체가 되는 테이블을 왼쪽에 배치.
• 상대적으로 더 작은 테이블을 왼쪽에 두어 성능 최적화.
• 외부 키가 있는 테이블을 오른쪽에 두고, 참조하는 테이블을 왼쪽에 배치.
• 필터 조건이 적용된 테이블을 왼쪽에 배치하여 데이터 양 축소.

성능을 좋게 만드려면,
"구동테이블(Driving Table)이 작을수록"
&
"내부 테이블(Inner Table)의 결합키 필드에 인덱스가 존재"

 

3) 결론

• inner join의 경우, 옵티마이저가 최적의 실행계획에 따라 결정
  • 최적의 실행계획은 위의 "구동테이블이 작고 내부테이블 결합키에 인덱스가 존재하는 경우" 라는 조건에 따라 실행 비용을 최소화하는 방향으로 결정
  • 만약 두 테이블 모두 인덱스가 있거나 없다면 레코드 수에 따라 옵티마이저가 알아서 구동테이블을 선택
• Outer Join 은 반드시 Outer 가 되는 테이블을 먼저 읽어야하기 때문에 옵티마이저가 조인의 순서를 선택할 수 없음
  • Left Outer Join 일 땐 왼쪽 테이블이, Right Outer Join 일 땐 오른쪽 테이블이 드라이빙 테이블이 됨
  • left에 모든 정보가 나와야 하는 비교적 작은 데이터인 master 테이블을, right에 주문정보와 같은 detail 테이블을 둬야 함