1. 정의
- 어떤 데이터가 어디에 있는지 위치 정보를 가진 주소록 같은 개념, 데이터의 주소값을 가지고 있는것.
- 오라클에서는 데이터의 주소를 ROWID 라고 한다.
- 인덱스를 사용하면 어떤 데이터가 어디에 저장되어 있다는 것을 다 알기때문에 데이터를 호출할때 빠르게 작업이 가능
- 인덱스 생성이나 관리를 잘못하면 오히려 성능이 떨어지는 역효과 발생
2. 인덱스의 생성 원리
- Table Full Scan : 데이터가 어디에 있는지 모든 데이터를 읽어봄.
- 인덱스를 만드는 동안 데이터가 변경되면 문제가 되므로 해당 데이터들이 변경되지 못하도록 조치 후
- 메모리(PGA의 Sort Area) 에서 정렬을 함 --시간이 오래걸림, 정렬이 쿼리 수행 속도에 영향을 준다는 것을 알수있음.
- ( 만약, 메모리가 부족하게 되면 임시 테이블스페이스(Temporary tablespace)를 사용해서 정렬을 함 )
- 정렬과정이 모두 끝난 데이터들은 파일의 블록에 순서대로 기록을 함.
- 요약 : 전체 테이블 스캔 >> 정렬 >> Block 기록
3. 인덱스 구조와 작동 원리 ( B-TREE 인덱스 기준)
- 인덱스 컬럼은 2개! (Key 와 ROWID)
- SQL 을 수행하면 서버 프로세스가 파싱이란 과정을 마친 후 메모리(데이터베이스 버퍼 캐시) 에서 정보를 찾음.
- 메모리에 데이터가 없다면 하드디스크의 파일 전부를 다 메모리로 복사해서 찾아야함.
- 인덱스가 있다면 where절에 있는 조건에 맞는 데이터를 인덱스에 먼저가서 찾은후 해당 ROWID에 해당되는 블록만 복사.
4. 인덱스의 종류
* 데이터 처리 방법
OLTP (Online Transaction Processing) : 실시간 트랜잭션 처리용, 실시간으로 데이터가 입력되고 수정되는 환경
OLAP (Online Analytical Processing) : 온라인 분석 처리용, 분석이나 통계 정보등을 출력할때 사용되는 화면
(1) B-TREE 인덱스
- 주로 OLTP 환경에서 사용 ( Binary, Balance )
- 데이터의 값이 종류가 많고 동일한 데이터가 적을 경우에 사용
EX) 학번 컬럼에 데이터 종류는 전부다 다름. --데이터값의 종류가 많음
① UNIQUE INDEX
- 키값에 중복되는 데이터가 없음
- 성능이 좋음
- 향후에 중복된 값이 입력될 가능성이 있는 컬럼에는 사용하면 안됨
- 인덱스 이름은 회사마다 규칙이 있다. 일반적으로 인덱스를 뜻하는 접두어와 테이블이름과 컬럼명을 사용
- 인덱스 정렬 기본값 asc(오름차순)
- create UNIQUE INDEX 인덱스명 on 테이블이름 (컬럼명1 asc|desc, 컬럼명2, …)
② Non UNIQUE INDEX
- 중복되는 데이터가 들어가야만 하는 컬럼일 경우에 사용
- create INDEX 인덱스명 on 테이블이름 (컬럼명1 asc|desc, 컬럼명2, …)
③ Function Based INDEX (FBI : 함수기반 인덱스)
- where 절에 오는 조건 컬럼이나 조인 컬럼등에 만들어야 한다.
- 기존 인덱스를 활용할수 없다. (단점)
- create INDEX 인덱스명 on 테이블명(컬럼연산식)
- EX) create index idx_prof_pay_fbi on professor(pay+100)
④ DESCENDING INDEX
- 인덱스를 생성할때 큰값이 먼저오도록 인덱스를 생성 (DESC 내림차순으로)
- 주로 큰 값을 많이 조회하는 SQL에 생성
- create INDEX 인덱스명 on 테이블명(컬럼명 DESC)
- 사용 예) 계좌 조회시 최근날짜부터 조회, 매출 상위 매장 조회 등
⑤ 결합인덱스 (Composite Index)
- 인덱스 생성시 두개 이상의 컬럼을 합쳐서 만드는 것
- SQL문장에서 where절의 조건 컬럼이 2개 이상이 AND로 연결되어 함께 사용되는 경우에 많이 사용
- create INDEX 인덱스명 on 테이블명(컬럼명1, 컬럼명2,)
* 결합인덱스 CASE : 같은 테이블에 같은 SQL이지만 결합인덱스를 어떻게 생성하는가에 따라 속도나 검사횟수가 달라짐
select 이름, 성별
from 사원
where 성별='여자'
and 이름 ='유관순'
1. on 사원(성별, 이름) : 총 사원50명 중에서 성별을 먼저 검사후 25건을 걸러낸후 다시 이름을 검사해서 결과 출력
2. on 사원(이름, 성별) : 총 사원50명 중에서 이름을 먼저 검사해서 2건을 찾은후 다시 성별을 검사해서 결과출력
(2) BITMAP 인덱스
- 주로 OLAP 환경에서 사용
- 데이터가 어디 있다라는 지도정보(MAP)을 Bit로 표시
- 데이터의 값이 종류가 적고 동일한 데이터가 많을 경우에 많이 사용
EX) 성별 컬럼의 값의 종류는 남, 여 2가지뿐일 경우 --데이터값의 종류가 2가지뿐
- create BITMAP INDEX 인덱스명 on 테이블명(컬럼명)
EXAMPLE)
- 사원테이블의 성별컬럼에 인덱스 생성
> create bitmap index idx_사원_성별_bit on 사원(성별);
- 사원테이블의 지역컬럼에 인덱스 생성
> create bitmap index idx_사원_지역_bit on 사원(지역);
> 사원테이블에 데이터 한건을 추가해서 입력할 경우
- 기존에 생성되어 있던 BITMAP INDEX를 전부 수정해야 함. (울산 MAP을 새로 하나 생성해야함)
- B-TREE 인덱스는 관련 블록만 변경되면 되지만 BITMAP인덱스는 모든 맵을 다 수정해야함
- BITMAP 인덱스는 블록단위로 LOCK을 설정하기 때문에 같은 블록에 들어있는 다른 데이터도 수정이 안될수도..
- 데이터가 변경이 안되는 테이블이나 값이 종류가 적은 컬럼에 생성하는것이 유리함.
5. 인덱스의 주의사항
(1) DML에 취약하다.
① insert : 인덱스의 저장방식으로 인해 블록이 두개로 나누어지는 현상(INDEX Spilt) 발생
② delete : 데이터가 delete 되면 인덱스는 해당 데이터를 사용할수 없다는 표시만 해둠 >>쿼리수행속도 느려짐
③ update : 인덱스에서는 delete와 insert 작업으로 인식, 다른문장들 보다 더 큰 부하를 주게됨
(2) 타 SQL 실행에 악영향을 줄수 있다.
- 인덱스를 새로 생성한 후 옵티마이져가 실행 계획을 세우게 되는데
- 갑자기 없었던 인덱스가 테이블에 생기면
- 더 최근에 만들어진 인덱스가 더 좋을 것이라고 생각하고 잘 되어 있던 실행 계획을 바꾼다.
6. 인덱스 관리 방법
(1) 인덱스 조회하기
- 딕셔너리 조회
* 특정 사용자가 생성한 인덱스 조회 : USER_INDEXES, USER_IND_COLUMNS
* 전체 데이터베이스에 생성된 내역 조회 : DBA_INDEXES, DBA_IND_COLUMNS
(2) 사용여부 모니터링 하기
- 사용하지 않는 인덱스는 삭제하는 것이 성능향상에 도움이 됨.
* 모니터링 시작 > ALTER INDEX 인덱스명 MONITORING USAGE;
* 모니터링 중단 > ALTER INDEX 인덱스명 NOMONITORING USAGE;
* 사용유무 확인 > 딕셔너리 조회 : V$OBJECT_USAGE 에서 used 컬럼 확인
(V$ALL_INDEX_USAGE : 전체 인덱스 조회, sys계정, view 만들어서 사용)
(3) INDEX Rebuild 하기
step1. 테스트용 테이블 i_test를 생성한다.
step2. i_test 테이블에 데이터를 넣는다.
step3. 인덱스 생성
step4. 인덱스의 상태를 조회한다.
step5. 테이블에서 500건의 데이터 중 300건을 지운다.
step6. 인덱스 상태 조회 >> 밸런싱이 망가진 상태
step7. Rebuild 작업으로 수정한다.
- REBUILD ONLINE : 리빌드 작업중에 DML사용을 가능하게 만드는 옵션, but 성능이 많이 떨어짐.
step8. 밸런싱 조회
7. Invisible Index (인비저블 인덱스) -11g 추가
- 인덱스가 많을 경우 DML문장에 나쁜 영향을 주기 때문에 사용하지 않는 인덱스는 삭제하는 것이 좋다.
- 인덱스의 사용 유무를 알기위해서 모니터링 기능이 있다.
- 11g에서는 인덱스를 실제 삭제하기 전에 "사용안함" 상태로 만들어서 테스트를 해볼수 있는 기능을 제공
[출처] 오클기초(NVL,CONCAT
- user_indexes : 테이블이 소유하고 있는 인덱스 조회
- user_ind_columns : 인덱스가 어떤 컬럼들로 구성되어 있는지 조회
- ALTER INDEX 인덱스명 INVISIBLE/VISIBLE;
- 인덱스를 invisible 하면 옵티마이저가 실행계획을 세울때 해당 인덱스는 없다고 생각하고 실행계획을 세운다.
- but, 인덱스 내용은 계속 반영이 되므로 지워진 것은 아니다.
- SQL 힌트 구문에서 해당 인덱스를 사용하게 하기
8. 인덱스 활용 예제
(1) 인덱스를 활용하여 정렬한 효과를 내는 방법
- 정렬이 발생하는 SQL문장은 수행시간이 오래걸린다.
- order by 구문을 사용하지 않고 정렬 효과를 낼수 있느냐가 성능상 중요한 요건이 된다.
- 인덱스는 정렬이 되어 있기 때문에 테이블에서 데이터를 가져올때 인덱스를 활용해서 가져온다면 정렬이 된상태로 출력가능
step1. '사원' 테이블을 생성후 데이터를 입력한다.
step2. name 컬럼에 인덱스를 생성한다.
step3. 인덱스를 사용하지 않고 테이블을 조회한것과 인덱스를 사용해서 테이블을 조회한 결과를 비교한다.
* 컬럼명 >'0' --인덱스를 사용하라는 의미, 인덱스의 순서대로 데이터 출력
(2) 인덱스를 활용하여 최소값(MIN), 최대값(MAX)을 구하는 방법
- MIN / MAX 함수는 모든 데이터를 기준 컬럼으로 정렬한 후 최소값 / 최대값을 구하는 함수
- 인덱스를 활용하면 정렬을 하지 않고 최소값 / 최대값을 구할수 있고 성능향상에 중요한 부분.
① '사원' 테이블에서 name의 min값 구하기
> MIN 함수를 사용했을 경우와 인덱스를 사용하여 최대값을 구했을 경우 비교
② '사원' 테이블에서 name의 max 값 구하기
> MAX 함수를 사용했을 경우와 오라클 힌트를 사용하여 인덱스를 활용한 최대값 구했을 경우 비교.
※ 오라클 힌트를 사용하는 방법
댓글