인덱스 생성

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

ex2_10 테이블의 col11 컬럼에 인덱스를 만들어 보자.

입력

    CREATE UNIQUE INDEX ex2_10_ix01
    ON ex4210 (col11);

결과

    unique index EX2_10_IX01이(가) 생성되었습니다.

UNIQUE 키워드를 붙여 UNIQUE 인덱스가 만들어 졌는데, 이는 col11값에 중복 값을 허용하지 않는다는 뜻이다. 인덱스가 생성되면 user_indexes 시스템 뷰에서 내역을 확인해 볼 수 있다.

입력

    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

앞에서 제약조건을 설명하면서 UNIQUE에 대해서도 간단히 언급했었는데, 중복 값을 허용하지 않는다는 측면에서 이 제약조건과 UNIQUE 인덱스는 같은 역할을 한다. 따라서 별도로 UNIQUE 인덱스를 생성하지 않아도 UNIQUE 제약조건을 만들면 오라클은 자동으로 UNIQUE 인덱스를 생성해 준다. 더불어 기본키를 생성해도 오라클은 자동으로 UNIQUE 인덱스를 생성해 준다. 이때 생성되는 인덱스명은 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

 

또한, 한 개 이상의 컬럼으로 인덱스를 만들 수 있는데, 이를 결합 인덱스라고 한다.

입력

    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

col11과 col2 컬럼으로 NON-UNIQUE 인덱스가 만들어졌음을 확인할 수 있다. 인덱스는 테이블에 있는 데이터를 빨리 찾기 위한, 즉 조회 성능을 높이려는 목적에서 만들어 졌고 인덱스 자체에 키와 매핑 주소 값을 별도로 저장한다. 따라서 테이블에 데이터를 입력하거나 삭제, 수정할 때 인덱스에 저장된 정보도 이에 따라 생성, 수정이 이루어진다. 그러므로 인덱스를 너무 많이 만들면 SELECT 외에 INSERT, DELETE, UPDATE 시 성능에 부하가 뒤따른다. 인덱스를 생성할 때 고려해야 할 사항을 정리하면 다음과 같다.

❶ 일반적으로 테이블 전체 로우 수의 15%이하의 데이터를 조회할 때 인덱스를 생성한다
물론 15%는 정해진 것은 아니며 테이블 건수, 데이터 분포 정도에 따라 달라진다.

❷ 테이블 건수가 적다면(코드성 테이블) 굳이 인덱스를 만들 필요가 없다
데이터 추출을 위해 테이블이나 인덱스를 탐색하는 것을 스캔(scan)이라고 하는데, 테이블 건수가 적으면 인덱스를 경유하기보다 테이블 전체를 스캔하는 것이 빠르다.

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

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

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

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

인덱스는 스캔 성능을 극대화하기 위해 만든 객체인데, 너무 많이 만들면 INSERT, DELETE, UPDATE 시에 부하가 발생해 배보다 배꼽이 더 큰 결과를 초래한다.