[MSSQL] SQL Server 튜닝 - 조인

목차

시작하며

드디어 조인....!

예전에 정말 간단하게, 단순 구현쪽으로만 조인에 대해 포스팅을 작성했던 적이 있긴 한데, 좀더 자세히 공부해보고자 한다.

 

[MSSQL] JOIN 설명 및 사용법

 

왠만하면 LEFT OUTER JOIN 만 사용하자는 내부 규칙이 존재했었어서... 아무튼 크게 신경을 쓰지 않고 조인을 사용했었던 과거의 나를 반성하며 다시 공부를 해봤다.

 

조인(JOIN)이란?

조인은 관계가 있는 두 개 이상의 테이블을 주어진 조건으로 결합하여 하나의 결과 집합으로 출력하는 기능이다.

관계형 데이터베이스는 데이터들을 정규화하고 이를 분할된 테이블로써 관리하게 되는데, 이렇게 분할된 데이터를 다시 연결하여 하나의 결과값으로 출력하기 위해 조인을 사용하는것이다.

 

조인문을 작성하는 기본 구문은 다음과 같다.

 

SELECT *
    FROM 테이블명 AS a
        JOIN 테이블명 AS b
        ON 조인 조건

 

CROSS JOIN을 제외한 조인들은 결과 집합을 만들기 위해 테이블 간의 관계를 갖는 조건이 요구되는데, ON절에 기술된 열이 조인의 관계로 사용되는 키가 된다.

 

다음은 조인의 예시를 설명하기 위한 테이블 구조이고, 간단하게 실제 테이블도 만들어봤다.

--테이블 생성
CREATE TABLE Employee(
     Emp_No  INT IDENTITY(1, 1) NOT NULL
    ,Name NVARCHAR(200) NOT NULL
    ,Dept_no INT
)

CREATE TABLE Department(
     Dept_No INT NOT NULL
    ,Dept_Name NVARCHAR(200) NOT NULL
)

--테이블 데이터 넣기
INSERT INTO Employee (Name, Dept_no) VALUES ('Danial', 30)
INSERT INTO Employee (Name, Dept_no) VALUES ('Benjamin', 20)
INSERT INTO Employee (Name, Dept_no) VALUES ('Austin', 10)
INSERT INTO Employee (Name, Dept_no) VALUES ('Fredrick', 20)
INSERT INTO Employee (Name, Dept_no) VALUES ('Jesica', 10)
INSERT INTO Employee (Name, Dept_no) VALUES ('Henry', 10)
INSERT INTO Employee (Name) VALUES ('James')

INSERT INTO Department (Dept_no, Dept_Name) VALUES ('10', 'Development')
INSERT INTO Department (Dept_no, Dept_Name) VALUES ('20', 'Sales')
INSERT INTO Department (Dept_no, Dept_Name) VALUES ('30', 'Management')
INSERT INTO Department (Dept_no, Dept_Name) VALUES ('40', 'Design')
INSERT INTO Department (Dept_no, Dept_Name) VALUES ('50', 'Marketing')

 

직원의 정보를 저장하는 Employee 테이블과 부서 정보를 저장하는 Department 테이블이 있다.

두 테이블은 Dept_No 열로 관계를 갖는데, 여기서 특정 직원의 부서 이름을 출력해야 한다면 두 테이블을 조인하여 결과를 확인해야만 한다.

 

예를들어 'Jesica'의 부서명을 확인하는 구문은 다음과 같이 작성된다.

SELECT e.Name
      ,d.Dept_Name
    FROM Employee as e
        INNER JOIN Department as d
        ON e.Dept_no = d.Dept_No
    WHERE e.Name = 'Jesica'

Employee 테이블과 Department 테이블을 Dept_No 열을 기준으로 조인하고 Name 열에 조건을 지정한 구문이다.

Name = 'Jesica' 조건에 해당되는 Dept_No 값은 10이므로 Dept_Name의 값으로 'Development' 라는 결과를 얻을 수 있다.

실행계획

SELECT e.Name, d.Dept_Name     FROM Employee as e         INNER JOIN Department as d         ON e.Dept_no = d.Dept_No     WHERE e.Name = 'Jesica'
  |--Hash Match(Inner Join, HASH:([e].[Dept_no])=([d].[Dept_No]), RESIDUAL:([master].[dbo].[Department].[Dept_No] as [d].[Dept_No]=[master].[dbo].[Employee].[Dept_no] as [e].[Dept_no]))
       |--Table Scan(OBJECT:([master].[dbo].[Employee] AS [e]), WHERE:([master].[dbo].[Employee].[Name] as [e].[Name]=N'Jesica'))
       |--Table Scan(OBJECT:([master].[dbo].[Department] AS [d]))

실행계획을 확인해보면 위와 같다.

1. Employee 테이블 스캔하여 Name이 'Jesica' 인 결과집합 만듬
2. Department 테이블 스캔
3. Hash Match 실행하여 e.Dept_No = d.Dept_No 인 조건에 맞춰 결과집합 만듬

 

조인의 종류

조인의 종류는 크게 논리적 조인(Logical Join)과 물리적 조인(Physical Join)으로 분류된다.

논리적 조인은 INNER JOIN, OUTER JOIN, CROSS JOIN, APPLY와 같이 결과 집합의 데이터를 결정하는 방식이다.

