[SQLD] JOIN(Nested-Loop Join, Sort Merge Join, Hash Join) / AutoTrace 실행 계획 / SCAN의 종류

EUN JY·2022년 9월 1일
SQLD
1

Database

목록 보기
16/21

참고 사이트

1. ORACLE JOIN

1-1. JOIN의 방식

  • 조인연산(JOIN OPERATION)
    • 여러 테이블에 저장된 데이터를 한번에 조회할 수 있게 하는 DBMS의 기능
    • 두 집합(테이블) 간의 곱으로 데이터를 연결하는 가장 대표적인 데이터 연결 방법
  • 물리적 조인 : 옵티마이저에 의해 발생하는 조인
  • 논리적인 조인을 옵티마이저가 DBMS 내부에서 물리적인 조인으로 표현
  • Join 작업을 할 때 옵티마이저는 어떤 방식으로 조인을 할지 실행계획을 세우게 됨

1-1-1. Nested-Loop Join

  • 두개의 테이블의 행을 각각 모두 확인하여 조인하는 방법
  • 2개 이상의 테이블에서 하나의 집합을 기준으로 순차적으로 상대방 Row를 결합하여 원하는 결과를 조합하는 방식
  • 선행 테이블의 처리 범위를 하나씩 액세스하면서 추출된 값으로 연결할 테이블을 조인
  • 실행계획에서 먼저 실행되는 테이블이 Driving 테이블, 나중에 실행되는 것이 Driven 테이블
// 중첩된 for문의 원리와 동일함
for (i=0; i<100; i++) {
	for (j=0; j<100; j++) {
    	(생략)
    }
}

Nested-Loop Join 특징

  • 좁은 범위에 처리에 최적화 / 소량 데이터 조인 시 사용
  • 한 레코드씩 순차적으로 처리
  • Random Access 위주의 조인 방식 > 많은 양의 데이터 조인 시 Random Access 증가
  • 후행 테이블(Driven)에는 조인을 위한 인덱스 생성 필요
  • 실행속도 = 선행 테이블 사이즈 * 후행 테이블 접근횟수

Nested-Loop Join 주의사항

  • 데이터를 Random Access > 결과 집합이 많으면 느려짐
  • 조인 컬럼에 Index가 있어야 테이블 전체를 탐색하지 않고 필요한 행에 대해서만 탐색하여 효율적
  • Row 수가 적은 테이블을 Driving 테이블로 선정해야 처리 속도 향상
  • 테이블 중 적은 쪽을 Driven 테이블로 설정

1-1-2. Sort Merge Join

  • 조인의 대상범위가 넓을 경우 발생하는 Random Access를 줄이기 위한 경우나 연결고리에 마땅한 Index가 존재하지 않을 경우 해결하기 위한 조인 방안
  • 양쪽 테이블의 처리범위를 각자 Access하여 정렬한 결과를 차례로 Scan하면서 연결고리의 조건으로 Merge하는 방식
  • 두 개의 테이블을 조인 칼럼으로 정렬하여 조인

Sort Merge Join 특징

  • 연결을 위해 Random Access를 하지 않고 스캔을 하면서 수행
  • Nested Loop Join처럼 선행집합 개념이 없음
  • 정렬을 위한 영역(Sort Area Size)에 따라 효율에 큰 차이 발생
  • 조인 연산자가 '='이 아닌 경우 Nested Loop Join 보다 유리
  • Outer 집합이 정렬되어 있는 경우, Non equi join을 사용하는 경우 유리
  • 조인되는 컬럼에 Index가 없는 경우 유리

Sort Merge Join 주의사항

  • 두 결과 집합의 크기가 차이가 많이 나는 경우에는 비효율적
  • 정렬할 데이터가 많은 경우 부담이 가장 큰 방법
  • Sorting 메모리에 위치하는 대상은 join key뿐만 아니라 Select list도 포함되므로 불필요한 Select 항목 제거

1-1-3. Hash Join

- 해싱 함수(Hashing Function) 에 의한 탐색을 하여 조인을 수행하는 방식

  • 해싱 함수는 직접적인 연결을 담당하는 것이 아니라 연결될 대상을 특정 지역(partition)에 모아두는 역할만을 담당
  • 해시값을 이용하여 테이블을 조인하는 방식
    - Sort-Merge 조인은 정렬의 부하가 많이 발생하여, 이를 보완하기 위한 방법으로 Sort 대신 해쉬값을 이용하는 조인

