5. 옵티마이저와 실행계획

옵티마이저란

• 사용자가 질의한 SQL 문에 대한 최적의 실행방법을 결정하는 역할을 수행한다. 이러한 최적의 실행방법을 실행 계획이라고 한다.
• 사용자의 요구사항을 만족하는 결과를 추출할 수 있는 다양한 실행 방법이 존재함
• 다양한 실행 방법들 중에서 최적의 실행방법을 결정하는 것이 옵티마이저의 역할이다.

비용기반 옵티마이저

• SQL 문을 처리하는데 비용이 가장 적게 드는 실행계획을 선택하는 방식이다. 비용이란 SQL 문을 처리하는데 예상되는 시간 또는 자원 의미한다.
• 테이블, 인덱스 등의 통계 정보와 시스템 통계정보를 이용하여 최적의 실행계획을 도출한다.
• 인덱스를 사용하는 비용이 전체 테이블 스캔 비용보다 크다고 판단되면 테이블 풀 스캔을 유도 하게 된다.

옵티마이저의 구성요소

• 구성요소
  ◼ 질의 변환기
  ◆ 사용자가 작성한 SQL 문을 처리하기에 보다 용이한 형태로 변환
  
  ◼ 비용 예측기
  ◆ 대안 계획 생성시에 의해서 생성된 대안 계획의 비용을 예측하는 모듈
  ◆ 대안 계획의 정확한 비용을 측정하기 위해서 연산의 중간 집합의 크기 및 결과 집합의 크기, 분포도 등의 예측을 함, 보다 나은 예측을 위해서 정확한 통계 정보가 필요함
  
  ◼ 대안계획생성기
  ◆ 동일한 결과를 생성하는 다양한 대안 계획을 생성하는 모듈
  ◆ 대안 계획은 연산의 적용 순서, 연산방법변경, 조인 순서 변경 등을 통해서 생성
  ◆ 동일한 결과를 생성하는 가능한 모든 대안 계획을 생성해야 보다 나은 최적화를 수행할 수 있음

인덱스란?

• 인덱스는 원하는 데이터를 쉽게 찾을 수 있도록 돕는 책의 찾아보기와 유사한 개념이다.
• 검색조건에 부합하는 데이터를 효과적으로(빠르게) 검색할 수 있도록 돕는다.
• 한 테이블은 0 개~N 개의 인덱스를 가질 수 있다.
• 한 테이블에 과도하게 많은 인덱스가 존재하면 INSERT, UPDATE, DELETE 와 같은 DML 작업 시 부하가 발생한다

트리기반 인덱스 란?

• DBMS 에서 널리 사용되는 가장 일반적인 인덱스이다.
• 루트 블록, 브랜치 블록, 리프 블록으로 구성된다.
• 가장 상위에 존재하는 블록이 루트 블록이고 브랜치 블록은 분기를 목적으로 하는 블록이다.
• 리프 블록은 트리의 가장 아래 단계에 존재하는 블록이다.
• 리프 블록은 인덱스를 구성하는 칼럼의 데이터와 해당 데이터를 가지고 있는 행의 위치를 가리키는 레코드 식별자 인 ROWID 로 구성되어 있다.

인덱스 구조 상세

• 루프와 브랜치 블록에 있는 각 레코드는 하위 블록에 대한 주소 값을 갖는다. 키 값은 하위 블록에 저장된 키 값의 범위를 나타낸다.
• LMC 가 가리키는 주소로 찾아간 블록에는 키 값을 가진 첫번째 레코드보다 작거나 같은 레코드가 저장돼 있다.
• 리프 블록에 저장된 각 레코드는 키 값 순으로 정렬돼 있을 뿐만 아니라 테이블 레코드를 가리키는 주소값 즉 Rowid 를 갖는다.
• 인덱스 키 값이 같으면 Rowid 순으로 정렬된다.
• 인덱스를 스캔하는 이유는 검색조건을 만족하는 소량의 데이터를 빨리 찾고 거기서 Rowid 를 얻기 위해서이다

