행위

SQL 조인

DB CAFE

thumb_up 추천메뉴 바로가기

1 SQL 조인[편집]

1.1 Nested Loop 조인[편집]

1.1.1 Nested Loop Join 의 개념[편집]

assignment NL 조인의 개념
  • - 중첩(Nested) + 반복(Loop) + 연결(Join)
  1. 두개 이상의 테이블에서, 하나의 집합을 기준으로 순차적으로 상대방 테이블의 row 를 결합하여 원하는 결과를 추출하는 테이블 연결 방식
  2. 결합하기 위해 기준이 되는 테이블(선행) : driving 테이블( OUTER 테이블, 즉 바깥쪽 테이블)
  3. 결합되어지는 테이블(후행) : driven 테이블(INNER 테이블, 즉 안쪽 테이블)
  4. NL 조인에서는 드라이빙 테이블의 각 row 에 대하여 loop 방식으로 조인이 되는데 드라이빙 테이블의 집합을 어느정도 줄일 수 있는가에 따라 NL 조인의 성능이 결정됨.



  • 예시) USE_NL(각각 테이블에 어떤 컬럼에 인덱스를 이용 할것 인가? )
SELECT 고객.* ,주문.*
  FROM 고객 -- 1) [고객]테이블은 어떤 컬럼에 인덱스가 있으면 좋은가?
  JOIN 주문 -- 2) [주문]테이블은 어떤 컬럼에 인덱스가 있으면 좋은가?
    ON 주문.고객번호 = 고객.고객번호
 WHERE 고객.고객명='홍길동'
   AND 주문.주문일자='201909';
USE_NL 조인
  • 고객(outer)은 WHERE절의 = 조건(고객명) 인덱스 여부,
  • 주문(inner)은 고객번호 컬럼 인덱스 여부가 N/L조인의 성능을 결정.


1.1.2 NL조인 시 인덱스 의 중요성[편집]

assignment * 인덱스의 중요성
  1. outer(선행,driving ) 테이블이 한 row 씩 반복해 가면서 inner(후행,driven ) 테이블로 조인이 이루어짐
  2. inner(후행) 테이블의 컬럼은 outer(선행) 테이블의 컬럼을 받아서 데이터를 빨리 찾기하기 위해서는 인덱스가 반드시 있어야함.(성능 향상)
  3. inner 테이블의 크기가 적다면 테이블 전체를 메모리에 읽어서 반복적으로 검색하는 것이 빠름
  4. 조인되는 값들의 카디널리티(cardinality) 가 높을 수록, 한 번 스캔되어 조인된 자료가 다음 row 에서 조인에 사용될 확률이 낮아지기 때문에 스캔에 의한 조인 효율은 저하
  • 원하는 값이 존재하는 지 빠르게 확인하기 위한 목적과 그 값에 대한 데이터를 빠르게 읽어 내기 위해서 인덱스 오브젝트는 N/L 조인에서 (특히 inner 테이블의 액세스 시) 반드시 필요



1.1.3 인덱스 오브젝트 장점[편집]

record_voice_over

  • 인덱스 오브젝트의 장점
  1. 단일 칼럼을 조인의 연결고리로 사용할 경우 전체 테이블 row size 가 100바이트이고 인덱스 칼럼의 크기가 10바이트라면, 전체 테이블을 검색하는 것과 인덱스를 검색하는 것은 절대적인 양으로도 10배 차이가 발생
  2. 인덱스는 정렬되어 있기 때문에 검색 알고리즘을 적용할 수도 있으며, 한번 읽어진 인덱스 블록들은 buffer cache 에 어느 정도는 남겨져 있기 때문에 반복적인 I/O 양은 최소화


c20c_ja3.gif


1.1.4 인덱스 조건[편집]

record_voice_over

  1. outer 테이블은 WHERE절의 = 조건의 인덱스가 중요
  2. inner 테이블은 조인조건 컬럼의 인덱스가 중요
  3. outer 테이블 조회 후 1건씩 순차적으로 inner 테이블에 접근
  • INDEX 구성
  • EMP.IX_EMP_01 ( DEPTNO)
  • DEPT.IX_DEPT_01 ( DEPTNO))