물리적 조인은 논리적 조인을 처리하기 위한 방법으로 Nested Loop Join, Merge Join, Hash Join이 이에 속한다.

 

1. INNER JOIN (논리적 조인)

INNER JOIN은 조인 조건을 만족하는 데이터만 결과로 출력하는 논리적 조인이다.

INNER JOIN 의 집합 관계

조인 조건을 기준으로 A와 B의 교집합이 되는 부분이 최종 INNER JOIN된 결과 집합이 되는데, SQL 구문으로 사용하면 다음과 같다.

SELECT e.Name
      ,d.Dept_No
      ,d.Dept_Name
    FROM Employee as e
        INNER JOIN Department as d
        ON e.Dept_no = d.Dept_No

 

결과집합

조인 조건인 Dept_No 열을 기준으로 조인에 성공한 10, 20, 30 값을 가진 데이터만 출력한다.

SELECT e.Name       ,d.Dept_No       ,d.Dept_Name     FROM Employee as e         INNER JOIN Department as d         ON e.Dept_no = d.Dept_No
  |--Hash Match(Inner Join, HASH:([d].[Dept_No])=([e].[Dept_no]), RESIDUAL:([master].[dbo].[Department].[Dept_No] as [d].[Dept_No]=[master].[dbo].[Employee].[Dept_no] as [e].[Dept_no]))
       |--Table Scan(OBJECT:([master].[dbo].[Department] AS [d]))
       |--Table Scan(OBJECT:([master].[dbo].[Employee] AS [e]))

실행계획을 확인해보면 위와 같다.

1. Department 테이블 스캔
2. Employee 테이블 스캔
3. Hash Match 사용하여 d.Dept_No = e.Dept_No 인 데이터로 결과집합 만듬
4. 결과집합 출력

2.  OUTER JOIN (논리적 조인)

OUTER JOIN은 조인 조건을 만족하는 데이터와 함께 기준이 되는 테이블의 조인 실패 행까지 출력하는 조인이다.

기준이 되는 테이블은 LEFT, RIGHT, FULL 이렇게 3가지 형태로 정의할 수 있다.

 

LEFT OUTER JOIN

LEFT OUTER JOIN은 왼쪽 테이블이 기준 테이블이 된다.

조인 조건을 통해 A와 B의 교집합이 되는 데이터(2) 와 함께 A 테이블에서 조인이 실패하는 행(1) 까지 최종 결과 집합에 포함된다.

SQL 구문으로 사용하면 다음과 같다.

SELECT e.Name
      ,d.Dept_No
      ,d.Dept_Name
    FROM Employee as e
        LEFT OUTER JOIN Department as d
        ON e.Dept_no = d.Dept_No

기준이 되는 Employee 테이블은 조인의 실패하는 행을 NULL 값으로 입력하여 조인에 성공한 행과 함께 최종 출력한다.

SELECT e.Name       ,d.Dept_No       ,d.Dept_Name     FROM Employee as e         LEFT OUTER JOIN Department as d         ON e.Dept_no = d.Dept_No
  |--Nested Loops(Left Outer Join, WHERE:([master].[dbo].[Employee].[Dept_no] as [e].[Dept_no]=[master].[dbo].[Department].[Dept_No] as [d].[Dept_No]))
       |--Table Scan(OBJECT:([master].[dbo].[Employee] AS [e]))
       |--Table Scan(OBJECT:([master].[dbo].[Department] AS [d]))

위는 실행계획이다.

1. Employee 테이블을 스캔한다
2. Department 테이블을 스캔한다
3. Nested Loops 조인 방식으로써, (1) 에서 찾은 데이터의 행 수만큼 (2) 를 반복하여 조인된 결과 집합을 만든다.
4. 최종 결과 집합을 출력한다.

자세한 실행계획 내용을 보면, 1에서 찾은 데이터의 행 수인 7 개의 row 만큼 Department 테이블의 전체 row 수 인 5회 반복하여,

 

Department 테이블을 총 35row 읽는것을 볼 수 있다.

RIGHT OUTER JOIN

RIGHT OUTER JOIN은 LEFT OUTER JOIN과는 반대로 오른쪽 테이블이 기준 테이블이 된다.

조인 조건을 통해 교집합이 되는 데이터 (2) 와 함께, B 테이블에서 조인이 실패하는 행(1) 까지 최종 결과 집합에 포함한다.

SQL 구문으로 사용하면 다음과 같다.

SELECT e.Name
      ,d.Dept_No
      ,d.Dept_Name
    FROM Employee as e
        RIGHT OUTER JOIN Department as d
        ON e.Dept_no = d.Dept_No

기준이 되는 Department 테이블은 조인의 실패하는 행을 NULL 값으로 입력하여 조인에 성공한 행과 함께 최종 출력한다.

SELECT e.Name       ,d.Dept_No       ,d.Dept_Name     FROM Employee as e         RIGHT OUTER JOIN Department as d         ON e.Dept_no = d.Dept_No
  |--Nested Loops(Left Outer Join, WHERE:([master].[dbo].[Employee].[Dept_no] as [e].[Dept_no]=[master].[dbo].[Department].[Dept_No] as [d].[Dept_No]))
       |--Table Scan(OBJECT:([master].[dbo].[Department] AS [d]))
       |--Table Scan(OBJECT:([master].[dbo].[Employee] AS [e]))

