[Oracle] Index(인덱스)

 

인덱스(Index)란?

테이블에 있는 데이터를 빨리 찾기 위한 용도의 데이터베이스 객체이다.

조회 성능을 높이려는 목적에서 만들어졌으며, 인덱스 자체에 키와 매핑 주소 값을 별도로 저장한다.

따라서 테이블에 데이터를 입력하거나 삭제, 수정할 때 인덱스에 저장된 정보도 똑같이 작업이 이루어진다.

그래서 인덱스를 너무 많이 만들면 성능에 부하가 뒤따르기때문에 주의해야한다.

(ex. 책 내용 맨 뒤 찾아보기 항목..)

 

인덱스 구성 컬럼 개수에 따른 분류	단일 인덱스 / 결합 인덱스
유일성 여부에 따른 분류	UNIQUE 인덱스 / NON-UNIQUE 인덱스
인덱스 내부 구조에 따른 분류	B-tree 인덱스 / 비트맵 인덱스 / 함수 기반 인덱스

 

인덱스 구성 컬럼 개수에 따른 분류와 유일성 여부에 따른 분류는 형태와 속성에 따른 분류이며,

내부 구조에 따른 분류는 내부 알고리즘에 따른 분류로 보면 된다.

이 외에도 해시 클러스터 인덱스, 리버스 키 인덱스, 비트맵 조인 인덱스, 도메인 인덱스, 파티션 인덱스가 있다.

인덱스는 테이블에 있는 한 개 이상의 컬럼으로 만들 수 있다.

표준인 B-tree 인덱스는 인덱스 키(인덱스로 만들 테이블의 컬럼 값)와 이 키에 해당하는 컬럼 값을 가진 테이블의 로우가 저장된 주소 값으로 구성된다.

 

---

0. 인덱스 생성시 고려해야할 사항

  1) 일반적으로 테이블 전체 로우 수의 15% 이하의 데이터를 조회할 때 인덱스를 생성한다.

  2) 테이블 건수가 적다면(코드성 테이블) 굳이 인덱스를 만들 필요가 없다. -> 인덱스 경유보다 스캔하는 것이 빠르다.

  3) 데이터의 유일성 정도가 좋거나 범위가 넓은 값을 가진 컬럼을 인덱스로 만드는 것이 좋다.

  4) NULL이 많이 포함된 컬럼은 인덱스 컬럼으로 만들기 적당치 않다.

  5) 결합 인덱스를 만들 때에는 컬럼의 순서가 중요하다. -> 자주 사용되는 컬럼을 순서상 앞에 두는 것이 좋다.

  6) 테이블에 만들 수 있는 인덱스 수의 제한은 없으나, 너무 많이 만들면 성능 부하가 발생한다.

 

1. 인덱스 생성

-- 인덱스 생성
CREATE[UNIQUE] INDEX [스키마명.]인덱스명
ON [스키마명.]테이블명(컬럼1, 컬럼2, ...);

-- UNIQUE 키워드를 넣은 인덱스 생성
CREATE UNIQUE INDEX ex2_10_ix01
ON ex4210 (col11);

 

2. 인덱스 조회

별도로 UNIQUE 인덱스를 생성하지 않아도 UNIQUE 제약조건을 만들면 오라클은 자동으로 UNIQUE 인덱스를 생성해 준다. 더불어 기본키를 생성해도 오라클은 자동으로 UNIQUE 인덱스를 생성해 준다.

이때 생성되는 인덱스명은 UNIQUE나 기본키 객체명과 동일하게 생성된다.

-- 인덱스 조회 (입력)

SELECT index_name, index_type, table_name, uniqueness
FROM user_indexes
WHERE table_name = 'EX2_10';


-- 인덱스 조회 (결과)

INDEX_NAME  INDEX_TYPE TABLE_NAME UNIQUENESS
----------- ---------- ---------- ----------
EX2_10_IX01 NORMAL     EX2_10     UNIQUE

 

-- 제약조건 조회 입력

    SELECT constraint_name, constraint_type, table_name, index_name
      FROM user_constraints
     WHERE table_name = 'JOB_HISTORY';

-- 제약조건 조회 결과

    CONSTRAINT_NAME CONSTRAINT_TYPE TABLE_NAME   INDEX_NAME
    --------------- --------------- ------------ ---------------
    SYS_C0011070    C               JOB_HISTORY
    SYS_C0011071    C               JOB_HISTORY
    PK_JOB_HISTORY  P               JOB_HISTORY  PK_JOB_HISTORY
    
    
    
-- 인덱스 조회 입력

    SELECT index_name, index_type, table_name, uniqueness
      FROM user_indexes
     WHERE table_name = 'JOB_HISTORY';
     
-- 인덱스 조회 결과

    INDEX_NAME     INDEX_TYPE TABLE_NAME  UNIQUENESS
    -------------- ---------- ----------- ----------
    PK_JOB_HISTORY NORMAL     JOB_HISTORY UNIQUE
 

 

3. 결합 인덱스

-- 입력

    CREATE INDEX ex2_10_ix02
    ON ex2_10 (col11, col2);
    
-- 결과

    index EX2_10_IX02이(가) 생성되었습니다.
    
    
-- 입력

    SELECT index_name, index_type, table_name, uniqueness
      FROM user_indexes
    WHERE table_name = 'EX2_10';
    
-- 결과

    INDEX_NAME   INDEX_TYPE  TABLE_NAME UNIQUENESS
    ------------ ----------- ---------- -----------
    EX2_10_IX02  NORMAL      EX2_10     NONUNIQUE
    EX2_10_IX01  NORMAL      EX2_10     UNIQUE

 

4. 인덱스 삭제

-- 인덱스 삭제
DROP INDEX [스키마명.]인덱스명;
DROP INDEX ex2_10_ix02;

 

 

---

출처 및 참고 : thebook.io/006696/