Oracle - INDEX

INDEX

인덱스는 검색 연산을 최적화하기 위해 데이터베이스상의 로우들의 정보를 구현하는 데이터 구조

인덱스를 사용하면 전체 데이터를 검색하지 않고 데이터베이스에서 원하는 정보를 빠르게 검색할 수 있음

 

인덱스 내부 구조는 B* 트리 형식으로 구성됨

 

장점

트리 형식으로 구성되어 자동 정렬 및 검색 속도가 빨라짐

시스템에 걸리는 부하를 줄여 시스템 전체 성능 향상할 수 있음

 

단점

인덱스를 추가하기 위한 별도의 저장 공간이 필요함

인데스를 생성하는데 시간이 걸림

데이터 변경 작업(DML)이 빈번한 경우에는 오히려 성능이 저하됨

 

인덱스 관리 딕셔너리

SELECT *
  FROM USER_IND_COLUMNS;

인덱스 구조

ROWID

DB내 데이터 공유 주소, ROWID를 이용해 데이터 접근 가능

1 ~ 6 : 데이터 오브젝트 번호

7 ~ 9 : 파일번호

10~ 15 : 블럭번호

16 ~ 18 : 로우 번호

 

특징

1. 인덱스 생성 시 지정한 컬럼은 KEY, ROWID는 VALUE가 되어 MAP 처럼 구성

2. SELECT 시 WHERE절에 인덱스가 생성 되어 있는 컬럼을 추가하면 데이터 조회 시 테이블의 모든 데이터에 접근하는 것이 아닌 해당 컬럼(KEY)와 매칭 되는 ROWID(VALUE)가 가리키는 ROW의 주소 값을 조회함 => 속도가 빨라짐

SELECT ROWID, EMP_ID, EMP_NAME
  FROM EMPLOYEE;

 

예제

인덱스를 활용하지 않은 SELECT문

SELECT EMP_ID, EMP_NAME
  FROM EMPLOYEE
 WHERE EMP_NAME = '윤은해';

실행후 

계획설명(F10) 누르게되면 계획이나오게됩니다.

 

인덱스를 활용한 SEELCT문

SELECT EMP_ID, EMP_NAME
  FROM EMPLOYEE
 WHERE EMP_ID = '210';

FULL이아닌 INDEX ROWID 를 참조하여 조회한걸로나오게됩니다.

 

설명

WHERE절에 INDEX부여되지 않은 컬럼으로 조회 시

윤은해가 어느 곳에 있는지 모르기 때문에 EMPLOYEE테이블 데이터 전부를 DB BUFFER 캐시로 복사한 뒤 FULL SCAN 으로 찾게 됨

한마디로 1~100이 있다면 위치를찾기위해 1번부터 찾는다고 생각하면 편함

 

INDEX가 부여된 컬럼으로 조회 시

INDEX에 먼저 가서 '210' 정보가 어떤 ROWID를 가지고 있는지 확인한 뒤 해당 ROWID에 있는 블럭만 찾아가서 DB BUFFER 캐시에 복사

한마디로 1~ 100중에 50번의 위치가어딘지 정보를알고있고 1번부터 확인안하고 바로 50번 확인하러 간다는 의미

 

INDEX 사용방법

INDEX 생성

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

 

고유 인덱스 (UNIQUE INDEX)

UNIQUE INDEX에 생성 된 컬럼에는 중복 값 포함 불가(UNIQUE 제약조건)

오라클 PRIMARY KEY, UNIQUE KEY 제약 조건 설정 시 해당 컬럼에 대한 INDEX가 존재하지 않으면 자동으로 해당 컬럼에 UNIQUE INDEX가 생성 됨

PRIMARY KEY 를 이용 하여 ACCESS 하는 경우 성능 향상에 효과가 있음

 

UNIQUE_INDEX 생성

-- EMPLOYEE 테이블의 EMP_NAME 컬럼 UNIQUE_INDEX 생성하기
CREATE UNIQUE INDEX IDX_EMP
ON EMPLOYEE(EMP_NAME);

 

UNIQUE_INDEX 조회

-- 생성한 인덱스 조회
SELECT *
  FROM USER_INDEXES
 WHERE TABLE_NAME = 'EMPLOYEE';

PK(EMP_ID), UNIQUE(EMP_NO)에 대한 인덱스는 이미 자동 생성 되어 있으며 새로 생성한 IDX_EMP 인덱스가 추가 되어있음

 