위는 실행계획이다.

1. Department 테이블 스캔
2. Employee 테이블 스캔
3. NL Join 방식으로써 (1)에서 찾은 데이터의 행 수만큼 (2)를 반복하여 조인된 결과 집합을 만든다.
4. 최종 결과 집합을 출력한다.

좀더 자세한 플랜을 확인해보자

FULL OUTER JOIN

FULL OUTER JOIN은 양쪽 테이블이 모두 기준 테이블이 되며 조인 조건을 통해 교집합이 되는 데이터 (2) 와 함께 각 테이블에서 조인이 실패하는 행 (1) 까지 최종 결과 집합에 포함된다.

 

SQL 구문으로 사용하면 다음과 같다.

SELECT e.Name
      ,d.Dept_No
      ,d.Dept_Name
    FROM Employee as e
        FULL OUTER JOIN Department as d
        ON e.Dept_no = d.Dept_No

기준이 되는 두 테이블은 조인의 실패하는 행을 NULL값으로 입력하여 조인에 성공한 행과 함께 최종 출력한다.

SELECT e.Name       ,d.Dept_No       ,d.Dept_Name     FROM Employee as e         FULL OUTER JOIN Department as d         ON e.Dept_no = d.Dept_No
 9|--Concatenation
      8|--Nested Loops(Left Outer Join, WHERE:([master].[dbo].[Employee].[Dept_no] as [e].[Dept_no]=[master].[dbo].[Department].[Dept_No] as [d].[Dept_No]))
      6|    |--Table Scan(OBJECT:([master].[dbo].[Employee] AS [e]))
      7|    |--Table Scan(OBJECT:([master].[dbo].[Department] AS [d]))
      5|--Compute Scalar(DEFINE:([e].[Name]=NULL))
           4|--Nested Loops(Left Anti Semi Join, OUTER REFERENCES:([d].[Dept_No]))
                2|--Table Scan(OBJECT:([master].[dbo].[Department] AS [d]))
                3|--Top(TOP EXPRESSION:((1)))
                     1|--Table Scan(OBJECT:([master].[dbo].[Employee] AS [e]), WHERE:([master].[dbo].[Employee].[Dept_no] as [e].[Dept_no]=[master].[dbo].[Department].[Dept_No] as [d].[Dept_No]))

text실행계획에 순서를 매겨봤다.

1. Department 테이블을 스캔한다
2. Employee 테이블에서 e.Dept_no = d.Dept_No 조건에 맞는 결과 집합 탐색
3. (2)에서 만든 결과 집합에서 TOP 1 추출하는 로직
4. NL Join 방식으로써 (1)에서 찾은 데이터의 수 만큼 (2)~(3)을 반복하여 조인된 결과 집합을 만든다
5. NL Join의 결과값 중, e.Name = NULL 인 집합을 만든다.
6. Employee 테이블 스캔
7. Department 테이블 스캔
8. NL Join 방식으로써 (6)에서 찾은 데이터의 수만큼 (7)을 반복하여 조인된 결과 집합을 만든다
9.  (5) 와 (8) 을 결합하여 최종 결과 집합을 만든다
10. 최종 결과 집합 출력

(5) 결과집합

(8) 결과집합

Top Operations

3. CROSS JOIN (논리적 조인)

CROSS JOIN은 조인 조건 없이 두 테이블에 대한 모든 행을 조인하는 기능이다.

카티션 곱(Cartesian Product)이라고도 불리며, 결과 행 수는 각 테이블의 행을 곱한 수와 같다.

 

SQL 구문으로는 다음과 같이 사용된다.

SELECT e.Name
      ,d.Dept_No
      ,d.Dept_Name
    FROM Employee as e
        CROSS JOIN Department as d
            WHERE e.Emp_No IN (1, 2, 3)
                AND d.Dept_No IN (10, 20, 30)

Employee 테이블과 Department 테이블의 각 3건씩을 조인한 결과로 총 9건이 최종 출력된다.

SELECT e.Name       ,d.Dept_No       ,d.Dept_Name     FROM Employee as e         CROSS JOIN Department as d             WHERE e.Emp_No IN (1, 2, 3)                 AND d.Dept_No IN (10, 20, 30)
  |--Nested Loops(Inner Join)
       |--Table Scan(OBJECT:([master].[dbo].[Employee] AS [e]), WHERE:([master].[dbo].[Employee].[Emp_No] as [e].[Emp_No]=(1) OR [master].[dbo].[Employee].[Emp_No] as [e].[Emp_No]=(2) OR [master].[dbo].[Employee].[Emp_No] as [e].[Emp_No]=(3)))
       |--Table Scan(OBJECT:([master].[dbo].[Department] AS [d]), WHERE:([master].[dbo].[Department].[Dept_No] as [d].[Dept_No]=(10) OR [master].[dbo].[Department].[Dept_No] as [d].[Dept_No]=(20) OR [master].[dbo].[Department].[Dept_No] as [d].[Dept_No]=(30)))

위는 실행계획이다.

1. Employee 테이블 스캔 / e.Emp_No가 1이거나 2이거나 3인 결과 집합
2. Department 테이블 스캔 / d.Dept_No가 10이거나 20이거나 30인 결과 집합
3. NL Join 방식으로써 (1) 에서 찾은 데이터의 행 수만큼 (2) 를 반복하여 조인된 결과 집합을 만든다
4. 최종 결과 집합 출력

