최적의 인덱스를 만들기위한 조건
- 인덱스의 물리적 구조 이해
- 복잡한 SQL분석
- 만들어진 인덱스가 어떻게 사용될지 예측하는 능력
- 테이블 내 데이터 속성을 파악하는 능력
- JOIN의 내부적인 처리방법(NL, MERGE, HASH)의 이해
1. 인덱스의 기본개념
1.1 인덱스란?
-
테이블내 데이터를 찾을수있게 일부 데이터를 모아서 구성한 데이터 구조
-
인덱스를 이용하면 테이블내 데이터 조회성능 향상 시킬수있다.
-
통계정보 생성 명령어
올바른 성능 테스트를 위해서는 통계정보를 반드시 생성해주어야 한다.EXEC DBMS_STATS.GATHER_TABLE_STATS('테이블소유계정','테이블명');
1.2 인덱스의 종류
-
단일인덱스 : 인덱스에 하나의 컬럼만 사용
-
복합인덱스 : 인덱스에 두 개 이상의 컬럼을 사용
-
유니크 인덱스 : 인덱스 구성 컬럼들 값에 중복을 호용하지않는다
-
비 유니크 인덱스(Non-unique index):인덱스 구성 컬럼들 값에 중복을 허용한다.
-
물리적구조에 따라 B*트리인덱스, 비트맵인덱스로 분류
비트맵인덱스는 값의 종류가 많지 않은 컬럼에 사용 -
대용량 테이블에는 파티션을 구성하는것이좋다.
- 글로벌 인덱스
- 로컬 인덱스1.3 B*트리 구조와 탐색 방법
-
루트 블록 : 최상위에 단하나만 존재, 하위 브랜치블록의 인덱스 키값과 주소를 가지고있음
-
브랜치 블록 : 루트와 리프 중간에 위치, 여러층이 있을수있음, 하위브랜치의 인덱스키값과 주소 또는 하위 리프의 키값과 주소를 가지고 있다.
-
리프 블록 : 최하위에만 존재, 인덱스키값과 데이터의 ROWID를 가지고있다. 리프블록은 인덱스 키값 순으로 정렬되어있다.
1.4 데이터를 찾는방법
- 오라클에서 데이터를 찾는 방법은 아래 세가지가있다
- 테이블 전체읽기 (TABLE FULL SCAN)
- 인덱스를 이용한 찾기 (INDEX RANGE SCAN & TABLE ACCESS BY INDEX ROWID)
- ROWID를 이용한 직접찾기 (TABLE ACCESS BY INDEX ROWID)
1.5 TABLE FULL SCAN
- 테이블 풀 스캔이나 풀스캔, TABLE ACESS FULL 등으로 표현
- 찾고자하는 조건에 활용할 인덱스가 없거나 인덱스보다 테이블전체읽기가 효율적일때 사용
- 데이터가 테이블에 저장될때는 특정 순서를 가지지않는다(정렬이 되어있지 않다.)
- 찾아야할 데이터가많으면 FULL SCAN이, 적을수록 인덱스활용이 효율이 좋다
1.6 인덱스를 이용한 찾기
- INDEX RANGE SCAN, INDEX SKIP SCAN, INDEX FULL SCAN, INDEX FAST FULL SCAN
- INDEX RANGE SCAN : 기본이되는 방식이며 루트블록부터 L자 형태로 접근한다.
- 인덱스 루트블록부터 리프블록 시작점까지 찾기 > 리프블록 순차검색 > 조건에맞는 인덱스의 ROWID로 테이블접근(TABLE ACCESS BY INDEX ROWID)
1.7 INDEX RANGE SCAN VS TABLE ACCESS FULL
- 랜덤액세스 : I/O작업 한번에 하나의 블록을 가져오는 접근방법, ROWID를 이용해 테이블접근할때 랜덤엑세스 발생
- 적은양의 데이터를 읽을경우 INDEX RANGE SCAN이 효율적이지만
대용량일경우 싱글블록을찾는 INDEX SCAN보다 멀티블록을사용하는 풀스캔이 효율적 - FULL SCAN은 데이터가 쌓일수록 성능이 나빠진다 테이블 관리 전략이 필요(EX: 파티션테이블등)
이미지 출처
https://hoon93.tistory.com/52
https://jungmina.com/717
늙어가면서 기억을 남기는 개발자