Oracle - INDEX

INDEX

INDEX란 SQL명령문의 처리 속도를 향상시키기 위해서

컬럼에 대해 생성하는 오라클 객체로 내부 구조는 B*트리(2진트리) 형식으로 구성되어 있다.

 

[ 장점 ]

- 이진트리 형식으로 구성되어 있어 자동 정렬 및 검색 속도가 향상된다.
- 시스템에 걸리는 부하를 줄여 시스템 전체 성능을 향상시켜준다.

 

[ 단점 ]

- 인덱스를 추가하기 위한 별도의 저장 공간이 필요하다.
- 인덱스를 생성하는데 시간이 걸린다.
- 데이터 변경 작업인(DML (INSERT, UPDATE, DELETE))이 빈번하게 발생하는 경우에는 오히려 성능 저하를 초래한다.

 

[ INDEX 표현식 ]

CREATE [UNIQUE] INDEX 인덱스 명
ON 테이블 명(컬럼 명, 컬럼 명 | 함수 명, 함수 계산식);


CREATE UNIQUE INDEX IDX_BOARD_TITLE -- IDX_BOARD_TITLE
ON BOARD(BOARD_TITLE); -- BOARD 테이블의 BOARD_TITLE 컬럼에 고유 인덱스 생성

 

조회하면 이러하다.

SELECT * FROM USER_IND_COLUMNS

INDEX 구조

INDEX의 구조는 이렇게 생겼다.

인덱스 안에 ROWID가 있는데 ROWID는 DB 내 데이터의 공유 주소이다.

 

 

AAAE7U AAB AAALC5 AAA 

-- 1~6: 데이터 오브젝트 번호
-- 7~9: 파일 번호
-- 10~15: BLOCK 번호
-- 16~18: ROW번호

 

 

INDEX 종류

인덱스의 종류에는 고유 인덱스(UNIQUE INDEX), 비고유 인덱스(NONUNIQUE INDEX), 단일 인덱스(SINGLE INDEX)

결합 인덱스(COMPOSITE INDEX), 함수 기반 인덱스(FUNCTION-BASED INDEX) 가 있다.

 

 

고유 인덱스(UNIQUE INDEX)

고유 인덱스란 중복 값이 포함될 수 없고

PRIMARY KEY 제약조건을 생성하면 자동으로 생성된다.

 

[ 고유 인덱스 예시 ]

CREATE UNIQUE INDEX IDX_EMPNAME
ON EMPLOYEE(EMP_NAME);

조회하면 이렇게 나타난다.

SELECT * FROM USER_IND_COLUMNS
WHERE TABLE_NAME = ‘EMPLOYEE’;

* UNIQUE INDEX는 중복 값이 있는 컬럼에 생성 시 에러가 발생한다.

 

 

 

 

비고유 인덱스(NONUNIQUE INDEX)

비고유 인덱스란  빈번하게 사용되는 일반 컬럼을 대상으로 생성 주로 성능 향상을 위한 목적으로 생성된다.

주로 조회 성능 향상을 위해 사용한다.

 

[ 비고유 인덱스 예시 ]

CREATE INDEX IDX_DEPTCODE
ON EMPLOYEE(DEPT_CODE);

조회하면 이렇게 나타난다.

SELECT * FROM USER_IND_COLUMNS
WHERE TABLE_NAME = ‘EMPLOYEE’;

* NONUNIQUE INDEX는 중복 값이 있는 컬럼에도 생성 가능하다.

 

 

결합 인덱스(COMPOSITE INDEX)

결합 인덱스란  두 개 이상의 컬럼으로 구성한 인덱스이다.

 

[ 결합 인덱스 예시 ]

CREATE INDEX IDX_DEPT
ON DEPARTMENT(DEPT_ID, DEPT_TITLE);

조회하면 이렇게 나타난다.

SELECT * FROM USER_IND_COLUMNS
WHERE TABLE_NAME = ‘DEPARTMENT’;

* COMPOSITE INDEX는 두 개 이상의 컬럼을 하나의 인덱스로 생성이 가능하다.

* COLUMN_POSITION의 순서에 의해 성능이 차이날 수 있다.

 

 

함수 기반 인덱스(FUNCTION-BASED INDEX)

함수 기반 인덱스란 SELECT절이나 WHERE절에 산술 계산식이나 함수식이 사용된 경우 

계산식은 인덱스의 적용을 받지 않는다.

 

[ 함수 기반 인덱스 예시 ]

CREATE TABLE EMP_SAL
AS SELECT EMP_ID, EMP_NAME, SALARY, BONUS,
(SALARY + (SALARY + NVL(BONUS, 0)))*12 연봉 -- 영향 받지 않는다.
FROM EMPLOYEE;

 

또다른 예시

CREATE INDEX IDX_SALCALC
ON EMP_SAL ((SALARY + SALARY*NVL(BONUS, 0)))*12); -- 영향 받지 않는다

 

결과는 이러하다.

 

SELECT * FROM USER_IND_COLUMNS
WHERE TABLE_NAME = ‘EMP_SAL’;

 

 

INDEX 재생성

INDEX 재생성이란 DML 작업(특히 DELETE 명령)을 수행한 경우,

해당 인덱스 엔트리가 논리적으로만 제거되고

실제 엔트리는 그냥 남아있게 되므로 제거된 인덱스가

필요 없는 공간을 차지하고 있지 않도록 인덱스를 재생성 해야한다.

 

[ 표현식 ]

ALTER INDEX 인덱스명 REBUILD;