4. APPLY (논리적 조인)

APPLY는 일반적인 조인과 유사하지만 조인하는 과정에서 인라인뷰(Inline View)나 테이블 반환 함수의 매개변수로 값을 전달할 수 있다는 것에 차이가 있다.

결과를 출력하는 방식으로는 CROSS APPLY와 OUTER APPLY로 나누어진다

 

CROSS APPLY

SELECT d.Dept_Name
      ,d.Dept_No
      ,e.Name
    FROM Department as d
        CROSS APPLY (SELECT TOP 1 Name
                        FROM Employee as e
                        WHERE e.Dept_no = d.Dept_No
                        ORDER BY Emp_No DESC) as e

Department 테이블의 각 행을 Employee 테이블로 내부 조인한 결과 집합에서 Emp_No 열 기준으로 가장 높은 1건씩만 출력한다.

INNER JOIN처럼 조인에 성공하는 행만 출력하지만, Department 테이블의 Dept_No 값을 인라인 뷰 안쪽으로 전달할 수 있다는 것에서 차이가 있다.

 

SELECT d.Dept_Name       ,d.Dept_No       ,e.Name     FROM Department as d         CROSS APPLY (SELECT TOP 1 Name                         FROM Employee as e                         WHERE e.Dept_no = d.Dept_No                         ORDER BY Emp_No DESC) as e
  |--Nested Loops(Inner Join, OUTER REFERENCES:([d].[Dept_No]))
       |--Table Scan(OBJECT:([master].[dbo].[Department] AS [d]))
       |--Sort(TOP 1, ORDER BY:([e].[Emp_No] DESC))
            |--Table Scan(OBJECT:([master].[dbo].[Employee] AS [e]), WHERE:([master].[dbo].[Employee].[Dept_no] as [e].[Dept_no]=[master].[dbo].[Department].[Dept_No] as [d].[Dept_No]))

위는 실행계획이다.

1. Department 테이블 스캔
2. Employee 테이블 스캔 / e.Dept_no = d.Dept_No 조건으로 스캔
3.  (1) 결과를 Emp_No 기준으로 내림차순 정렬 후 TOP 1 산출
4. NL Join으로써 (1) 에서 찾은 데이터의 행 수만큼 (2) ~ (3) 반복하며 조인된 결과 집합 만든다.
5. 결과 출력

Department의 Dept_No엔 10, 20, 30, 40, 50 총 5개종류가 있으므로 Number of Executions는 5회 발생한것 확인 가능

Dept_No 가 10, 20, 30 인 row 각각 3, 2, 1개씩 해서 6의 Actual Number of Rows for All Executions 발생 확인 가능

10, 20, 30 각각 Sort연산 했으므로 Actual Number of Rows for All Executions 3회 발생

 

위와 마찬가지로 횟수 확인 가능

 

OUTER APPLY

SELECT d.Dept_Name
      ,d.Dept_No
      ,e.Name
    FROM Department as d
        OUTER APPLY (SELECT TOP 1 Name
                        FROM Employee as e
                        WHERE e.Dept_no = d.Dept_No
                        ORDER BY Emp_No DESC) as e

CROSS APPLY 가 INNER JOIN에 해당된다면, OUTER APPLY는 LEFT OUTER JOIN에 해당된다.

위 구문은 CROSS APPLY를 통해 추출한 결과 집합과 함께 조인에 실패하는 행도 NULL 값이 입력되어 최종 결과 집합에 포함된다.

SELECT d.Dept_Name       ,d.Dept_No       ,e.Name     FROM Department as d         OUTER APPLY (SELECT TOP 1 Name                         FROM Employee as e                         WHERE e.Dept_no = d.Dept_No                         ORDER BY Emp_No DESC) as e
  |--Nested Loops(Left Outer Join, OUTER REFERENCES:([d].[Dept_No]))
       |--Table Scan(OBJECT:([master].[dbo].[Department] AS [d]))
       |--Sort(TOP 1, ORDER BY:([e].[Emp_No] DESC))
            |--Compute Scalar(DEFINE:([Expr1004]=[master].[dbo].[Employee].[Name] as [e].[Name]))
                 |--Table Scan(OBJECT:([master].[dbo].[Employee] AS [e]), WHERE:([master].[dbo].[Employee].[Dept_no] as [e].[Dept_no]=[master].[dbo].[Department].[Dept_No] as [d].[Dept_No]))

위는 실행계획이다.

1. Department 테이블 스캔
2. Employee 테이블 스캔 / e.Dept_no = d.Dept_No 조건으로 스캔
3. Expr1004 변수에 (1)에서 찾은 결과의 Name 컬럼의 데이터를 e.Name으로 저장
4. (2) 결과를 Emp_No 기준으로 내림차순 정렬 후 TOP 1 산출
5. NL Join으로써 (1)에서 찾은 데이터의 수 만큼 (2)~(4) 반복하며 조인된 결과 만든다. / NULL인 결과도 포함됨
6. 결과 출력

(1)의 결과를 Expr1004 변수에 저장하는것을 볼 수 있다.

여기서 눈여겨봐야할점은 Estimated Number of Executions가 5라는 점인데, Dept_No가 40, 50인 값도 Null로 저장한다는것이다.

