INDEX
SQL 명령문의 검색 처리 속도를 향상시키기 위해 컬럼에 대해서 셍성하는 오라클 객체
하드디스크의 어느 위치인지에 대한 정보를 가진 주소록
DATA - ROWID로 구성
ROWID 구조 : 오브젝트 번호, 상대 파일 번호, 블록 번호, 데이터 번호
SELECT
ROWID -- 주소값
, E.EMP_ID
, E.EMP_NAME
FROM EMPLOYEE E;
-- 출력
-- AAAR/uAAHAAAACUAAA 200 김혜수
인덱스의 내부 구조는 이진트리 형식으로 구성되어 있고 인덱스를 생성하기 위해서는 시간이 필요하다.
또한 인덱스를 위한 추가 저장 공간이 필요하기 때문에 반드시 좋은 것은 아니다.
=> 인덱스가 생성 된 컬럼에서 DML 작업이 빈번한 경우 처리 속도가 느려진다.
따라서 일반적으로 테이블 전체 로우의 15& 이하의 데이터를 조회할 때 인덱스를 생성한다.
장점
- 검색 속도가 빨라짐
- 시스템에 걸리는 부하를 줄여서 시스템 전체의 성능을 향상시킴
단점
- 인덱스를 부여해 추가 저장 공간 필요
- 인덱스를 생성하는데 시간이 걸림
- 데이터의 변경 작업 (INSERT/UPDATE/DELETE)이 자주 일어나는 경우
REBUILD 작업을 주기적으로 해주어야 하고, REBUILD를 자주 해주지 않으면 성능이 오히려 저하된다.
인덱스를 관리하는 데이터 딕셔너리
PK, UNIQUE 제약 조건이 있으면 자동으로 INDEX 객체가 생성 된다.
SELECT *
FROM USER_IND_COLUMNS;
인덱션의 종류
1. UNIQUE INDEX
고유 인덱스
UNIQUE INDEX로 생성 된 컬럼에는 중복 값이 포함될 수 없음
오라클 PRIMARY KEY, UNIQUE 제약조건을 생성하면 자동으로 해당 컬럼에 UNIQUE INDEX가 생성 됨
해당 컬럼으로 ACCESS 하는 경우 성능 향상의 효과가 있음
2. NONUNIQE INDEX
비고유 인덱스
NON-UNIQUE 인덱스는 인덱스를 사용한 컬럼에 중복 데이터 값을 가질수 있다.
WHERE절에서 빈번하게 사용 되는 일반 컬럼을 대상으로 생성. 주로 성능 향상을 위한 목적으로 생성함
CREATE INDEX IDX_DEPTCODE
ON EMPLOYEE(DEPT_CODE);
3. SINGLE INDEX
단일 인덱스
하나의 칼럼으로 인덱스가 구성된 것.
4. CONPOSITE INDEX
결합 인덱스
여러 개의 컬럼에 생성할 수 있으며 결합 인덱스가 가질수 있는 최대 컬럼 값은 16개 이다
중복 값이 낮은 값이 먼저 오는 것이 검색 속도를 향상시킨다.
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';
5. FUNCTION BASED INDEX
함수 기반 인덱스
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) > 10000000;
인덱스 힌트 /*+ */
일반적으로는 옵티마이저가 적절한 인덱스를 타거나 풀 스캐닝을 해서 비용이 적게 드는 효율적인 방식으로 검색함
하지만 우리가 원하는 테이블에 있는 인덱스를 사용할 수 있도록 해주는 구문(힌트)를 통해 선택 가능
질의 결과 확인 후 계획 설명을 보면
인덱스 힌트를 사용한 경우와 사용하지 않은 경우의 실행 차이를 볼 수 있다.
SELECT절 첫 줄에 힌트 주석(/*+ 내용 */)을 작성하여 적절한 인덱스를 부여할 수 있다.
SELECT /*+ INDEX(E 엔터티1_PK)*/
E.*
FROM EMPLOYEE E;
인덱스가 내림차순으로 생성 되어서 인덱스 영역에서 역방향으로 스캔하라는 뜻
예전에 넣었던 데이터부터 순서대로 나오도록 정렬
SELECT /*+ INDEX_DESC(E 엔터티1_PK)*/
E.*
FROM EMPLOYEE E;
인덱스 DDL 구문
UNIQUE 제약 조건에 의해 이미 인덱스가 존재한 경우 중복 생성할 수 없음
CREATE UNIQUE INDEX IDX_EMPNO
ON EMPLOYEE(EMP_NO);
인덱스의 구조는 테이블과 독립적이므로 인덱스의 삭제는 테이블의 데이터에는 영향을 미치지 않는다.
인덱스의 소유자이거나 DROP ANY INDEX권한을 가지고 있어야만 인덱스 삭제가 가능하다.
단, PK나 UNIQUE 제약 조건으로 인해 생성 된 인덱스는 DROP 할 수 없음
-- 고유/기본 키 적용을 위한 인덱스를 삭제할 수 없습니다.
DROP INDEX SYS_C007788;
중복 값이 있는 컬럼은 UNIQUE 인덱스 생성하지 못함
-- 중복 키가 있습니다. 유일한 인덱스를 작성할 수 없습니다
CREATE UNIQUE INDEX IDX_DEPTCODE
ON EMPLOYEE(DEPT_CODE);
'백엔드 과정 > Oracle(SQL)' 카테고리의 다른 글
[Oracle] Chap14 권한과 ROLL (0) | 2022.01.29 |
---|---|
[Oracle] Chap13 SYNONYM (0) | 2022.01.28 |
[Oracle] Chap11 SEQUENCE (0) | 2022.01.27 |
[Oracle] Chap10 VIEW (0) | 2022.01.27 |
[Oracle] Chap09 TCL 트랜잭션 (0) | 2022.01.25 |