DAY 65. [DataBase] 객체 INDEX

INDEX

오라클에서 제공하는 객체이다.

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

내부 구조는 B*트리 형식으로 구성되어 있다.

테이블과 독립되어 작성되는 객체이지만 테이블에 다소 의존적인 객체이다.

검색 연산에 최적화 하기 위해서 데이터 베이스의 행들에 대한 정보를 구성하는 객체

테이블 전체를 검색하는 것이 아니라 데이터 베이스에서 원하는 정보를 빨리 검색할 수 있다.

• 장점 : 검색 속도가 빨라지고 시스템에 걸리는 부하를 줄여 시스템 전체 성능이 향상된다.
• 단점 : 인덱스를 위한 추가 저장 공간이 필요하다. 인덱스를 생성하는데 시간이 걸린다. 데이터의 변경 작업(INSERT/UPDATE/DELETE)이 자주 일어날 경우 오히려 성능이 저하된다.

 

[표현식]

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

-- 데이터 딕셔너리
SELECT * FROM USER_IND_COLUMNS;

 

---

 

INDEX 구조

SELECT ROWID, EMP_ID, EMP_NAME
FROM EMPLOYEE;

 

 

• ROWID : 행들마다 가지는 행들이 가질 수 있는 고유한 ID
• PRIMARY KEY 생성 → 자동INDEX 생성, PK 값을 보고 어느 위치에 저장되어 있는지 알 수 있다. ROWID를 보고 찾아온다. 색인 열할

---

INDEX 종류

1. 고유 인덱스(UNIQUE INDEX) - 중복 값이 포함될 수 없음 (중복 값이 있는 컬럼에 생성하면 에러가 발생된다.) - PRIMARY KEY 제약조건, UNIQUE 제약조건을 생성하면 자동으로 생성된다. - CREATE UNIQUE INDEX 인덱스명 ON 테이블명(컬럼명|함수명) 2. 비고유 인덱스(NONUNIQUE INDEX) - 빈번하게 사용되는 일반 컬럼을 대상으로 생성 - 주로 성능 향상을 위한 목적으로 생성 - 검색 속도가 다소 빨라진다. - 중복 값이 있는 컬럼에도 생성 가능 3. 단일 인덱스(SINGLE INDEX) - 한 개의 컬럼으로 구성한 인덱스 4. 결합 인덱스(COMPOSITE INDEX) - 한 개의 컬럼으로 구성한 인덱스 - 두 개 이상의 컬럼을 하나의 인덱스로 생성 가능하다. - COLUMN_POSITION 순서에 의해 성능이 차이날 수 있다. - CREATE INDEX 인덱스명 ON 테이블명(컬럼명, 컬럼명, ...) 5. 함수 기반 인덱스(FUNCTION-BASED INDEX) - SELECT 절이나 WHERE 절에 산술 계산식이나 함수식이 사용된 경우 - 계산식은 인덱스의 적용을 받지 않는다. - 8g 버전부터 제공

 

-- 비고유 INDEX 생성하기
CREATE INDEX IDX_STUDENT_NAME
ON TB_STUDENT(STUDENT_NAME);

 

 

INDEX 재생성

DML 작업(특히 DELETE 명령) 을 수행한 경우, 해당 인덱스 엔트리가 논리적으로만 제거되고 실제 엔트리는 그냥 남아있게 된다.

이 때, 제거된 인덱스가 필요 없는 공간을 차지하고 있지 않도록 인덱스를 재생성 해야한다.

ALTER INDEX 인덱스명 REBUILD;

 

 

INDEX 활용한 정렬

인덱스를 선언하면 인덱스가 선언된 컬럼을 가지고 오름차순으로 정렬이 된다.

ORDER BY를 쓰는 것 보다( 전체테이블 가져와서 정렬 )

WHERE 절에서 조건을 넣으면 인덱스를 가지고 검색하는데 내가 지정한 컬럼을 가지고 위 데이터를 이미 정렬해 놓은 것을 가지고 어느 행에 있는지 보기 때문에 이미 정렬해 놓은 것을 가져다가 사용하는 것이 더 좋은 성능을 보인다.

 

 

<계획 설명 탭 뜻> CARDINALTY : 결과 행의 갯수 COST : 메모리의 사용량을 수치로 만들어 놓은 것 (숫자가 크면 클 수록 메모리 사용량이 많다는 뜻) OPTIONS : FULL 테이블 데이터를 메모리에 올리는데 데이터가 많으면 많을수록 메모리가 많아진다. → OPTION이 BY INDEX ROWID이 되면 가지고 테이블에서 원하는 데이터를 가지고 온다. PK 값으로 설정된 인덱스로 검색하면 OPTION이 UNIQUE SCAN이 되면서 검색 결과도 빠르고 COST도 적게 나온다. 실행 속도는 우리가 제공하는 서비스의 품질과 신뢰성을 높이게 된다. 인덱스를 고민하고 작성해볼 것