[SQL] SQL Booster - JOIN과 성능

2024, Apr 29

7. JOIN과 성능

7.1 조인의 내부적인 처리 방식

7.1.1 조인의 3가지 처리 방식

조인

이너-조인
아우터-조인
카테시안-조인

조인의 내부적인 처리 방식

NESTED LOOPS JOIN
MERGE JOIN
HASH JOIN

7.1.2 NESTED LOOPS JOIN

: NL조인, 중첩된 반복문 형태

선행 집합과 후행 집합의 정의가 매우 중요
힌트 LEADING 사용
필요한 부분에 인덱스를 정확히 만들어 주면, 가장 적은 비용으로 빠르게 조인 결과를 얻을 수 있음
많은 양의 데이터를 조인하기에는 한계가 있음

실행계획 예시

"SQL_ID  0j6rqjda79y77, child number 0"
-------------------------------------
SELECT  /*+ GATHER_PLAN_STATISTICS LEADING(T1) USE_NL(T2) */    
"T1.RGN_ID ,T1.CUS_ID ,T1.CUS_NM    ,T2.ORD_DT ,T2.ORD_ST ,T2.ORD_AMT  "
"FROM    M_CUS T1    ,T_ORD T2  WHERE   T1.CUS_ID = T2.CUS_ID"
 
Plan hash value: 1392340618
 
-----------------------------------------------------------------------------------------------
| Id  | Operation          | Name  | Starts | E-Rows | A-Rows |   A-Time   | Buffers | Reads  |
-----------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |       |      1 |        |    501 |00:00:00.06 |     357 |     27 |
|   1 |  NESTED LOOPS      |       |      1 |   3082 |    501 |00:00:00.06 |     357 |     27 |
|   2 |   TABLE ACCESS FULL| M_CUS |      1 |     90 |     15 |00:00:00.06 |       9 |      6 |
|*  3 |   TABLE ACCESS FULL| T_ORD |     15 |     34 |    501 |00:00:00.01 |     348 |     21 |
-----------------------------------------------------------------------------------------------
 
Predicate Information (identified by operation id):
---------------------------------------------------
 
"   3 - filter(""T1"".""CUS_ID""=""T2"".""CUS_ID"")"
 

Starts → NL 조인에서 후행 집합에 접근한 횟수

7.1.3 MERGE JOIN

: 두 데이터 집합을 연결 조건 값으로 정렬한 후 조인을 처리

USE_MERGE 힌트를 이용함
머지 조인은 테이블에 반복해서 접근할 필요가 없음 (조인할 데이터를 먼저 정렬해서 한번만 읽음)
소트 작업을 어떻게 줄이느냐가 성능 향상의 주요 포인트~

실행계획 예시

"SQL_ID  18a5gm8p95p4t, child number 0"
-------------------------------------
SELECT /*+ GATHER_PLAN_STATISTICS LEADING(T1) USE_MERGE(T2) */    
"T1.RGN_ID ,T1.CUS_ID ,T1.CUS_NM    ,T2.ORD_DT ,T2.ORD_ST ,T2.ORD_AMT  "
"FROM    M_CUS T1    ,T_ORD T2  WHERE   T1.CUS_ID = T2.CUS_ID"
 
Plan hash value: 954095496
 
---------------------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                    | Name     | Starts | E-Rows | A-Rows |   A-Time   | Buffers | Reads  |  OMem |  1Mem | Used-Mem |
---------------------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT             |          |      1 |        |    501 |00:00:00.01 |      33 |      1 |       |       |          |
|   1 |  MERGE JOIN                  |          |      1 |   3082 |    501 |00:00:00.01 |      33 |      1 |       |       |          |
|   2 |   TABLE ACCESS BY INDEX ROWID| M_CUS    |      1 |     90 |     15 |00:00:00.01 |      10 |      1 |       |       |          |
|   3 |    INDEX FULL SCAN           | PK_M_CUS |      1 |     90 |     15 |00:00:00.01 |       5 |      1 |       |       |          |
|*  4 |   SORT JOIN                  |          |     15 |   3047 |    501 |00:00:00.01 |      23 |      0 |   178K|   178K|  158K (0)|
|   5 |    TABLE ACCESS FULL         | T_ORD    |      1 |   3047 |   3047 |00:00:00.01 |      23 |      0 |       |       |          |
---------------------------------------------------------------------------------------------------------------------------------------
 
Predicate Information (identified by operation id):
---------------------------------------------------
 
