study:oracle:datadb:3week

3.2.2. 데이터 연결을 위한 실행계획

3.2.2.1. 내포 조인(Nested loops Join)
1. 가장 고전적 형태의 조인방식이나 현실적으로 가장 많이 적용
2. SINGLE BLOCK I/O
3. 전처리 집합의 처리범위가 전체 일량을 좌우
4. 다량의 랜덤 액세스 발생
5. 따라서 소량의 액세스는 유용, 다량의 액세스는 큰 부하 발생
6. NESTED LOOPS JOIN = 내포조인 = 중첩루프조인
7. 진보된 내포조인(Advanced Nested Loop Join)
  1. 클러스터링 팩터가 좋다면, 많은 부분을 한번의 블록 액세스에서 연결가능
  2. 물리적, 논리적 블록 액세스 량이 감소 → 효율성 증대
  3. DBMS 또는 버전별로 차등 지원

3.2.2.2. 정렬 병합 조인(Sort Merge Join)
1. NESTED LOOPS JOIN의 문제점을 해결하기 위한 방안
  1. NESTED LOOPS JOIN의 문제점
  2. 조인 대상 범위가 넓을 경우 랜덤 액세스 발생
  3. 연결고리 이상으로 인한 다량의 액세스 발생
2. SORT MERGE JOIN의 특징
  1. 랜덤액세스를 하지 않고 스캔으로 수행
  2. 최소한 두 개의 집합의 연결 고리는 필요
  3. 정렬을 대신 할수 있는 인덱스가 존재할 경우 부하 감소
  4. 연결고리에 인덱스 유무가 문제되지 않음
  5. SORT 부하 발생
    1. SORT AREA SIZE에 따른 효율성 차이 발생
  6. 해쉬조인에 비해
    1. 이미 정렬된 집합이 존재
    2. 인덱스를 활용한 정렬 대체가 가능할 경우 높은 효율성 보장 가능
  7. Nested Loop 조인에 비해
    1. =(EQUAL)이 아닌 (LIKE, BETWEEN, <. ⇐, >, >일 경우 유리할 수 있음
    2. 선행 집합 개념이 없음으로 선행처리 개념이 불필요
    3. 각 집합이 독립적인 처리 수행

3.2.2.3. 해쉬 조인(Hash Join)
1. 해쉬 함수 기법을 적용하여 조인 수행
2. 조인 기법의 진화
  1. NESTED LOOPS JOIN : 랜덤 액세스 부하 발생
  2. SORT MERGE JOIN : 데이터량의 증가에 따른 정렬 부하 발생
  3. HASH JOIN : 랜덤 액세스와 정렬의 부하 감소
3. 해쉬 함수의 특징
  1. 대량 범위에 대한 조인에 유리
  2. 연결 대상을 특정 지역(PARTITION) 구분
  3. PARTIION : 동일 해쉬 값을 보유한 데이터들을 모아둔 공간
  4. PARITION PAIR : 조인 대상에 대한 연결
  5. IN MEMORY HASH가 가능한 경우 빠른 수행속도 보장
    1. (한쪽 집합이 해쉬영역보다 작을 경우)
  6. 연산자의 제약 : ‘=‘(EQUAL) 조인만 가능

3.2.2.4. 세미 조인(Semi Join)
1. 세미조인이란?
  1. 서브쿼리가 다양한 연산자에 의해 메인 쿼리와 연결되는 경우(廣義의 의미)
2. 조인과 서브쿼리의 비교
  1. 조인 : 조인되는 집합간 수행 순서에 관계없이 논리적으로는 수평적 관계
  2. 서브쿼리 : 메인쿼리의 집합의 변형이 없는 수직적(종속적)관계
3. 메인쿼리와 서브쿼리간의 관계 차수에 따른 실행계획
  1. 메인쿼리 : 서브쿼리 = M : 1의 관계?
  2. 옵티마이져는 조인과 동일한 실행계획 수립
  3. 메인쿼리 : 서브쿼리 = 1 : M의 관계?
    1. 옵티마이져는 서브쿼리를 1의 집합으로 만들기 위한 중간 처리 수행
    2. 제공자 역할의 서브쿼리일 경우 SORT(UNIQUE) 처리
    3. 확인자 역할의 서브쿼리일 경우 FILTER 형 처리
4. 힌트의 사용
  1. ORACLE 10G 이전
    1. MERGE_SJ, MERGE_AJ, HASH_SJ, HASH_AJ을 사용
  2. ORACLE 10G 이후
    1. 서브쿼리에 USE_HASH, USE_MERGE

3.2.2.5. 카티젼 조인(Cartesian Join)
1. 조인이 되는 두 개의 집합간에 연결고리 조건이 전혀 없는 경우
2. M:M 조인을 의미
3. 실행계획의 특징
  1. SORT MERGE JOIN만이 CARTESIAN 실행계획 명기
  2. 타 JOIN의 경우 정상적인 조인의 실행계획으로 표기, 단 결과상 확인 가능
4. 카티젼 조인(CARTESIAN JOIN)의 발생 원인
  1. 쿼리 생성자의 실수
  2. 사용자가 특별한 목적하에 고의적인 카티젼 조인 발생
  3. 3개 이상의 집합 조인시 조인 순서의 잘못으로 발생

3.2.2.6. 아우터 조인(Outer Join)
1. 아우터 조인(OUTER JOIN)
  1. 외측 루프는 언제나 보존되고, 내측 루프는 선택적인 조인
2. 아우터 조인(Outer Join)의 종류
  1. NESTED LOOPS 아우터 조인
    1. 주의사항
      1. OUTER LOOP : 기준/보존 집합
      2. INNER LOOP : OUTER LOOP의 결과 집합 영향을 주지 않도록 수행
    2. USE_NL(TABLE1, TABLE2)
  2. NESTED LOOPS(OUTER)
3. 해쉬 아우터 조인
  1. Hash Function을 이용해서 메모리와 CPU를 많이 사용해서 일반적으로 배치작업에서 주로 사용됨
    1. /*+ use_hash(테이블) */
    2. 적은테이블과 큰테이블의 조인시에 유리
    3. Equal 조인에서만 가능
    4. Driving Table에 인덱스를 필요로 하지 않고 각 테이블을 한번만 읽음
    5. 다른조인방법보다 CPU자원을 많이 소비하며 양쪽 테이블의 scan이 동시에 일어남
4. SORT MERGE 아우터 조인
  1. 선택 조건
    1. NESTED LOOPS 조인으로는 부담이 되는 대량의 데이터 또는 인덱스 문제가 있는 경우
    2. 조건 연산자로 인해 HASH 조인이 불가능 할 경우
    3. 이미 다른 처리에 의해 조인을 위한 정렬이 선행되어 있는 경우
5. 전체 아우터 조인
  1. 조인에 참여하는 양 집합이 모두 기준집합이며 대응집합일 경우
  2. 수행방식
    1. 한 쪽을 기준으로 아우터 조인을 수행한 결과
    2. 다른 쪽을 기준으로 부정형 조인을 할 결과
    3. 위 두 집합을 UNION ALL

3.2.2.7. 인덱스 조인
1. 인덱스 조인
  1. 테이블 엑세스 없이 하나 이상의 인덱스들을 결합하여 쿼리를 수행
2. 인덱스 조인의 특징
  1. 사용된 모든 컬럼이 어떤 인덱스에라도 존재
  2. 비교 연산자가 ‘=’이 아니어도 인덱스 조인에 참여 가능
  3. 반드시 인덱스의 선두 컬럼이 아니어도 인덱스 조인에 참여 가능
  4. 조건절을 기준으로 인덱스 조인을 결정
  5. 인덱스 조인이 논리적으로 가능하다고 해서 항상 발생되는 것은 아님
  6. 실행계획상에는 해쉬 조인으로 수행되는 것으로 보임