SELECT /*+ USE_NL(B) LEADING(A) */ ENAME,JOB,B.DNAME   
  FROM EMP A   
  JOIN DEPT B     
    ON A.DEPTNO = B.DEPTNO
 
-------------------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                    | Name       | Starts | E-Rows |E-Bytes| Cost (%CPU)| E-Time   | A-Rows |   A-Time   | Buffers |
-------------------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT             |            |      1 |        |       |    17 (100)|          |     14 |00:00:00.01 |      11 |
|   1 |  NESTED LOOPS                |            |      1 |     14 |   672 |    17   (0)| 00:00:01 |     14 |00:00:00.01 |      11 |
|   2 |   NESTED LOOPS               |            |      1 |     14 |   672 |    17   (0)| 00:00:01 |     14 |00:00:00.01 |      10 |
|   3 |    TABLE ACCESS FULL         | EMP        |      1 |     14 |   364 |     3   (0)| 00:00:01 |     14 |00:00:00.01 |       7 |
|*  4 |    INDEX RANGE SCAN          | IX_DEPT_01 |     14 |      1 |       |     0   (0)|          |     14 |00:00:00.01 |       3 |
|   5 |   TABLE ACCESS BY INDEX ROWID| DEPT       |     14 |      1 |    22 |     1   (0)| 00:00:01 |     14 |00:00:00.01 |       1 |
-------------------------------------------------------------------------------------------------------------------------------------

1.1.5 LEADING 이나 ORDERED 힌트와 같이 사용 추천[편집]

  • INDEX 구성
  • EMP.IX_EMP_01 ( DEPTNO)
  • DEPT.IX_DEPT_01 ( DEPTNO))
SELECT /*+ USE_NL(A,B) LEADING(A) */ ENAME,JOB,B.DNAME   
  FROM EMP A   JOIN DEPT B     ON A.DEPTNO = B.DEPTNO
 
-------------------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                    | Name       | Starts | E-Rows |E-Bytes| Cost (%CPU)| E-Time   | A-Rows |   A-Time   | Buffers |
-------------------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT             |            |      1 |        |       |    17 (100)|          |     14 |00:00:00.01 |      11 |
|   1 |  NESTED LOOPS                |            |      1 |     14 |   672 |    17   (0)| 00:00:01 |     14 |00:00:00.01 |      11 |
|   2 |   NESTED LOOPS               |            |      1 |     14 |   672 |    17   (0)| 00:00:01 |     14 |00:00:00.01 |      10 |
|   3 |    TABLE ACCESS FULL         | EMP        |      1 |     14 |   364 |     3   (0)| 00:00:01 |     14 |00:00:00.01 |       7 |
|*  4 |    INDEX RANGE SCAN          | IX_DEPT_01 |     14 |      1 |       |     0   (0)|          |     14 |00:00:00.01 |       3 |
|   5 |   TABLE ACCESS BY INDEX ROWID| DEPT       |     14 |      1 |    22 |     1   (0)| 00:00:01 |     14 |00:00:00.01 |       1 |
-------------------------------------------------------------------------------------------------------------------------------------

1.1.6 USE_NL 괄호 안의 테이블은 NL조인 적용 대상 테이블[편집]

SELECT /*+ USE_NL(A,B) LEADING(B) */ ENAME,JOB,B.DNAME   
  FROM EMP A   
  JOIN DEPT B     ON A.DEPTNO = B.DEPTNO
 

------------------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                    | Name      | Starts | E-Rows |E-Bytes| Cost (%CPU)| E-Time   | A-Rows |   A-Time   | Buffers |
------------------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT             |           |      1 |        |       |     6 (100)|          |     14 |00:00:00.01 |      10 |
|   1 |  NESTED LOOPS                |           |      1 |     14 |   672 |     6   (0)| 00:00:01 |     14 |00:00:00.01 |      10 |
|   2 |   NESTED LOOPS               |           |      1 |     20 |   672 |     6   (0)| 00:00:01 |     14 |00:00:00.01 |       9 |
|   3 |    TABLE ACCESS FULL         | DEPT      |      1 |      4 |    88 |     3   (0)| 00:00:01 |      4 |00:00:00.01 |       7 |
|*  4 |    INDEX RANGE SCAN          | IX_EMP_01 |      4 |      5 |       |     0   (0)|          |     14 |00:00:00.01 |       2 |
|   5 |   TABLE ACCESS BY INDEX ROWID| EMP       |     14 |      4 |   104 |     1   (0)| 00:00:01 |     14 |00:00:00.01 |       1 |
------------------------------------------------------------------------------------------------------------------------------------

