아래 CREATE INDEX 옵션들은 2008 기준입니다. 2008 이전 버전과는 사용법의 차이가 있으니 주의하세요.

1.     PAD_INDEX & FILLFACTOR

2.     SORT_IN_TEMPDB

3.     IGNORE_DUP_KEY

4.     STATICS_NORECOMPUTE

5.     DROP_EXISTING

6.     ONLINE

7.     ALLOW_ROW_LOCKS & ALLOW_PAGE_LOCKS

8.     MAXDOP

9.     DATA_COMPRESSION / ON PARTITIONS

출처 : http://www.sqler.com/322711#1

쿼리변환

비용기반 옵티마이저의 서브엔진으로서 Query Transformer, Estimator, Plan Generator가 있다고 설명했는데, 이 중 Query Transformer가 그런 역할을 담당한다.

서브쿼리 Unnesting

중첩된 서브쿼리를 풀어내는 것

뷰 Merging 

인라인뷰에서 쿼리를 풀어내는 것

조건절 Pushing

옵티마이저가 뷰를 처리함에 있어 1차적으로 뷰 Merging을 고려하지만, 조건절(Predicate) Pushing을 시도할 수도 있다. 이는 뷰를 참조하는 쿼리 블록의 조건절을 뷰 쿼리 블록 안으로 밀어 넣는 기능을 말한다. 조건절이 가능한 빨리 처리되도록 뷰 안으로 밀어 넣는다면, 뷰 안에서의 처리 일량을 최소화하게 됨은 물론 리턴되는 결과 건수를 줄임으로써 다음 단계에서 처리해야 할 일량을 줄일 수 있다. 조건절 Pushing과 관련해 DBMS가 사용하는 기술로는 다음 3가지가 있다.

조건절 Pushdown

group by절을 포함한 아래 뷰를 처리할 때, 쿼리 블록 밖에 있는 조건절을 쿼리 블록 안쪽에 밀어 넣을 수 있다면 group by 해야 할 데이터량을 줄일 수 있다. 인덱스 상황에 따라서는 더 효과적인 인덱스 선택이 가능해지기도 한다.

조건절 Pullup

조건절을 쿼리 블록 안으로 밀어 넣을 뿐만 아니라 안쪽에 있는 조건들을 바깥 쪽으로 끄집어 내기도 하는데, 이를 ‘조건절(Predicate) Pullup’이라고 한다. 그리고 그것을 다시 다른 쿼리 블록에 Pushdown 하는 데 사용한다. 아래 실행계획을 보자.

조인조건 Pushdown

말 그대로 조인 조건절을 뷰 쿼리 블록 안으로 밀어 넣는 것으로서, NL Join 수행 중에 드라이빙 테이블에서 읽은 조인 칼럼 값을 Inner 쪽(=right side) 뷰 쿼리 블록 내에서 참조할 수 있도록 하는 기능이다. 아래 실행계획에서 group by절을 포함한 뷰를 액세스하는 단계에서 ‘view pushed predicate’ 오퍼레이션(id=3)이 나타났다. 그 아래 쪽에 emp_deptno_idx 인덱스가 사용된 것을 볼 수 있는데, 이는 dept 테이블로부터 넘겨진 deptno에 대해서만 group by를 수행함을 의미한다.

조건절이행

「(A = B)이고 (B = C)이면 (A = C)이다」 라는 추론을 통해 새로운 조건절을 내부적으로 생성해 주는 쿼리변환이다. 「(A > B)이고 (B > C)이면 (A > C)이다」와 같은 추론도 가능하다. 예를 들어, A 테이블에 사용된 필터 조건이 조인 조건절을 타고 반대편 B 테이블에 대한 필터 조건으로 이행(移行)될 수 있다. 한 테이블 내에서도 두 칼럼간 관계정보(예를 들어, col1 >= col2)를 이용해 조건절이 이행된다.

불필요한조인제거

