:: 작업장

9월 5일에 시험을 본 SQLD 합격했습니다.

(네이버 블로그에는 포스팅을 했는데 티스토리는 몰아서올렸네요..ㅎㅎㅎ 티스토리로 정착하고싶은데 아직 네이버 블로그 접속수가 많아서 ㅠㅠ)

회사를 다니며 2~3주가량 준비해서 시험을 준비했는데요. 첫시험에 재응시없이 합격해서 기분이 더 좋네요.

저의 공부방법은 책을보고 블로그에 포스팅하는 방식으로 했습니다. 두번 세번은 못봤고 한번할때 실습까지 하면서 제대로 보려고노력했습니다. 사실 블로그 포스팅하다가 중간단계에서는 너무 시간이 많이걸리는것같아서... 밑줄치며 공부를 했습니다.

음 팁이아닌 팁을 드리자면 연습문제를 무조건 다 풀어보시고 인터넷에 공유되어있는 SQLD/P 기출문제를 보시는것을 추천드립니다. 연습문제랑 거의 똑같은 문제도 나오기 때문이죠...

연습문제랑 기출 꼭 풀어보고가세요!

아무쪼록 시험준비하시는분들은 건승하시길 바랍니다!

[SQLD] 2과목 SQL 기본및 활용 - 3장 SQL 최적화 기본 원리 3절 조인 수행 원리

- 조인이란 두 개 이상의 테이블을 하나의 집합으로 만드는 연산이다.

- SQL문에서 FROM 절에 두 개 이상의 테이블이 나열된 경우 조인이 수행된다.

- 조인 연산은 두 테이블 사이에 수행된다.

- 테이블을 조인할 때는 조인 단계별로 다른 조인 기법을 사용할 수 있다.

( 테이블 A와 테이블 B를 조인할 때는 NL Join 기법을 수행하고, 해당 조인결과를 테이블 C와 조인할 때는 Hash Join 기법을 사용할 수 있다.)

1. NL Join

- 중첩된 반복문과 유사한 방식으로 조인을 수행함.

- 반복문의 외부에 있는 테이블을 선행테이블 또는 Outer table이라고 하고, 반복문 내부에 있는 테이블을 후행 테이블 Inner 테이블 이라고한다.

FOR 선행테이블 읽음 -> 외부테이블

    FOR 후행테이블 읽음 -> 내부테이블

선행 테이블과 후행 테이블을 조인

- 먼저 선행 테이블의 조건에 맞는 행을 추출하여 후행 테이블을 읽으면서 조인을 수행한다.

(선행 테이블의 조건을 만족하는 모든 행의 수만큼 반복 수행한다)

- NL Join에서는 선행 테이블의 조건을 만족하는 행의 수가 많으면, 그 만큼 후행 테이블의 조인 작업은 반복 수행된다.

 그러므로 테이블 결과 행의 수가 적은 테이블을 조인 순서상 선행 테이블로 선택하는 것이 전체 일량을 줄일 수 있다.

- NL Join의 작업 방법

1) 선행 테이블에서 주어진 조건을 만족하는 행을 찾음.

2) 선행 테이블의 조인 키 값을 가지고 후행 테이블에서 조인 수행

3) 선행 테이블의 조건을 만족하는 모든 행에 대해 1번 작업 반복 수행

ex) NL Join 수행 방식

1) 선행 테이블에서 조건에 맞는 첫번째 행을 찾음.

- 이경우 선행테이블에 주어진 조건을 만족하지 않을 경우 해당 데이터는 필터링됨.

2) 선행테이블의 조인 키를 가지고 후행 테이블에 조인 키가 존재하는지 찾으러감

- 조인시도

3) 후행 테이블의 인덱스에 선행 테이블의 조인키가 존재하는지 확인

- 선행 테이블의 조인 값이 후행 테이블에 존재하지 않으면 선행 테이블 데이터는 필터링됨

(더이상 조인 작업을 진행할 필요가 없음)

4) 인덱스에서 추출한 레코드 식별자를 이용하여 후행 테이블을 엑세스

- 인덱스 스캔을 통한 테이블 엑세스

후행 테이블에 주어진 조건까지 모두 만족하면 해당 행을 출력버퍼에 넣음.

추출버퍼는 SQL문의 실행결과를 보관하는 버퍼로서 일정 크기를 설정하여 추출버퍼에 결과가 모두 차거나 더 이상 결과가 없어서 추출버퍼를 채울것이 없으면 결과를 반환한다.

추출버퍼는 운반단위, Array Size, Prefetch size라고도 한다.

NL Join 기법은 조인이 성공하면 바로 조인결과를 사용자에게 보여줄 수 있다. 그래서 결과를 가능한 빠르게 보여줘야하는 온라인 프로그램에 적당한 조인 기법이다.

