오라클 인덱스(index) 생성/수정/삭제/리빌드

오라클 인덱스를 생성 또는 수정, 삭제, 리빌드 하는 방법을 알아보도록 하겠습니다.

인덱스(INDEX)란?

오라클 데이터베이스의 인덱스(INDEX)는 테이블에 담겨 있는 데이터를 조회할 때 빠르게 데이터를 조회하기 위한 기능입니다. 데이터가 많아질수록 검색 시 속도저하가 발생하게 되고 인덱스를 통해 검색속도를 향상할 수 있습니다. 인덱스는 또 다른 용어로 색인이라고도 불립니다. 우리가 책을 읽다가 책갈피를 꽂아놓고 다시 읽을 때 거기서부터 책을 읽을 수 있는 것처럼 방대한 데이터에 손쉽게 데이터를 찾아갈 수 있도록 인덱스란 책갈피를 표시해 놓는 것입니다. 인덱스 생성하면 데이터를 저장할 때마다 인덱스 정보를 저장하게 되고 데이터 양도 인덱스가 없을 때보다 증가하게 됩니다. 이때 너무 많은 책갈피를 표시해 놓으면 헷갈리고 찾기가 더 힘들어지겠죠? 인덱스도 마찬가지입니다. 그래서 인덱스도 적절한 사용과 설계가 필요합니다.

인덱스 종류별 사용법

인덱스 종류에는 B-Tree인덱스, Bitmap인덱스, 함수 기반 인덱스, Reverse key인덱스, 결합 인덱스가 있습니다.

B-Tree(Balanced Tree) 인덱스

가장 일반적이고 기본적인 인덱스입니다. 인덱스의 키값을 기반으로 트리구조를 형성하여 데이터를 탐색합니다. 루트 노드를 두고 자식 노드를 가지는 구조입니다. 특정 데이터를 칼럼기준으로 정렬하거나 범위 데이터를 조회할 때 유용한 인덱스입니다.

-- B-Tree인덱스 생성 예제
CREATE INDEX IDX_MEMBER_ID ON CUSTOMER(MEMBER_ID);

-- B-Tree인덱스 이름변경 수정 예제
ALTER INDEX IDX_MEMBER_ID RENAME ON IDX_MEMBER_ID2;

-- B-Tree인덱스 삭제 예제
DROP INDEX IDX_MEMBER_ID;

Bitmap 인덱스

데이터 분포도가 낮은 칼럼에 대해 조회 성능을 향상할 수 있는 인덱스입니다. 특정 칼럼의 값을 비트로 표현하고 각 비트를 하나의 인덱스 엔트리로 사용합니다. 대용량 테이블에서도 효과적이고 특정 값을 빠르게 찾을 수 있습니다. 하지만 DML 작업에서 성능이 떨어지고 인덱스 업데이트 때문에 작업이 지연될 가능성이 있고 특정 중복 데이터가 많은 칼럼에서는 인덱스의 크기가 커질 수 있으며 메모리도 많이 사용할 수 있습니다. 그래서 이 인덱스는 특정 상황에서만 사용하는 걸 권장합니다.

-- Bitmap인덱스 생성 예제
CREATE BITMAP INDEX IDX_MEMBER_ID ON CUSTOMER(MEMBER_ID);

-- Bitmap인덱스 이름변경 수정 예제
ALTER INDEX IDX_MEMBER_ID RENAME ON IDX_MEMBER_ID2;

-- Bitmap인덱스 삭제 예제
DROP INDEX IDX_MEMBER_ID;

함수 기반 인덱스