Hash Join 특징

  • Random Access와 정렬에 대한 부담을 해소 > 대용량의 데이터 처리 시 사용됨
  • parallel processing을 이용한 Hash Join은 대용량 데이터를 처리하기 위한 최적의 솔루션
  • 2개의 조인 테이블 중 small rowset을 가지고 hash_area_size에 지정된 메모리 내에서 hash table 생성
  • CBO에서만 가능하며, CPU 성능에 의존적(CPU 자원 많이 소비)
  • Hash table 생성 후 Nested Loop처럼 순차적인 처리 형태로 수행
  • 배치 작업에 유리

Hash Join 주의사항

  • 대용량 데이터 처리에서는 상당히 큰 hash area를 필요로 함으로, 메모리의 지나친 사용으로 오버헤드 발생 가능성
    • 소량의 데이터를 조인할 때 오히려 불필요한 I/O가 증가할 수 있음
  • 연결조건 연산자가 ‘=’인 동치조인인 경우에만 가능
  • Equi Join에서만 가능 > Non-Equal 조인 조건인 경우 Hash Join으로 수행되지 못하고 Merge Join으로 수행됨
  • Outer Table의 해시 키 칼럼에 중복값이 적은 경우에 사용
  • 두 집합 중 크기가 작은 집합을 Outer Table로 결정
  • 조인 칼럼에 적당한 Index가 없는데 Nested Loop Join이 비효율 적일 때 사용
    • Nested Loop Join에서 조인 칼럼 Index가 있더라도 Random Access 부하가 심할 때 사용

1-2. JOIN의 종류

1-4-1. CROSS JOIN

  • 두 개의 테이블의 레코드 수를 곱한 수 = 전체 row
  • 아래 두 쿼리가 같은 기능을 함
SELECT * FROM A, B;
SELECT * FROM A CROSS JOIN B;

1-4-2. FULL OUTER JOIN

  • 두 테이블 간의 합집합을 나타냄
  • 서로의 테이블에 존재하지 않는 값에 대해 NULL 표기
SELECT * FROM A FULL OUTER JOIN B ON A.C1 = B.C1;

1-4-3. LEFT OUTER JOIN

  • 왼쪽 테이블 기준으로 JOIN 하며, RIGHT OUTER JOIN은 반대되는 개념
    • 기호가 붙지 않은 테이블이 기준 테이블이 됨
  • 아래 두 쿼리가 같은 기능을 함
SELECT * FROM A LEFT OUTER JOIN B ON A.C1 = B.C1;
SELECT * FROM A, B WHERE A.C1 = B.C1(+);

1-4-4. JOIN

  • 아래 두 쿼리가 같은 기능을 함
SELECT * FROM A JOIN B ON A.C1 = B.C1;
SELECT * FROM A, B WHERE A.C1 = B.C1;

1-4-5. NATURAL JOIN

  • 동일한 타입과 이름을 가진 컬럼을 조인 조건으로 이용

2. 실행 계획

  • 실행계획이란 사용자가 SQL을 실행하여 데이터를 추출하려고 할 때 옵티마이저가 수립하는 작업 절차
  • 쿼리문의 실행 계획을 확인하는 방법 : EXPLAIN PLAN, AUTOTRACE, SQL TRACE

AutoTrace

  • AutoTrace 결과에는 SQL을 튜닝하는데 유용한 정보들이 많이 포함되어 있어 가장 즐겨 사용되는 도구 중 하나임
  • SQL Plus에서 실행계획을 가장 쉽고 빠르게 확인해 볼 수 있는 방법
    • Operation : 각 단계에서의 어떤 작업이 일어났는지 표시
    • Name : 테이블명이나 Index 명을 표시함
    • Rows : 해당 쿼리 계획 단계에서 나올 것으로 예상되는 행의 수
    • Byte : 실행 계획의 각 단계가 반환할 것으로 예상 되는 데이터의 크기를 바이트로 나타낸 수
    • Cost : CBO가 쿼리 계획의 각 단계에 할당한 비용
    • Time : 각 단계별 수행 시간

