[ORACLE] INDEX 관리 및 유지 보수

인덱스 구조

- INDEX는 ROOT, BRANCH, LEAF로 구성된 계층 구조를 가지며 Optimizer가 데이터베이스의 환경과 실행계획을 보고 스스로 판단하여 쿼리를 수행한다. 오라클 서버가 쿼리 실행 시 INDEX SCAN이 유리하다고 판단되면 생성된 INDEX 트리의 ROOT 부터 조회한다.
ROOT는 BRANCH BLOCK 시작 정보를 가지고 있으며 BRANCH는 LEAF BLOCK의 시작 정보를 가지고 있다. 트리를 타고 내려가며 LEAF에서 해당 데이터의 고유 ROWID를 찾아 그에 해당하는 데이터를 조회한다.

ROWID SELECT 예시

ROWID 구조

- 모든 테이블에는 ROWID라는 칼럼이 있으며 ROWID = FILE_NO + BLOCK_NO + ROW_NO로 구성된다. ROWID는 해당 데이터의 주소 의미를 가지며 INDEX는 이 ROWID를 통해 DATA BLOCK에 접근한다. INDEX는 데이터를 빠르게 검색하기 위해 데이터베이스의 조회 성능을 높여주는 것으로 오름차순으로 정렬된 데이터의 주소라고 할 수 있다.

- DDL 명령(INSERT, UPDATE, DELETE)이 지속적으로 발생하는 테이블은 INDEX의 크기도 지속적으로 늘어난다.

- INDEX의 특성 중 데이터가 INSERT 될 때, 테이블과 달리 새로 입력되는 데이터가 들어가는 자리가 정해져 있다. 만약 들어가야할 블럭에 이미 다른 데이터로 꽉 차 있는 경우, 데이터를 잘라 새로운 LEAF 블록에 절반을 저장한다.

- 새로운 LEAF 블록 생성시 한 건을 분리하는 것이 아닌 절반을 분리하는 이유는 새로 들어올 데이터들의 자리를 마련하기 위함이다. 이런 작업이 지속적으로 발생하면 LEAF BLOCK의 수는 지속적으로 증가하고, 그로 인해 작업시 마다 생기는 LEAF BLOCK을 메모리에 올렸으나 아무 데이터도 없는 LEAF BLOCK이 생겨날 수도 있어 메모리 성능에도 영향을 미친다.

- 이런 이유들로 데이터베이스 성능을 위해 사용되는 인덱스는 지속적으로 유지보수/ 관리하는 작업이 필요하며 DBA가 이런 인덱스들을 재구성하는 튜닝작업이 필요하다.