"   4 - access(""T1"".""CUS_ID""=""T2"".""CUS_ID"")"
"       filter(""T1"".""CUS_ID""=""T2"".""CUS_ID"")"

MERGE JOIN 의 자식 단계
- INDEX FULL SCAN → 인덱스 리프 블록을 처음부터 끝까지 차례대로 읽는 작업으로, PK_M_CUS를 CUS_ID를 정렬한 것과 같은 결과를 얻음
- SORT JOIN → TABLE ACCESS FULL 결과를 위해 정렬하는 작업

7.1.4 HASH JOIN

조인 성능 문제 대부분이 해시 조인으로 해결되는 경우가 많음. 하지만 다른 방식보다 많은 CPU와 메모리 자원을 사용하기에 무조건 해시 조인으로 해결하면 안된다.
해시함수를 이용한 처리 방식

실행계획 예시

"SQL_ID  2vckg3w0twy29, child number 0"
-------------------------------------
SELECT /*+ GATHER_PLAN_STATISTICS LEADING(T1) USE_HASH(T2) */    
"T1.RGN_ID ,T1.CUS_ID ,T1.CUS_NM    ,T2.ORD_DT ,T2.ORD_ST ,T2.ORD_AMT  "
"FROM    M_CUS T1    ,T_ORD T2  WHERE   T1.CUS_ID = T2.CUS_ID"
 
Plan hash value: 3240201901
 
-----------------------------------------------------------------------------------------------------------------
| Id  | Operation          | Name  | Starts | E-Rows | A-Rows |   A-Time   | Buffers |  OMem |  1Mem | Used-Mem |
-----------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |       |      1 |        |    501 |00:00:00.01 |      18 |       |       |          |
|*  1 |  HASH JOIN         |       |      1 |   3082 |    501 |00:00:00.01 |      18 |   990K|   990K| 1269K (0)|
|   2 |   TABLE ACCESS FULL| M_CUS |      1 |     90 |     90 |00:00:00.01 |       7 |       |       |          |
|   3 |   TABLE ACCESS FULL| T_ORD |      1 |   3047 |    501 |00:00:00.01 |      11 |       |       |          |
-----------------------------------------------------------------------------------------------------------------
 
Predicate Information (identified by operation id):
---------------------------------------------------
 
"   1 - access(""T1"".""CUS_ID""=""T2"".""CUS_ID"")"
 

7.2 NL 조인과 성능

7.2.2 후행 집합에 필요한 인덱스

후행 테이블 쪽의 조인 조건 컬럼에 인덱스가 필수다.

"SQL_ID  64rutf4xk2sjd, child number 0"
-------------------------------------
"SELECT  /*+ GATHER_PLAN_STATISTICS */    T1.CUS_ID  ,MAX(T1.CUS_NM) "
"CUS_NM ,MAX(T1.CUS_GD) CUS_GD ,COUNT(*) ORD_CNT    ,SUM(T2.ORD_QTY * "
"T2.UNT_PRC) ORD_AMT  FROM    M_CUS T1    ,T_ORD_JOIN T2  WHERE   "
T1.CUS_ID = T2.CUS_ID  AND     T1.CUS_ID = 'CUS_0009'  AND     
T2.ORD_YMD = '20170218'  GROUP BY T1.CUS_ID
 
Plan hash value: 1756537872
 
------------------------------------------------------------------------------------------------------
| Id  | Operation                     | Name       | Starts | E-Rows | A-Rows |   A-Time   | Buffers |
------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT              |            |      1 |        |      1 |00:00:00.20 |   26468 |
|   1 |  SORT GROUP BY NOSORT         |            |      1 |      1 |      1 |00:00:00.20 |   26468 |
|   2 |   NESTED LOOPS                |            |      1 |    363 |   2000 |00:00:00.04 |   26468 |
|   3 |    TABLE ACCESS BY INDEX ROWID| M_CUS      |      1 |      1 |      1 |00:00:00.01 |       2 |
|*  4 |     INDEX UNIQUE SCAN         | PK_M_CUS   |      1 |      1 |      1 |00:00:00.01 |       1 |
|*  5 |    TABLE ACCESS FULL          | T_ORD_JOIN |      1 |    363 |   2000 |00:00:00.04 |   26466 |
------------------------------------------------------------------------------------------------------
 
Predicate Information (identified by operation id):
---------------------------------------------------
 
"   4 - access(""T1"".""CUS_ID""='CUS_0009')"
"   5 - filter((""T2"".""CUS_ID""='CUS_0009' AND ""T2"".""ORD_YMD""='20170218'))"
 