NL Join의 Actual Number of Rows for All Executions도 5인것을 확인할 수 있다.

5. Nested Loop Join (물리적 조인)

Nested Loop Join 은 중첩된 반복 조인이다.

선행 테이블의 결과 집합을 한 건씩 후행 테이블에 조인하고 이를 반복하여 최종 결과 집합을 만들어 낸다.

선행 테이블의 결과 집합 건수만큼 조인이 반복되기 때문에 결과 집합의 크기에 따라 조인의 전체 일량이 결정된다.

 

NL Join 처리 과정은 다음과 같이 나타낼 수 있다.

 

CREATE NONCLUSTERED INDEX IX_DEPTNO ON Dept(Deptno)
CREATE NONCLUSTERED INDEX IX_EMP ON Emp(Deptno)

SELECT e.EName
      ,e.Sal
      ,d.DName
    FROM Dept as d
        INNER LOOP JOIN Emp as e
        ON d.Deptno = e.Deptno
    WHERE d.Deptno = 20
        AND e.Sal > 2000

NL Join의 처리 과정

Dept 테이블의 Deptno = 20 조건에 만족하는 행과 Emp 테이블의 Sal > 2000 조건에 만족하는 행을 Deptno 열을 기준으로 NL Join 하는 과정을 나타낸다.

두 테이블의 조인 절차는 아래와 같다.

1. Dept 테이블의 Deptno = 20 조건에 만족하는 데이터를 IX_DEPTNO 인덱스로 범위 탐색한다.
2. IX_DEPTNO 인덱스로부터 탐색한 행의 RID 값으로 Dept 테이블에 RID Lookup을 수행한다.
3. 조인 조건인 Deptno 열을 기준으로 Emp 테이블의 IX_EMP 인덱스와 조인한다.
4. 조인된 결과에서 IX_EMP의 RID 값으로 Emp 테이블에 RID Lookup을 수행한다.
5. 조인된 결과 집합에서 Emp 테이블의 조건인 Sal > 2000 을 만족하는 데이터만 운반 단위에 전달한다.
6. (1)에서 탐색한 행 수만큼 (2) ~ (5) 과정을 반복하여 최종 결과 집합을 출력한다.

 

6. Merge Join (물리적 조인)

Merge Join은 두 테이블의 처리 범위 내에서 조인 키 기준으로 정렬한 다음 병합하는 과정으로 조인하는 방식이다.

두 테이블에 대해 정렬을 선행하게 되는데, 인덱스를 통해 미리 정렬된 데이터를 사용한다면 이 과정은 생략할 수 있다.

 

NL Join은 후행 테이블을 반복 스캔하면서 조인하는 반면 Merge Join은 한번만 스캔하고 조인을 수행하기 때문에, 인덱스로 정렬을 생략할 수 있는 상황에서 데이터가 크다면 성능적인 면으로 이점을 가질 수 있다.

 

Merge Join 사용시 주의해야 할 사항

1. Merge Join을 수행할 때 정렬이 되어있지 않다면 그 크기에 따라 리소스 부담이 생길 수 있다. 정렬을 수행하기 위해 메모리를 사용하며 메모리에서 모든 처리가 불가능할 땐 디스크까지 사용하기 때문이다.
2. 선행 테이블의 조인 키를 기준으로 중복되는 데이터가 많으면 WorkTable 사용에 대한 부담이 가해질 수 있다. 선행 테이블의 동일한 값을 다시 조인하기 위해 후행 테이블에 대상 데이터를 WorkTable에 저장하여 재사용하기 때문이다.

아래 그림은 Merge Join에 대한 처리 과정이다.

Dept 테이블의 Deptno > 20 조건에 만족하는 행 Emp 테이블의 Sal > 2000 조건에 만족하는 행을 Deptno 열을 기준으로 Merge Join 하는 과정을 나타낸다.

 

두 테이블의 조인 처리 절차는 다음과 같다.

1. Dept 테이블에서 Deptno > 20 조건에 만족하는 데이터를 찾고 조인 조건인 Deptno 열로 정렬을 수행한다. Emp 테이블에서도 Sal > 2000 조건에 만족하는 데이터를 찾고 Deptno 열을 기준으로 정렬을 수행한다.
2. Deptno로 정렬된 데이터를 기준으로 마지막 값을 만날 때까지 스캔하면서 최종 결과 집합을 출력한다.

 

Merge Join은 1:N 구조일 때와 N:M 구조일 떄의 처리 방식이 달라지는데 각각의 조인 절차를 보여주고자 한다.

아래 그림은 파란색 선이 조인에 매칭되면서 결과값으로 도출되는 데이터를 표시한 것이다.

 

1:N 형식의 Merge Join

1:N 구조의 Merge Join 에서는 Sb 열만 중복된 데이터 값을 가지고 있는 구조이다.

Sa 열의 값 1을 비교할 때 Sb 열의 첫 번째 1(a)과 비교한 뒤 다음 행의 1(b) 값과 비교를 진행한다.

 

그리고 다음 행의 3 값을 비교할 때는 조인 조건에 부합하지 않기 때문에 Sa 열의 1 값은 비교를 중단한다.

그 다음 Sa 열의 2 값과 다시 Sb 열의 3을 비교하게 되는데 Sa 값이 더 작기 때문에 다시 다음 행으로 이동하여 Sa 열의 3 값과 Sb 열의 3을 비교하는 방식으로 진행된다.

 

