i-hope devlog

Oracle 12c 데이터베이스 11 - 검색성능개선을 위한 INDEX

  • 2021년 06월 23일

💡 본 글은 ‘실습과 함께하는! 데이터베이스 Oracle 12c’ 강의 내용을 바탕으로 정리하였습니다.

인덱스(INDEX) 개념

  • 검색을 빠르게 하기 위한 자료구조 (주로 B트리 계열)
    • 레코드 추가, 수정, 삭제 시 해당 컬럼으로 다시 정렬한 후 검색에 필요한 값들을 미리 계산해 검색 속도를 높임
    • 검색보다 추가, 삭제가 빨라야 한다면? 인덱스를 사용하지 않는 편이 나음
  • 기본키는 자동으로 인덱스 설정
  • 인덱스 On/Off를 통해 검색 속도 차이 체감
  • 조인(JOIN) 시에도 영향을 줌
  • RDBMS의 핵심 기능

정렬

  • 빠른 검색을 위해서는 데이터가 반드시 정렬되어 있어야 함
  • 정렬되어 있지 않다면 평균적으로 전체 데이터의 절반 필요 (최악: N, 평균: N/2)
  • 정렬되어 있다면 데이터를 빠른 시간 안에 찾을 수 있음 (O(NlogN) - O(N^2))
  • 퀵 정렬/ 힙 정렬 계열을 주로 사용

인덱스 종류

  • 최대 log2(N)번 내에 검색 가능
  • 한 가운데 값을 확인한 후 크고 작은지 비교하여 절반을 다시 탐색
  • 데이터가 추가, 삭제, 변경될 때마다 한 가운데 / 왼쪽 / 오른쪽 가운데 값 등을 미리 계산해 놓음 → 인덱스라고 통칭함

📌 데이터 추가, 수정, 삭제 시 정렬 및 인덱스 업데이트

B-트리

  • 이진 탐색과 유사하지만 한 번에 두 번 비교 (a, b : a < b)
    • x < a
    • a < x < b
    • x > b

📌 데이터 추가, 수정, 삭제 시 a, b 값 업데이트

인덱스 설정

자동 생성

  • 기본키, 외래키

인덱스 종류

  • 고유(Unique) 인덱스
  • 비고유(Non-unique) 인덱스: 기본적으로는 비고유 인덱스로 설정됨
  • 단일인덱스
  • 복합인덱스
  • 함수기반 인덱스

인덱스 추가 및 삭제

  CREATE INDEX 인덱스명 ON 테이블명(컬럼명)
  CREATE INDEX 인덱스명 ON 테이블명(컬럼명1, 컬럼명2, ...)
  CREATE UNIQUE INDEX 인덱스명 ON 테이블명(컬럼명)
  ALTER TABLE 테이블명 DROP INDEX 인덱스명
  ALTER INDEX 인덱스명 REBUILD

인덱스 확인

  • user_indexes, user_ind_columns 테이블 확인
  SELECT index_name, index_type, table_name FROM user_indexes WHERE table_name in (테이블명1, 테이블명2)
  SELECT index_name, table_name, column_name FROM user_idn_columns WHERE table_name in (테이블명1, 테이블명2)

Visibility 인덱스

  • 오라클에만 있음
  SELECT index_name, index_type, visibility FROM user_indexes WHERE table_name in (테이블명1, 테이블명2)

Invisible 인덱스

  • 인덱스가 필요 없을 때 삭제하지 않고 Invisible 상태로 전환 가능
  • 삭제 전 Invisible 상태로 전환 후 확인 가능
  ALTER INDEX 인덱스명 INVISIBLE;
검색