[Oracle] 오라클 인덱스(Index) 개념과 활용 방법

인덱스는 SQL 문장의 처리 속도를 높이기 위해 테이블의 특정 컬럼에 생성하는 오라클 객체입니다. 인덱스를 사용하면 검색을 더욱 빠르게 수행할 수 있지만, 모든 경우에 적합하지는 않습니다. 이번 포스팅에서는 인덱스를 언제, 어떻게 사용해야 하는지, 그리고 인덱스의 다양한 유형과 관리 방법을 소개합니다.

 

1. 인덱스를 사용하는 경우

인덱스를 활용하면 조회 성능이 크게 향상되지만, 테이블이 자주 수정되는 경우에는 오히려 성능이 저하될 수 있습니다. 다음은 인덱스를 사용하는 것이 유리한 경우입니다.

• 테이블에 **행(row)**이 많을 때
• WHERE 절에서 자주 사용되는 컬럼에 인덱스를 걸 때
• 검색 결과가 전체 데이터의 2% ~ 4% 정도로 적은 양일 때
• JOIN 절에 자주 사용되는 컬럼일 때
• NULL을 포함하지 않는 컬럼일 때

 

2. 인덱스를 사용하지 말아야 할 경우

다음과 같은 경우에는 인덱스를 사용하지 않는 것이 좋습니다.

• 테이블에 행(row) 수가 적을 때
• WHERE 절에서 특정 컬럼이 자주 사용되지 않을 때
• 검색 결과가 전체 데이터의 10% ~ 15% 이상일 때
• 테이블에 DML 작업(입력, 수정, 삭제)이 빈번하게 발생할 때

 

3. 인덱스 관련 딕셔너리 뷰

오라클에서는 사용자가 생성한 인덱스와 인덱스가 걸린 컬럼들을 조회할 수 있는 딕셔너리 뷰를 제공합니다.

• USER_INDEXES: 인덱스 이름 및 고유성 정보 포함
• USER_IND_COLUMNS: 인덱스 이름, 테이블 이름, 컬럼 이름 정보 포함

 

4. 인덱스 관리하기

인덱스를 생성, 제거, 재생성하는 기본 명령어와 인덱스를 조회하는 방법입니다.

인덱스 생성

특정 컬럼에 인덱스를 생성하여 검색 성능을 향상시킵니다.

CREATE INDEX 인덱스명 ON 테이블명(컬럼명);
-- 예시
CREATE INDEX idx_emp_ename ON emp(ename);

인덱스 제거

필요 없는 인덱스는 제거하여 불필요한 데이터 관리 비용을 줄일 수 있습니다.

DROP INDEX 인덱스명;
-- 예시
DROP INDEX idx_emp_ename;

인덱스 재생성

효율성이 떨어진 인덱스는 재생성하여 성능을 최적화할 수 있습니다.

ALTER INDEX 인덱스명 REBUILD;
-- 예시
ALTER INDEX idx_emp_ename REBUILD;

인덱스 조회

현재 사용자가 가진 인덱스를 조회할 수 있습니다.

SELECT object_name, object_type
FROM user_objects
WHERE object_type = 'INDEX';

 

5. 인덱스의 종류

오라클 인덱스는 다양한 형태로 나뉘며, 각 상황에 따라 적합한 인덱스를 선택하여 성능을 최적화할 수 있습니다.

1) Unique Index (고유 인덱스)

• 유일한 값을 가지는 컬럼에 대해 생성하며, 중복된 값을 허용하지 않습니다.

2) NonUnique Index (비고유 인덱스)

• 중복된 데이터를 허용하며, 데이터의 중복을 방지할 필요가 없는 경우 사용합니다.

3) Single Index (단일 인덱스)

• 하나의 컬럼에 대해 인덱스를 생성합니다.

4) Composite Index (결합 인덱스)

• 여러 개의 컬럼에 대해 하나의 인덱스를 생성합니다.
• 예시:

CREATE INDEX ind_dept_com ON dept(deptno, loc);

5) Function-Based Index (함수 기반 인덱스)

• 컬럼의 값을 변형하거나 특정 표현식을 사용하는 경우 유용한 인덱스입니다.
• 예시:

CREATE INDEX ename_upper_ix ON employees(UPPER(ename));

 

6. 인덱스 활용 예시

다양한 조건에 따라 인덱스를 활용하는 예시입니다.

1) 특정 형식의 컬럼 검색

컬럼 중간의 특정 부분을 검색해야 하는 경우:

CREATE INDEX from_loc_idx ON orders(SUBSTR(ship_id, 5, 3));

2) 조인 컬럼이 함수나 연산식을 사용하는 경우

조인 조건을 충족시키기 위해 결합 인덱스를 생성할 수 있습니다.

CREATE INDEX group_cd_idx ON item_group(class1 || class2 || class3);

3) NULL 값을 치환하여 검색하는 경우

NULL 값을 특정 값으로 치환한 후 검색하는 경우에도 인덱스를 생성할 수 있습니다.

CREATE INDEX end_date_idx ON account_history(NVL(end_date, '99991231'), start_date);

 

7. 인덱스를 타지 않는 경우

다음과 같은 상황에서는 인덱스가 활용되지 않으며, 효율적인 검색이 어려울 수 있습니다.

• 인덱스 컬럼에 변형을 가한 경우
• 데이터 타입 변환이 필요한 경우
• NULL 조건을 사용하는 경우
• 부정형 조건을 사용하는 경우 (!=, NOT IN 등)
• LIKE 연산자를 %로 시작하는 경우 (예: '%S%')

 

8. 최적화 힌트(Optimizer Hint) 사용하기

특정 인덱스를 사용하도록 오라클 최적화에 힌트를 줄 수 있습니다. 예시로 INDEX 힌트를 사용한 쿼리는 아래와 같습니다.

SELECT /*+ INDEX(e1 e_job) */ * 
FROM emp e1 
WHERE job = 'CLERK';