참고 사이트
- 계층형 쿼리, 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
1. ORACLE JOIN
1-1. JOIN의 방식
- 조인연산(JOIN OPERATION)
- 여러 테이블에 저장된 데이터를 한번에 조회할 수 있게 하는 DBMS의 기능
- 두 집합(테이블) 간의 곱으로 데이터를 연결하는 가장 대표적인 데이터 연결 방법
- 물리적 조인 : 옵티마이저에 의해 발생하는 조인
- 논리적인 조인을 옵티마이저가 DBMS 내부에서 물리적인 조인으로 표현
- Join 작업을 할 때 옵티마이저는 어떤 방식으로 조인을 할지 실행계획을 세우게 됨
1-1-1. Nested-Loop Join
- 두개의 테이블의 행을 각각 모두 확인하여 조인하는 방법
- 2개 이상의 테이블에서 하나의 집합을 기준으로 순차적으로 상대방 Row를 결합하여 원하는 결과를 조합하는 방식
- 선행 테이블의 처리 범위를 하나씩 액세스하면서 추출된 값으로 연결할 테이블을 조인
- 실행계획에서 먼저 실행되는 테이블이 Driving 테이블, 나중에 실행되는 것이 Driven 테이블
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
- 추출하고자 하는 데이터가 조인하는 인덱스에 모두 포함되어 있고 추출하는 데이터의 정렬이 필요없는 경우