오라클 INDEX

1. INDEX의 개념

• 책에서 목차나 색인의 역할을 함
• ROWID(file번호 + block번호 + row번호를 통해 data block에 접근함
• 데이터를 찾기 위해 오름차순으로 정렬된 주소체계임
• 테이블에 대한 검색 속도를 높여주는 자료구조임
• ROOT, BRANCH, LEAF로 구성된 계층적 구조임
• 오라클 서버에서 옵티마이저는 FULL SCAN보다 INDEX SCAN이 유리하다고 판단되면 생성된 INDEX의 ROOT부터 찾음
• ROOT에는 BRANCH 블럭의 시작점에 대한 정보를, BRANCH LEVEL에서는 LEAF블럭의 시작점에 대한 정보를 갖고 있음
• LEAF에서 해당 데이터의 ROWID를 알 수 있음
• 트리 구조를 따라 검색해  ROWID를 알아냄으로써 빠르게 데이터에 접근함
• INDEX에는 NULL값이 들어갈 수 없음
• INDEX 테이블은 정렬되어 있고 데이터 수정 시 재정렬됨
• WHERE 컬럼명 LIKE ‘A%’에서는 유효하지만, LIKE ‘%A’ 또는 ‘%A%’에서는 무효함
• 인덱스 컬럼를 변경해 사용하면 인덱스를 타지 않음. 예를 들어, 인덱스로 잡힌 날짜 컬럼을 TO_CHAR(날짜컬럼)으로 변경해 검색하면 인덱스를 타지 않음.

 

 

2. INDEX 사용이유

• 검색속도 향상을 위함
• FULL SCAN에서 모든 테이블의 데이터를 읽어오는 방법과는 다르게 ROOT-BRANCH-LEAF-DATA BLOCK의 총 4번의 IO를 통해 접근함
• 구별되는 값이 많은 컬럼에서 사용함. 기본키는 UNIQUE INDEX를 생성함. 검색하려는 모든 데이터가 고유한 값이면 INDEX구조에서 중복이 없음
• WHERE절에서 자주 조회되는 칼럼을 사용함. 중복 데이터가 있더라도 자주 조건절에 사용되는 컬럼이라면 INDEX SCAN이 효율적임
• 큰 테이블에서 적은 데이터가 필요할 때 사용함. 그래서 비율을 가장 많이 차지하는 데이터를 NULL로 INSERT하고 나머지 데이터를 구분 후 INDEX를 생성하면, NULL을 제외한 데이터 ROW들에 대해서만 INDEX SCAN을 할 수 있음. 예를들어, 100건 중 95건을 NULL로, 나머지 5건을 각각 구분하면, FULL SCAN시에는 100건을 탐색하지만 INDEX SCAN에서는 5건만 탐색함
• ORDER BY 절에서 이미 정렬된 데이터를 가져옴으로써 부하를 줄일 수 있음
• 정렬된 데이터를 대상으로 함으로써 MIN, MAX 작업의 부하를 줄일 수 있음

 

 

3. INDEX 사용시 장단점

3.1.  장점

• 데이터 양이 많아져도 속도차이가 많이 안남
• SELECT 구문에서는 보통 속도 향상의 장점이 있음

3.2. 단점

• 꼭 INDEX SCAN이 유리하지는 않음. 예를 들어, 데이터가 1건인 경우에는 FULL SCAN이 유리함
• INDEX SCAN은 IO단위가 1블럭임. 그래서 DB_FILE_MULTIBLOCK_READ_COUNT 설정에 따라 한번에 여러 블럭을 읽는 것이 INDEX SCAN보다 효율적일 수 있음
• INDEX를 생성하기 위해서 별도의 저장공간이 필요함
• INSERT, UPDATE, DELETE 작업 시, 오름차순 정렬된 INDEX에 대한 추가 작업이 필요함

 

 

4. INDEX 사용 방법

4.1. INDEX 생성

• INDEX는 조건절에 자주 등장하는 컬럼,항상  ‘=’ 으로 비교되는 컬럼, 중복되는 데이터가 최소한인 컬럼, ORDER BY 절에서 자주 사용되는 컬럼, 조인 조건으로 자주 사용되는 컬럼을 생성하는 것이 권장이 됨

• CREATE INDEX [인덱스명] ON [테이블명](컬럼1, 컬럼2, … )
• CREATE UNIQUE INDEX -> 컬럼 중복 없는 INDEX 생성

 

4.2. INDEX 조회

• SELECT * FROM USER_INDEXES WHERE TABLE_NAME = [테이블명]

• SELECT * FROM ALL_IND_COLUMNS WHERE TABLE_NAME = [테이블명]

 

4.3. INDEX 수정

• DROP INDEX [기존 인텍스 명] TO [바뀔 인덱스 명]
• ALTER INDEX [기존 인덱스 명] RENAME TO [바뀔 인덱스 명]

• 오라클에서는 인덱스의 구성 컬럼을 추가하는 ALTER문이 불가함. DROP 후에 새로 CREATE 해주어야 함

 

4.4. INDEX 삭제

• DROP INDEX [인덱스 명]

• 조회성능을 극대화하기 위한 객체이므로 너무 많이 만들면 INSERT, UPDATE, DELETE시에 부하가 발샘함. 안쓰는 INDEX는 삭제하는 것이 권장됨

 

4.5. INDEX 리빌드

• 삽입, 수정, 삭제 등이 오랫동안 일어나면 트리 한쪽이 무거워지고 전체적으로 트리의 깊이가 깊어져 검색속도가 하락함. 그래서 주기적인 리빌딩 작업이 권장됨
• INDEX 트리의 깊이가 4 이상인 INDEX가 주로 리빌딩  대상이 됨 
• 깊이가 4 이상인 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;

참고 출처

https://myjamong.tistory.com/184 

 

https://coding-factory.tistory.com/419 

 

https://coding-factory.tistory.com/746