[ORACLE] INDEX 기본 구조와 사용

인덱스란?

인덱스는 테이블이나 클러스트에서 쓰어지는 선택적인 객체로서, 오라클 데이터베이스 테이블 내의 원하는 레코드를 빠르게 찾아갈 수 있도록 만들어진 데이터 구조이다.
일종의 색인 기술로써 테이블에 index를 생성하게 되면 index table을 생성해 관리한다. index 생성 시 아무 옵션 없이 default로 생성하면 B-Tree index가 생성된다.

- 자동 인덱스 : 프라이머리 키 또는 UNIQUE 제한 규칙에 의해 자동적으로 생성되는 인덱스
                    가장 기본적인 B-Tree 인덱스로 인덱스 키 (인덱스로 만들 테이블의 컬럼 값)
                    + 키에 해당하는 컬럼값을 가진 테이블 로우가 저장된 주소로 구성

- 수동 인덱스 : CREATE INDEX 명령을 직접 실행하여 만드는 인덱스

인덱스는 언제 사용할까?

1. 구별되는 값이 많은 칼럼

PRIMARY KEY로 지정되는 칼럼에는 UNIQUE한 INDEX가 생성된다. 검색하려는 모든 데이터가 고유한 값 이라면, INDEX 구조 내에서도 중복되는 데이터 확인 필요 없이 가장 최적화되어 있는 상태.

2. WHERE 절에서 자주 조회되는 칼럼

WHERE절에서 사용이 안된다면 굳이 INDEX를 만들어줄 필요가 없다. STORAGE만 차지하고 사용도 안 한다면 없애주어야 한다. 중복되는 데이터가 있다고 하더라도 자주 WHERE절의 조건으로 사용되는 칼럼이라면 INDEX SCAN이 효율적이다.

3. 큰 테이블에서 적은 데이터가 필요할 때

위 그림의 재직상태 예시에서 테이블에는 재직 상태에 대한 칼럼이 있는데 이 데이터들 중 재직이 90% 이상을 차지하고, 나머지 상태가 상대적으로 매우 작은 비중을 차지했을 때 INDEX에는 NULL값이 들어갈 수 없다는 특징을 사용할 수 있다. INDEX에는 NULL값이 들어갈 수 없음 재직 상태에 INDEX 칼럼을 사용하기 위해 가장 비율을 많이 차지하고 있는 "재직"을 NULL로 INSERT하고 나머지 상태는 따로 구분한다.

이렇게 데이터를 입력해주면, FULL SCAN으로 검색했더라면 100건 모두 탐색했어야 하는데 INDEX를 활용해서 5 건의 데이터에 대한 INDEX SCAN을 진행하게 된다.

인덱스 사용

1.인덱스 생성

-- 문법
CREATE INDEX [인덱스명] ON [테이블명] (컬럼1, 컬럼2, 컬럼3........)

--예제
CREATE INDEX EX_INDEX ON CUSTOMERS(NAME,ADDRESS);

--예제 컬럼 중복 X
CREATE[UNIQUE] INDEX EX_INDEX ON CUSTOMERS(NAME,ADDRESS);

*UNIQUE 옵션: 인덱스로 설정하는 칼럼 값에 중복 값을 허용하지 않음

2. 인덱스 조회

SELECT * FROM USER_INDEXES WHERE TABLE_NAME = 'CUSTOMERS';

- 인덱스는 USER_INDEXES 시스템 뷰에서 조회할 수 있음

3. 인덱스 삭제

--문법
DROP INDEX [인덱스 명]

- 조회 성능을 높이기 위해 만든 객체지만 저장공간을 많이 차지하여 DDL작업(INSERT, DELETE, UPDATE) 시 부하가 많이 발생해 전체적인 데이터베이스 성능을 저하시킨다. DBA는 주기적으로 INDEX를 검토하여 사용하지 않는 인덱스를 삭제하는 것이 데이터베이스 전체 성능을 향상 시킬 수 있다.

4. 인덱스 리빌드

- 생성된 인덱스는 기본적으로 ROOT, BRANCH, LEAF로 구성된 트리 구조를 가지며 DDL 작업이 오랜시간 발생하면 트리의 하위 레벨이 많아져 트리 구조의 한쪽이 무거워지는(깊어지는) 현상이 생긴다. 이러한 현상은 인덱스의 검색 속도를 저하시키고 전체 데이터베이스 성능에 영향을 미친다. 그러므로 주기적으로 INDEX를 리빌딩하는 작업을 해주어야 한다.

- 성능에 영향을 미치는 INDEX 조회

SELECT I.TABLESPACE_NAME,I.TABLE_NAME,I.INDEX_NAME, I.BLEVEL, DECODE(SIGN(NVL(I.BLEVEL,99)-3),1,DECODE(NVL(I.BLEVEL,99),99,'?','Rebuild'),'Check') CNF 
FROM USER_INDEXES I 
WHERE I.BLEVEL > 4 
ORDER BY I.BLEVEL DESC

 

--인덱스 리빌드

--문법
ALTER INDEX [인덱스명] REBUILD;

--예제
ALTER INDEX EX_INDEX REBUILD;

 

- 리빌딩 해야하는 인덱스가 많아 일일이 리빌드를 해주기 힘든 경우 USER_INDEXES 딕셔너리에 있는 인덱스를 조회하여 인덱스 리빌드 쿼리를 만들어 한번에 전체 리빌딩을 실행하는 방법이 있다.

-- 전체 인덱스 리빌드 쿼리문
SELECT 'ALTER INDEX ' ||INDEX_NAME||' REBUILD; 'FROM USER_INDEXES;

 

인덱스를 사용하는 것이 무조건 좋은가?

- INDEX가 없는 경우 INDEX를 생성하는 칼럼에 따라 full scan이 더 좋은 효율을 가질 수도 있다. 데이터 건 수에 따라 소량의 테이블을 조회하는 경우, INDEX를 타는 것보다 full scan이 더 유리할 수 있다.

- 읽어들이는 블록의 개수뿐 아니라 I/O 횟수도 고려해야 한다. index scan 은 각 row를 블록 단위로 읽으므로 I/O 단위는 1블록이 되며 full scan의 경우 모두 다 읽어야 하지만 DB_FILE_MULTIBLOCK_READ_COUNT 파라미터 설정을 통해 한번에 여러 블록을 읽어 index scan보다 full scan이 더 효율적일 수 있다.

- 현재까지 여러 오라클 버전이 나오면서 Optimizer 성능 또한 좋아져 오라클 서버가 스스로 실행계획을 비교, 판단하여 유리한 작업으로 진행한다. 따라서 모든 컬럼에 index를 만들어주는 것이 좋은가?

*index를 모든 컬럼에 대해 추가하면 조회 성능은 좋아지겠지만 index는 select 효율을 극대화시키는 목적으로 사용되는 객체이다. 따라서 DDL 작업이 자주 발생하면 index도 함께 수정되어야 하므로 데이터 베이스 효율이 급격히 하락할 수 있다. (index가 없으면 그냥 작업이 가능하지만 index가 있으면 정렬되어있는 row들을 DDL 작업 후 새로 데이터를 맞춰 수정하는 작업이 수반된다.)

* 또한 index는 하나의 오브젝트 객체로써 저장공간을 필요로 한다. 따라서 select절의 성능 향상을 위해 index를 어느 칼럼을 사용해야 하는 index 칼럼 선정 작업 역시 중요하다.