1:M 관계인 두 테이블을 조인하는 쿼리문에서 조인문을 제외한 어디에서도 1쪽 테이블을 참조하지 않는다면, 쿼리 수행 시 1쪽 테이블은 읽지 않아도 된다. 결과집합에 영향을 미치지 않기 때문이다. 옵티마이저는 이 특성을 이용해 M쪽 테이블만 읽도록 쿼리를 변환하는데, 이를 ‘조인 제거(Join Elimination)’ 또는 ‘테이블 제거(Table Elimination)’라고 한다.

OR조건은 Union으로 변환

아래 쿼리가 그대로 수행된다면 OR 조건이므로 Full Table Scan으로 처리될 것이다. (아니면, job 칼럼 인덱스와 deptno 칼럼 인덱스를 결합하고 비트맵 연산을 통해 테이블 액세스 대상을 필터링하는 Index Combine이 작동할 수도 있다.)

만약 job과 deptno에 각각 생성된 인덱스를 사용하고 싶다면 아래와 같이 union all 형태로 바꿔주면 된다.

사용자가 쿼리를 직접 바꿔주지 않아도 옵티마이저가 이런 작업을 대신해 주는 경우가 있는데, 이를 ‘OR-Expansion’이라고 한다. 

기타 쿼리변환

출처 : http://www.dbguide.net/db.db?cmd=view&boardUid=148219&boardConfigUid=9&categoryUid=216&boardIdx=139&boardStep=1

클러스터링 팩터

클러스터링 팩터는 우리말로 하자면 군집성 계수 즉 데이터가 모여있는 정도라고 번역할수 있다. 

인덱스 로우의 순서와 데이터 로우의 순서가 얼마나 비슷한 순서로 저장되어 있느냐의 정도를 나타낸다

B-Tree 인덱스를 사용한 Range Scan 비용 계산식 의 중요한 요소이며, 비용계산 결과에 오차를 일으키는 가장 큰 원인이 될 수 있다. 

클러스터링 팩터는 데이터가 테이블 전체에 무작위로 분산된 정도를 나타내는 하나의 숫자이다.

(테이블 내 데이터의 흩어짐(Scatter)을 표현할 수 있는 숫자를 만든 발상은 매우 훌륭하다.)

클러스터링팩터는 인덱스 스캔시의 비용(cost)에 영향을 미치며, dba_indexes, all_indexes, user_indexes의 인덱스뷰의 클러스터링 팩터라는 컬럼을 통해 확인할수 있다.

참고 : http://mypledge.tistory.com/9

출처 :

http://up730.tistory.com/entry/%ED%81%B4%EB%9F%AC%EC%8A%A4%ED%84%B0%EB%A7%81-%ED%8C%A9%ED%84%B0%EB%9E%80

http://wiki.gurubee.net/pages/viewpage.action?pageId=3080204

http://whale.oceanmate.co.kr/3

옵티마이저

SQL을 가장 빠르고 효율적으로 수행할 최적의 처리경로를 생성해주는 DBMS의 내부의 핵심엔진

사용자가 SQL로 결과집합을 요구하면 이를 생성하는데 필요한 처리경로는 옵티마이저가 자동으로 생성해준다.

옵티마이저의 종류

최적화목표

옵티마이저에 영향을 미치는 요소

옵티마이저의 한계

통계정보를 이용한 비용계산 원리

선택도 

카디널러티 

특정액세스 단계를 거치고 난 후 출력될 것으로 예상되는 결과 건수

행의 수 * 선택도

히스토그램 - 미리 저장된 히스토그램 정보가 있으면, 옵티마이저는 그것을 사용해 더 정확하게 카디널리티를 구할 수 있다. 분포가 균일하지 않은 컬럼은 히스토그램으로 더 정확한 카디널리티를 산출할 수있다.

히스토그램 생성은 컬럼 통계수집시 버킷을 2개 이상으로 지정한다.

도수분포 히스토그램

값별로 빈도수를 저장하는 히스토그램

dba_histograms

높이 균형 히스토그램

비용

