소스: sql-src/batch_02_join_methods/01_join_result.sql, 02_index_effect.sql, 03_non_equi.sql.
dept 5행, emp 300행, orders 3,000행을 재귀 CTE 로 만들어 씁니다. 조인 방식이 바뀌어도 결과가 같다는 것과 인덱스 유무만 H2 로 확인하고, 방식별 동작은 도식으로 보입니다.
dept 5행과 emp 300행을 조인하면 건수는 emp 와 같습니다. 부서 조건을 붙이면 emp 5분의 1인 60건이 남습니다.
SELECT
COUNT(*) AS join_cnt
FROM dept d
JOIN emp e ON e.dept = d.code;JOIN_CNT
--------
300
(1행)SELECT
COUNT(*) AS join_cnt
FROM dept d
JOIN emp e ON e.dept = d.code
WHERE d.dname = '개발팀';JOIN_CNT
--------
60
(1행)이 조건은 dept 에 있습니다. dept 를 드라이빙 표로 삼으면 바깥에서 1행만 남고, 그 1행으로 emp 를 찾는 셈입니다. emp 쪽 dept 컬럼에 인덱스가 있다면 Nested Loop 가 효율적입니다.
FROM 에 emp 를 먼저 쓰고 dept 를 뒤에 써도 결과는 같습니다. 어느 표를 먼저 읽을지는 옵티마이저가 통계로 정하기 때문입니다.
SELECT
COUNT(*) AS join_cnt
FROM emp e
JOIN dept d ON e.dept = d.code
WHERE d.dname = '개발팀';JOIN_CNT
--------
60
(1행)부서별 건수도 같은 조인으로 확인합니다.
SELECT
d.dname
, COUNT(*) AS cnt
FROM dept d
JOIN emp e ON e.dept = d.code
GROUP BY d.dname
ORDER BY d.dname;DNAME | CNT
-------+----
개발팀 | 60
기획팀 | 60
영업팀 | 60
인사팀 | 60
재무팀 | 60
(5행)orders 3,000행이 emp 를 emp_id 로 참조합니다. 사원 10번의 주문을 조인으로 세면 인덱스가 있든 없든 건수는 10건입니다.
SELECT
COUNT(*) AS join_cnt
FROM emp e
JOIN orders o ON o.emp_id = e.id
WHERE e.id = 10;JOIN_CNT
--------
10
(1행)같은 조인의 H2 EXPLAIN 은 인덱스에 따라 달라집니다. 아래는 H2 형식이며 오라클이나 MySQL 계획의 근거가 아닙니다. 출력이 길어 조인 방식과 관련된 줄만 옮깁니다.
-- 인덱스 없음
FROM "PUBLIC"."ORDERS" "O"
/* PUBLIC.ORDERS.tableScan */
INNER JOIN "PUBLIC"."EMP" "E"
/* PUBLIC.PRIMARY_KEY_1: ID = 10
AND ID = O.EMP_ID */인덱스가 없으면 H2 는 orders 를 통째로 읽는 것(tableScan)에서 시작해 emp 를 기본 키로 찾습니다. orders 3,000행을 다 읽는 셈입니다. emp_id 에 인덱스를 만들면 계획이 바뀝니다.
CREATE INDEX ix_orders_emp ON orders (emp_id);-- 인덱스 있음
FROM "PUBLIC"."EMP" "E"
/* PUBLIC.PRIMARY_KEY_1: ID = 10 */
INNER JOIN "PUBLIC"."ORDERS" "O"
/* PUBLIC.IX_ORDERS_EMP: EMP_ID = E.ID
AND EMP_ID = 10 */이제 emp 에서 10번 1행을 먼저 찾고, 그 키로 orders 의 인덱스를 탑니다. 드라이빙 표가 바뀌고 읽는 행이 3,000행에서 10행 안팎으로 줄었습니다. 인덱스 하나가 드라이빙 표까지 바꾼 예입니다.
주의H2 EXPLAIN 은 H2 의 형식과 결정일 뿐 Oracle, MySQL, MSSQL 이 같은 쿼리에서 같은 방식을 고른다는 근거가 아닙니다. 인덱스가 조인 순서를 바꿀 수 있다는 사실만 가져가고, 실제 DB 의 계획은 그 DB 에서 확인합니다.
급여 등급 표 sal_grade(등급별 하한 lo, 상한 hi)와 emp 를 BETWEEN 으로 조인합니다. 조건이 같음이 아니라 범위입니다.
SELECT
g.grade
, COUNT(*) AS cnt
FROM emp e
JOIN sal_grade g ON e.sal BETWEEN g.lo AND g.hi
GROUP BY g.grade
ORDER BY g.grade;GRADE | CNT
------+----
A | 74
B | 77
C | 75
D | 74
(4행)이 데이터에는 급여 700 이상이 없어 E 등급은 나오지 않습니다. 모든 사원이 정확히 한 등급에 들어가는지도 건수로 확인합니다.
SELECT
COUNT(*) AS join_cnt
FROM emp e
JOIN sal_grade g ON e.sal BETWEEN g.lo AND g.hi;JOIN_CNT
--------
300
(1행)같은 조건을 등가로 바꾸면 하한과 딱 같은 급여만 맞아 3건뿐입니다.
SELECT
COUNT(*) AS join_cnt
FROM emp e
JOIN sal_grade g ON e.sal = g.lo;JOIN_CNT
--------
3
(1행)범위 조건은 급여 하나에 등급 하나를 해시로 곧바로 찾을 수 없습니다. 해시 값은 같음만 구분하고 크고 작음은 모르기 때문입니다. 이런 조인에는 Nested Loop 나 Sort Merge 가 쓰입니다.