2. Sort Merge Join

- 조인 칼럼을 기준으로 데이터를 정렬하여 조인을 수행한다.

- NL Join은 랜덤엑세스 방식으로 데이터를 읽는 반면 Sort Merge 조인은 주로 스캔 방식으로 데이터를 읽는다.

- Sort Merge Join은 랜덤엑세스로 NL Join에서 부담이 되던 넓은 범위의 데이터를 처리할 때 이용되는 조인기법이다.  그러나 Sort Merge Join은 정렬할 데이터가 많아 메모리에서 모든 정렬을 수행하기 어려운경우 임시영역을 사용하기 때문에 성능이 떨어질 수 있다.

- 일반적으로 대량의 데이터 조인 작업에서 정렬작업을 필요로 하는 Sort merge join 보다는 CPU 작업 위주로 처리하는 Hash Join이 성능상 유리하다.

- Sort Merge Join은 Hash Join과는 달리 동등 조인 뿐만 아니라 비동등 조인에 대해서도 조인 작업이 가능하다는 장점이 있다.

- Sort Merge Join은 조인 칼럼의 인덱스를 사용하지 않기 때문에 조인 칼럼의 인덱스가 존재하지 않을 경우에도 사용할 수 있는 조인기법. 

- 조인할 테이블 중에서 이미 앞 단계의 작업을 수행하는 도중에 정렬 작업이 미리 수행되었다면 조인을 위한 정렬 작업은 발생하지 않을 수 있다.

- Sort Merge Join 동작

1) 선행 테이블에서 주어진 조건을 만족하는 행을 찾음.

2) 선행 테이블의 조인 키를 기준으로 정렬 작업을 수행.

1 ~ 2번 작업을 선행 테이블 조건에 만족하는 모든 행에 대해 반복 수행

3) 후행 테이블에서 주어진 조건에 맞는 행을 찾음.

4) 후행 테이블의 조인 키를 기준으로 정렬 작업 수행

3~4번 작업을 후행 테이블의 조건에 만족하는 모든 행에 대해 반복 수행

5) 정렬된 결과를 이용하여 조인을 수행하며 조인에 성공하면 추출 버퍼에 넣음.

3. Hash Join

- 해슁 기법을 이용하여 조인을 수행한다.

- 조인을 수행할 테이블의 조인칼럼을 기준으로 해쉬 함수를 수행하여 소로 딩알한 해쉬값을 갖는 것들 사이에 실제 값이 같은지 비교하면서 조인을 수행한다.

- Hash Join은 NL Join의 랜덤 액세스 문제점과 Sort Merge Join의 문제점인 정렬작업의 부담을 해결 위한 대안으로 등장하였다.

- HASH Join은 칼럼의 인덱스를 사용하지 않기 때문에 조인칼럼의 인덱스가 존재하지 않아도 사행 가능하다.

- Hash Join은 해쉬함수를 사용해서 Join을 수행하기 때문에 '='로 수행하는 조인 즉 동든 조인에서만 사용할 수 있다.

- 해쉬함수가 적용될 때 동일한 값은 항상 같은 값으로 해슁됨이 보장된다.

- 해쉬함수를 적용할 때 보다 큰 값이 항상 더 큰 값으로 되고 작은값이 항상 작은 값으로 된다는 보장이 없으므로 동등 조인에만 적용가능하다.

- 해쉬조인은 조인작업을 수행하기 위해 해쉬테이블을 메모리에 생성해야 한다.

- 해쉬조인을 할 때는 결과 행의 수가 적은 테이블을 선행 테이블로 사용하는 것이 좋다. (선행테이블의 결과를 완전히 메모리에 저장할 수 있다면 임시 영역에 저장하는 작업이 발생하지 않기 때문)

- Hash Join에서는 선행 테이블을 이용하여 먼저 해쉬 테이블을 생성한다고 해서 선행테이블을 Build Input 이라고도 하며, 후행 테이블은 만들어진 해쉬테이블에 대해 해쉬값의 존재여부를 검사한다고 해서 Prove Input 이라고도 한다.

- Hash Join 동작

1) 선행 테이블에서 주어진 조건을 만족하는 행을 찾음

2) 선행 테이블의 조인키를 기준으로 해쉬 함수를 적용하여 해쉬테이블 생성.

-> 조인칼럼과 SELECT  절에서 필요로 하는 칼럼도 함꼐 저장됨.

1 ~ 2번 작업을 선행테이블의 조건을 만족하는 모든 행에 대해 반복 수행

3) 후행 테이블에서  주어진 조건에 맞는 행을 찾음.