쿼리를  수행하는데 소요되는 일량, 시간

I/O비용모델

예상되는 I/O요청 횟수만큼 쿼리 수행 비용으로 간주해 실행계획을 평가

인덱스를 경유한 테이블 액세스비용

- I/O 비용 모델에서의 비용은 디스크 I/O Call 횟수(논리적/물리적으로 읽은 블록 개수가 아닌 I/O Call 횟수)를 의미한다. 그리고 인덱스를 경유한 테이블 액세스 시에는 Single Block I/O 방식이 사용된다. 이는 디스크에서 한 블록을 읽을 때마다 한 번의 I/O Call을 일으키는 방식이므로 읽게 될 물리적 블록 개수가 I/O Call 횟수와 일치한다. 따라서 인덱스를 이용한 테이블 액세스 비용은 아래와 같은 공식으로 구할 수 있다. 

비용 = blevel                                                 -- 인덱스 수직적 탐색 비용 

         + (리프 블록 수 × 유효 인덱스 선택도)        -- 인덱스 수평적 탐색 비용

         + (클러스터링 팩터 × 유효 테이블 선택도)   -- 테이블 Random 액세스 비용 

Full Scan에 의한 테이블 액세스비용

Full Scan에 대해서는, 테이블 전체를 순차적으로 읽어 들이는 과정에서 발생하는 I/O Call 횟수로 비용을 계산한다. Full Scan할 때는 한 번의 I/O Call로써 여러 블록을 읽어 들이는 Multiblock I/O 방식을 사용하므로 총 블록 수를 Multiblock I/O 단위로 나눈 만큼 I/O Call이 발생한다. 예를 들어, 100블록을 8개씩 나누어 읽는다면 13번의 I/O Call이 발생하고, I/O Call 횟수로써 Full Scan 비용을 추정한다. 따라서 Multiblock I/O 단위가 증가할수록 I/O Call 횟수가 줄고 예상비용도 줄게 된다.

CPU비용모델 - I/O + 시간

출처 : http://www.dbguide.net/db.db?cmd=view&boardUid=148218&boardConfigUid=9&categoryUid=216&boardIdx=139&boardStep=1

http://wiki.gurubee.net/pages/viewpage.action?pageId=29065610

클러스터드 인덱스가 있는 테이블의 경우 데이터는 클러스터드 인덱스의 키로 정렬이됩니다.

이 키로 데이터를 찾을 경우 "Clustered Index Seek"가 발생하게 됩니다.

클러스터드 인덱스가 없는 테이블의 경우 데이터는 추가된 순서대로 쌓이게 됩니다. 그래서 힙이라고 하죠.

이때 넌클러스터드 인덱스를 만들게 되면 넌클러스터드 인덱스의 리프레벨에는 데이타의 위치를 가르키는 RID가 기록됩니다. 넌클러스터드 인덱스로 데이터를 찾을 경우에는 그래서 "RID Lookup"이 발생하게 됩니다. 

근데 클러스터드 인덱스가 있는 테이블에 넌클러스터드 인덱스를 만들면 이 인덱스의 리프레벨에는 RID가 아닌 클러스터드 인덱스의 키가 기록됩니다. 그래서 이 넌클러스터드 인덱스로 데이터를 찾을 경우에는 넌클러스터드 인덱스의 리프레벨에서 클러스터드 인덱스의 키를 확인한 후 이 키를 이용해서 데이터를 찾는 "Key Lookup"이 발생합니다.

"Index Scan"이란 이런겁니다. 

주소록 테이블에 전화번호로 인덱스를 만들었습니다.

근데 찾고 싶은 데이터는 전화번호의 4번째가 7이고 다섯번째와 여섯번째의 차가 3인 그런 데이타입니다. -_-;;

우리가 전화번호부를 이용해서 이런 데이터를 찾으려고 해도... 결국 전화번호부 책을 몽땅 뒤질수밖에 없죠.

SQL SERVER도 마찬가지입니다. 인덱스를 이용해서 저런 데이타를 찾을 수가 없습니다.