Note
-----
   - dynamic sampling used for this statement (level=2)

후행 테이블에 CUS_ID 인덱스 걸고 실행 계획

"SQL_ID  2r1tcta4ru6j7, child number 0"
-------------------------------------
SELECT  /*+ GATHER_PLAN_STATISTICS LEADING(T1) USE_NL(T2) INDEX(T2 
"X_T_ORD_JOIN_1) */    T1.CUS_ID ,MAX(T1.CUS_NM) CUS_NM ,MAX(T1.CUS_GD) "
"CUS_GD ,COUNT(*) ORD_CNT    ,SUM(T2.ORD_QTY * T2.UNT_PRC) ORD_AMT  "
"FROM    M_CUS T1    ,T_ORD_JOIN T2  WHERE   T1.CUS_ID = T2.CUS_ID  "
AND     T1.CUS_ID = 'CUS_0009'  AND     T2.ORD_YMD = '20170218'  
GROUP BY T1.CUS_ID
 
Plan hash value: 1352034644
 
-------------------------------------------------------------------------------------------------------------------
| Id  | Operation                     | Name           | Starts | E-Rows | A-Rows |   A-Time   | Buffers | Reads  |
-------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT              |                |      1 |        |      1 |00:00:00.12 |     646 |    155 |
|   1 |  SORT GROUP BY NOSORT         |                |      1 |      1 |      1 |00:00:00.12 |     646 |    155 |
|   2 |   NESTED LOOPS                |                |      1 |    363 |   2000 |00:00:00.03 |     646 |    155 |
|   3 |    TABLE ACCESS BY INDEX ROWID| M_CUS          |      1 |      1 |      1 |00:00:00.01 |       2 |      0 |
|*  4 |     INDEX UNIQUE SCAN         | PK_M_CUS       |      1 |      1 |      1 |00:00:00.01 |       1 |      0 |
|*  5 |    TABLE ACCESS BY INDEX ROWID| T_ORD_JOIN     |      1 |    363 |   2000 |00:00:00.03 |     644 |    155 |
|*  6 |     INDEX RANGE SCAN          | X_T_ORD_JOIN_1 |      1 |  51637 |  55000 |00:00:00.09 |     156 |    155 |
-------------------------------------------------------------------------------------------------------------------
 
Predicate Information (identified by operation id):
---------------------------------------------------
 
"   4 - access(""T1"".""CUS_ID""='CUS_0009')"
"   5 - filter(""T2"".""ORD_YMD""='20170218')"
"   6 - access(""T2"".""CUS_ID""='CUS_0009')"
 
Note
-----
   - dynamic sampling used for this statement (level=2)
 

오히려 더 느려짐
- T_ORD_JOIN TABLE ACCESS 할때, INDEX RANGE SCAN 55000건 읽어와서 2000건만 사용해서 그럼
해결방법
- ORD_YMD까지 복합인덱스로 건다.

후행 테이블에 CUS_ID, ORD_YMD 인덱스 걸고 실행 계획

"SQL_ID  aw25899rc9ryw, child number 0"
-------------------------------------
SELECT  /*+ GATHER_PLAN_STATISTICS LEADING(T1) USE_NL(T2) INDEX(T2 
"X_T_ORD_JOIN_2) */    T1.CUS_ID ,MAX(T1.CUS_NM) CUS_NM ,MAX(T1.CUS_GD) "
"CUS_GD ,COUNT(*) ORD_CNT    ,SUM(T2.ORD_QTY * T2.UNT_PRC) ORD_AMT  "
"FROM    M_CUS T1      ,T_ORD_JOIN T2  WHERE   T1.CUS_ID = T2.CUS_ID  "
AND     T1.CUS_ID = 'CUS_0009'  AND     T2.ORD_YMD = '20170218'  
GROUP BY T1.CUS_ID
 
Plan hash value: 802225516
 