함수기반 인덱스는 인덱스 생성 시 함수의 결과를 가지고 인덱스를 생성하는 방식을 말합니다. 예를 들어 칼럼의 타입이 날짜(DATE) 타입이고 파라미터로 비교하는 데이터가 YYYYMM형태의 String 문자열로 넘어온다고 가정하겠습니다. 이 경우 YYYYMM의 데이터를 DATE 타입으로 변경해서 조회할 수도 있지만 칼럼타입이 날짜인 칼럼에 함수기반 인덱스를 추가해서 데이터 조회 시 성능을 향상할 수도 있습니다. 함수기반 인덱스를 사용할 때는 데이터타입이나 값, 매개변수 등 정확한 값이 비교될 수 있게 고려를 잘해서 인덱스를 지정하여야 합니다. 복잡한 함수를 사용하는 경우 오히려 성능이 저하가 될 수 있다는 점도 유의해서 사용하여야 합니다.

-- 함수 기반 인덱스 생성 예제
CREATE INDEX IDX_INS_DATE ON CUSTOMER(TO_CHAR(INS_DATE, 'YYYYMM'));

-- 함수 기반 인덱스 사용 예제
-- 인덱스의 경우 컬럼의 변경이 일어나면 INDEX가 타지 않는데 함수 기반 인덱스는 
-- 지정한 타입의 함수형태로 조회해야 인덱스가 제대로 탑니다.
SELECT *
  FROM CUSTOMER
 WHERE TO_CHAR(INS_DATE, 'YYYYMM') = '202303';

-- 함수 기반 인덱스 이름변경 수정 예제
ALTER INDEX IDX_INS_DATE RENAME ON IDX_INS_DATE2;

-- 함수 기반 인덱스 삭제 예제
DROP INDEX IDX_INS_DATE;

Reverse key 인덱스

Reverse key 인덱스는 명칭에서도 알 수 있듯이 키 값을 역순으로 저장하는 인덱스입니다. 이 인덱스는 키값의 분포도가 높을 경우 인덱스 스캔 시 많은 시간이 소요되는데 이를 해결할 수 있는 인덱스입니다. 하지만 Reverse key 인덱스를 생성할 경우 인덱스의 구조가 변경되므로 주의해서 사용해야 합니다.

-- Revers key 인덱스 생성 예제
CREATE INDEX IDX_REV_INS_DATE ON CUSTOMER(INS_DATE) REVERSE;

-- Revers key 인덱스 이름변경 수정 예제
ALTER INDEX IDX_REV_INS_DATE RENAME ON IDX_REV_INS_DATE2;

-- Revers key 인덱스 삭제 예제
DROP INDEX IDX_REV_INS_DATE;

결합 인덱스

결합인덱스는 두 개 이상의 칼럼을 조합해서 만드는 인덱스로 여러 개의 칼럼을 묶어 하나의 인덱스로 생성하는 것을 말합니다. 테이블을 중복키로 생성할 때 결합 인덱스가 자동으로 생성이 되며 사용자가 임의로 자주 사용되는 칼럼을 묶어 결합인덱스로 생성을 할 수 있습니다. 생성 시에는 자주 사용되는 칼럼 순으로 나열을 해서 만드는 게 좋고 너무 많은 칼럼을 결합하는 건 추천하지 않습니다.

-- 결합 인덱스 생성 예제
CREATE INDEX IDX_CUST ON CUSTOMER(INS_DATE, CUST_ID, CUST_NAME);

-- Revers key 인덱스 이름변경 수정 예제
ALTER INDEX IDX_CUST RENAME ON IDX_CUST2;

-- Revers key 인덱스 삭제 예제
DROP INDEX IDX_CUST;

데이터도 방대하고 구조도 복잡하게 사용하는 경우 결합인덱스는 거의 필수라고 생각됩니다. 하지만 이 결합 인덱스는 조회 쿼리에 따라 인덱스를 타지 않는 경우도 발생을 합니다. 어떤 경우가 있는지 예제로 확인해 보겠습니다.

-- 인덱스 생성
CREATE INDEX IDX_CUST ON CUSTOMER(INS_DATE, CUST_ID, CUST_NAME);

-- 결합 인덱스 조회 예제1 (인덱스가 정상적으로 동작합니다.)
SELECT *
  FROM CUSTOMER
 WHERE INS_DATE = TO_DATE(SYSDATE, 'YYYY-MM-DD HH24:MI:SS')
   AND CUST_ID = 'ID1'
   AND CUST_NAME = 'TEST';
   