N:M 형식의 Merge Join

N:M 구조일 경우 WorkTable이 사용된다. 기본적인 방식은 1:N 구조와 동일하지만, 선행 테이블의 중복 값이 있을 경우엔 첫 번째 값을 비교할 때 후행 테이블의 값을 WorkTable에 저장하고 재사용하는 방식으로 수행된다.

 

중복된 값에 대한 비교가 끝날 때 WorkTable에서 값은 삭제된다.

 

7. Hash Join (물리적 조인)

Hash Join은 해시 함수를 적용하여 조인하는 방식이다.

• 빌드 입력(Build Input) : 조인할 때 선행으로 읽어내는 테이블
• 프로브 입력(Probe Input) : 조인할 때 후행으로 읽는 테이블

 

빌드 입력은 해시 함수를 적용하여 해시 테이블을 생성하고, 프로브 입력의 값을 해시 함수로 적용하면서 해시 테이블에 조인하는 과정으로 수행된다.

 

Hash Join도 해시 테이블을 생성할 때 메모리를 사용하게 된다.

• 인 메모리 해시 조인 : 메모리만 사용하여 처리할 경우
• 유예 해시 조인 : 메모리 용량 부족으로 디스크 영역을 사용할 경우

Hash Join은 아래와 같이 처리된다.

CREATE NONCLUSTERED INDEX IX_DEPTNO ON Dept(Deptno)

SELECT e.eName
      ,e.Sal
      ,d.dName
    FROM Dept as d
        INNER HASH JOIN Emp as e
        ON d.Deptno = e.Deptno
    WHERE d.Deptno > 20
        AND e.Sal > 2000

 

Dept 테이블의 Deptno > 20 조건에 만족하는 행과 Emp 테이블의 Sal > 2000 조건에 만족하는 행을 Deptno 열을 기준으로 Hash Join 하는 과정을 나타낸다.

 

두 테이블의 조인 처리 절차는 아래와 같다.

1. Dept 테이블에서 Deptno > 20 조건에 만족하는 행을 IX_DEPTNO 인덱스로 탐색한 후 해시 함수를 적용하여 해시 테이블을 생성한다.
2. Emp 테이블에서 Sal > 2000 조건에 해당하는 행을 해시 함수를 적용하면서 해시 테이블에 조인한다.
3. 조인에 성공한 행은 최종 결과 집합에 포함하며, Emp 테이블의 행이 모두 비교될 때까지 (2)를 반복한다.

 

암시적으로 조인이 사용되는 구문들

SQL 구문에서 명시적으로 조인을 사용하지 않아도 내부적으로 조인하여 처리하는 구문이 있다.

아래 스크립트로 테스트용 테이블을 생성하고 스칼라 반환 함수와 스칼라 서브쿼리로 예시를 설명한다.

SELECT number as id
      ,CASE WHEN number <= 5 THEN 'A'
        ELSE 'B'
        END as class
      ,newid() as txt
      ,getdate() as date
      INTO TB_First  
    FROM master.dbo.spt_values
    WHERE type = 'P'
        AND number BETWEEN 1 AND 1000

SELECT *
      ,newid() as code
      INTO TB_Second
    FROM TB_First


-- 스칼라 함수 생성
CREATE FUNCTION dbo.FN_GetGode (@pid INT)
RETURNS VARCHAR(64)
AS
BEGIN
    DECLARE @lCode VARCHAR(64)
    
    SELECT @lCode = code
        FROM TB_Second
        WHERE id = @pid

        RETURN @lCode
END
GO

TB_First

TB_Second

1. 스칼라 반환 함수 (Scalar Function)

스칼라 반환 함수는 외부로부터 1개의 값을 입력받고 내부 구문을 수행하여 결과를 반환한다.

함수는 단일 값으로 반환되며 사용 예시는 다음과 같다.

SELECT *
      ,dbo.FN_GetGode(id) as Code
      FROM TB_First

Query Messages

Started executing query at Line 157
(1000 rows affected)
Table 'Worktable'. Scan count 1000, logical reads 4021, physical reads 0, page server reads 0, read-ahead reads 0, page server read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob page server reads 0, lob read-ahead reads 0, lob page server read-ahead reads 0.
Table 'TB_Second'. Scan count 1, logical reads 9, physical reads 0, page server reads 0, read-ahead reads 0, page server read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob page server reads 0, lob read-ahead reads 0, lob page server read-ahead reads 0.
Table 'TB_First'. Scan count 1, logical reads 7, physical reads 0, page server reads 0, read-ahead reads 0, page server read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob page server reads 0, lob read-ahead reads 0, lob page server read-ahead reads 0.
(8 rows affected)
(1 row affected)
Total execution time: 00:00:00.236

스칼라 반환 함수는 1개의 값을 전달받아 1개의 값을 반환하기 때문에 TB_First 테이블의 결과 집합인 1000건에 대해서 반복 수행하게 된다.

이는 NL Join과 동일한 방식으로 볼 수 있고, 실제 실행계획을 확인했을때도 옵티마이저가 NL Join을 실행하는것을 확인할 수 있다.