4) 후행 테이블의 조인키를 기준으로 해쉬함수를 적용하여 해당 버킷을 찾음.

-> 조인키를 이용해서 실제 조인될 데이터를 찾음

5) 조인에 성공하면 추출 버퍼에 넣음

3~5번 작업을 후행 테이블의 조건을 만족하는 모든 행에 대해서 반복 수행

[SQLD] 2과목 SQL 기본및 활용 - 3장 SQL 최적화 기본 원리 2절 인덱스 기본

1.인덱스 특징과 종류

- 인덱스는 원하는 데이터를 쉽게 찾을 수 있도록 돕는 책의 찾아보기와 유사한 개념.

- 테이블을 기반으로 선택적으로 생성할 수 있는 구조.

- 인덱스의 기본적인 목적은 검색 기능의 최적화이다.

가. 트리기반 인덱스

- DBMS에서 가장 일반적인 인덱스는 B-Tree 인덱스이다.

* B Tree 인덱스

- B Tree 인덱스는 브랜치블록과 리프블록으로 구성된다. 브랜치 블록 중에서 가장 상위에서 있는 블록을 루트 블록 이라고 한다. 브랜치 블록은 분기를 목적으로 하는 블록이다.

- 브랜치 블록은 다음 단계의 블록을 가리키는 포인터를 가지고 있다.

- 리프 블록은 인덱스를 구성하는 칼럼의 데이터와 데이터를 가지고있는 행의 위치를 가리키는 레코드 식별자(RID, Record Identifier/Rowid)로 구성되어 있다.

- 인덱스 데이터는 인덱스를 구성하는 칼럼의 값으로 정렬된다.(데이터의 값이 동일하면 레코드 식별자의 순서로 저장됨)

- 리프 블록은 양방향 링크를 가지고 있다.  이것을 통해 오름 차순과 내림 차순 검색을 쉽게 겁색할 수 있다.

- B Tree 인덱스는 =로 검색하는 일치 검색과 BETWEEN, >, 등과 같은 범위 검색에 모두 적합한 구조이다.

인덱스에서 원하는 값을 찾는 과정.

1단계: 브랜치 블록의 가장 왼쪽 값이 찾고자 하는 값보다 작거나 같으면 왼쪽포인터로 이동

2단계: 찾고자 하는 값이 브랜치 블록의 값 사이에 존재하면 가운데 포인터로 이동

3단계: 오른쪽에 있는 값보다 크면 오른쪽 포인터로 이동

이과정을 리프 블록을 찾을 때까지 반복한다. 리프 블록에서 찾고자 하는 값이 존재하면 해당 값을 찾을 것이고, 없으면 해당 값은 존재하지 않아 검색에 실패하게된다.

인덱스를 생성할 때 동일 칼럼으로 구성된 인덱스를 중복해서 생성할 수 없다. 

인덱스 구성칼럼은 동일하지만 순서가 서로 다르면 생성가능하다 ( JOB + SAL, SAL + JOB 가능)

오라클에서 트리 기반 인덱스에는 B-Tree 인덱스 이외에도 비트맵 인덱스, 리버스키 인덱스, 함수기반 인덱스 등이 존재한다.

나. SQL Server의 클러스터형 인덱스

- SQL Server의 인덱스 종류는 저장 구조에 따라 클러스터형 인덱스와 비클러스터형 인덱스로 나뉜다.

클러스터형 인덱스는 두 가지 중요한 특징이있다.

1. 인덱스의 리프 페이지가 곧 데이터 페이지다.

- 테이블 탐색에 필요한 레코드 식별자가 리프 페이지에 없다

(인덱스 키 칼럼과 나머지 칼럼을 리프페이지에 같이 저장하기 때문에 테이블을 랜덤 엑세스할 필요가 없다.)

- 클러스터형 인덱스의 리프 페이지를 탐색하면 해당 테이블의 모든 칼럼 값을 곧바로 얻을 수 있다.

2. 리프 페이지의 모든 로우는 인덱스 키 칼럼순으로 물리적으로 정렬되어 저장된다.

- 테이블 로우는 물리적으로 한 가지 순서로만 정렬될 수 있다. 그러므로 클러스터형 인덱스는 테이블당 한 개만 생성할 수 있다.

- 리프블록에 인덱스 키 칼럼 외에도 테이블의 나머지 칼럼이 모두 함께 있다.

2. 전체 테이블 스캔과 인덱스 스캔

가. 전체 테이블 스캔

- 전체 테이블 스캔 방식으로 데이터를 검색하는 것은 테이블에 존재하는 모든 데이터를 읽어 가면서 조건에 맞으면 결과로 추출하고 조건에 맞지 않으면 버리는 방식으로 검색한다.

