[DB & SQL] SQL 고급활용 및 튜닝 / 인덱스와 조인

SQL 고급활용 및 튜닝 / 인덱스와 조인

 

Index Range Scan

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

 

Index Skip Scan

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

 

Index Full Scan

Index Full Scan은 수직적 탐색없이 인덱스 리프블록을 처음부터 끝까지 수평적으로 탐색하는 방식으로서, 대개는 데이터 검색을 위한 최적의 인덱스가 없을 때 차선으로 선택된다.

 

Index Unique Scan

Index Unique Scan은 수직적 탐색만으로 데이터를 찾는 스캔 방식으로서, Unique 인덱스를 '=' 조건으로 탐색하는 경우에 작동한다.

 

Index Fast Full Scan

Index Fast Full Scan은 Index Full Scan보다 빠르다. Index Fast Full Scan이 Index Full Scan보다 빠른 이유는, 인덱스 트리 구조를 무시하고 인덱스 세그먼트 전체를 Multiblock Read 방식으로 스캔하기 때문이다.

 

비트맵 인덱스

비트맵 인덱스는 Lock에 의한 DML 부하가 심한것이 단점이다. 레코드 하나만 변경되더라도 해당 비트맵 범위에 속한 모든 레코드에 Lock이 거린다. OLTP성 환경에 비트맵 인덱스를 쓸 수 없는 이유가 여기에 있다. 비트맵 인덱스는 읽기 위주의 대용량 DW(특히, OLAP) 환경에 아주 적합하다.

 

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

• 쿼리 수행 빈도
• 업무상 중요도
• 클러스터링 팩터
• 데이터량
• DML 부하(= 기존 인덱스 개수, 초당 DML 발생량, 자주 갱신되는 칼럼 포함 여부 등)
• 저장 공간
• 인덱스 관리 비용 등

 

결합 인덱스 구성

첫 번째 기준은, 조건절에 항상 사용되거나, 적어도 자주 사용되는 칼럼들을 선정

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

세 번째 기준은, 소트 오퍼레이션을 생략하도록 하기 위해 칼럼을 추가

 

Hash Join 성능을 좌우하는 키 포인트

• 한 쪽 테이블이 가용 메모리에 담길 정도로 충분히 작아야 함
• Build Input 해시 키 칼럼에 중복 값이 거의 없어야 함

 

Hash Join 선택 기준

• 조인 칼럼에 적당한 인덱스가 없어 NL Join이 비효율 적일 때
• 조인 칼럼에 NL Join 드라이빙 집합에서 Inner 쪽 집합으로의 조인 액세스량이 많아 Random 액세스 부하가 심할 때
• Sort Merge Join 하기에는 두 테이블이 너무 커 소트 부하가 심할 때
• 수행빈도가 낮고 쿼리 수행 시간이 오래 걸리는 대용량 테이블을 조인할 때

 

서브쿼리 중에서 함수처럼 한 레코드당 정확히 하나의 값만을 리턴하는 서브쿼리를 Scalar Subquery라고 한다. Scalar Subquery는 주로 select-list에서 사용되지만 몇 가지 예외사항을 뺀다면 칼럼이 올 수 있는 대부분 위치에서 사용 가능하다.

 

선분이력이란?

예를 들어 고객별연체금액 변경이력을 관리할 때 이력의 시작시점만을 관리하는 것을 '점이력' 모델이라고 하고, 시작시점과 종료시점을 함께 관리하는 것을 '선분이력' 모델이라고 한다.

 

Local 파티션 인덱스

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

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

 

Oracle이 지원하는 파티션 유형

1.  Range 파티셔닝
   • 파티션 키 값의 범위(Range)로 분할
   • 파티셔닝의 가장 일반적인 형태이며, 주로 날짜 칼럼을 기준으로 함 예) 판매 데이터를 월별로 분할
2. Hash 파티셔닝
   • 파티션 키 값에 해시 함수를 적용하고, 거기서 반환된 값으로 파티션 매핑
   • 데이터가 모든 파티션에 고르게 분산되도록 DBMS가 관리 -> 각 로우의 저장 위치 예측 불가
   • 파티션 키의 데이터 분포가 고른 칼럼이어야 효과적 예) 고객번호, 주문일련번호 등
   • 병렬처리 시 성능효과 극대화
   • DML 경합 분산에 효과적
3. List 파티셔닝
   • 불연속적인 값의 목록을 각 파티션에 지정
   • 순서와 상관없이, 사용자가 미리 정한 그룹핑 기준에 따라 데이터를 분할 저장 예) 판매 데이터를 지역별로 분할
4.  Composite 파티셔닝
   • Range나 List 파티션 내에 또 다른 서브 파티션(Range, Hash, List) 구성 예) Range + List 또는 List + Hash 등
   • Range나 List 파티션이 갖는 이점 + 각 서브 파티션 구성의 이점

 

 

 

 

 

 

 

참조:

SQL자격검정실전문제