-------------------------------------------------------------------------------------------------------------------
| Id  | Operation                     | Name           | Starts | E-Rows | A-Rows |   A-Time   | Buffers | Reads  |
-------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT              |                |      1 |        |      1 |00:00:00.05 |      29 |     10 |
|   1 |  SORT GROUP BY NOSORT         |                |      1 |      1 |      1 |00:00:00.05 |      29 |     10 |
|   2 |   NESTED LOOPS                |                |      1 |    363 |   2000 |00:00:00.05 |      29 |     10 |
|   3 |    TABLE ACCESS BY INDEX ROWID| M_CUS          |      1 |      1 |      1 |00:00:00.01 |       2 |      0 |
|*  4 |     INDEX UNIQUE SCAN         | PK_M_CUS       |      1 |      1 |      1 |00:00:00.01 |       1 |      0 |
|   5 |    TABLE ACCESS BY INDEX ROWID| T_ORD_JOIN     |      1 |    363 |   2000 |00:00:00.05 |      27 |     10 |
|*  6 |     INDEX RANGE SCAN          | X_T_ORD_JOIN_2 |      1 |     58 |   2000 |00:00:00.05 |      11 |     10 |
-------------------------------------------------------------------------------------------------------------------
 
Predicate Information (identified by operation id):
---------------------------------------------------
 
"   4 - access(""T1"".""CUS_ID""='CUS_0009')"
"   6 - access(""T2"".""CUS_ID""='CUS_0009' AND ""T2"".""ORD_YMD""='20170218')"
 
Note
-----
   - dynamic sampling used for this statement (level=2)

Buffers 가 29로 개선된 것을 확인할 수 있다.

결론

후행 집합의 조인 조건 컬럼에는 인덱스가 필수
후행 집합에 사용된 조인 조건과 WHERE 조건 컬럼에 복합 인덱스를 고려해야 한다.

7.2.3 선행 집합 변경에 따른 쿼리 변형

선행 집합을 T_ORD_JOIN 으로 하고 실행계획

"SQL_ID  4jh305fymk6gj, child number 0"
-------------------------------------
SELECT  /*+ GATHER_PLAN_STATISTICS LEADING(T2) USE_NL(T1) INDEX(T2 
"X_T_ORD_JOIN_2) */    T1.CUS_ID ,MAX(T1.CUS_NM) CUS_NM ,MAX(T1.CUS_GD) "
"CUS_GD ,COUNT(*) ORD_CNT    ,SUM(T2.ORD_QTY * T2.UNT_PRC) ORD_AMT  "
"FROM    M_CUS T1      ,T_ORD_JOIN T2  WHERE   T1.CUS_ID = T2.CUS_ID  "
AND     T1.CUS_ID = 'CUS_0009'  AND     T2.ORD_YMD = '20170218'  
GROUP BY T1.CUS_ID
 
Plan hash value: 2519304977
 
--------------------------------------------------------------------------------------------------------------------
| Id  | Operation                      | Name           | Starts | E-Rows | A-Rows |   A-Time   | Buffers | Reads  |
--------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT               |                |      1 |        |      1 |00:00:00.04 |    2031 |     16 |
|   1 |  SORT GROUP BY NOSORT          |                |      1 |      1 |      1 |00:00:00.04 |    2031 |     16 |
|   2 |   NESTED LOOPS                 |                |      1 |        |   2000 |00:00:00.18 |    2031 |     16 |
|   3 |    NESTED LOOPS                |                |      1 |    170 |   2000 |00:00:00.03 |      31 |     16 |
|   4 |     TABLE ACCESS BY INDEX ROWID| T_ORD_JOIN     |      1 |    170 |   2000 |00:00:00.03 |      27 |     16 |
|*  5 |      INDEX RANGE SCAN          | X_T_ORD_JOIN_2 |      1 |    170 |   2000 |00:00:00.01 |      11 |      0 |
|*  6 |     INDEX UNIQUE SCAN          | PK_M_CUS       |   2000 |      1 |   2000 |00:00:00.01 |       4 |      0 |
|   7 |    TABLE ACCESS BY INDEX ROWID | M_CUS          |   2000 |      1 |   2000 |00:00:00.01 |    2000 |      0 |
--------------------------------------------------------------------------------------------------------------------
 
Predicate Information (identified by operation id):
---------------------------------------------------
 
"   5 - access(""T2"".""CUS_ID""='CUS_0009' AND ""T2"".""ORD_YMD""='20170218')"
"   6 - access(""T1"".""CUS_ID""='CUS_0009')"
 

쿼리에서 T_ORD_JOIN에는 CUS_ID 조건을 주지도 않았는데 알아서 T2.CUS_ID = ‘CUS_0009’ 를 가져옴 → 쿼리 변형 이라고 함
- 변형된 이유 : 실행 결과에 전혀 영향이 없고, 변형이 성능에 더 좋음

7.2.4 조인 횟수를 줄이자#1

