[Oracle] SQL문법 12. INDEX

INDEX

색인이라고도 불리며 컬럼에 대해 생성하는 데이터베이스 객체 중 하나로 목차와 같은 기능을 한다

테이블과는 별개의 독립된 객체로 작성되지만 테이블에 의존적이며, 기본키와 같은 제약조건이 지정되면 따로 인덱스를 생성하지 않더라도 해당 키에 대한 인덱스가 자동으로 생성된다

인덱스의 역할은 검색속도의 향상에 있다
테이블에서 데이터를 조회할 때 인덱스가 없다면 테이블의 모든 데이터를 (FULL-SCAN) 하나하나 조회해서 사용자가 원하는 데이터를 가져오게 된다
따라서 목차와도 같은 인덱스 설정을 해두면 테이블의 모든 행들을 뒤지지 않고 원하는 조건에 해당하는 것들만 빠르게 가져올 수 있다

INDEX 특징

[ 특징 ]

내부구조는 B*트리 형식으로 구성되어 있다
SQL명령문의 처리 속도를 향상시키기 위해 생성한다
인덱스는 인덱스로 설정한 컬럼의 데이터들을 별도로 오름차순 정렬하여 특정 메모리 공간에 물리적 주소(ROWID)와 함께 저장시킨다
PRIMARY KEY 설정을 하면 해당 컬럼에 대한 인덱스가 자동으로 생성된다
각 인덱스들은 메모리 공간에 중복 제한 없이 저장되는 것이기 때문에 많이 만들면 만들수록 그만큼 공간을 잡아먹는다, 따라서 신중하게 만들 필요가 있다

[ 장점 ]

검색속도가 빨라진다
=> WHERE 절에 인덱스의 컬럼을 사용하게 되면 훨씬 빠르게 연산이 가능하다
이미 오름차순으로 정렬되어 있어, ORDER BY 연산을 사용할 필요가 없다
마찬가지로 이미 정렬되어 있기 때문에 MIN, MAX 등의 집계함수로 값을 찾을 때 연산 속도가 매우 빠르다
시스템에 걸리는 부하를 줄이고 시스템 전체 성능을 향상시킨다

[ 단점 ]

인덱스를 위한 추가 저장공간이 필요하다
=> 인덱스를 생성하면 할수록 저장공간이 부족해지게 되므로 적절한 수준을 유지해야 한다
인덱스를 생성에 시간이 필요하다
DML문에 취약하다
=> 검색 외 데이터 변경(INSERT/UPDATE/DELETE)가 빈번히 일어날 경우 오히려 성능이 저하될 수 있다
=> 데이터가 새롭게 변경될 경우 인덱스 테이블 안에 있는 값들도 다시 정렬하고 ROWID(물리적 주소)값도 수정되어야 하기 때문이다
인덱스를 이용한 INDEX-SCAN보다 단순한 FULL-SCAN이 더 유리할 때가 있다
=> 일반적으로 테이블의 전체 데이터 중 10~15%의 데이터를 처리해야 하는 경우에만 인덱스가 효율적이다

[ 인덱스가 필요할 때 ]

테이블 행의 수가 많을 경우
WHERE문에 특정 컬럼이 많이 사용될 경우
검색 결과가 전체 데이터의 2~4% 정도일 경우
JOIN에 자주 사용되는 컬럼일 경우
NULL을 포함하는 행이 많은 컬럼일 경우

[ 인덱스가 필요하지 않을 때 ]

데이터가 적은(수천 건 미만) 경우
조회보다 삽입, 수정, 삭제처리가 많은 테이블일 경우
조회 결과가 전체 행의 15% 이상일 것으로 예상되는 경우

[ 효율적인 인덱스 사용방법 ]

조건절에 자주 등장하는 컬럼
항상 =으로 비교되는 컬럼
중복되는 데이터가 최소한인 컬럼(=분포도가 높은)
ORDER BY 절에 자주 사용되는 컬럼
JOIN 조건으로 자주 사용되는 컬럼

결론 : 데이터의 분포도가 높고, 조건절에 자주 호출되며, 중복값이 적은 컬럼
=> PK 컬럼에 사용하는 것이 제일 좋다

INDEX의 종류

고유 인덱스 (UNIQUE INDEX)
- 중복값이 포함될 수 없으며 성능이 좋다
- PRIMARY KEY 제약조건을 생성하면 자동으로 생성된다
- 인덱스를 생성한 컬럼에 대해서 중복되는 값을 테이블에 삽입하려고 할 시 에러가 발생한다
비고유 인덱스 (NONUNIQUE INDEX)
- 중복되는 값이 포함될 수 있다
- 빈번하게 사용되는 일반 컬럼을 대상으로 생성한다
- 주로 성능 향상을 위한 목적으로 생성한다
단일 인덱스 (SINGLE INDEX)
- 한 개의 컬럼으로 구성한 인덱스이다
결합 인덱스 (CONPOSITE INDEX)
- 두 개 이상의 컬럼으로 구성한 인덱스이다
- WHERE 절의 조건 컬럼이 1개 이상 AND로 연결되어 사용되는 경우에 많이 사용한다
함수 기반 인덱스 (FUNCTION-BASED INDEX)
- SELECT절이나 WHERE절에 산술 계산식/함수식이 사용된 경우 계산식은 인덱스의 적용을 받지 않는다
- 쿼리 조건이 바뀌면 인덱스를 다시 생성해야하는 단점이 있다

[ INDEX 선언 ]

[표현법]
CREATE [UNIQUE] INDEX 인덱스명 ON 테이블(컬럼1, 컬럼2, ...);

-- 예시
CREATE INDEX IND_TEST ON A_TABLE(A_COLUMN);

UNIQUE : 고유 인덱스 선언시 사용하며 생략 가능하다 (중복값이 있는 컬럼에 생성시 에러 발생)
인덱스를 생성했다고 해서 다 사용되는 것은 아니고 해당 인덱스의 활용 여부는 옵티마이저가 판단하게 된다
생성된 인덱스 확인 방법
- SELECT * FROM USER_INDEXES; : 현재 계정에 생성된 인덱스들 확인 가능
- SELECT * FROM USER_IND_COLUMNS; : 현재 계정에서 인덱스와, 인덱스가 적용된 컬럼들 확인 가능

[ INDEX 제거와 재생성 ]

[표현법]
DROP INDEX 삭제할 인덱스명; -- 인덱스 제거
ALTER INDEX 재생성할 인덱스명 REBUILD; -- 인덱스 재생성

-- 예시
DROP INDEX IND_TEST;
ALTER INDEX IND_TEST REBUILD;

INDEX 재생성
- DML작업(특히 DELETE)을 수행한 경우에 필요하다
- 해당 인덱스 엔트리가 논리적으로만 제거되고 실제 엔트리는 남아있게 되므로 제거된 인덱스가 필요없는 공간을 차지하고 있지 않도록 인덱스 자체를 재생성 해주는 것이다

[ INDEX 효율 확인 ]

쿼리문을 실행하게 되면 실행계획을 확인할 수 있게 된다
실행 계획 상에서 INDEX가 사용되었는지 아닌지도 확인 가능하며 각 계획상 CARDINALITY가 얼마인지도 확인이 가능하다

※ 실행 계획이란 DBMS 특정 쿼리문을 실행하는데 있어서 실행할 계획으로 단축키 F10으로 계획설명 란을 띄워 확인 가능하다

Gom's