SELECT *       ,dbo.FN_GetGode(id) as Code       FROM TB_First
  |--Compute Scalar(DEFINE:([Expr1012]=CONVERT_IMPLICIT(varchar(64),CONVERT_IMPLICIT(varchar(64),CASE WHEN [Expr1006]=(0) THEN NULL ELSE [Expr1007] END,0),0)))
       |--Nested Loops(Inner Join, OUTER REFERENCES:([master].[dbo].[TB_First].[id]))
            |--Table Scan(OBJECT:([master].[dbo].[TB_First]))
            |--Compute Scalar(DEFINE:([Expr1006]=CONVERT_IMPLICIT(int,[Expr1016],0)))
                 |--Stream Aggregate(DEFINE:([Expr1016]=Count(*), [Expr1007]=ANY([master].[dbo].[TB_Second].[code])))
                      |--Index Spool(SEEK:([master].[dbo].[TB_Second].[id]=[master].[dbo].[TB_First].[id]))
                           |--Table Scan(OBJECT:([master].[dbo].[TB_Second]))

위는 실행계획이다.

1. TB_First 테이블 전체 스캔
2. TB_Second 테이블 전체 스캔하여 Index Spool 물리연산 실행 / (입력 행을 검색하고 각 행의 복사본을 숨겨진 스풀 파일에 배치하여 행에 대해 비클러스터형 인덱스를 작성한다. 이렇게 하면 인덱스의 검색 기능을 사용하여 SEEK:() 조건자에 부합되는 행만 출력할 수 있다. 예를 들어 Nested Loops 연산자로 연산자를 다시 돌리지만 다시 바인딩할 필요가 없을 경우 입력 사항을 다시 검색하는 대신 스풀된 데이터를 사용한다.)
3. Stream Aggregate 연산자 실행되며 함수 결과 집계
4. NL Join 발생하며 (1)에서 집계된 row 수만큼 (2)~(3) 실행

 

2. 스칼라 서브 쿼리 (Scalar SubQuery)

서브 쿼리는 SQL 구문 안에 존재하는 SQL 구문을 의미한다.

FROM 절에 정의되는 SQL을 제외하고 SELECT 절이나 WHERE 절 등에 작성될 수 있으며 단일 값의 결과만 반환할 수 있다.

 

다음은 서브쿼리의 사용 예시이다.

SELECT f.*
      ,(SELECT Code
            FROM TB_Second as s
            WHERE s.id = f.id) as Code
    FROM TB_First as f

Query Messages

Started executing query at Line 161
(1000 rows affected)
Table 'Worktable'. Scan count 1000, logical reads 4021, physical reads 0, page server reads 0, read-ahead reads 0, page server read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob page server reads 0, lob read-ahead reads 0, lob page server read-ahead reads 0.
Table 'TB_Second'. Scan count 1, logical reads 9, physical reads 0, page server reads 0, read-ahead reads 0, page server read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob page server reads 0, lob read-ahead reads 0, lob page server read-ahead reads 0.
Table 'TB_First'. Scan count 1, logical reads 7, physical reads 0, page server reads 0, read-ahead reads 0, page server read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob page server reads 0, lob read-ahead reads 0, lob page server read-ahead reads 0.
(8 rows affected)
(1 row affected)
Total execution time: 00:00:00.158

SELECT f.*       ,(SELECT Code             FROM TB_Second as s             WHERE s.id = f.id) as Code     FROM TB_First as f
  |--Compute Scalar(DEFINE:([Expr1005]=[Expr1007]))
       |--Nested Loops(Left Outer Join, OUTER REFERENCES:([f].[id]))
            |--Table Scan(OBJECT:([master].[dbo].[TB_First] AS [f]))
            |--Assert(WHERE:(CASE WHEN [Expr1006]>(1) THEN (0) ELSE NULL END))
                 |--Stream Aggregate(DEFINE:([Expr1006]=Count(*), [Expr1007]=ANY([master].[dbo].[TB_Second].[code] as [s].[code])))
                      |--Index Spool(SEEK:([s].[id]=[master].[dbo].[TB_First].[id] as [f].[id]))
                           |--Table Scan(OBJECT:([master].[dbo].[TB_Second] AS [s]))

1. TB_First 테이블 전체 스캔
2. TB_Second 테이블 전체 스캔하여 Index Spool 물리연산 실행
3. Stream Aggregate 연산자 실행되며 함수 결과 집계
4. NL Join 발생하며 (1)에서 집계된 row 수만큼 (2)~(3) 실행

 

서브쿼리는 메인 쿼리와 조인 조건을 기준으로 OUTER JOIN 되어 결과 집합을 반환한다.

스칼라 서브쿼리는 1건의 행만 반환할 수 있기 때문에, 유니크 인덱스 정의나 DISTINCT의 사용과 같이 결과 집합이 1건임을 보장하지 못한다면, NL Join 방식으로 수행되어 검증 과정을 거치게 된다.

 

반대로, 1건이 보장되는 환경에서는 다른 조인 방식이 사용될 수 있다.

 

조인의 힌트

힌트는 SQL 구문을 수행할 때 실행 방법을 고정하는 명령이다.

조인하는 3가지 방식(Loop, Merge, Hash)도 고정할 수 있는 힌트가 제공되는데, 다음은 그 예시를 설명한다.

 

1. Nested Loop

Loop 힌트를 사용하면 Nested Loop Join으로 수행 방식이 고정되며 다음과 같다.