X_T_ORD_JOIN_2 → CUS_ID, ORD_YMD

"SQL_ID  55u3gdh55w3sg, child number 0"
-------------------------------------
SELECT  /*+ GATHER_PLAN_STATISTICS LEADING(T2) USE_NL(T1) INDEX(T2 
"X_T_ORD_JOIN_2) */    T1.CUS_ID ,MAX(T1.CUS_NM) CUS_NM ,MAX(T1.CUS_GD) "
"CUS_GD ,COUNT(*) ORD_CNT    ,SUM(T2.ORD_QTY * T2.UNT_PRC) ORD_AMT  "
"FROM    M_CUS T1    ,T_ORD_JOIN T2  
WHERE   T1.CUS_ID = T2.CUS_ID  "
AND     T2.ORD_YMD = '20170218'  
AND     T1.CUS_GD = 'A'  
GROUP BY 
T1.CUS_ID
 
Plan hash value: 4291467870
 
--------------------------------------------------------------------------------------------------------------------
| Id  | Operation                      | Name           | Starts | E-Rows | A-Rows |   A-Time   | Buffers | Reads  |
--------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT               |                |      1 |        |      6 |00:00:00.13 |   12384 |     88 |
|   1 |  SORT GROUP BY NOSORT          |                |      1 |     60 |      6 |00:00:00.13 |   12384 |     88 |
|   2 |   NESTED LOOPS                 |                |      1 |        |   9000 |00:00:00.02 |   12384 |     88 |
|   3 |    NESTED LOOPS                |                |      1 |   6149 |  12000 |00:00:00.02 |     384 |     88 |
|   4 |     TABLE ACCESS BY INDEX ROWID| T_ORD_JOIN     |      1 |   9121 |  12000 |00:00:00.01 |     380 |     88 |
|*  5 |      INDEX SKIP SCAN           | X_T_ORD_JOIN_2 |      1 |   9121 |  12000 |00:00:00.01 |     282 |      6 |
|*  6 |     INDEX UNIQUE SCAN          | PK_M_CUS       |  12000 |      1 |  12000 |00:00:00.01 |       4 |      0 |
|*  7 |    TABLE ACCESS BY INDEX ROWID | M_CUS          |  12000 |      1 |   9000 |00:00:00.01 |   12000 |      0 |
--------------------------------------------------------------------------------------------------------------------
 
Predicate Information (identified by operation id):
---------------------------------------------------
 
"   5 - access(""T2"".""ORD_YMD""='20170218')"
"       filter(""T2"".""ORD_YMD""='20170218')"
"   6 - access(""T1"".""CUS_ID""=""T2"".""CUS_ID"")"
"   7 - filter(""T1"".""CUS_GD""='A')"

INDEX SKIP SCAN : 인덱스를 이용해 데이터를 검색하는 방법 중 하나

[참고] 인덱스 스캔 종류

INDEX RANGE SCAN : INDEX 값이 중복을 허용할 때, 2번 이상 범위를 탐색하는 방법
INDEX UNIQUE SCAN : INDEX 값이 하나만 존재할때 한번만 조회하는 방법
INDEX SKIP SCAN : 인덱스의 첫 컬럼이 WHERE에 있지 않아도, 인덱스를 이용할 수 있는 방법
INDEX FULL SCAN : SELECT 컬럼이 모두 인덱스로 있을 때
INDEX FAST FULL SCAN : FULL SCAN에서 싱글I/O 방식이 아닌 멀티블록I/O 방식으로 조회
INDEX MERGE SCAN : 여러 개의 인덱스 사용
INDEX BITMAP MERGE SCAN : 여러 개의 인덱스를 비트맵 인덱스로 변환해서 스캔 (INDEX MERGE SCAN 보다 우수한 성능)

후행 집합의 접근 횟수

M_CUS - CUS_GD = ‘A’ 인 데이터 → 60건
T_ORD_JOIN - ORD_YMD = ‘20170218’ 인 데이터 → 12,000건

⇒ M_CUS를 선행으로 하면 T_ORD_JOIN에 60번만 접근 하는거임

"SQL_ID  d0mkxka3juy5t, child number 0"
-------------------------------------
SELECT  /*+ GATHER_PLAN_STATISTICS LEADING(T1) USE_NL(T2) INDEX(T2 
"X_T_ORD_JOIN_2) */    T1.CUS_ID ,MAX(T1.CUS_NM) CUS_NM ,MAX(T1.CUS_GD) "
"CUS_GD ,COUNT(*) ORD_CNT    ,SUM(T2.ORD_QTY * T2.UNT_PRC) ORD_AMT  "
"FROM    M_CUS T1    ,T_ORD_JOIN T2  WHERE   T1.CUS_ID = T2.CUS_ID  "
AND     T2.ORD_YMD = '20170218'  AND     T1.CUS_GD = 'A'  GROUP BY 
T1.CUS_ID
 
