데이터베이스 조인 방식과 실행 계획 최적화

조인 선택은 읽는 순서에서 시작된다

조인은 여러 테이블에 흩어진 데이터를 하나의 SQL로 묶어 조회하는 과정이다. 같은 조인 조건이라도 어떤 테이블을 먼저 읽는지, 인덱스를 어떻게 활용하는지, 정렬이나 해시를 선택하는지에 따라 성능 특성이 달라진다.

Nested Loop Join, Sort Merge Join, Hash Join은 서로 대체 가능한 이름이 아니다. 데이터 양과 테이블 크기 차이, 조인 조건, 메모리 여건을 실행 계획과 함께 봐야 한다.

작은 결과셋을 먼저 읽는 Nested Loop Join

Nested Loop Join은 선행 테이블의 각 행마다 후행 테이블에서 조인 조건을 만족하는 행을 찾는다. 중첩 반복문과 유사한 방식이다.

⤢✕

선행 테이블의 범위가 작을수록 유리하다. 선행 테이블은 주로 순차 스캔(Sequential Scan)으로 처리하고, 후행 테이블은 인덱스를 통한 랜덤 액세스(Random Access)로 처리한다. 옵티마이저에 이 방식을 지시하려면 /*+ USE_NL(A B) */ 힌트를 사용할 수 있다.

인덱스가 잘 구성되어 있고 소량 데이터를 찾는 조회에 적합하다.

SELECT /*+ USE_NL(e d) */ e.employee_name, d.department_name
FROM employees e, departments d
WHERE e.department_id = d.department_id
AND e.hire_date > '2020-01-01';

이 쿼리에서는 hire_date 조건으로 employees 테이블의 검색 범위가 작다면 Nested Loop Join이 효율적이다.

정렬된 입력을 병합하는 Sort Merge Join

Sort Merge Join은 두 테이블을 조인 키로 정렬한 다음, 정렬된 결과를 순차적으로 읽으며 일치하는 행을 병합한다.

⤢✕

정렬 과정에서는 임시 공간(Temporary Tablespace)이 사용된다. 두 테이블 크기가 비슷할 때 효율적이며, 조인 키 인덱스로 정렬 단계를 생략할 수 있으면 성능이 향상된다. SELECT 항목을 불필요하게 넓히지 않는 것도 정렬 부하를 줄이는 방법이다. 힌트 구문은 /*+ USE_MERGE(A B) */이다.

대용량 처리와 BETWEEN, >, < 같은 범위 검색에 유리하다.

SELECT /*+ USE_MERGE(o c) */ o.order_id, c.customer_name
FROM orders o, customers c
WHERE o.customer_id = c.customer_id
AND o.order_date BETWEEN '2021-01-01' AND '2021-12-31';

orders와 customers 테이블이 모두 크고 범위 검색이 포함된 경우 Sort Merge Join을 선택할 수 있다.

해시 테이블로 탐색하는 Hash Join

Hash Join은 작은 테이블을 Build Input으로 삼아 해시 테이블을 만들고, 큰 테이블인 Probe Input을 스캔하면서 일치하는 키를 찾는다.

⤢✕

작은 테이블이 메모리에 완전히 로드될 수 있고 두 테이블의 크기 차이가 클 때 효율적이다. 등가 조인(= 연산자)에서만 사용할 수 있으며, CBO(Cost-Based Optimizer)에서만 지원된다. 디스크 액세스 측면에서도 매우 효율적이다. /*+ USE_HASH(A B) */ 힌트로 사용을 지시할 수 있다.

데이터 웨어하우스에서 집계 쿼리를 처리하는 경우에 활용할 수 있다.

SELECT /*+ USE_HASH(s p) */ p.product_name, SUM(s.sale_amount)
FROM sales s, products p
WHERE s.product_id = p.product_id
GROUP BY p.product_name;

sales 테이블이 매우 크고 products 테이블이 상대적으로 작다면 Hash Join이 효율적인 선택이다.

데이터 특성에 맞춰 조인 방식을 고른다

소량 데이터를 다루는 OLTP 환경에서는 Nested Loop Join이 적합하다. 인덱스가 잘 구성된 환경에서 성능을 기대할 수 있고 트랜잭션 처리에 맞는다.

중간 규모 데이터를 처리할 때는 Sort Merge Join을 검토할 수 있다. 특히 조인 조건이 등가(=) 조건이 아니거나 두 테이블 크기가 비슷한 경우에 유리하다.

대용량 데이터 웨어하우스에서는 Hash Join이 대체로 우수하다. 메모리 자원이 충분한 환경에서 배치 처리와 분석 쿼리에 적합하다.

최근 거래 조회에서 선행 테이블을 바꾼 경우

금융 거래 시스템에서 계좌 정보와 최근 거래 내역을 조회하는 쿼리는 다음과 같이 작성할 수 있다.

-- 최적화 전
SELECT a.account_number, a.account_name, t.transaction_amount, t.transaction_date
FROM accounts a, transactions t
WHERE a.account_id = t.account_id
AND t.transaction_date > SYSDATE - 7;

-- 최적화 후
SELECT /*+ USE_NL(t a) INDEX(t idx_transaction_date_account_id) */
       a.account_number, a.account_name, t.transaction_amount, t.transaction_date
FROM transactions t, accounts a
WHERE t.account_id = a.account_id
AND t.transaction_date > SYSDATE - 7;

transaction_date 조건으로 범위가 작은 transactions 테이블을 Driving Table로 지정하고, transaction_date와 account_id를 묶은 복합 인덱스로 accounts 테이블 액세스를 줄이는 방식이다.

월간 판매 보고서에서 Hash Join을 적용한 경우

월간 판매 보고서를 생성하는 대규모 리포팅 시스템에서는 다음과 같은 쿼리를 사용한다.

-- 최적화 전
SELECT p.category, SUM(s.amount)
FROM sales s, products p
WHERE s.product_id = p.product_id
AND s.sale_date BETWEEN '2021-01-01' AND '2021-01-31'
GROUP BY p.category;

-- 최적화 후
SELECT /*+ USE_HASH(s p) FULL(s) */
       p.category, SUM(s.amount)
FROM sales s, products p
WHERE s.product_id = p.product_id
AND s.sale_date BETWEEN '2021-01-01' AND '2021-01-31'
GROUP BY p.category;

이 경우 products 테이블을 해시 테이블로 구축하고, 큰 테이블인 sales는 FULL 스캔으로 처리해 I/O를 최소화한다.

실행 계획에서 확인할 항목

조인 성능을 분석할 때는 실행 계획(Execution Plan)을 검토한다.

EXPLAIN PLAN FOR
SELECT e.employee_name, d.department_name
FROM employees e, departments d
WHERE e.department_id = d.department_id;

SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY);

선택된 조인 방식이 데이터 특성에 맞는지, 테이블 접근 방식이 Full Table Scan인지 Index Scan인지 확인한다. 이어 조인 순서가 적절한지와 불필요한 정렬 작업이 발생하는지를 검토한다.

힌트는 옵티마이저에 조인 방식을 지시할 수 있지만, 최종 선택은 시스템 자원, 데이터 분포, 쿼리 특성을 함께 고려한 실행 계획 검토에 근거해야 한다.