풀 테이블 스캔과 인덱스 스캔

풀테이블 스캔

• 테이블에 존재하는 모든 데이터를 읽어가면서 조건에 맞으면 결과로 추출하고 조건에 맞지 않으면 버리는 방식
• HIGH WATER MARK 는 테이블에 데이터가 쓰여졌던 블록 상의 최상위 위치로써 테이블 풀 스캔 시는 HWM 까지의 블록에 있는 모든 데이터를 읽어야 하기 때문에 시간이 오래 걸릴 수 있다.
• 풀 테이블 스캔으로 읽은 블록은 재 사용성이 낮다고 보고 메모리 버퍼 캐시에서 금방 제거될 수 있도록 관리한다.
• 옵티마이저가 풀 테이블 스캔을 선택하는 경우
  ◼ SQL 문에 조건이 존재하지 않는 경우
  ◼ SQL 문의 조건을 기준으로 사용 가능한 인덱스가 없는 경우
  ◼ 옵티마이저의 판단으로 풀 테이블 스캔이 유리하다고
  판단하는 경우
  ◼ 전체 테이블 스캔을 하도록 강제로 힌트를 지정한 경우

인덱스 스캔

• 인덱스 스캔은 인덱스를 구성하는 칼럼의 값을 기반으로 데이터를 추출하는 액세스 기법
• 인덱스 리프 블록은 인덱스를 구성하는 칼럼과 ROWID 로 구성
• 인덱스의 리프 블록을 읽으면 인덱스 구성 칼럼의 값과 ROWID 를 알 수 있음
• 즉 인덱스를 읽어서 대상 ROWID 를 찾으면 해당 ROWID 로 다시 테이블을 찾아 가야함
• 하지만 SQL 문에서 필요로 하는 칼럼이 모두 인덱스 구성칼럼이라면 테이블을 찾아갈 필요 없음
• 일반적으로 인덱스 스캔을 통해 데이터를 추출하면 해당 결과는 인덱스의 칼럼의 순서로 정렬된 상태로 반환됨

인덱스 스캔 유형

• 인덱스 범위 스캔
  ◼ 인덱스를 이용하여 한건 이상의 데이터를 추출하는 방식
  ◼ 인덱스 스캔으로 특정 범위를 스캔하면서 대상 레코드를 하나하나 리턴하는 방식임
• 인덱스 유일 스캔
  ◼ 인덱스를 사용하여 단 하나의 데이터를 추출하는 방식
  ◼ 유일인덱스는 중복 레코드를 허용하지 않음
  ◼ 유일인덱스는 반드시 '='조건으로 조회 해야 됨(그렇게 할 수 밖에 없음)
• 인덱스 전체 스캔
  ◼ 인덱스를 처음부터 끝까지 전체를 읽으면서 조건에 맞는 데이터를 추출함
  ◼ 데이터를 추출 시 리프 블록에 있는 ROWID 로 테이블의 레코드를 찾아가서 조건에 부합하는지 판단하고
  ◼ 조건에 부합되면 해당 행을 리턴 함
• 인덱스 스킵 스캔
  ◼ 인덱스 선두 컬럼이 조건절에 없어도 인덱스를 활용하는 스캔 방식이다.
  ◼ 조건절에 빠진 인덱스 선두 컬럼(성별)의 Distinct Value 의 개수가 적고, 후행 컬럼(연봉)의 Distinct Value 의 개수가 많을 때 유용
  ◼ Index Skip Scan 은 루트 또는 브랜치에서 읽은 컬럼 값 정보를 이용해 조건절에 부합하는 레코드를 포함할 가능성이 있는 리프 블록만 액세스 한다.
• 인덱스 고속 전체 스캔
  ◼ Index Fast Full Scan 은 물리적으로 디스크에 저장된 순서대로 인덱스 리프 블록들을 Multi Block I/O 방식으로 읽어 들인다. 또한 병렬 인덱스 스캔도 가능하다.