Plan hash value: 222718093
 
-----------------------------------------------------------------------------------------------------------
| Id  | Operation                      | Name           | Starts | E-Rows | A-Rows |   A-Time   | Buffers |
-----------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT               |                |      1 |        |      6 |00:00:00.01 |     235 |
|   1 |  SORT GROUP BY NOSORT          |                |      1 |     60 |      6 |00:00:00.01 |     235 |
|   2 |   NESTED LOOPS                 |                |      1 |        |   9000 |00:00:00.01 |     235 |
|   3 |    NESTED LOOPS                |                |      1 |   6149 |   9000 |00:00:00.01 |     161 |
|*  4 |     TABLE ACCESS BY INDEX ROWID| M_CUS          |      1 |     60 |     60 |00:00:00.01 |       3 |
|   5 |      INDEX FULL SCAN           | PK_M_CUS       |      1 |     90 |     90 |00:00:00.01 |       1 |
|*  6 |     INDEX RANGE SCAN           | X_T_ORD_JOIN_2 |     60 |    101 |   9000 |00:00:00.01 |     158 |
|   7 |    TABLE ACCESS BY INDEX ROWID | T_ORD_JOIN     |   9000 |    102 |   9000 |00:00:00.01 |      74 |
-----------------------------------------------------------------------------------------------------------
 
Predicate Information (identified by operation id):
---------------------------------------------------
 
"   4 - filter(""T1"".""CUS_GD""='A')"
"   6 - access(""T1"".""CUS_ID""=""T2"".""CUS_ID"" AND ""T2"".""ORD_YMD""='20170218')"
 

⇒ 6번에서 Starts가 60이 됨

결론 : 조인 횟수를 줄이고, 선행집합 선정에 카운트 하는 방식을 사용하면 좋다.

선행집합을 데이터 적은걸 잡아야 한다.

7.3 MERGE 조인과 성능

7.3.1 대량의 데이터 처리

: 머지 조인은 대량의 데이터를 조인할 때 적합

T_ORD_BIG 테이블을 NL 조인

"SQL_ID  a319wbtvb7mzx, child number 0"
-------------------------------------
SELECT  /*+ GATHER_PLAN_STATISTICS LEADING(T2) USE_NL(T1) FULL(T2) */  
"  T1.CUS_ID ,MAX(T1.CUS_NM) CUS_NM ,MAX(T1.CUS_GD) CUS_GD ,COUNT(*) "
"ORD_CNT    ,SUM(T2.ORD_AMT) ORD_AMT    ,SUM(SUM(T2.ORD_AMT)) OVER() "
"TTL_ORD_AMT  FROM    M_CUS T1    ,T_ORD_BIG T2  WHERE   T1.CUS_ID = "
T2.CUS_ID  AND     T2.ORD_YMD LIKE '201702%'  GROUP BY T1.CUS_ID
 
Plan hash value: 4214578612
 
------------------------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                      | Name      | Starts | E-Rows | A-Rows |   A-Time   | Buffers | Reads  |  OMem |  1Mem | Used-Mem |
------------------------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT               |           |      1 |        |     72 |00:00:18.71 |    2238K|    258K|       |       |          |
|   1 |  WINDOW BUFFER                 |           |      1 |     90 |     72 |00:00:18.71 |    2238K|    258K|  6144 |  6144 | 6144  (0)|
|   2 |   HASH GROUP BY                |           |      1 |     90 |     72 |00:00:18.71 |    2238K|    258K|   726K|   726K| 4980K (0)|
|   3 |    NESTED LOOPS                |           |      1 |        |   1980K|00:00:36.74 |    2238K|    258K|       |       |          |
|   4 |     NESTED LOOPS               |           |      1 |   1799K|   1980K|00:00:35.40 |     258K|    258K|       |       |          |
|*  5 |      TABLE ACCESS FULL         | T_ORD_BIG |      1 |   1799K|   1980K|00:00:34.01 |     258K|    258K|       |       |          |
|*  6 |      INDEX UNIQUE SCAN         | PK_M_CUS  |   1980K|      1 |   1980K|00:00:00.78 |       4 |      0 |       |       |          |
|   7 |     TABLE ACCESS BY INDEX ROWID| M_CUS     |   1980K|      1 |   1980K|00:00:00.82 |    1980K|      0 |       |       |          |
------------------------------------------------------------------------------------------------------------------------------------------
 
