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
  • 추출하고자 하는 데이터가 조인하는 인덱스에 모두 포함되어 있고 추출하는 데이터의 정렬이 필요없는 경우

3. 정렬

3-1. ORDER BY

• 조회된 데이터들을 다양한 목적에 맞게 특정 칼럼을 기준으로 정렬하여 출력하는데 사용
• 컬럼명 대신 ALIAS나 컬럼 순서를 나타내는 정수 사용도 가능
• 기본적인 정렬 순서는 오름차순
• 숫자형 데이터 타입 : 오름차순으로 정렬했을 경우 가장 작은 값부터 출력
• 날짜형 데이터 타입 : 오름차순으로 정렬했을 경우 날짜 값이 가장 빠른 값이 먼저 출력 (과거가 먼저 출력)

ORDER BY절의 NULL 값

• Oracle은 NULL 값을 가장 큰 값으로 간주
  • 오름차순으로 정렬했을 경우 가장 마지막에, 내림차순의 경우 가장 먼저 위치
• SQL SERVER는 NULL 값을 가장 작은 값으로 간주

4. 그룹

4-1. GROUP BY

4-2. ROLLUP

• 인수의 순서에 영향을 받음

ROLLUP(item_id, month)

SELECT item_id, month, SUM(amount) AS amount
FROM sales
GROUP BY ROLLUP(item_id, month);

ROLLUP(month, item_id)

SELECT month, item_id, SUM(amount) AS amount
FROM sales
GROUP BY ROLLUP(month, item_id);

4-3. CUBE

• 그룹핑 컬럼이 가질 수 있는 모든 경우의 수에 대하여 소계(SUBTOTAL)과 총계(GRAND TOTAL)을 생성

SELECT item_id, month, SUM(amount) AS amount
FROM sales
GROUP BY CUBE(item_id, month);

4-4. GROUPING SETS

• 인자별 소계(SUBTOTAL)만이 생성됨
• ROLLUP과 CUBE와 달리 계층 구조가 나타나지 않음 > 인자의 순서가 달라도 결과는 동일

SELECT item_id, month, SUM(amount) AS amount
FROM sales
GROUP BY GROUPING SETS(item_id, month);

• 괄호로 묶은 집합별로도 집계를 구할 수 있음

SELECT item_id, month, company, SUM(amount) AS amount
FROM sales
GROUP BY GROUPING SETS((item_id, month), company);

4-5. GROUPING

• 직접 그룹별 집계를 구하지는 않지만 ROLLUP, CUBE, GROUPING SETS를 지원하는 역할
• 집계가 계산된 결과는 GROUPING(표현식) = 1, 그 외에는 GROUPING(표현식) = 0

SELECT 
	CASE GROUPING(item_id) WHEN 1 THEN '모든 상품ID' ELSE item_id END AS item_id, 
    CASE GROUPING(month) WHEN 1 THEN '모든 월' ELSE month END AS month,
    SUM(amount) AS amount
FROM sales
GROUP BY GROUPING SETS((item_id, month), company);

5. 집합 연산자

• 서로 다른 두 개의 결과를 연산을 통해 새로운 결과 추출

집합 연산자 사용 조건

• 두 집합의 SELECT 절에 오는 컬럼의 개수, 데이터 타입이 동일해야 함
• 두 집합의 컬럼명은 달라도 상관 없음

집합 연산자의 종류

• UNION : 두 집합을 더해서 결과를 출력 / 중복 값 제거하고 정렬
• UNION ALL : 두 집합을 더해서 결과를 출력 / 중복 값 제거 안하고 정렬 안함
• INTERSECT : 두 집합의 교집합 결과를 정렬하여 출력
• MINUS : 두 집합의 차집합 결과를 출력 / 쿼리의 순서 중요 / INTERSECT와 반대의 결과

5-1. INTERSECT

• 데이터의 타입, 순서, 개수가 맞아야 함

SELECT col1, col2 FROM table1
INTERSECT
SELECT col3, col4 FROM table2;

참고 사이트

• 계층형 쿼리, WITH절, 분석 함수와 window 함수, 다중 테이블 INSERT https://thebook.io/006696/part01/ch07/
• 계층형 쿼리 - CONNECT BY~ https://tiboy.tistory.com/563
• 계층형 쿼리 - CONNECT_BY_ISLEAF https://devjhs.tistory.com/171
• 데이터베이스 실행 계획 https://coding-factory.tistory.com/744
• 데이터베이스 실행 계획 2 https://positivemh.tistory.com/364
• Nested Loop, Sort-Merge, Hash Join https://needjarvis.tistory.com/162
• Nested Loop, Sort-Merge, Hash Join 2 https://myjamong.tistory.com/238
• Nested Loop, Sort-Merge, Hash Join 3 https://regein.tistory.com/194
• 그룹 함수 ROLLUP, CUBE, GROUPING https://byul91oh.tistory.com/372
• HAVING COUNT(*) https://m.blog.naver.com/PostView.naver?isHttpsRedirect=true&blogId=diceworld&logNo=220152871523
• 오라클 JOIN 종류 https://m.blog.naver.com/PostView.naver?isHttpsRedirect=true&blogId=tyboss&logNo=70008936838
• 그룹별 순번, 순위 https://coding-factory.tistory.com/444
• RATIO_TO_REPORT https://crosstheline.tistory.com/107
• NULL 값 정렬 기준 https://qqqqqq.tistory.com/entry/ORDER-BY-%EC%A0%88%EC%9D%98-NULL-%EA%B0%92
• ORDER SIBLINGS BY https://diaryofgreen.tistory.com/188
• 집합 연산자 INTERSECT https://mine-it-record.tistory.com/82
• 오라클 Window Function https://loghada.tistory.com/19
• 오라클 Window Function 2 https://mizykk.tistory.com/121
• 오라클 Window Function 3 https://velog.io/@yewon-july/Window-Function