그래서 결국 테이블을 몽땅 스캔합니다. 이것이 "Clustered Index Scan" 또는 "Table Scan" 라고 합니다.

근데 찾으려는 데이타가 인덱스의 키에 포함되어 있을 경우 테이블을 몽땅 뒤지는것보다 인덱스를 뒤지는게 더 빠릅니다.

이유는 인덱스 페이지가 더 작을것이기 때문이죠. 이때 발생하는것이 "Index Scan"이라고 합니다.

책을 예로 설명해보면

보통 기술서적들 보면 맨 뒤에 키워드를 인덱스로 만들어놨습니다.

SQL 관련 서적을 예로 들면 "클러스터드 인덱스"에 대해 설명해놓은 페이지를 찾으려면

"ㅋ"섹션에서 "클러스터드 인덱스" 키워드를 찾은 후 517페이지 (<- 이게 RID) 에 있다는 걸 확인한 후 517페이지를 펼치게 됩니다.

이런게 "Index Seek"

근데 키워드 중에 중간에 "덱"이 들어간 페이지를 찾고 싶으면??

인덱스 a~z ㄱ~ㅎ까지 몽땅 확인해야죠? 중간에 "덱"이런거 들어간 키워드가 있나...

이런 동작이 "Index Scan"입니다.

"Index Seek"동작이나 "Index Scan"동작은 어쨋든 인덱스 페이지에서 인덱스 키를 찾는 동작입니다.

이 동작후에는 데이터 페이지로 이동하기 위해 "RID Lookup"이나 "Key Lookup"을 하게 되죠.

클러스터드 인덱스가 있을때는 Key Lookup 넌클러스터드만 있다면 RID Lookup을 하게 됩니다.

근데 만약에 원하는 데이타가 인덱스 키 안에 모두 포함되어 있다면

말하자면 전화번호부 테이블에서 "전화번호, 이름"으로 인덱스가 만들어져 있는데

이런 쿼리를 날린다면

전화번호와 이름이 모두 인덱스 페이지에 있기 때문에 더 이상 데이터 페이지로 이동하지 않아도 모든 데이터를 확인 할 수 있습니다. 그러면 Lookup 동작 자체를 안하게 됩니다. 인덱스 이외에 같은 행에 있는 데이터를 가져올 필요가 없기 때문이죠. 이런걸 "커버드 인덱스"라고 합니다.

따로 룩업을 안하고 인덱스 페이지에서 모든 데이터를 가져가는것이 마치 클러스터드 인덱스의 동작과 비슷합니다.

그래서 성능이 클러스트드 인덱스에 필적한다고 하죠.

사실 커버드 인덱스란 인덱스의 종류가 따로 있는 것이 아니라 어떤 쿼리가 인덱스 컬럼 내에서만 select 할때

그때 사용된 인덱스가 쿼리를 커버하는 것이죠.

요약

RID

LOOKUP

RID LOOKUP

KEY LOOKUP

특징

RID LOOKUP을 발생시키기위한조건

출처 : http://www.sqler.com/612541 댓글

그림출처 : https://blogs.msdn.microsoft.com/craigfr/2006/06/30/bookmark-lookup/

인덱스란

포인트쿼리(Point Query)

범위쿼리(Range Query)

커버드쿼리(Covered Query)

클러스터인덱스

넌클러스터인덱스

결론

그러면 어떤 컬럼에 인덱스를 걸어야할까.

※ 아래의 조인 알고리즘들은 R  A=B S 를 기준으로 설명함

( 릴레이션 R과 S의 각 튜플들이 R의 A애트리뷰트와, S의 B 애트리뷰트 값이 일치하면 결합)

중첩 반복 조인(Nested Loop Join)

  가. Nested Loop Join 알고리즘

- 2개 이상의 테이블에서 하나의 집합을 기준으로 순차적으로 상대방 Row를 결합하여 원하는 결과를 추출.