Predicate Information (identified by operation id):
---------------------------------------------------
 
"   5 - filter(""T2"".""ORD_YMD"" LIKE '201702%')"
"   6 - access(""T1"".""CUS_ID""=""T2"".""CUS_ID"")"
 

Buffers가 2238K 나옴

T_ORD_BIG, 머지조인 사용

"SQL_ID  4h89vg74pgwh3, child number 0"
-------------------------------------
SELECT  /*+ GATHER_PLAN_STATISTICS LEADING(T1) USE_MERGE(T2) FULL(T2) 
"*/    T1.CUS_ID ,MAX(T1.CUS_NM) CUS_NM ,MAX(T1.CUS_GD) CUS_GD "
",COUNT(*) ORD_CNT    ,SUM(T2.ORD_AMT) ORD_AMT    "
",SUM(SUM(T2.ORD_AMT)) OVER() TTL_ORD_AMT  FROM    M_CUS T1    "
",T_ORD_BIG T2  WHERE   T1.CUS_ID = T2.CUS_ID  AND     T2.ORD_YMD LIKE "
'201702%'  GROUP BY T1.CUS_ID
 
Plan hash value: 3683264484
 
------------------------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                      | Name      | Starts | E-Rows | A-Rows |   A-Time   | Buffers | Reads  |  OMem |  1Mem | Used-Mem |
------------------------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT               |           |      1 |        |     72 |00:00:19.77 |     258K|    258K|       |       |          |
|   1 |  WINDOW BUFFER                 |           |      1 |     90 |     72 |00:00:19.77 |     258K|    258K|  6144 |  6144 | 6144  (0)|
|   2 |   SORT GROUP BY NOSORT         |           |      1 |     90 |     72 |00:00:19.82 |     258K|    258K|       |       |          |
|   3 |    MERGE JOIN                  |           |      1 |   1799K|   1980K|00:00:19.76 |     258K|    258K|       |       |          |
|   4 |     TABLE ACCESS BY INDEX ROWID| M_CUS     |      1 |     90 |     90 |00:00:00.01 |       3 |      0 |       |       |          |
|   5 |      INDEX FULL SCAN           | PK_M_CUS  |      1 |     90 |     90 |00:00:00.01 |       1 |      0 |       |       |          |
|*  6 |     SORT JOIN                  |           |     90 |   1799K|   1980K|00:00:19.60 |     258K|    258K|    66M|  2819K|   58M (0)|
|*  7 |      TABLE ACCESS FULL         | T_ORD_BIG |      1 |   1799K|   1980K|00:00:37.05 |     258K|    258K|       |       |          |
------------------------------------------------------------------------------------------------------------------------------------------
 
Predicate Information (identified by operation id):
---------------------------------------------------
 
"   6 - access(""T1"".""CUS_ID""=""T2"".""CUS_ID"")"
"       filter(""T1"".""CUS_ID""=""T2"".""CUS_ID"")"
"   7 - filter(""T2"".""ORD_YMD"" LIKE '201702%')"
 

Buffers가 258K로 개선됨
처리순서
- 5 : PK_M_CUS 를 INDEX FULL SCAN으로 읽음 (CUS_ID 순서대로)
- 4 : ROWID를 이용해 M_CUS에 접근
- 7 : T_ORD_BIG 을 FULL SCAN 하는데 filter 조건
  - 가져온걸 cus_id 순서로 정렬
- M_CUS와 T_ORD_BIG을 머지
NL 조인과 차이점 : 후행 테이블을 반복해서 다시 읽지 않는다.

7.4 HASH 조인과 성능

7.4.1 대량의 데이터 처리

머지 조인의 단점 : 데이터를 정렬해야하는 부담

→ 해쉬 조인은 이 단점을 해결함
해쉬 조인의 단점 : CPU와 메모리에 추가적인 부하가 발생한다.