1.2 HASH 조인(HASH JOIN)[편집]

해시 함수를 이용하여 조직 테이블(데이터 작은쪽)에 해시테이블을 만들고 실적집계 테이블(데이터 많은쪽)을 탐색하면서 조인.

SQL_361.jpg

c20c_ja2.gif

  • Hash Join에서는 작은 테이블을 먼저 접근하는 것이 성능이 좋음(해시테이블을 빨리 build 하기때문).
  이후 큰 테이블을 접근하여 해시 함수를 사용해서 순차적으로 해시 테이블로 탐색 한다.
  • 대량 데이터를 처리하는 배치성 프로그램에 적합한 조인 방식.

1.3 HASH 조인의 특징[편집]

  • Hash Join 성능을 좌우하는 두 가지 포인트
  • 한 쪽 테이블이 가용 메모리에 담길 정도로 충분히 작아야 함
  • Build Input 해시 키 칼럼에 중복 값이 거의 없어야 함 (조인컬럼의 값이 중복이 많을 수록 해시키 충돌에 의해 성능은 저하된다.)
  • Inner루프로 Hash Area에 생성해둔 해시테이블을 이용한다는 것 외에 NL조인과 유사하다.
  • 해시테이블 만들 때(Build Input)는 전체범위처리가 불가피하나, Probe Input을 스캔하는 단계는 부분범위 처리가능하다.
  • 해시조인은 해시테이블이 PGA영역에 할당 되므로, NL조인보다 빠르다.
  • NL조인은 Outer테이블에서 읽히는 레코드마다 Inner쪽 테이블 버퍼캐시 탐색을 위해 래치획득을 반복하나, 해시조인은 래치 획득과정없이 PGA에서 빠르게 데이터를 탐색할 수 있다.

1.3.1 HASH 조인 장점[편집]

  • NL조인처럼 조인시 발생하는 Random엑세스 부하가 없다.
  • 소트머지조인처럼 조인전에 양쪽 집합을 정렬해야 하는 부담이 없다.

1.3.2 HASH 조인 단점[편집]

  • 해시테이블을 생성하는 비용이 수반
  • Build Input이 작을 때 효과적이다.(PGA에 할당되는 Hash Area에 담길정도로 충분히 작아야 함)
  • 해시키 값으로 사용되는 컬럼에 중복값이 거의 없을 경우 효과적이다.(중복값이 많을수록 성능이 저하된다.)

1.3.3 HASH 조인을 고려해야 할때[편집]

  • 조인 칼럼에 적당한 인덱스가 없어 NL Join이 비효율적일 때
  • 조인 칼럼에 인덱스가 있더라도 NL Join 드라이빙 집합에서 Inner 쪽 집합으로의 조인 액세스량이 많아 Random 액세스 부하가 심할 때
  • Sort Merge Join 하기에는 두 테이블이 너무 커 소트 부하가 심할 때
  • 수행빈도가 낮고 조인할 때

1.4 HASH JOIN 예시[편집]

SELECT /*+ USE_HASH(B) */
       A.조직명,A.조직코드,SUM(B.실적),...
  FROM 조직 A
     , 실적집계 B
 WHERE A.조직코드 = B.조직코드.  -- hash 함수를 사용 하여 해시키 생성 후 조인
   AND A.사업부   = 'ㅇ사업부서명'. -- index를 사용 하여 성능 개선
   AND B.집계년월 = '201908'  -- index를 사용 하여 성능 개선     
 GROUP BY A.조직명,A.조직코드

(설명)

1. 조직 테이블(A)에서 사업부가 ‘ㅇ사업부서명’조회 후, 조인 컬럼인 조직코드를 해시 함수로 분류한 다음, 해시테이블 을 생성. (Build Input , 해시 함수를 이용해 해시 테이블 구성)