SELECT *
    FROM TB_First as f
        INNER LOOP JOIN TB_Second as s
            ON f.id = s.id
    WHERE f.class = 'A'

Query Message

Started executing query at Line 168
Warning: The join order has been enforced because a local join hint is used.
(5 rows affected)
Table 'TB_Second'. Scan count 1, logical reads 45, physical reads 0, page server reads 0, read-ahead reads 0, page server read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob page server reads 0, lob read-ahead reads 0, lob page server read-ahead reads 0.
Table 'TB_First'. Scan count 1, logical reads 7, physical reads 0, page server reads 0, read-ahead reads 0, page server read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob page server reads 0, lob read-ahead reads 0, lob page server read-ahead reads 0.
(4 rows affected)
(1 row affected)
Total execution time: 00:00:00.115

아래는 실행계획이다.

SELECT *     FROM TB_First as f         INNER LOOP JOIN TB_Second as s             ON f.id = s.id     WHERE f.class = 'A'
  |--Nested Loops(Inner Join, WHERE:([master].[dbo].[TB_Second].[id] as [s].[id]=[master].[dbo].[TB_First].[id] as [f].[id]))
       |--Table Scan(OBJECT:([master].[dbo].[TB_First] AS [f]), WHERE:([master].[dbo].[TB_First].[class] as [f].[class]='A'))
       |--Table Scan(OBJECT:([master].[dbo].[TB_Second] AS [s]))

 

2. Merge

Merge 힌트를 사용하면 Merge Join으로 수행 방식이 고정되며 다음과 같다.

SELECT *
    FROM TB_First as f
        INNER MERGE JOIN TB_Second as s
            ON f.id = s.id
    WHERE f.class = 'A'

Query Messages

Started executing query at Line 174
Warning: The join order has been enforced because a local join hint is used.
(5 rows affected)
Table 'Worktable'. Scan count 0, logical reads 0, physical reads 0, page server reads 0, read-ahead reads 0, page server read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob page server reads 0, lob read-ahead reads 0, lob page server read-ahead reads 0.
Table 'TB_Second'. Scan count 1, logical reads 9, physical reads 0, page server reads 0, read-ahead reads 0, page server read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob page server reads 0, lob read-ahead reads 0, lob page server read-ahead reads 0.
Table 'TB_First'. Scan count 1, logical reads 7, physical reads 0, page server reads 0, read-ahead reads 0, page server read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob page server reads 0, lob read-ahead reads 0, lob page server read-ahead reads 0.
(6 rows affected)
(1 row affected)
Total execution time: 00:00:00.165

아래는 실행계획이다.

SELECT *     FROM TB_First as f         INNER MERGE JOIN TB_Second as s             ON f.id = s.id     WHERE f.class = 'A'
  |--Merge Join(Inner Join, MANY-TO-MANY MERGE:([f].[id])=([s].[id]), RESIDUAL:([master].[dbo].[TB_Second].[id] as [s].[id]=[master].[dbo].[TB_First].[id] as [f].[id]))
       |--Sort(ORDER BY:([f].[id] ASC))
       |    |--Table Scan(OBJECT:([master].[dbo].[TB_First] AS [f]), WHERE:([master].[dbo].[TB_First].[class] as [f].[class]='A'))
       |--Sort(ORDER BY:([s].[id] ASC))
            |--Table Scan(OBJECT:([master].[dbo].[TB_Second] AS [s]))

 

3. Hash

Hash 힌트를 사용하면 Hash Join으로 수행 방식이 고정되며 다음과 같다.

SELECT *
    FROM TB_First as f
        INNER HASH JOIN TB_Second as s
            ON f.id = s.id
    WHERE f.class = 'A'

Query Messages

Started executing query at Line 180
Warning: The join order has been enforced because a local join hint is used.
(5 rows affected)
Table 'Workfile'. Scan count 0, logical reads 0, physical reads 0, page server reads 0, read-ahead reads 0, page server read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob page server reads 0, lob read-ahead reads 0, lob page server read-ahead reads 0.
Table 'Worktable'. Scan count 0, logical reads 0, physical reads 0, page server reads 0, read-ahead reads 0, page server read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob page server reads 0, lob read-ahead reads 0, lob page server read-ahead reads 0.
Table 'TB_Second'. Scan count 1, logical reads 9, physical reads 0, page server reads 0, read-ahead reads 0, page server read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob page server reads 0, lob read-ahead reads 0, lob page server read-ahead reads 0.
Table 'TB_First'. Scan count 1, logical reads 7, physical reads 0, page server reads 0, read-ahead reads 0, page server read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob page server reads 0, lob read-ahead reads 0, lob page server read-ahead reads 0.
(4 rows affected)
(1 row affected)
Total execution time: 00:00:00.073

아래는 실행계획이다.

SELECT *     FROM TB_First as f         INNER HASH JOIN TB_Second as s             ON f.id = s.id     WHERE f.class = 'A'
  |--Hash Match(Inner Join, HASH:([f].[id])=([s].[id]))
       |--Table Scan(OBJECT:([master].[dbo].[TB_First] AS [f]), WHERE:([master].[dbo].[TB_First].[class] as [f].[class]='A'))
       |--Table Scan(OBJECT:([master].[dbo].[TB_Second] AS [s]))