INDEX
인덱스는 테이블이나 클러스트에서 쓰이는 선택적인 객체로서, 오라클 데이터베이스 테이블내의 원하는 레코드를 빠르게 찾아갈 수 있도록 만들어진 데이터 구조이다. 일종의 색인 기술로써 테이블에 index를 생성하게 되면 index table을 생성해 관리한다. index 생성 시 아무 옵션 없이 default로 생성하면 B-Tree index가 생성된다.
자동 인덱스 : primary key 또는 UINQUE 제한 규칙에 의해 자동적으로 생성되는 인덱스.
가장 기본적인 B-Tree 인덱스로 인덱스 키(인덱스로 만들 테이블의 컬럼 값)
+ 키에 해당하는 컬럼값을 가진 테이블 로우가 저장된 주소로 구성
수동 인덱스 : CREATE INDEX 명령을 직접 실행하여 만드는 인덱스
인덱스의 종류
일반적으로 인덱스의 종류는 B-TREE 인덱스와 BITMAP 인덱스로 나누어진다.
B-TREE 인덱스
실제 테이블의 주소는 Leaf Block들에 전부 들어 있다.
해당 데이터들에 대한 데이터들이 Branch Block과 Root Block에 들어 있다.
특정 데이터는 찾아야 할 경우 Root Block에서 Branch Block 정보를 찾고
그다음 Leaf Block 정보를 찾아가서 해당 데이터의 ROWID를 찾아 데이터가 들어 있는 블록을 메모리로 복사해 온다.
B-TREE 인덱스의 종류는 UNIQUE INDEX, Non UNIQUE INDEX, Function Based INDEX, DESCENDING INDEX, 결합 인덱스(Compisite INDEX) 등이 있다.
결합 인덱스는 인덱스를 생성할 때 두 개 이상의 칼럽을 합쳐서 인덱스를 만드는 것을 말한다.
예를 들어 성별과 이름을 이용하여 데이터를 찾는다고 가정해보자.
테이블 데이터는 총 60건의 데이터가 존재한다.
성별은 남자이고 이름은 홍길동 인 데이터를 찾아보겠다.
첫 번째 사용 : 성별 + 이름
60명 -> '남자' -> 25명
25명 -> '홍길동' -> 2명
성별을 먼저 이용하니 총 25회의 검사가 필요하였다.
두 번째 사용 : 성별 + 이름
60명 -> '홍길동' -> 2명
2명 -> '홍길동' -> 2명
이름을 먼저 이용하니 총 2회의 검사가 필요하였습니다.
위의 상황처럼인덱스의 사용 순서도 중요한 역할을 하게 됩니다.
BITMAP INDEX
B-TREE 형식의 인덱스는 주로 데이터의 값의 종류가 많고 동일한 데이터가 적을 경우에 사용하는 인덱스였다.
BITMAP INDEX는 데이터의 값이 종류가 적고 동일한 데이터가 많을 경우에 사용한다.
이름처럼 데이터가 어디 있다는 지도정보를 Bit로 표시한다.
데이터가 있으면 '1', 없으면 '0'으로 표시된다.
맵은 인덱스 칼럼의 개수만큼 만들어진다.
예를 들어 회원정보에 '지역'이라는 칼럼이 있다면
| 서울 : 1 0 1 0 0 대전 : 0 1 0 0 1 대구 : 0 0 0 1 1 |
이런 식으로 MAP이 생성된다.
이때 데이터가 '울산'이라는 데이터가 추가된다면
BITMAP INDEX를 전부 수정해야 하고 '울산'이라는 MAP을 추가해야 한다.
즉, B-TREE INDEX는 관련 블록만 변경되면 되지만 BITMAP INDEX는 모든 맵을 다 수정해야 한다는 큰 문제점이 있다.
그래서 주로 데이터가 변경이 안 되는 테이블과 값의 종류가 작은 칼럼에 생성하는 것이 유리하다.
INDEX 사용
1. 인덱스 생성
--문법
CREATE INDEX [인덱스명] ON [테이블명](컬럼1, 컬럼2, 컬럼3.......)--예제
CREATE INDEX EX_INDEX ON CUSTOMERS(NAME,ADDRESS);
--예제 컬럼 중복 X
CREATE[UNIQUE] INDEX EX_INDEX ON CUSTOMERS(NAME,ADDRESS); * UNIQUE 옵션 : 인덱스로 설정하는 칼럼 값에 중복 값을 허용하지 않음
2. 인덱스 조회
SELECT * FROM USER_INDEXES WHERE TABLE_NAME = 'CUSTOMERS';- 인덱스는 USER_INDEXES 시스템 뷰에서 조회할 수 있음
3. 인덱스 삭제
--문법
DROP INDEX [인덱스 명]- 조회 성능을 높이기 위해 만든 객체지만 저장공간을 많이 차지하며 DDL작업(INSERT, DELETE, UPDATE) 시 부하가 많이 발생해 전체적인 데이터베이스 성능을 저하시킨다. DBA는 주기적으로 INDEX를 검토하여 사용하지 않는 인덱스는 삭제하는 것이 데이터베이스 전체 성능을 향상할 수 있다.
4. 인덱스 리빌드
- 생성된 인덱스는 기본적으로 ROOT, BRANCH, LEAF로 구성된 트리 구조를 가지며 DDL 작업이 오랜 시간 발생하면 트리의 하위 레벨이 많아져 트리 구조의 한쪽이 무거워지는(깊어지는) 현상이 생긴다. 이러한 현상은 인덱스의 검색 속도를 저하시키고 전체 데이터베이스 성능에 영향을 미친다. 그러므로 주기적으로 INDEX를 리빌딩하는 작업을 해주어야 한다.
- 성능에 영향을 미치는 index 조회
SELECT I.TABLESPACE_NAME,I.TABLE_NAME,I.INDEX_NAME, I.BLEVEL,
DECODE(SIGN(NVL(I.BLEVEL,99)-3),1,DECODE(NVL(I.BLEVEL,99),99,'?','Rebuild'),'Check') CNF
FROM USER_INDEXES I
WHERE I.BLEVEL > 4
ORDER BY I.BLEVEL DESC
- 인덱스 리빌드
--문법
ALTER INDEX [인덱스명] REBUILD;--예제
ALTER INDEX EX_INDEX REBUILD;
- 리빌딩해야 하는 인덱스가 많아 일일이 리빌드를 해주기 힘든 경우 USER_INDEXES 딕셔너리에 있는 인덱스를 조회하여 인덱스 리빌드 쿼리를 만들어 한번에 전체 리빌딩을 실행하는 방법이 있다.
-- 전체 인덱스 리빌드 쿼리문
SELECT 'ALTER INDEX '||INDEX_NAME||' REBUILD; 'FROM USER_INDEXES;
reference
https://lee-mandu.tistory.com/483
https://kwomy.tistory.com/72