2. 실적집계 테이블(B) 에서 집계년월이 ‘201908’를 조회 후, 조인 컬럼인 조직코드를 해시 함수로 변환 후 해시 테이블로 순차 적으로 접근한다. (Probe Input 해시 함수를 통하여 해시 테이블을 탐색)

  • 조회 조건 컬럼인 A.사업부 컬럼과 B.집계년월 컬럼의 인덱스를 사용하여 성능을 개선하고 ,
  • 조인 컬럼인 A.조직코드 = B.조직코드는 인덱스가 존재하더라도 사용되지 않는다.


USE_HASH 조인

2 소트머지 조인 (Sort Merge Join)[편집]

2.1 개요[편집]

  1. 양쪽 테이블의 처리범위를 각자 Access하여 Sort(정렬)한 결과를 차례로 Scan하면서 연결고리의 조건으로 Merge하는 방식
  2. 조인의 대상범위가 넓을 경우 발생하는 Random Access를 줄이기 위한 경우나 연결고리에 마땅한 인덱스가 존재하지 않을 경우 해결하기 위한 조인

2.2 Sort Merge Join의 특징[편집]

  1. 연결을 위해 랜덤 액세스를 하지 않고 스캔을 하면서 수행
  2. Nested Loop Join처럼 선행집합 개념이 없음
  3. 정렬을 위한 영역(Sort Area Size)에 따라 효율에 큰 차이 발생
  4. 조인 연산자가 '='이 아닌 경우 nested loop 조인보다 유리한 경우가 많음

2.3 Sort Merge 사용 시 주의사항[편집]

  1. 두 결과집합의 크기가 차이가 많이 나는 경우에는 비효율적
  2. Sorting 메모리에 위치하는 대상은 join key뿐만 아니라 Select list도 포함되므로 불필요한 select 항목 제거

3 조인시 성능 튜닝 요소[편집]

3.1 NL조인[편집]

  1. NL조인은 Random 액세스 발생량에 의해 성능 결정
  2. WHERE 조건이 없는 경우에는 드라이빙테이블(OUTER)은 작은 집합을 드라이빙하는 것이 유리.
  3. WHERE 조건이 있는 경우에는 WHERE 조건절 컬럼의 인덱스 존재 및 구성여부에 따라 성능이 결정되며, 작은 쪽 집합을 드라이빙하는 것이 유리.
  4. INNER쪽 테이블의 조인 컬럼에 인덱스 존재 여부가 성능에 결정적인 요소.
  5. NL조인에서는 Random 액세스 발생량 외에도 Inner 쪽 인덱스 구성 및 조건절 연산자 형태에 따라 성능이 크게 좌우되는데, 인덱스 스캔 효율에 차이가 생기기 때문.

3.1.1 NL조인 Prefetch[편집]

3.1.2 NL조인 Batching[편집]

3.2 해시 조인[편집]

  1. Build Input을 작은 쪽 테이블로 드라이빙하는것이 유리 (SWAP_JOIN_INPUTS 힌트로 조절)
  2. 해시조인은 Hash Area에 Build Input을 모두 채울 수 있느냐가 관건

3.3 소트머지 조인[편집]

  1. 소트머지 조인과 해시조인에서도 순서가 중요
  2. 소트머지 조인은 PGA상에서 정렬된 집합(Sort Area)을 통해 조인 액세스가 일어나기 때문에 Random 액세스 발생량 보다 소트 부하에 의해 성능 결정됨.
  3. 디스크 소트가 발생할 정도의 큰 테이블을 포함할 때는 큰 테이블을 드라이빙하는 것이 빠르지만 메모리 소트방식으로 조인할 때는 적은 쪽 테이블을 드라이빙하는 것이 조금 더 빠름
select /*+ ordered use_merge(e) */ d.deptno, d.dname, e .empno, e .ename 
from dept d, emp e 
where d.deptno = e.deptno

0467_001.jpg

원본 주소 "http://dbcafe.co.kr/wiki/index.php?title=SQL_조인&oldid=10469"