- R(외부, 선행 테이블)의 각각의 레코드에 대해 S(내부, 후행 테이블)의 모든 레코드를 검색하여, 두 레코드가 조인 조건(A=B)을 만족하는지 확인하여 만족하면 조인하는 방법

  나. 색인된 중첩 반복 조인(=단일 반복 조인, single loop join) 알고리즘

         - S(내부 테이블)의 B에 인덱스가 존재할 경우 사용

- R의 각 레코드들에 대하여 S의 인덱스 접근 구조를 사용하여 S의 레코드들 중에서 조인조건을 만족하는 레코드를 직접 검색

  다. 사용법 및 사용예제

    힌트: /*+ USE_NL(A B) */

    select /*+ use_nl(b,a) */ a.dname, b.ename, b.sal

    from emp b, dept a

    where a.loc = 'NEW YORK'

    and b.deptno = a.deptno

  라. Nested Loop의 특징

    - 주로 좁은 범위에 유리

    - 순차적으로 처리하며, Random Access 위주

    - 후행(Driven) 테이블에는 조인을 위한 인덱스가 생성되어 있어야 함

    - 실행속도 = 선행 테이블 사이즈 * 후행 테이블 접근횟수

  마. Nested Loop사용 시 주의사항

    - 데이터를 랜덤으로 액세스하기 때문에 결과 집합이 많으면 수행속도가 저하됨

    - 선행(Driving) 테이블의 크기가 작거나, Where절 조건을 통해 결과 집합을 제한할 수 있어야 함

    - 조인 연결고리 인덱스가 없거나, 조인 집합을 구성하는 검색조건이 조인 범위를 줄여주지 못할 경우 비효율적임

- Loop 개수를 줄이기 위해 조인에 참여하는 테이블 중 Row수가 적은 쪽을 Driving으로 설정하고, inner 테이블의 연결고리를 결합인덱스를 이용해 최적화함

  바. Nested Loop사용 예

정렬 합병 조인(Sort Merge Join)

  가. Sort Merge Join 알고리즘

- R과 S의 레코드들이 각각 조인 애트리뷰트 A, .B 값에 따라 물리적으로 정렬되어 있는 경우, 두 파일을 모두를 조인 애트리뷰트의 순서에 따라 동시에 스캔하면서 A, B 값이 동일한 레코드를 검색

- 정렬되어 있지 않은 경우는 우선 외부 정렬을 사용하여 정렬 후 조인

-조인의 대상범위가 넓을 때 발생하는 랜덤 액세스를 줄이기 위한 경우나 연결고리에 마땅한 인덱스가 존재하지 않을 때 해결하기 위한 대안

  나. 사용법 및 사용예제

     힌트: /* USE_MERGE(A B) */

select /*+ use_merge(a b) */a.dname, b.empno, b.ename
from   dept a,emp b
where  a.deptno = b.deptno
and    b.sal > 1000 ;

  다. Sort Merge Join의 특징

- 연결을 위해 랜덤 액세스를 하지 않고 스캔을 하면서 이를 수행

- 정렬을 위한 영역(Sort Area Size)에 따라 효율에 큰 차이가 남

- 조인 연결고리의 비교 연산자가 범위 연산(‘>’,’< ‘)인 경우 Nested Loop 조인보다 유리

  라. Sort Merge Join사용시 주의사항

- 두 결과집합의 크기가 차이가 많이 나는 경우에는 비효율적

- Sorting 메모리에 위치하는 대상은 join key뿐만 아니라 Select list도 포함되므로 필요한 select 항목 제거

  마. Sort Merge Join사용 예

해시 조인(Hash Join)

  가. Hash Join 알고리즘

- 해싱 함수(Hashing Function) 기법을 활용하여 조인을 수행하는 방식으로 해싱 함수는 직접적인 연결을 담당하는 것이 아니라 연결될 대상을 특정 지역(partition)에 모아두는 역할만을 담당함

- R의 애트리뷰트 A와 S의 애트리뷰트 B를 해시 키로 하고, 동일한 해시 함수를 사용하여 해시