• 인덱스 역순 범위 스캔
  ◼ 인덱스 리프 블록은 Doubly Linked List 방식으로 저장되어 있음
  ◼ 즉 이 성질을 이용하여 인덱스를 역순으로(거꾸로) 읽을 수 있음
  ◼ 인덱스를 뒤에서부터 앞쪽으로 스캔하기 때문에 내림차순으로 정렬된 결과 집합을
  얻을 수 있다. (스캔 순서를 제외 하고는 Range Scan 과 동일함)

테이블 스캔 VS 인덱스 스캔

풀 테이블 스캔	인덱스 스캔
항상 이용가능	인덱스가 존재해야만 이용가능
한번에 여러 개의 block을 읽음		한번에 한 개의 블록만을 읽음
많은 데이터를 조회 시 성능 상 유리		극히 일부분의 데이터를 조회 시 유리
table random access 부하 없음	table random access에 의한 부하가 발생됨
읽었던 블록을 반복해서 읽는 경우 없음	읽었던 블록을 반복해서 읽는 비효율 발생(논리적인 블록 I/O의 개수도 많아짐)

조인 수행 원리

조인이란

• 조인이란 두개 이상의테이블을 하나의 집합으로 만드는 연산이다.
• SQL 문의 FROM 절에 두개 이상의 테이블 혹은 집합이 존재할 경우 조인이 수행된다.
• 조인은 3 개 이상의 테이블을 조인한다고 하더라도 특정 시점에 2 개의 테이블 단위로 조인이 된다.
• A, B, C 집합을 조인한다면 A, B 조인 후 해당 결과 집합을 C 와 조인 하는 방식이다.
• 각각의 조인 단계에서는 서로 다른 조인 기법이 사용될 수 있다.
• 즉 A, B 조인 시에는 NL 조인을 수행하고 A, B 조인의 결과와 C 를 조인 시에는 해시 조인이 수행될 수 있다.

NL 조인

NL 조인은 작은 집합이 Driving 되어야 하고, Inner 테이블의 인덱스 스캔이 매우 중요하다.

• RANDOM 액세스 위주(인덱스구성이 완벽 해도 대량 데이터 조인 시 불리)
• 한 레코드 씩 순차 진행(부분 범위 처리를 유도해야 효율적 수행)
• DRIVING 테이블 처리 범위에 의해 전체 성능이 결정됨
• 인덱스 유무, 인덱스 구성에 크게 영향 받음
• 소량의 데이터를 처리하거나 부분범위처리가 가능한 OLTP 환경에 적합

소트 머지 조인

정렬 작업을 생략할 수 있는 인덱스가 존재하는 경우 사용

• 실시간 인덱스 생성 : 양쪽 집합을 정렬한 다음에는 NL 조인과 같은 오퍼레이션
• 인덱스 유무에 영향을 받지 않음 : 미리 정렬된 인덱스가 있으면 좀 더 빠르게 수행할 수는 있음
• 양쪽 집합을 개별적으로 읽고 나서 조인 : 조인 컬럼에 인덱스가 없는 상황에서 두 테이블을 독립적으로 읽어 조인 대상 집합을 줄일 수 있을 때 아주 유리
• 스캔(Scan) 위주의 액세스 방식 : 양쪽 소스 집합에서 정렬 대상 레코드를 찾는 작업은 인덱스를 이용 Random 액세스 방식으로 처리될 수 있음

해시 조인

작은 집합을 Build Input 으로 하고 큰집합을 probe input 으로 하는 것이 중요

• 대량의 데이터 처리가 필요하고 쿼리 수행 시간이 오래 걸리는 대용량 테이블을 조인할 때(배치 프로그램, DW, OLAP 성 쿼리) 사용
• NL 조인처럼 Random 액세스 부하 없음
• 소트 머지 조인처럼 정렬 부하 없음
• 해시 테이블을 생성하는 비용에 따라서 Build Input 이 (Hash Area 에 담을 수 있을 정도로
  충분히) 작을 때라야 효과적