-- 결합 인덱스 조회 예제2 (인덱스가 정상적으로 동작합니다.)
SELECT *
  FROM CUSTOMER
 WHERE INS_DATE = TO_DATE(SYSDATE, 'YYYY-MM-DD HH24:MI:SS')
   AND CUST_ID = 'TEST';
   
-- 결합 인덱스 조회 예제3 (결합인덱스를 사용하지 못합니다.)
SELECT *
  FROM CUSTOMER
 WHERE CUST_ID = 'ID1'
   AND CUST_NAME = 'TEST';

결합인덱스의 경우 인덱스가 생성된 칼럼의 순서대로 색인정보를 확인합니다. 그래서 예제 1의 경우 INS_DATE, CUST_ID, CUST_NAME 순으로 인덱스를 찾게 됩니다. 예제 2의 경우는 순서대로 INS_DATE, CUST_ID까지 인덱스를 찾아 데이터를 보여줍니다. 하지만 예제 3의 경우 결합인덱스의 시작인 INS_DATE가 검색조건으로 존재하지 않기 때문에 이경우 IDX_CUST의 결합인덱스를 이용할 수가 없습니다. 이처럼 인덱스에 칼럼이 포함은 되어 있지만 순서를 생각하지 않고 검색을 하게 된다면 인덱스가 있는데도 불구하고 전체데이터 검색(FULL TABLE SCAN)이 될 수 있습니다.

인덱스 리빌드

인덱스 리빌드는 말 그대로 생성된 인덱스를 새로 색인시키는 작업입니다. 데이터베이스를 사용하다 보면 불가피하게 데이터 복구를 하는 상황도 생길 수 있고 많은 데이터를 마이그레이션 해야 하는 과정도 생깁니다. INSERT, UPDATE, DELETE가 너무 빈번하게 일어나고 플래그먼트가 많이 발생하게 되면 인덱스가 생성이 되어 있어도 데이터 조회에 상당시간 느려질 수 있습니다. 이럴 때 인덱스 리빌드를 통해 블록을 다시 정렬할 수 있게 됩니다. 오라클은 쿼리를 실행하기 전에 옵티마이저가 실행계획을 생성하는데 실행계획에 따라 조회 성능차이가 많이 발생할 수 있습니다. 인덱스 리빌드를 하는 경우 실행계획이 변경되는 경우도 발생할 수 있으니 상황을 잘 고려해서 진행해야 합니다. 리빌드를 진행하는 동안에는 인덱스를 사용할 수 없으므로 인덱스를 사용하는 쿼리를 중지시키고 진행해야 합니다. 아래는 인덱스 리빌드 절차입니다.

-- 1. 인덱스를 더이상 사용하지 못하도록 비활성화 합니다.
ALTER INDEX IDX_EMP OFFLINE;

-- 2. 인덱스 리빌드를 진행합니다.
ALTER INDEX IDX_EMP REBUILD;

-- 3. 인덱스를 다시 사용할 수 있게 활성화 해줍니다.
ALTER INDEX IDX_EMP ONLINE;

인덱스 리빌드할 때 테이블 크기에 따라 많은 시간이 소요될 수 있습니다. DATABASE 설정에 따라 PARALLEL옵션을 지정할 수 있는데 인덱스 리빌드시 병렬로 처리를 할 수 있습니다.

-- Parallel옵션 사용유무 확인하기(결과가 TRUE인경우 Parallel 사용가능)
SELECT * FROM v$option WHERE parameter = 'Parallel';

-- Parallel옵션 사용해서 인덱스 REBUILD하기
-- 병렬 프로세스 4개를 지정해 인덱스 리빌드를 합니다.
ALTER INDEX INS_CUST REBUILD PARALLEL 4;

지금까지 인덱스에 대해 알아봤습니다. 많은 도움 되셨길 바라요