Join Algorithms for Database Optimization
Contents
1️⃣조인 (Overview Of Join)
2️⃣조인의 동작 방식(How Join Works)
3️⃣Comparison of Join Methods (조인 방식 비교)
Database Joins and Performance Optimization (데이터베이스 조인과 성능 최적화)
지금까지 데이터 베이스의 물리적설계와 구현에 대해서 배웟다. DB의 물리적 설계 부분은 dbms와 굉장히 밀접한 연관이 있다. 저장 구조를 어떻게 설정할 것인지,인덱스를 어떻게 만들 것인지, 파티션 여부 등 저장 구조와 관련된 부분, 동시성 제어, 락 관리, 트랜잭션은 구현, 구축 부분이다. 이러한 데이터베이스 설계 구축에서 가장 중요시 하는 첫번째 목표는 “반응속도”이다. 사용자가 쿼리를 주었는데 늦게 나온다면 문제가 된다. 잘 가져오는것도 중요하지만 속도가 중요한 부분을 차지한다. 그렇게 하기위해 dbms는 많은 노력을 하고있다. 관계형 데이터 베이스를 테이블의 형태로 저장하는데 이때 가장 안좋은 점은 "조인"을 할 때이다. 하나의 테이블만 보면 데이터가 아무리 많아도 인덱스로 평균적인 퍼포먼스 활용이 가능하다. 혹은 파티션 생성으로도 접근속도를 빠르게 할 수 있다. 그런데 두 개 이상의 테이블을 접근해서 조인으로 가져오는 경우는 사실 많은 노력이 필요하다. 속도 또한 갑자기 느려질 수 있다. 그래서 쿼리 설계시 특히 조인 쿼리 설계시 많은 주의가 요구된다. 조인 과정이 어떻게 동작하는지 머릿속에 그릴 수 있어야 성능 좋은 쿼리를 생성할 수 있게 된다. 이번 시간은 조인을 좀 더 정리하는 시간을 갖을 것이다.
1️⃣조인 (Overview Of Join)
💡요약: 관계형 데이터베이스는 테이블의 형태로 데이터베이스를 분리해서 저장한다. 이 과정을 정규화(normalization) 라고 하는데 이를 통해 중복을 최소화하는 relation으로 분리하게 된다. 사용자가 원하는 것은 분리되서 저장하는 것도 그렇고 데이터를 모아서 보는 것이다. 이를 위해 데이터를 결합하는 조인 과정을 통해 데이터를 가져와야 하기 때문에 조인 과정이 필요하게 된다. 조인의 종류는 크로스 조인, 내부조인, 외부조인이 있는데 주로 크로스조인, 내부조인을 사용한다.
✅ 조인의 정의 (Definition of Joins)
조인은 두 개 이상의 테이블을 묶어 하나의 결과 집합으로 만드는 것(Combining Two or More Tables into a Single Result Set)을 의미한다. 예를 들어)
departments 테이블에는 부서 정보가 있다.
locations 테이블에는 부서의 위치 정보가 저장되어 있다. 보통 부서의 정보를 가져올땐 location id를 가져오진 않는다. 우리가 원하는 것은 주로 어느 시티에 있는지, 국가에 있는지를 찾아보게 된다. 이런 데이터를 따로 보고 싶을 때 조인이 필요하다.
✅ 조인의 필요성(Need for Joins): 부서 정보와 함께 위치 정보(도시 및 국가)를 확인하려면 두 테이블의 데이터를 결합해야 한다.
✅조인 성능 최적화 (Join Performance Optimization):
조인의 성능을 높이기 위해서는,
인덱스(Indexes)를 활용한다.
파티션(Partitions)을 설정하여 데이터 접근 속도를 높인다.
조인 쿼리를 설계할 때 실행 계획을 이해한 뒤 최적화해야 한다.
✅조인 종류 (Types of Joins):
크로스 조인(Cross Join): 두 테이블의 모든 행을 조합하여 반환한다.
내부 조인(Inner Join): 두 테이블에서 매칭되는 데이터만 반환한다.
외부 조인(Outer Join): 한쪽 테이블에서 매칭되지 않은 데이터도 포함한다.
조인 #1: Cross Join and Cartesian Product (크로스 조인과 카티션 프로덕트)
💡요약: 두 테이블의 데이터의 모든 행을 곱하는 연산을 릴레이션에서는 카티션 프로덕트라고 한다. 이 카티션 프로덕트를 구현한 것이 크로스 조인이다.
✅카티션 프로덕트의 특징 (Characteristics of Cartesian Product):
모든 조합 반환(Returns All Combinations): 테이블 간 모든 행 조합을 생성한다.
조건이 없는 조인(Unconditional Join):
ON또는WHERE절이 없다.큰 결과 집합 생성(Large Result Set): 테이블의 크기가 클수록 행의 수가 기하급수적으로 증가한다.
✅크로스 조인의 정의 (Definition of Cross Join)
크로스 조인(Cross Join)은 데이터베이스에서 두 개의 테이블을 곱하는 조인 방식이다. 이 연산은 테이블 간의 모든 가능한 조합을 반환하며, 카티션 프로덕트(Cartesian Product)를 생성한다.
크로스 조인은 두 테이블의 모든 행을 곱한다.
각 행은 두 테이블의 모든 가능한 조합을 나타낸다.
✅예제 (Example):
departments의 각 행이 locations의 모든 행과 매칭되었다. departments 테이블의 첫 번째 행과 locations 테이블의 23개 행이 조합된다.
같은 방식으로 departments 테이블의 나머지 26개 행도 반복된다.
departments 테이블: 27개의 행 (Rows)
locations 테이블: 23개의 행 (Rows)
크로스 조인 결과: 27 × 23 = 621개의 행이 생성되었다.
크로스 조인 쿼리:
SELECT * FROM departments CROSS JOIN locations;
또는 크로스 조인 생략(Without Explicit Cross Join):
SELECT * FROM departments, locations;
✅주의점 (Cautions)
큰 테이블 사용 시 성능 문제(Performance Issues with Large Tables): 크로스 조인은 결과 집합이 매우 커질 수 있으므로 메모리와 처리 속도에 영향을 미친다.
사용 목적(Purpose of Use): 보통 특정 테스트 또는 모든 조합이 필요한 경우에만 사용한다.
성능 저하를 방지하려면 필터 조건을 추가하는 것이 중요하다. (Add Filter Conditions to Avoid Performance Degradation)
조인 #2: Inner Join and Efficient Execution (내부 조인과 효율적인 실행)
inner join은 조건이 있다. 크로스 조인이 무조건 2개를 곱하는 것 이라면 내부 조인은 조건을 달아 연관된 데이터만 반환하는 방식이다. 이때, 데이터를 결합하는 과정은 크게 세 가지 단계로 나뉜다.
✅ 내부 조인의 정의 (Definition of Inner Join):
두 테이블의 모든 행을 카티션 프로덕트로 결합한다.
이후 ON 조건이나 WHERE 절을 사용하여 특정 조건을 충족하는 데이터만 필터링한다.
✅ 쿼리 예제와 결과 (Query Examples and Results):
기본 내부 조인 (Basic Inner Join):
SELECT * FROM departments INNER JOIN locations ON departments.location_id = locations.location_id;
- 결과: 카티션 프로덕트한 전체 집합에서 조건에 맞는 레코드만 남게된다.
departments와locations의location_id가 같은 27개의 레코드 반환.
- 결과: 카티션 프로덕트한 전체 집합에서 조건에 맞는 레코드만 남게된다.
간단히 표현한 내부 조인 (Simplified Inner Join):
SELECT * FROM departments, locations WHERE departments.location_id = locations.location_id;
- 좀 더 간단하게 표현도 가능하다. INNER JOIN 대신 WHERE 만으로도 동일한 결과를 얻을 수 있다.
조건 추가 (Adding Conditions):
SELECT * FROM departments, locations WHERE departments.location_id = locations.location_id AND department_id <= 100;
AND를 붙여 조인 조건에 조건을 줄 수 있다. where 로 가져 왔는데 department_id가 100보다 작거나 같은 것만 꺼내라는 조건을 AND뒤에 붙인것이다.
결과: 부서 ID가 100 이하인 경우만 반환 (10개의 레코드).
✅내부조인의 실행 절차
카티션 프로덕트 생성 (Cartesian Product):
SELECT * FROM departments INNER JOIN locations
- 두 테이블의 모든 행 조합을 생성 (621개의 행).
조건 필터링 (Filtering by Condition):
그 중에서 department.location_id와 locations.location_id가 같은 것을 뽑아낸다.
ON departments.location_id = locations.location_id
location_id가 같은 데이터만 필터링되어 27개의 행이 출력된다.
추가 조건 적용 (Applying Additional Conditions):
WHERE department_id <= 100;
- department_id가 100보다 작은 것을 뽑아 낸다. 10개가 생성되었다.
💡DBMS의 입장에서 621-> 27->10 를 추출하는 과정은 비효율적인 면이 있다. 이러한 이유로 거꾸로 하면 빠르지 않을까? 라는 해결법이 제시 되었다.
✅ DBMS 최적화 방법 (DBMS Optimization):
필터 조건을 먼저 적용(Apply Filters Early):
department_id가 100보다 작은 결과를 원한다면, 100보다 큰 것은 처음부터 조인을 하지 않으면 된다.department_id <= 100조건을 먼저 처리하여 10개의 행만 선택한다.department 조인에 참여하는 숫자를 줄인다. 불필요한 조인을 줄여 성능 향상하는 것이다.
인덱스 활용(Using Indexes):
dept_id_pk(부서 ID)와loc_id_pk(위치 ID) 인덱스를 사용하여 효율적으로 데이터를 조회.인덱스 range scan을 통해 100보다 작은 10개를 뽑아내게 되었다. 인덱스 덕분에 필요한 데이터만 검색이 가능해졌다.
Sort Merge Join:
- 그 뒤 필터링된 데이터(10개)를 정렬한 뒤 병합하여 결합한다.
✅ 실행 계획 분석 (Execution Plan Analysis):
첨부된 실행 계획 이미지에서 주요 과정:
Index Range Scan:
department_id <= 100조건에 따라 필터링.
Table Access:
- 필터링된 10개의 데이터를 가져옵니다.
Sort and Merge:
departments.location_id = locations.location_id조건에 따라 정렬 및 병합.
💡이것이 내부조인을 dbms에서 효율적으로 구현하는 방법의 예가 된다. 쓸데없는 조인을 줄이게 된다. 조인을 설계할 때 이런식의 효과적인 접근방법이 나오게 설계하는 것이 굉장히 중요하다. 그러기 위해선 조인이 어떻게 동작 하는지를 이해하고 있어야한다.
2️⃣조인의 동작 방식(How Join Works)
💡요약: 조인의 동작방식에 대해 더 자세히 알아본다. 조인이 수행될 때 테이블 간에 접근하는 방식이 중요하다고 배웠다. 조인 방식에 따라 쿼리의 비용과 성능이 달라지기 때문이다. DBMS의 쿼리 옵티마이저가 접근 방식을 결정한다. 오라클에서 주로 사용하는 조인 방식은 크게 3가지가있다. 중첩 루프 조인(Nested Loop Join), 소트 머지 조인(Sort Merge Join), 해시 조인(Hash Join)은 오라클 DBMS에서 주로 사용되는 조인 방식이다. 각각의 조인 방식은 데이터 크기, 정렬 여부, 조건에 따라 성능이 달라진다.
조인의 동작 방식 #1: 중첩 루프 조인 (How Join Works - Nested Loop Join )
✅ 중첩 루프 조인 (Nested Loop Join):
중첩 루프 조인은 간단하지만 성능은 데이터 크기와 인덱스 여부에 따라 달라집니다.
(Nested Loop Join is Simple but Performance Depends on Data Size and Index Presence)인덱스 설정은 중첩 루프 조인의 핵심 최적화 전략입니다. (Indexing is Key to Optimizing Nested Loop Join)
작동 방식:
첫번째 테이블(outer relation)의 각각의 로우에 대해서 두번째 테이블(inner relation)의 모든 로우를 비교한다. 첫번째 테이블에 46은 두번째 테이블에 없으므로 넘어간다. 다음 로우 0은 두번째 테이블에 로우 3에 있다. 그 뒤엔 어떻게 될까? 중복 여부에 따라 달라진다. 중복이 될 수 없다면 이 0 결합 후 끝나게 된다. 중복이 허용 된다면 테이블 끝까지 full scan을 진행하게 된다. 이 경우 인덱스가 있었다면 중복여부와 상관없이 정렬이 되어있기 때문에 훨식 효율적이다.
결론적으로 inner relation에 인덱스 여부가 중요한 지표가 된다. 인덱스가 없다면 전부 돌아야하기 때문이다. 이 예제에 중복허용이 안된다면 0에서 끝나지만 중복 허용이 되었다면 full scan을 진행하므로 첫 번째 테이블의 10은 두번째 테이블의 10과 결합 된다. 모든 레코드에 대해 모든 로우를 비교하는 것을 중접 루프 조인의 동작 방식이라고 한다.
우리가 흔히 생각하는 조인이다.
첫번째 테이블(outer relation)의 각각의 로우에 대해서 두번째 테이블(inner relation)의 모든 로우를 비교하여 조건에 맞는 로우(데이터)를 결합한다.
카티션 프로덕트를 생성한 뒤 조건을 적용하여 데이터를 필터링한다.
특징:
인덱스 구성이 되어있어야 한다. 인덱스가 없다면 엄청나게 느려질수있다.
조인을 한 레코드씩 순차적으로 진행한다. 먼저 액세스 되는 테이블의 처리 범위에 의해 전체 조인 성능이 결정된다. outer relation 에 있는 로우를 줄여야 한다. 그래서 이것으로 inner relation으로 스캔하게 된다. 이렇듯 조인에 참여하는 로우의 갯수를 줄이는 게 중요함을 확인 할 수있다.
작은 데이터셋에 적합하다.
조인 컬럼의 인덱스 여부와 인덱스 컬럼의 구성 방식에 따라 조인 효율이 크게 달라진다. index가 있는것이 가장좋고 적어도 secondary index라도 있어야 좋다.
두 테이블 간 정렬이 필요없다.
팁을 언급 하자면 조인 쿼리를 많이 작성해야 할 때, 많이 등장하는 컬럼에 인덱스를 설치하게 되면 접근성이 증가할 수 있게 된다.
단점:
- 인덱스 구성이 되어 있어도 대량의 데이터를 조인할 때 매우 비효율적이다.
큰 테이블에 사용하면 비효율적(연산량 증가).
테이블의 로우가 많으면 많을수록 m2이므로 선형적으로 증가하게 된다.
✅ 중첩루프조인의 PL/SQL 구현예제 1 - 조인 연산 없이
이 예제는 조인 연산 없이 PL/SQL을 구현한 예제이다.
SET serveroutput ON;
BEGIN
FOR outer IN (SELECT * FROM departments) LOOP
FOR inner IN (SELECT * FROM locations WHERE location_id = outer.location_id) LOOP
DBMS_OUTPUT.PUT_LINE(
outer.department_id || ' ' ||
outer.department_name || ' ' ||
inner.location_id || ' ' ||
inner.city
);
END LOOP;
END LOOP;
END;
외부 루프(Outer relation)
FOR outer IN (SELECT * FROM departments) LOOP
departments테이블의 모든 row(행)을 가져온다. (27개)
내부 루프(Inner relation)
- 각각의 로우에 대해 Inner relation에 있는 값을 비교한다.
FOR inner IN (SELECT * FROM locations WHERE location_id = outer.location_id) LOOP
locations테이블에 23개의 row가 있는데WHERE location_id = outer.location_id서 첫번째 레코드 23번 비교 두번째 레코드 23번 비교 .. 이렇게 된다.만약 인덱스가 있다면 인덱스 1번만 비교하게 될 것이다. 621번 했어야 하는 비교가 27로 바뀌게 된다.
- 출력:
department_id,department_name,location_id,city.
✅ 중첩루프조인의 PL/SQL 구현예제 2- 조인 연산 사용 WHERE
조인 테이블 확인
SELECT * FROM locations;에선country_id가 조인키가 된다.SELECT * FROM countries;또한country_id가 조인키가 된다.
조인 완료
SELECT l.location_id,l.city, l.state_province,c.country_id,까지는 location테이블에 있다.c.country_name은 countries에 있다.예제의 결과처럼 조인된 결과를 보여주기 위해선 아래의 명령어를 사용한다.
SELECT l.location_id, l.city, l.state_province, c.country_id, c.country_name
FROM locations l, countries c WHERE l.country_id = c.country_id;
- WHERE은 조인 조건으로써
l.country_id와c.country_id가 동일한 조건이다.
조인 실행 계획
Nested Loops: 중첩 루프 조인을 사용.
거기에 locations, full 테이블 조인이 되었다. 23개의 레코드가 있었음을 볼 수 있다.
Index Scan:
country_id에 인덱스(COUNTRY_C_ID_PK)가 설정.- 내부 테이블을 풀 스캔하지 않고, 필요한 데이터만 검색.
✅인덱스의 중요성 (Importance of Index):
인덱스가 없는 경우: 내부 테이블의 모든 행을 풀 스캔(Full Scan), 성능 저하를 일으킨다.
인덱스가 있는 경우: 필요한 데이터만 검색, 예:
location_id에 인덱스가 있으면 비교 횟수가 27번으로 줄어듦.조인 컬럼의 인덱스 설정: 조인 조건에 자주 사용되는 컬럼에 인덱스 추가할 수 있다. 예)
location_id,country_id.
조인의 동작 방식 #2: 소트 머지 조인 (How Join Works - Sort Merge Join)
💡요약: 소트 머지 조인(Sort Merge Join)에 대해 알아보자. 중첩 루프 조인은 좀 무식한 방법이기 때문에 dbms도 이를 최후의 방법으로 여기고 소트머지조인(Sort Merge Join)을 우선으로 처리하는 편이다. 소트 머지 조인(Sort Merge Join)은 두 테이블의 데이터를 정렬(Sorting)한 뒤 병합(Merge)하여 조인을 수행하는 방식이다.
✅ 소트 머지 조인의 작동 방식 (How Sort Merge Join Works):
정렬(Sort):
- PGA영역의 Sort 영역에서 정렬한 뒤 Nested Loop 조인 방식으로 진행된다.
쉽게 설명하면 두 테이블을 각각 정렬한 다음에 두 집합을 합치면서 조인을 수행하는 방식이다.
정렬 후에 합치기 때문에 inner relation은 이미 1, 1, 1 이런식으로 정렬된 상태가 된다.
즉 인덱스가 없는데도 이와 비슷한 역할을 하는 셈이다.
정렬 효율이 빨라지고 full table scan을 하지 않게 된다. 버퍼캐시를 사용하는 NL보다 빠르다.
예:
employees와departments테이블의department_id기준으로 정렬.
병합(Merge):
- 정렬된 두 테이블을 순차적으로 비교하여 조인 조건에 맞는 데이터를 결합.
✅ 소트 머지 방식의 단점 (Disadvantage of Sort Merge Join)
정렬 비용 추가: 정렬 작업이 필요하므로 추가 비용 발생.
이미 정렬된 경우에는 불필요: 정렬된 데이터에선 성능 이점이 적음.
단점은 정렬을 하는데 이 정렬 비용이 추가적으로 들게 된다는 점이다. 만약 정렬하는 비용이 더 들 것 같다고 DBMS가 판단 하면 중첩 루프 조인을 선택하게 될 것 이다. 일반적으로 소트 머지 조인은 버퍼 캐시를 사용하는 Nested Loop보다 빠르게 수행된다. 인덱스가 없는 경우 인덱스를 실시간으로 생성하는 효과를 볼 수 있다. 미리 정렬이 되어 있기 때문이다. 만약 인덱스가 있는 경우엔 조인 속도가 바로 증가하게 된다. 데이터가 정렬되어 있기 때문에 비교값이 없거나 다 찾은 후에는 종료하게 된다.
✅ 예제 쿼리와 결과 (Query and Results):
이 예제를 통해 인덱스가 없어도 정렬을 통해 빠르게 접근이 가능한 것을 알수있다. Nested Loop Join과 달리, =, <, >, <=, >= 조건에 모두 적용 가능하다. 또한 대용량 데이터 처리도 가능해서 대규모 테이블에 적합하다.
SELECT e.employee_id, e.first_name, e.last_name, e.department_id, d.department_name
FROM employees e, departments d
WHERE e.department_id = d.department_id;
Employees 테이블 (Outer Relation): 모든 데이터를 읽기 때문에 Full Table Scan (107개 레코드).
Departments 테이블 (Inner Relation):
department_id에 인덱스가 존재하여 인덱스를 사용하였다.정렬 및 병합: 조건(WHERE):
e.department_id = d.department_id, 두 테이블의department_id기준으로 정렬 후 병합되었다.
✅소트 머지 조인의 실행 계획
employee테이블에서 outer relation은 모든 레코드를 비교해야하므로 full, 107개가 확인된다.
inner relation인 department테이블이 중요한데, 인덱스가 없다면 full table scan을 하게된다. 이 경우에는 인덱스가 있으므로 첫번째 인덱스만 찾아 가져왔다.
자, 여기까지보면 nested loop와 차이가 없는것처럼 보인다. 하지만 sort merge join에서는 department id에 대해서 sort를 한다. 그래서 sort merge join 이 일반적으론 빠른편에 속한다.
✅소트 머지 조인의 실행 계획에서 알수있는 특징
1) 첫번째 테이블에 소트 연산을 대체할 인덱스가 있을 때 유용하다.
- 두 테이블을 정렬하기 때문이다. 부분 범위 처리가 가능하다는 뜻이다.
2) 첫번째 테이블이 이미 정렬되어 있을 때 유용하다.
- group by, order by 등을 먼저 수행한 경우이다.
3) 조인 조건식이 = 조건이 아닐 때에도 적용 가능하다.
- 이 부분이 제일 중요하다. 참고로 해시조인은 조인 조건식이 =일 경우에만 사용할 수 있다.
조인의 동작 방식 #3: 해시 조인 (How Join Works - Hash Join)
💡 요약: 해시 조인은 기존의 중첩 루프 조인(Nested Loop Join)과 소트 머지 조인(Sort Merge Join)이 비효율적인 경우에 성능을 개선하기 위해 개발된 방식이다.
✅ 해시 조인의 특징 (Characteristics of Hash Join):
중첩루프조인과 소트머지조인이 효과적이지 않은 상황에 대한 대안으로 개발
- "효과적이지 않은 상황”을 정의하긴 좀 어렵다. 보통은 인덱스가 없거나, 대규모의 데이터 처리에 적합하지 않을 때리 할 수 있겠다.
대규모의 데이터 처리에 적합하다.
- 원리는 무엇일까? 해시라는 것은 해시함수를 적용하여 범위를 줄여준다. 해시를 이용해서 주소가 1~1000까지 있을때 이 1000까지 주소 되어 있는 부분을 10개씩 묶을 수 있게 된다. 그럼 1000개를 비교하는대신 100개를 10번 비교하는 형태가 된다. 이런식으로 비교 범위를 확 줄여주는 것이 해시조인의 기본 원리라고 할 수 있다.
일반적인 경우 중첩루프조인이나 소트머지조인보다 나은 성능을 보임
- 또 "언제" 더 나은 성능을 보이느냐? 대부분은 outer relation에 비해 inner relation의 로우의 숫자 더 많을 때 이다.
두 테이블 중 작은 사이즈의 테이블을 읽어 해시 영역에 해시 테이블 생성한다.
- Build Input(작은 테이블) 이라고 한다.
나머지 큰 테이블의 레코드를 하나씩 읽어 해시 테이블에 연결하는 방식이다.
- Probe Input(큰 테이블)이라고 한다.
✅ 해시 조인의 동작 과정 (Hash Join Execution Process):
해시 조인은 큰 두 개의 테이블에서 조인 조건에 맞는 데이터를 효율적으로 찾아내는 방법이다. 데이터를 메모리에 저장한 후, 해시 알고리즘을 통해 데이터를 비교하고 매칭한다. 이 과정은 특히 대규모 데이터에서 유용하다.
1️⃣ Table Scan (테이블 스캔) - Vehicles Table
첫 번째로, "Vehicles Table"이라는 테이블을 읽는다.
이 과정에서 데이터를 스캔하고, 조인에 필요한 컬럼 값을 뽑아낸다.
이 값들을 활용해 2️⃣ 해시 테이블(Hash Table)이라는 것을 만든다. 해시 테이블은 데이터를 빠르고 효율적으로 저장하고 검색하기 위해 사용하는 자료구조이다.
이럴때 vehicles table은 값이 작은 것을 확인할 수 있다. sales는 판매될수록 계속 늘어나게 될 것이다.
(비유): "차량"이라는 박스를 열어 안에 들어 있는 필요한 자료만 꺼내오는 과정이다.
2️⃣ Hash Table 생성
"Vehicles Table"에서 꺼낸 값들을 사용해 해시 테이블이라는 특별한 데이터 구조를 만든다.
이 해시 테이블은 메모리(PGA)에 저장되며, 검색을 빠르게 도와준다.
(비유): 필요한 자료들을 바구니(PGA)에 넣고 정리해둔 상태라고 볼 수 있다.
3️⃣ Table Scan (테이블 스캔) - Sales Table
(설명): 두 번째 테이블인 "Sales Table"을 읽는다.
이 과정에서 조인 조건에 맞는 데이터만 추려낸다. (해시 테이블 생성)
테이블 스캔이 한번 진행된다. 중요한 부분이다. 위에서 부터 아래로 한번만 한다. 이로써 해시 테이블을 구성한다.
(비유): "판매 기록"이라는 박스를 열어 차량 정보와 관련된 것만 가져오는 작업이다.
4️⃣ Row Sent to Hash Join (행을 해시 조인으로 전달)
- "Sales Table"에서 가져온 데이터 중, 조인에 필요한 데이터만 선택한다.
이 데이터는 해시 조인 알고리즘에 따라 다시 해시 테이블로 보내진다.
(비유): 판매 데이터에서 차량과 관련된 부분만 선별해서 바구니에 추가한다.
5️⃣ Hash Join 수행
마지막으로 "Sales Table"에서 가져온 데이터와 "Vehicles Table"로 만든 해시 테이블을 비교하게 된다.
두 테이블의 데이터가 매칭되는지를 확인하여 결과를 출력한다.
(비유): 두 박스에서 꺼낸 데이터를 서로 비교하여 연결 가능한 것들만 짝짓는 작업이다.
✅ Hash Join with Temporary Tables (임시 테이블을 이용한 해시 조인)
해시 조인은 기존의 중첩 루프 조인(Nested Loop Join)이나 소트 머지 조인(Sort Merge Join)보다 효율적이지 않은 경우, 특히 인덱스가 없거나 대규모 데이터를 처리할 때 성능을 개선하기 위해 사용되는 조인방법이다.
1️⃣ 임시 테이블 생성 (Creating Temporary Tables)
DROP TABLE emp_temp;
DROP TABLE dept_temp;
DROP TABLE loc_temp;
DROP TABLE coun_temp;
CREATE TABLE emp_temp AS SELECT * FROM employees;
CREATE TABLE dept_temp AS SELECT * FROM departments;
CREATE TABLE loc_temp AS SELECT * FROM locations;
CREATE TABLE coun_temp AS SELECT * FROM countries;
실습을 위해 인덱스나 제약 조건 없이 테이블 생성을 하였다.
인덱스가 없으므로 Full Table Scan이 필요하며, 해시 함수를 적용하여 데이터 비교한다.
데이터만 가지고와서 똑같은 테이블을 만드는 것이고 인덱스나 다른 제약조건이 없는 상태이다.
2️⃣해시 조인 예제 1 (Hash Join Example 1)
Location ID 기준 조인 (Example 1: Join on Location ID)
SELECT * FROM dept_temp d, loc_temp l
WHERE d.location_id = l.location_id;
dept_temp와loc_temp를 Full Scan한다.location_id기준으로 해시 테이블 생성한다.해시 테이블을 사용해 조건에 맞는 데이터를 결합한다.
Full Table Scan:
dept_temp(27개),loc_temp(23개).Hash Join: 해시 테이블을 생성하고 매칭하였다.
3️⃣해시 조인 예제 2 (Hash Join Example 2 with Filter)
Location ID와 Country ID 조합 (Example 2: Join on Location ID and Filter)
SELECT l.location_id, l.city, l.state_province, c.country_id, c.country_name
FROM loc_temp l, coun_temp c
WHERE l.country_id = c.country_id
AND l.location_id <= 2000;
loc_temp에서location_id <= 2000조건으로 필터링한다.coun_temp와country_id를 기준으로 해시 테이블 생성한다.필터링된
loc_temp와 해시 테이블을 매칭한다.
- 해시 조인을 통해 조건에 맞는 데이터만 결합하였다.
4️⃣해시 조인 예제 3 (Hash Join Example 3)
Department ID 기준 조인 (Example 3: Join on Department ID)
SELECT e.last_name, d.department_id, d.department_name
FROM dept_temp d, emp_temp e
WHERE d.department_id = e.department_id;
dept_temp(작은 테이블)를 Build Input으로 사용해 해시 테이블 생성한다.emp_temp(큰 테이블)를 Probe Input으로 사용해 매칭한다.
Full Table Scan:
dept_temp(27개),emp_temp(110개).Hash Join:
department_id기준으로 해시 테이블 생성하였다.
✅해시 조인이 유용한 상황 (Scenarios Where Hash Join is Useful)
조인 컬럼에 적당한 인덱스가 없을 때
인덱스가 없으면 Nested Loop Join은 Inner 테이블의 모든 행을 반복적으로 스캔해야 하기 때문에 비효율 적이게 된다.
하지만 해시 조인을 사용하면 해시 테이블을 생성하고, 비교 범위를 줄여 효율적으로 데이터 검색이 가능해지게 된다.
인덱스가 있어도 Inner 테이블 액세스량이 많을 때:
Inner 테이블에 대규모 데이터가 포함된 경우, 인덱스 접근만으로도 많은 비용이 발생하게 된다.
한번 스캔하는 것이 비용이 많이 드는 경우 해시 조인을 통해 한 번의 Full Table Scan 후 데이터를 그룹화(나누기)하여 해당하는 부분을 Nested Loop로 조인할 수 있다. 이로써 비용 절감이 가능해진다.
대용량 테이블 조인 시:
- 수행 빈도가 낮은 대용량 테이블에서 쿼리 시간이 오래 걸릴 때 해시 조인을 활용한다. 한 번의 테이블 스캔만으로 데이터를 해시 테이블에 저장하고 비교할 수 있게 된다.
스캔 비용이 높은 경우:
큰 테이블을 Full Scan해야 할 때, 데이터를 해시 함수로 그룹화하여 비교.
그룹화된 데이터는 Nested Loop Join을 통해 효율적으로 조합 가능.
✅ 해시 조인 사용 조건 (Conditions for Using Hash Join):
한쪽 테이블이 충분히 작아야 함:
Build Input(해시 테이블 생성에 사용되는 테이블)이 해시 영역(PGA)에 들어갈 정도로 작아야 함.
작은 테이블에서 해시 테이블을 생성하여 비교 작업을 단순화.
Build Input의 해시 키에 중복 값이 적어야 함:
해시 키 컬럼에 중복 값이 많으면 해시 테이블의 효율성이 떨어질 수 있음.
중복 값이 많을 경우 Nested Loop Join이나 Sort Merge Join이 더 적합할 수 있음.
3️⃣Comparison of Join Methods (조인 방식 비교)
💡요약: 조인 방식은 데이터 크기, 인덱스 유무, 작업 범위에 따라 효율성이 달라진다. 중첩 루프 조인, 소트 머지 조인, 해시 조인의 특징과 적합한 상황을 비교한다.
✅ 중첩 루프 조인 (Nested Loop Join)
특징:
기본 조인 방법: 테이블의 모든 행을 다른 테이블의 모든 행과 비교한다. (카티션 프로덕트)
소량 데이터에 적합: 데이터가 적을수록 빠른 성능을 가진다.
인덱스 필수: 조인 컬럼에 인덱스가 필요하다.
순차적 접근: 테이블 접근 순서에 따라 성능이 달라지므로 테이블의 접근 순서가 중요하다.
장점: 소량 데이터 처리에 효율적이고 부분 범위 처리 가능한 점
단점: 대량 데이터에서 비효율적. 인덱스가 없으면 성능이 크게 저하된다.
✅소트 머지 조인 (Sort Merge Join)
특징:
대량 데이터 처리에 적합: 데이터를 정렬(Sort)한 뒤 병합(Merge)하는 방식이다.
인덱스 불필요: 정렬 작업이 임시 인덱스 역할을 한다.
전체 범위 처리: 모든 데이터를 처리해야 하는 작업에 적합하다.
장점: 대량 데이터 처리가 효율적이다. 정렬 작업 후 빠른 병합이 가능하다.
단점: 소트 부하가 발생한다. 즉 정렬 작업에 따른 추가 비용이 든다는 뜻이다. 데이터가 이미 정렬되어 있으면 불필요한 소트 작업 발생할 수 있다.
✅ 해시 조인 (Hash Join)
특징:
대량 데이터와 전체 범위 처리에 적합하다. 특히 작은 테이블과 큰 테이블 조인 시 유용하다.
해시 테이블 생성: 작은 테이블을 기반으로 해시 테이블 생성 후 큰 테이블과 매칭하는 방식이다.
메모리 사용: 해시 테이블 생성에 메모리 의존으로 메모리 사용량에 영향을 받는다.
장점: 대량 데이터 처리 효율적이다. 인덱스 없이도 빠른 조인 수행이 가능하다.
단점: 메모리 크기에 의존한다. 즉 작은 테이블(Build Input)이 PGA 메모리에 들어갈 정도로 작아야한다. 해시 함수에 의한 추가 작업 필요하다.
