12_INDEX

INDEX

SQL명령문의 처리 속도를 향상 시키기 위해서 컬럼에 대해서 생성하는 오라클 객체이다.

검사에 자주 쓰이는 컬럼을 인덱스로 하여, FULL SCAN을 하지 않고 인덱스를 타고 간다.

인덱스의 내부구조는 이진트리형식으로 구성되어 있다. 인덱스를 생성하기 위해서는 시간이 필요하며 인덱스를 위한 추가 저장 공간이 필요하다. 또한, DML 작업이 빈번한 경우 수정, 삭제로 불균형 형태가 된다면 정렬이 잘 된 효과를 보지 못해 처리 속도가 느려진다.

INDEX 표현식

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

하드디스크의 어떤 위치에 대한 정보를 가진 주소록으로 인덱스는 DATA와 ROWID로 구성되어 있다.

ROWID의 구조 : 오브젝트 번호, 상대파일 번호, 블록 번호, 데이터 번호(행을 찾아갈 수 있는 값)

AAAR9oA AHA AAACX AAA ← ROWID 200 선동일

인덱스를 관리하는 데이터 딕셔너리

→ PK, UNIQUE 제약조건이 걸린 컬럼은 자동으로 INDEX 객체가 생성된다.

SELECT
	   UIC.*
  FROM USER_IND_COLUMNS UIC;

인덱스의 종류

1. 고유 인덱스 (UNIQUE INDEX)
2. 비고유 인덱스 (NONUNIQUE INDEX)
3. 단일 인덱스 (SINGLE INDEX)
4. 결합 인덱스 (COMPOSITE INDEX)
5. 함수 기반 인덱스 (FUNCTION BASED INDEX)

 

UNIQUE INDEX

고유 인덱스로 생성된 컬럼에는 중복 값이 저장되지 않는다.

PK, UNIQUE 제약 조건을 생성하면 자동으로 해당 컬럼에 UNIQUE INDEX가 생성된다.

해당 컬럼으로 ACCESS 하는 경우 성능 향상의 효과가 있다.

 

인덱스 힌트

일반적으로는 옵티마이저가 적절한 인덱스를 타거나 풀 스캐닝을 해서 비용이 적게 드는 효율적인 방식을 선택한다. 하지만 우리는 원하는 테이블에 있는 인덱스를 사용할 수 있도록 하는 구문(힌트)를 통해서 선택할 수도 있다.

SELECT절 첫 줄에 힌트 주석 /+ 내용/ 을 작성하여 적절한 인덱스를 부여할 수 있다.

SELECT 할 때 옵티마이저가 계획을 한다

→ FULL SCAN을 할지 INDEX를 타고 물리적인 위치를 찾아갈지

SELECT /*+ INDEX(E 엔터티1_PK)*/  -> E 테이블에 있는 엔터티1_PK 이름의 인덱스이다
        E.*
  FROM  EMPLOYEE E;


--인덱스 영역에서 역방향으로 스캔하라는 의미(INDEX_DESC)

SELECT /*+ INDEX_DESC(E 엔터티1_PK)*/
        E.*
  FROM  EMPLOYEE E;

NONUNIQUE INDEX (비고유 인덱스)

WHERE절에서 빈번하게 사용되는 일반 컬럼을 대상으로 검색 성능 향상을 목적으로 생성한다.

잘 만들지는 않지만 조회 상황에서 많이 쓰인다면 고려하면 좋다.

COMPOSITE INDEX(결합 인덱스)

결합 인덱스는 중복 값이 낮은 값이 먼저 오게 하여 검색 속도를 향상 시킨다.

조회 조건에서 쓰일 때~

CREATE INDEX IDX_DEPT
ON DEPARTMENT (DEPT_ID, DEPT_TITLE);

SELECT /*+ INDEX_DESC(D IDX_DEPT)*/
       D.DEPT_ID
  FROM DEPARTMENT D
 WHERE D.DEPT_TITLE > '0'
   AND D.DEPT_ID > '0';    -> '0'보다 작은 문자열은 없다.
                              검색 조건에 해당 컬럼을 쓰면서 인덱스 동작하도록 하는 구문

함수 기반 인덱스

SELECT절이나 WHERE절에서 산술 계산 식이나 함수가 사용된 경우 계산에 포함된 컬럼은 인덱스의 적용을 받지 않는다. 계산 식으로 검색하는 경우가 많다면, 수식이나 함수 식으로 이루어진 컬럼을 인덱스로 만들 수 있다.

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


SELECT /*+ INDEX_DESC(E IDX_EMP_SALCALC)*/
        E.EMP_ID
     ,  E.EMP_NAME
     ,  (E.SALARY + (E.SALARY * NVL(E.BONUS,0))) * 12 연봉
  FROM  EMPLOYEE E
 WHERE  (E.SALARY + (E.SALARY * NVL(E.BONUS,0))) * 12 > 1000000;