- Oracle의 경우 검색조건에 맞는 데이터를 찾기 위해 테이블의 고수위마크(HWM) 아래의 모든 데이터를 읽는다.

- 고수위 마크는 테이블에 데이터가 쓰여졌던 블록 상의 최상위 위치를 의미한다.

- 전체 테이블 스캔 방식으로 데이터를 검색할 때 고수위 마크까지의 블록 내 모든 데이터를 읽어야 하기 때문에 모든 결과를 찾을 때까지 시간이 오래 걸릴 수 있다.

옵티마이저가 Full table scan을 선택하는 이유는 일반적으로 다음과 같다.

1) SQL문에 조건이 존재하지 않는 경우

- SQL문에 조건이 존재하지 않는다는 것은 테이블에 존재하는 모든데이터가 답이 된다는 의미이므로 테이블의 모든 블록을 읽으면서 무조건 결과로서 반환한다.

2) SQL문의 주어진 조건에 사용가능한 인덱스가 존재하지 않는 경우.

- 사용가능한 인덱스가 존재하지 않는다면 데이터를 엑세스할 수 있는 방법은 모든 데이터를 읽으면서 주어진 조건을 만족하는지를 검색하는 방법 뿐이다.

- 또한 사용가능한 인덱스는 존재하나 함수를 사용하여 인덱스 칼럼을 변경한 경우에도 인덱스를 사용할 수 없다.

3) 옵티마이저의 취사 선택

조건을 만족하는 데이터가 많을 경우 결과 데이터를 추출하기위해 테이블의 대부분 블록을 엑세스 한다고 옵티마이저가 판단하면 조건에 맞는 인덱스가 존재해도 전체 테이블 스캔 방식으로 읽을 수 있다.

4) 그 밖의 경우

- 병렬 처리 방식으로 처리할 경우 또는 전체 테이블 스캔 방식의 힌트를 사용한 경우 전체 테이블 스캔 방식으로 데이터를 읽을 수 있다.

나. 인덱스 스캔

- 인덱스 스캔은 인덱스를 구성하는 칼럼의 값을 기반으로 데이터를 추출하는 엑세스 기법.

- 인덱스는 인덱스 구성 칼럼의 순서로 정렬되어 있다. (A+B 구성의 인덱스일 경우 A 칼럼으로 정렬되고 A칼럼 값이 동일할 경우 B 칼럼으로 정렬된다. B 칼럼까지 동일한경우 RID로 정렬된다.)

- 인덱스 순서와 동일한 정렬 순서를 사용자가 원하는 경우 정렬작업을 수행하지 않을 수 있다.

1) Index Unique Scan (인덱스 유일 스캔)

유일 인덱스를 사용하여 단 하나의 데이터를 추출하는 방식. 유일 인덱스는 중복을 허락하지 않는 인덱스로 유일인덱스 구성 칼럼에 모두 '='로 값이 주어지면 결과는 최대 1건이 된다.

인덱스 유일 스캔은 유일 인덱스 구성칼럼에 대해 모두 '='로 값이 주어진 경우에만 가능한 인덱스 스캔방식이다.

2) Index Range Scan

- 인덱스 범위 스캔은 인덱스를 이용하여 한 건 이상의 데이터를 추출하는 방식

- 유일 인덱스의 구성 컬럼 모두에대해  '='로 값이 주어지지 않는 경우와 비유일 인덱스를 이용하여 모든 엑세스 방식은 인덱스 범위 스캔 방식으로 데이터를 엑세스 하는것이다.

3) 인덱스 역순 범위스캔은 리프블록의 양방향 링크를 통해 내림차순으로 데이터를 읽는 방식. 이 방식을 이용하여 Max Value를 쉽게 찾을 수 있다.

다. Full Table scan과 Index Scan 방식의 비용.

- 인덱스 스캔 방식은 사용가능한 적절한 인덱스가 있을 경우에만 이용할 수 있는 스캔방식 이지만 전체 테이블 스캔 방식은 인덱스의 존재 유무와 상관없이 항상 이용가능한 스캔방식.

- 인덱스 스캔은 인덱스에 존재하는 레코드 식별자를 이용해서 검색하는 데이터의 정확한 위치를 알고서 데이터를 읽는다. 그러므로 불필요하게 다른 블록을 더 읽을 필요가 없다.

 따라서 한번의 I/O 요청에 한 블록씩 데이터를 읽는다.

- 전체 테이블 스캔은 한번의 I/O 요청으로 여러 블록을 한꺼번에 읽는다.

* 데이터를 찾을 때 여러 대부분의 데이터를 읽을 거라면 한번에 여러 블록씩 읽는 전체 테이블 스캔 방식이 유리할 수 있다.