"SQL_ID  7tdby1j1tk4r0, child number 0"
-------------------------------------
SELECT  /*+ GATHER_PLAN_STATISTICS LEADING(T1) USE_MERGE(T2) */    
"T1.CUS_ID ,MAX(T1.CUS_NM) CUS_NM ,MAX(T1.CUS_GD) CUS_GD    ,COUNT(*) "
"ORD_CNT ,SUM(T2.ORD_AMT) ORD_AMT ,SUM(SUM(T2.ORD_AMT)) OVER() "
"TTL_ORD_AMT  FROM    M_CUS T1    ,T_ORD_BIG T2  WHERE   T1.CUS_ID = "
T2.CUS_ID  GROUP BY T1.CUS_ID
 
Plan hash value: 3683264484
 
-------------------------------------------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                      | Name      | Starts | E-Rows | A-Rows |   A-Time   | Buffers | Reads  | Writes |  OMem |  1Mem | Used-Mem | Used-Tmp|
-------------------------------------------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT               |           |      1 |        |     90 |00:01:13.87 |     258K|    404K|    146K|       |       |          |         |
|   1 |  WINDOW BUFFER                 |           |      1 |     90 |     90 |00:01:13.87 |     258K|    404K|    146K|  6144 |  6144 | 6144  (0)|         |
|   2 |   SORT GROUP BY NOSORT         |           |      1 |     90 |     90 |00:01:15.23 |     258K|    404K|    146K|       |       |          |         |
|   3 |    MERGE JOIN                  |           |      1 |     30M|     30M|00:01:01.44 |     258K|    404K|    146K|       |       |          |         |
|   4 |     TABLE ACCESS BY INDEX ROWID| M_CUS     |      1 |     90 |     90 |00:00:00.01 |       3 |      0 |      0 |       |       |          |         |
|   5 |      INDEX FULL SCAN           | PK_M_CUS  |      1 |     90 |     90 |00:00:00.01 |       1 |      0 |      0 |       |       |          |         |
|*  6 |     SORT JOIN                  |           |     90 |     30M|     30M|00:00:57.98 |     258K|    404K|    146K|   644M|  8328K|  171M (1)|     576K|
|   7 |      TABLE ACCESS FULL         | T_ORD_BIG |      1 |     30M|     30M|00:00:14.15 |     258K|    258K|      0 |       |       |          |         |
-------------------------------------------------------------------------------------------------------------------------------------------------------------
 
Predicate Information (identified by operation id):
---------------------------------------------------
 
"   6 - access(""T1"".""CUS_ID""=""T2"".""CUS_ID"")"
"       filter(""T1"".""CUS_ID""=""T2"".""CUS_ID"")"
 

7.4.2 빌드 입력 선택의 중요성

해시 조인 성능 향상을 위해 선행 집합 선택이 중요함
선행 집합 → 빌드 입력(Build-Input)으로 처리
- 조인할 대상에 해시함수를 적용해 조인 준비
- 빌드 입력 데이터가 적을수록 성능에 유리
후행 집합 → 검증 입력(Probe-Input)으로 처리
- 후행 집합에 해시 함수를 적용해 빌드 입력과 비교해 조인을 처리

7.4.4 어떤 조인을 사용할 것인가?

NL 조인 : 자주 실행되는 SQL은 NL 조인만으로 처리하는 것이 데이터베이스 전체 성능에 도움됨
해시 조인 : 대량의 데이터를 조회해서 분석을 수행해야하는 상황
머지 조인 : 잘 사용 안함
어떤 조인을 할지는 옵티마이저가 결정함. 인덱스를 잘 구성하여 옵티마이저가 적절한 인덱스를 선택하도록 하자

# SQL # Oracle

[SQL] SQL Booster - JOIN과 성능

목차

7. JOIN과 성능

7.1 조인의 내부적인 처리 방식

7.1.1 조인의 3가지 처리 방식

7.1.2 NESTED LOOPS JOIN

7.1.3 MERGE JOIN

7.1.4 HASH JOIN

7.2 NL 조인과 성능

7.2.2 후행 집합에 필요한 인덱스

7.2.3 선행 집합 변경에 따른 쿼리 변형

7.2.4 조인 횟수를 줄이자#1

7.3 MERGE 조인과 성능

7.3.1 대량의 데이터 처리

7.4 HASH 조인과 성능

7.4.1 대량의 데이터 처리

7.4.2 빌드 입력 선택의 중요성

7.4.4 어떤 조인을 사용할 것인가?

다른 글

[김영한의 실전 자바] 중급 1편 - 래퍼 클래스

[김영한의 실전 자바] 중급 1편 - String 클래스

[김영한의 실전 자바] 중급 1편 - 불변 객체

[VS Code] 자주 쓰는 단축키 (윈도우)