2-1. 실행 계획 해석

  • 실행 계획은 여러 가지 단계로 이루어져 있는데 이것을 스텝이라고 함
  • 각각의 스텝에는 그 단계에서 수행된 명령, 처리된 데이터 건수, 소요된 비용과 시간을 표시

실행 계획 순서 읽기

  • 위에서 아래로 읽어 내려가며 제일 먼저 읽을 스텝 파악
  • 같은 레벨의 들여쓰기 존재 시 위 -> 아래 순서로 읽기
  • 하위 스텝이 존제하면 최하위 스텝부터 시작하여 상위 스텝으로 읽어나옴
  • 5 > 4 > 6 > 3 > 7 > 2 > 8 > 1 > 0 순으로 읽음

2-2. SCAN의 종류

  • SCAN : 데이터를 읽는 작업
  • SCAN을 수행하는 방식을 접근 경로라고 함
  • 테이블에 데이터가 많지 않아 INDEX를 타는 시간이 불필요하거나, 데이터가 아주 많은 경우 FULL TABLE SCAN이 유리
  • 많은 데이터가 있는 테이블에서 원하는 데이터를 추출해야 하는 상황이라면 INDEX SCAN이 유리

2-2-1. FULL TABLE SCAN

  • 테이블의 전체 데이터를 읽어 조건에 맞는 데이터를 추출하는 방식

FULL TABLE SCAN 사용

  • 조건절에서 비교한 칼럼에 INDEX가 없는 경우
  • INDEX가 있지만 조건에 만족하는 데이터가 테이블의 많은 양을 차지하여 FULL TABLE SCAN이 낫다고 옵티마이저가 판단
  • INDEX가 있지만 테이블의 데이터 자체가 적어 FULL TABLE SCAN이 낫다고 옵티마이저가 판단

2-2-2. ROWID SCAN

  • ROWID를 기준으로 데이터를 추출하며 단일 행에 접근하는 방식 중에서 가장 빠름

ROWID SCAN 사용

  • 조건절에 ROWID를 직접 명시할 경우
  • INDEX SCAN을 통해 ROWID를 추출한 후 테이블에 접근할 경우

2-2-3. INDEX SCAN

  • 인덱스를 활용하여 원하는 데이터를 추출하는 방식

INDEX SCAN 사용

  • INDEX UNIQUE SCAN
    • UNIQUE INDEX를 구성하는 모든 컬럼이 조건에 "="로 명시된 경우
  • INDEX RANGE SCAN
    • UNIQUE 성격의 결합 인덱스의 선두 컬럼이 WHERE절에 사용되는 경우 / 일반 인덱스의 컬럼이 WHERE절에 존재하는 경우
  • INDEX RANGE SCAN DESCENDING
    • INDEX RANGE SCAN을 수행함과 동시에 ORDER BY DESC절을 만족하는 경우
  • INDEX SKIP SCAN
    • 결합 인덱스의 선행 컬럼이 WHERE절에 사용되는 경우 / 옵티마이저가 INDEX SKIP SCAN이 FULL TABLE SCAN보다 낫다고 판단하는 경우
  • INDEX FULL SCAN
    • ORDER BY / GROUP BY의 모든 컬럼이 인덱스의 전체 또는 일부로 정의된 경우 / 정렬이 필요한 명령에서 INDEX ENTRY를 순차적으로 읽는 방식으로 처리된 경우
  • INDEX FULL SCAN DESCENDING
    • INDEX FULL SCAN을 수행함과 동시에 ORDER BY DESC절을 만족하는 경우
  • INDEX FAST FULL SCAN
    • FULL TABLE SCAN을 하지 않고도 INDEX FAST FULL SCAN으로 원하는 데이터를 추출할 수 있고 추출된 데이터의 정렬이 필요 없으며 결합 인덱스를 구성하는 컬럼 중에 최소 한개 이상은 NOT NULL인 경우
  • INDEX JOIN
    • 추출하고자 하는 데이터가 조인하는 인덱스에 모두 포함되어 있고 추출하는 데이터의 정렬이 필요없는 경우
profile
EUN JY
개린이
이전 포스트

[SQLD] 데이터 모델링과 엔터티(Entity), 속성(Attribute), 관계(Relationship)

다음 포스트

[SQLD] 정렬과 ORDER BY / 그룹 함수 GROUP BY, ROLLUP, CUBE, GROUPING SETS / 집합 연산자

0개의 댓글