- 1단계(분할 단계, partitioning phase):  더 적은 수의 레코드를 가진 화일(R )의 레코드들을 해시 파일 버켓들로 해시

- 2단계(조사 단계, probing Phase): 다른 파일(S)의 각 레코드를 해시하여 R에서 동일한 해시 주소를 갖는 버켓 내의 레코드들이 실제로 조인 조건을 만족하면 두 레코드를 결합

  나. 사용법 및 사용예제

    힌트: /*+ USE_HASH(A B) */

select /*+ use_hash(a b) */ a.dname, b.empno, b.ename
from dept a, emp b
where a.deptno = b.deptno
and a.deptno between 10 and 20;

  다. Hash Join의 특징

1. Nested Loop 조인과 Sort Merge 조인의 문제점을 해결
2.  대용량 처리의 선결조건인 랜덤 액세스와 정렬에 대한 부담을 해결할 수 있는 대안으로 등장
3. Hash 조인만을 이용하는 것보다 parallel processing을 이용한 hash 조인은 대용량 데이터를 처리하기 위한 최적의 솔루션 제공
4. 2개의 조인 테이블 중 small rowset을 가지고 hash_area_size에 지정된 메모리 내에서 hash table 생성
5. Hash bucket이 조인집합에 구성되어 Hash 함수 결과를 저장하여야 하는데, 이러한 처리에 많은 메모리와 CPU자원이 소모됨
6. CBO(Cost Based Optimizer) 모드에서 옵티마이저가 판단가능하며, 테이블의 통계정보가 있어야 함
7. Hash table 생성 후 Nested Loop처럼 순차적인 처리 형태로 수행함

  라. Hash Join사용시 주의사항

1. 대용량 데이터 처리에서는 상당히 큰 hash area를 필요로 함으로, 메모리의 지나친 사용으로 오버헤드 발생 가능성
2. 연결조건 연산자가 ‘=’인 동치조인인 경우에만 가능

  마. Hash Join정리

    1) 수행순서

           - 두 테이블을 스캔하여 사이즈가 작은 테이블을 선행 테이블로 결정

           - 선행 테이블을 이용하여 해쉬 테이블을 구성한다(Build Input)

           - 후행 테이블은 해쉬 값을 이용하여 선행 테이블과 조인한다(Prove Input)

     2) Build Input 크기

           -  Hash Area 만으로 Hash Table 생성이 불충분하다면 Hash Table Overflow가 발생

             내Hash Area 사이즈를 증가 필요함

     3) 이용

           - 대용량 데이터 엑세스, 배치처리, 전체 테이블을 조인할 때 유리하다

           - 양쪽 테이블의 조건으로 각각 범위를 줄일 수 있을 때 유리하다

           - 병행 처리로 수행속도 향상이 가능하다

           - Hash Area 사이즈 조정으로 수행속도 향상이 가능하다

조인 연산 비교

  가. Nested Loop와 Hash Join의 비교

나. sort merge와 Hash join의 비교

출처 : http://www.jidum.com/jidums/view.do?jidumId=167

SQL의 구문분석 절차

1. 동일한 SQL의 수행여부확인

 - Soft Parsing : 동일한 SQL이 이전에 수행되었고, 실행계획등의 실행정보가 메모리에 저장되어있다.

 - Hard Parsing : 실행정보가 존재하지않아 구문 분석을 새로 시작해야한다.

2. 문법/ 절차 확인

 - 문법이 맞는지 확인. 틀리면 SYNTAX 에러 메시지 발생

3. SEMANTIC

 - Database Resolution단계, SQL에 사용된 테이블과 테이블 컬럼이 실제 DB에 존재하는지 확인

4. 권한 확인

  - 해당 SQL을 실행 할수 있는지 권한 확인

Query Transformation

Transitivity

특징

View Merging

View Merging 종류

인라인뷰

인라인뷰의 종류

Non-Mergeable인라인 뷰확인하기