UNIQUE_INDEX 조회 불가능

이미 EMP_NAME 의 '하이유' 라는 이름은 저장되어있어서 정상 추가가 되지않았다.

INSERT INTO EMPLOYEE
VALUES(100, '하이유', '111111-2222222', 'hi@kh.or.kr', '01011112222', 'D1'
      , 'J7', 'S3', 3000000, 0.3, 201, SYSDATE, NULL, DEFAULT);

 

UNIQUE_INDEX 생성 불가

컬럼 값들 중 중복 되는 값이 있을 경우 UNIQUE_INDEX 생성 불가하다

DEPT_CODE 의 값내용

아래 코드중 중복값이 있기때문에 오류 발생함

CREATE UNIQUE INDEX IDX_DEPTCODE
ON EMPLOYEE(DEPT_CODE);

 

비고유 인덱스 (NONUNIQUE INDEX)

빈번하게 사용되는 일반 컬럼을 대상으로 생성

주로 성능 향상을 목적으로 사용

 

단일 인덱스 : 한 개의 컬럼으로 구성 된 인덱스

 

인덱스 이름 수정

-- EMPLOYEE 테이블의 DEPT_CODE 컬럼에 인덱스 생성
CREATE INDEX IDS_DEPTCODE
ON EMPLOYEE(DEPT_CODE);
--> 비고유(중복 값 포함 가능)이면서 단일(컬럼이 한개)인 인덱스

-- 인덱스 이름 수정
ALTER INDEX IDS_DEPTCODE
RENAME TO IDX_DEPTCODE;

 

인덱스 삭제

DROP INDEX IDX_DEPTCODE;

 

결합 인덱스

두 개 이상의 컬럼으로 구성 된 인덱스

조건시 아래와 같이 사용하면 결합인덱스 동작하지않음 

SELECT *
  FROM DEPARTMENT 
 WHERE DEPT_ID = 'D4'
   AND DEPT_TITLE = '국내영업부';

 

그래서 서브쿼리에서 사용했던것처럼 묶어서 사용해줘야함

SELECT *
  FROM DEPARTMENT 
 WHERE (DEPT_ID, DEPT_TITLE) = (SELECT
                                       DEPT_ID
                                     , DEPT_TITLE
                                     FROM DEPARTMENT
                                     WHERE DEPT_ID = 'D1');

 

함수 기반 인덱스

SELECT절이나 WHERE절에 산술 계산식이나 함수식 사용 된 경우

계산식은 인덱스의 적용을 받지 않음

계산식으로 검색하는 경우가 많으면 수식이나 함수식을 인덱스로 만들 수 있음

 

IDX_SAL 인덱스 생성

CREATE INDEX IDX_SAL
ON EMPLOYEE(SALARY);

 

SELECT

-- 산술 계산식, 함수식 사용하여 SELECT
SELECT EMP_ID, EMP_NAME, SALARY,
       ((SALARY+ (SALARY * NVL(BONUS,0))) * 12)
       FROM EMPLOYEE
       WHERE ((SALARY+ (SALARY * NVL(BONUS,0))) * 12) > 30000000;
--> SALARY 컬럼에 대한 인덱스가 있더라도 계산식/함수식에 대한 인덱스는 없어 FULL SCAN
--> 함수 기반 인덱스 생성 이후에는 해당 인덱스를 통해 검색 됨

 

함수 기반 인덱스 생성

CREATE INDEX IDEX_SALYEAR
ON EMPLOYEE(((SALARY+ (SALARY * NVL(BONUS,0))) * 12));

 

인덱스 재구성

DML 작업(특히 DELETE) 명령을 수행한 경우

해당 인덱스 내에서 엔트리가 논리적으로만 제거되고 실제 엔트리는 그냥 남아있게 되어

인덱스가 필요 없는 공간을 차지 => 인덱스 재생성해줘야함

ALTER INDEX IDX_SAL REBUILD;

 

INDEX는 데이터가 적어도 5만개 이상 정도의 데이터에서 그나마 효율적으로 동작함

적은 데이터의 양에서는 오히려 비효율적일 수 있음

DML을 많이 실행하지 않는 조회 목적의 테이블에서는 호율적이지만

DML을 실행하면 다시 인덱스를 조정하는 시간이 걸려 비효율적일 수 있음