(SQLP SQLD 핵심 정리 9) 인덱스와 조인

INDEX RANGE SCAN

- 인덱스 루트 블록에서 리프 블록까지 수직적으로 탐색한 후에 리프 블록을 필요한 범위(Range)만 스캔하는 방식

 

INDEX SKIP SCAN

- 루트 또는 브랜치 블록에서 읽은 칼럼 값 정보를 이용해 조건에 부합하는 레코드를 포함할 '가능성이 있는' 하위 블록(브랜치 또는 리프 블록)만 골라서 액세스하는 방식

 

INDEX FULL SCAN

- 수직적 탐색없이 인덱스 리프 블록을 처음부터 끝까지 수평적으로 탐색하는 방식

- 데이터 검색을 위한 최적의 인덱스가 없을 때 차선책으로 선택하는 스캔 방법

INDEX UNIQUE SCAN

- 수직적 탐색만으로 데이터를 찾는 스캔방식

- '=' 조건으로 탐색하는 경우

 

INDEX FAST FULL SCAN

- INDEX FULL SCAN 보다 빠르다. 

- 이유는 인덱스 트리 구조를 무시하고 인덱스 세그먼트 전체를 Multi Block Read 방식으로 스캔하기 때문이다.

 

비트맥 인덱스(BITMAP INDEX)

- Lock에 의한 DML 부하가 심한 것이 단점

- 레코드 하나만 변경되더라도 해당 비트맵 범위에 속한 모든 레코드에 Lock이 걸린다.

- OLTP성 환경에 비트맵 인덱스를 쓸 수 없는 이유.

- 읽기 위주의 대용량 DW(특히, OLAP) 환경에 아주 적합한 것이 특징

 

인덱스 설계를 위해 고려해야 할 요소

- 쿼리 수행 빈도

- 업무상 중요도

- 클러스터링 팩터

- 데이터량

- DML 부하(= 기존 인덱스 갯수, 초당 DML 발생량, 자주 갱신되는 칼럼 포함 여부 등)

- 저장 공간

- 인덱스 관리 비용 등

 

결합 인덱스 구성

1. 조건절에 항상 사용되거나, 적어도 자주 사용되는 칼럼들을 선정

2. 그렇게 선정된 칼럼 중 '=' 조건으로 자주 조회되는 칼럼을 앞쪽에 두어야 한다.

3. 소트(Sort) 오퍼레이션을 생략하도록 하기 위해 칼럼을 추가

 

HASH JOIN 성능을 좌우하는 키 포인트

- 한 쪽 테이블이 가용 메모리에 담길 정도로 충분히 작아야 함

- Build Input 해시 키 칼럼에 중복 값이 거의 없어야 함

 

HASH JOIN 선택 기준

- 조인 칼럼에 적당한 인덱스가 없어 NL JOIN이 비효율적 일 때

- 조인 칼럼에 인덱스가 있더라도 NL JOIN 드라이빙 집합에서 INNER 쪽 집합으로의 조인 액세스량이 많아 Random 액세스 부하가 심할 때

- SORT MERGE JOIN하기에는 두 테이블이 너무 커 소트 부하가 심할 때

- 수행빈도가 낮고 쿼리 수행 시간이 오래 걸리는 대용량 테이블을 조인할 때

 

스칼라 서브 쿼리(Scalar Subquery)

- 서브쿼리 중에서 함수처럼 한 레코드당 정확히 하나의 값만 리턴하는 서브쿼리를 말함.

- 주로 select-list에서 사용 됨.

- 몇 가지 예외사항을 제외한다면 칼럼이 올 수 있는 대부분 위치에서 사용이 가능하다.

 

스칼라 서브 쿼리(Scalar Subquery) 특징

- 스칼라 서브쿼리를 이용한 조인은 NL 조인처럼 한 레코드씩 순차적으로 진행한다.

- 메인 쿼리의 레코드마다 스칼라 서브쿼리를 통해 하나의 레코드, 하나의 값만 리턴한다.

- 조인에 실패할 경우, Null을 리턴한다.

 

선분이력

- 예를 들어 고객별연체금액 변경이력을 관리할 때 이력의 시작시점만을 관리하는 것을 '점이력'모델이라고 함

- 시작지점과 종료시점을 함께 관리하는 것을 '선분이력' 보델이라고 한다.

 

LOCAL 파티션 인덱스

- 테이블 파티션과 1:1로 대응되도록 파티셔닝한 인덱스.

- 인덱스 파티션 키를 사용자가 따로 지정하지 않으며, 테이블과 1:1관계를 유지하도록 DBMS가 자동으로 관리해 줌.

- SQL Server에선 '정렬된(aligned) 파티션 인덱스'라고 부른다.

 

Oracle이 지원하는 파티션 유형

1. Range 파티셔닝

  - 파티션 키 값의 범위(Range)로 분할

  - 파티셔닝의 가장 일반적인 형태이며, 주로 날짜 칼럼을 기준으로 함.

 ex) 판매 데이터를 월별로 분할

 

2. Hash 파티셔닝

  - 파티션 키 값에 해시 함수를 적용하고, 거기서 반환된 값으로 파티션 매핑

  - 데이터가 모든 파티션에 고르게 분산되도록 DBMS가 관리

      → 각 로우의 저장 위치 예측 불가

  -  파티션 키의 데이터 분포가 고른 칼럼이어야 효과적

      ex) 고객번호, 주문일련번호 등

  - 병렬처리 시 성능효과 극대화

  - DML 경합 분산에 효과적

 

3. List 파티셔닝

  - 불연속적인 값의 목록을 각 파티션에 지정

  - 순서와 상관없이, 사용자가 미리 정한 그룹핑 기준에 따라 데이터를 분할 저장

      ex) 판매 데이터를 지역별로 분할

 

4. Composite 파티셔닝

  - Range나 List 파티션 내에 또 다른 서브 파티션(Range, Hash, List) 구성

      ex) Range + List 또는 List + Hash 등

  - Range나 List 파티션이 갖는 이점 + 각 서브 파티션 구성의 이점