1. 인덱스란?
데이터들의 ROWID 정보를 별도의 세그먼트(대표적 세그먼트가 테이블)에 넣어서 저장하고 관리하는데 이 세그먼트를 인덱스라 한다.
2. 인덱스 생성 원리
전체테이블 스캔 → 정렬 → Block 기록
3. 인덱스 구조와 작동 원리 (B-TREE 인덱스 기준)
인덱스는 컬럼이 두개다. Key, ROWID
Key 컬럼은 인덱스를 생성하라고 지정한 컬럼 값
SELECT *
FROM 사원
WHERE 이름='홍길동' ;
위 SQL을 수행하면
1. 서버 프로세스가 파싱이란 과정을 마친 후 메모리(데이터베이스 버퍼 캐시)에 이름이 홍길동인 사람의 정보가 있는지 살펴본다.
2. 정보가 없으면 하드 디스크의 파일에서 홍길동 정보를 가진 블록을 복사해서 메모리로 가져온 후 홍길동 정보만 복사해서 사용자에게 보여준다.
(하드디스크의 데이터 파일 블록이 10만개라고 했으므로 서버 프로세스는 홍길동 정보가 어느 블록에 들어있는지 모를 경우
10만개 블록 전부다 메모리로 복사를 해야 한다.)
그래서 이렇게 하기 전에 WHERE 절에 있는 이름 컬럼에 인덱스가 만들어져 있는지 확인한다.
3. 인덱스가 만들어져 있는 경우 인덱스에 먼저 가서 홍길동 정보가 어떤 ROWID를 가지고 있는지 확인한 후
해당 ROWID 에 해당되는 블록만 찾아가서 복사를 해온다.
4. 인덱스의 종류
데이터를 처리하는 방법 중에
OLTP (OnLine Transcation Processing 실시간 트랜잭션 처리용) 시스템인 경우가 있고,
OLAP (OnLine Analytical Processing 온라인 분석 처리용) 시스템인 경우가 있다.
우리가 흔히 접하는 대부분의 경우가 OLTP 환경인데 회원테이블, 판매테이블 등과 같이 실시간으로 데이터가 입력되고 변경되는 경우다.
반면 OLAP 이란 대량의 데이터를 한꺼번에 입력한 후 주로 분석이나 통계 정보 등을 출력할 때 사용하는 환경을 의미한다.
아주 많은 데이터를 넣고 분석하고 통계를 뽑는 경우라서 실시간으로 데이터가 자주 변하지는 않는다.
OLTP 환경에서는 주로 B-TREE 인덱스들이 많이 사용되며
OLAP 환경에서는 BITMAP 인덱스가 많이 사용된다.
(1) B-TREE 인덱스
■ Root Block
┌ | ┐
■ ■ ■ Branch Block
┌ | ┐ ┌ | ┐ ┌ | ┐
■ ■ ■ ■ ■ ■ ■ ■ ■ Leaf Block
실제 테이블의 데이터들의 주소는 Leaf Block 들에 전부 들어있으며, 해당 데이터들에 대한 데이터들이 Branch Block과 Root Block에 들어있다.
특정 데이터를 찾아야 할 경우 Root Block에서 Branch Block 정보를 찾고 Branch Block에서 Leaf Block 정보를 찾아가서 해당 데이터의 ROWID를 찾은 후에 데이터가 들어 있는 블록을 메모리로 복사해 오는 것이다.
B-TREE 에서 B란 Binary 란 의미도 있고 Balance 란 의미도 있다. 즉 Root Block 을 기준으로 왼쪽과 오른쪽에 들어있는 데이터의 Balance가 맞을 때 성능이 가장 좋다는 것이 이 유형 인덱스의 가장 큰 특징이다.
1) UNIQUE INDEX
인덱스 안에 있는 Key 값에 중복되는 데이터가 없다는 뜻이다.
이 인덱스가 성능은 아주 좋은데 혹시 현재 중복된 값이 없다 하더라도 향후에 중복된 값이 입력될 가능성이 있는 컬럼에는 절대로 이 인덱스를 생성하면 안된다.
CREATE UNIQUE INDEX idx_dept2_dname
ON dept2(dname) ;
인덱스 생성시 설정하는 이름은 회사마다 규칙이 있으나 일반적으로 인덱스를 뜻하는 접두어_테이블이름_컬럼이름 을 사용한다.
컬럼 이름 뒤에 asc를 쓰면 오름차순(기본값), desc 를 쓰면 내림차순 정렬로 인덱스가 생성된다.
INSERT INTO dept2
VALUES (9100, '임시매장', 1006, '서울지사') ;
INSERT INTO dept2
VALUES (9101, '임시매장', 1006, '부산지사') ;
*
ERROR at line 1:
ORA-00001: unique constraint (SCOTT.IDX_DEPT2_DNAME) violated
2) Non UNIQUE INDEX
UNIQUE INDEX가 생성이 좋긴 하지만 모든 컬럼에 다 만들 수 있는 것은 아니다.
중복되는 데이터가 들어가야만 하는 컬럼일 경우는 UNIQUE INDEX를 생성할 수 없기 때문에 이럴 경우 Non UNIQUE INDEX를 생성한다.
CREATE INDEX idx_prof_position
ON professor (position DESC) ;
3) Function Based INDEX (FBI - 함수기반 인덱스)
인덱스는 WHERE 절에 오는 조건 컬럼이나 조인 컬럼 등에 만들어야 한다.
예를 들어 WHERE SAL = 100 이란 조건일 경우 SAL 컬럼에 인덱스를 만들어야 한다.
만약 SAL 컬럼으로 인덱스를 생성했는데
WHERE SAL + 100 = 200 이라는 조건으로 조회를 했다고 하면
SAL 컬럼의 인덱스는 사용할 수 없게 된다.
이런 현상을 INDEX Suppressing Error 라고 한다.
※ 인덱스를 사용하려면 WHERE 절의 조건을 절대로 다른 형태로 가공해서 사용하면 안된다.
꼭 이렇게 써야 한다면 인덱스를 함수기반 인덱스로 생성하면 된다.
CREATE INDEX idx_prof_pay_fbi
ON professor (pay+100) ;
위와 같이 생성 했어도 쿼리의 조건이 변경된다면 인덱스를 다시 만들어야 하고, 기존 인덱스를 활용할 수 없다는 단점도 있다.
4) DESCENDING INDEX
DESC INDEX 는 큰 값이 먼저 오도록, 즉 내림차순으로 인덱스를 생성하는 것이다.
주로 최근 날짜부터 보여주어야 하는 계좌조회, 매출이 큰 값부터 보여주는 매출 테이블 등에 쓴다.
하나의 메뉴에 오름차순, 내림차순을 한꺼번에 조회할 경우 같은 컬럼에 인덱스를 또 만드냐?
(인덱스가 많으면) DML 성능에 악영향을 미치게 되어 안된다.
그래서 오라클에서는 인덱스를 위에서부터 읽게하거나 아래서부터 읽게하는 힌트라는 방법을 제공한다.
이 힌트들을 이용하여 튜닝에서는 정렬을 하지 않고 정렬을 한 효과를 내기도 하고 최대값과 최소값 등을 구하기도 한다.
CREATE INDEX idx_prof_pay
ON professor (pay DESC) ;
5) 결합 인덱스 (Composite INDEX)
결한 인덱스란 인덱스를 생성할 때 두 개 이상의 컬럼을 합쳐서 인덱스를 만드는 것을 의미한다.
이 인덱스는 주로 SQL 문장에서 WHERE 절의 조건 컬럼이 2개 이상이 AND로 연결되어 함께 사용이 되는 경우에 많이 사용하게 된다.
결합 인덱스를 만드는 컬럼의 배치 순서에 따라 성능이 차이가 날 수 있다.
SELECT 이름, 성별
FROM 사원
WHERE 성별 = '여자'
AND 이름 = '유관순' ;
CREATE INDEX idx_사원_성별_이름
ON 사원 (성별, 이름) ;
CASE1: 성별 + 이름
50명 → 여자 → 25명 → 유관순 → 2명
25회 검사
CASE2: 이름 + 성별
50명 → 유관순 → 2명 → 여자 → 2명
2회 검사
※ 조회할 데이터를 한 번에 많이 줄여 놓는게 빠르다.
(2) BITMAP INDEX
B-TREE 형식의 인덱스는 주로 데이터의 값의 종류가 많고 동일한 데이터가 적을 경우에 사용하는 인덱스들이었다.
BITMAP 인덱스는 데이터의 값의 종류가 적고 동일한 데이터가 많을 경우에 많이 사용하는 인덱스 이다.
예를 들어 학생 테이블로 치면 성별 컬럼엔 남, 여의 값만 있을 것이다.
학생이 1000명이라 하더라도 성별 컬럼의 값의 종류는 2가지 뿐이므로 BITMAP INDEX가 적당하다.
그러나 같은 테이블이라도 학번컬럼에는 1000개의 다른 데이터가 있을 것이므로 B-TREE INDEX가 적당하다.
CREATE BITMAP INDEX idx_사원_성별_bit
ON 사원 (성별) ;
5. 인덱스 주의사항
(1) DML에 취약하다.
1) INSERT
테이블은 데이터가 입력될 때 정렬되지 않고 차례대로 입력되나 인덱스는 정렬되어야 하는데 기존 블록에 여유 공간이 없는 상태에서 블록에 새로운 데이터가 입력되어야 할 경우 오라클은 기존 블록의 내용 중 일부를 새 블록에다가 기록한 후 기존 블록에 빈 공간을 만들어서 새로운 데이터를 추가하게 된다.
즉 하나의 블록에 있던 내용이 두 개의 블록으로 나누어진다는 의미로 INDEX Spilit이라 부른다.
2) DELETE
테이블에서는 지워져도 인덱스에는 DELETE가 되지 않는다.
3) UPDATE
INDEX 에는 UPDATE 개념이 없다.
DELETE가 먼저 발생한 후 INSERT 실행되어 부하가 증가된다.
(2) 타 SQL 실행에 악영향을 줄 수 있다.
옵티마이져가 실행 계획을 세우는데 갑자기 없던 인덱스가 테이블에 생성되면 최근에 만들어진 것이 더 좋을 것이라 생각하고 잘 되고 있던 실행계획을 바꾸기 때문이다. 만약 바꾼 실행계획이 더 빨라진다면 다행이지만 그렇지 않은 경우도 많기 때문에 어떤 테이블에 새로운 인덱스를 생성해야 할 경우 기존에 있던 다른 SQL 문장들까지 전부 고려한 후 인덱스를 생성해야만 한다.
6. 인덱스 관리방법
(1) 인덱스 조회하기
USER_INDEXS 생성된 인덱스 조회
USER_IDN_COLUMS 컬럼 이름까지 모두
DBA_INDEXES 데이터베이스 전체
DBA_IND_COLUMS 데이터베이스 전체에서 컬럼 이름까지
(2) 사용 여부 모니터링 하기
모니터링 시작
ALTER INDEX idx_dept2_dname MONITORING USAGE ;
모니터링 중단
ALTER INDEX idx_dept2_dname NOMONITORING USAGE ;
사용유무 확인하기
SELECT index_name, used
FROM v$object_usage
WHERE index_name = 'IDX_DEPT2_DNAME' ;
해당 계정의 인덱스만 볼 수 있다.
(3) INDEX Rebuild 하기
앞에서 살펴본대로 테이블은 데이터가 입력도 되고 삭제도 되지만 인덱스는 입력만 되고 삭제는 되지 않는다.
그래서 대량의 DML작업 등을 수행한 후에는 일반적으로 인덱스의 밸런싱 상태를 조사해서 문제가 있는 경우 수정해 주는 작업들을 수행한다.
즉 인덱스는 한번 만들어 놓으면 영구적으로 잘 작동하는 것이 아니라 생성 후에도 꾸준하게 관리해 주어야 좋은 성능을 기대할 수 있다는 뜻이다.
STEP1 테스트용 테이블을 생성하고 데이터를 넣고 인덱스를 생성
CREATE TABLE i_test
(no number) ;
BEGIN
FOR i IN 1..1000 LOOP
INSERT INTO i_test VALUES (i) ;
END LOOP ;
COMMIT ;
END ;
/
CREATE INDEX IDX_ITEST_NO
ON i_test(no) ;
STEP2 인덱스의 상태를 조회
ANALYZE INDEX idx_itest_no VALIDATE STRUCTURE ;
SELECT (del_lf_rows_len / lf_rows_len) * 100 BALANCE
FROM index_stats ;
BALANCE
----------
0
0 에 가까울 수 록 좋은 상태를 의미한다.
STEP3 테이블에서 1000건의 데이터 중 300건을 지운 후 인덱스 상태를 조회한다.
DELETE
FROM i_test
WHERE no BETWEEN 1 AND 300 ;
SELECT COUNT(*)
FROM i_test ;
SELECT (del_lf_rows_len / lf_rows_len) * 100 BALANCE
FROM index_stats ;
BALANCE
----------
0
ANALYZE INDEX idx_itest_no VALIDATE STRUCTURE ;
SELECT (del_lf_rows_len / lf_rows_len) * 100 "BALANCE"
FROM index_stats ;
BALANCE
----------
29.5346182
대략 30% 정도 밸런싱이 망가진 상태다.
STEP4 Rebuild 작업으로 수정한다.
ALTER INDEX idx_itest_no REBUILD ;
ANALYZE INDEX idx_itest_no VALIDATE STRUCTURE ;
SELECT (del_lf_rows_len / lf_rows_len) * 100 BALANCE
FROM index_stats ;
BALANCE
----------
0
Rebuild 옵션 중에 ONLINE 옵션은 Rebuild 중에 데이터를 사용 가능하게 해주지만 전체적인 성능이 많이 떨어지기 때문에 잘 선택해서 사용해야 한다.
7. 인덱스 활용 예제
(1) 인덱스를 활용하여 정렬한 효과를 내는 방법
정렬은 모든 DBMS에 부하를 아주 많이 준다.
ORDER BY 를 사용하지 않고 정렬할 수 있는 방법이 INDEX다.
인덱스를 활용한 정렬하기
STEP1 예제 사원 테이블을 생성하고 데이터를 입력
CREATE TABLE MEMB
(
no number,
name varchar2(10),
sal number
) ;
INSERT INTO MEMB VALUES (1, '홍길동', 300) ;
INSERT INTO MEMB VALUES (2, '김설희', 250) ;
INSERT INTO MEMB VALUES (3, '모병환', 430) ;
INSERT INTO MEMB VALUES (4, '도루묵', 220) ;
INSERT INTO MEMB VALUES (5, '나얼짱', 620) ;
INSERT INTO MEMB VALUES (6, '심슨', 810) ;
COMMIT ;
STEP2 name 컬럼에 인덱스를 생성한다.
CREATE INDEX idx_memb_name
ON memb(name) ;
STEP3 인덱스를 사용하지 않는 일반적인 SQL을 작성한다.
SELECT name
FROM memb ;
인덱스가 있어도 정렬되지 않음.
STEP4 인덱스를 사용하도록 SQL을 작성
SELECT name
FROM memb
WHERE name > '0' ; ← 인덱스를 쓰라는 의미
(2) 인덱스를 활용하여 최소값, 최대값을 구하는 방법
MAX / MIN 함수는 모든 데이터를 기준 컬럼으로 정렬한 후 최대값 / 최소값을 구하는 함수이다.
성능상 문제가 되는 부분은 모든 데이터를 다 읽은 후 정렬을 한다는 것이다.
그래서 정렬을 하지 않고 최대값 / 최소값을 구하는 것이 성능 향상에 중요한 부분이다.
SELECT MIN(name)
FROM memb ;
SORT 발생함.
SELECT name
FROM memb
WHERE name > '0' ← 인덱스를 쓰라는 의미
AND rownum = 1 ; ← 1건만 출력하라는 의미
SORT 발생 안함.
인덱스를 아래쪽부터 읽으라는 것은 오라클 Hint 라는 기능을 이용하면 된다.
Hint 란 실행 계획을 세워주는 옵티마이저에게 사람의 의도를 알려주는 기능으로 보다 자세한 내용은 SQL 튜닝 부분을 참조.
SELECT MAX(name)
FROM memb ;
SELECT /*+ index_desc (s idx_memb_name) */ name
FROM memb s
WHERE name >= '0'
AND rownum = 1 ;
이 방식은 인덱스가 삭제되거나 인덱스 컬럼이 변경된다면 잘못된 결과를 만들어낸다.
SELECT /*+ index_desc (s idx_memb_name) */ MAX(name)
FROM memb s
WHERE name >= '0' ;
8. Invisible Index (인비저블 인덱스) - 11g New Feature
인덱스가 많을 경우 DML 문장에 나쁜 영향을 주기 때문에 사용하지 않는 인덱스는 삭제해 주는 것이 좋다.
문제는 해당 인덱스를 삭제하려고 했을 때 정말 사용하지 않는지 알아야 한다는 것인데
9i부터 이 부분을 도와주는 기능으로 인덱스 사용 유무를 모니터링 하는 기능이 등장했다는 것도 앞에서 살펴보았다.
그런데 만약 모니터링 기간이 잘못되었다던지 해서 인덱스를 삭제했는데 나중에 생각지도 못한 부분에서 문제가 발생할 수 있다.
11g에서는 인덱스를 삭제하기 전에 "사용 안함" 상태로 만들어서 테스트 해볼 수 있는 기능을 제공한다.
이것이 Invisible Index 이다.
CREATE INDEX idx_emp_ename ON emp(ename) ;
SELECT table_name, index_name, visibility
FROM user_indexes
WHERE table_name = 'EMP' ;
TABLE_NAME INDEX_NAME VISIBILITY
------------------------------ ------------------------------ ----------
EMP IDX_EMP_ENAME VISIBLE
EMP PK_EMP VISIBLE
ALTER INDEX IDX_EMP_ENAME INVISIBLE ;
SELECT table_name, index_name, visibility
FROM user_indexes
WHERE table_name = 'EMP' ;
TABLE_NAME INDEX_NAME VISIBILITY
------------------------------ ------------------------------ ----------
EMP IDX_EMP_ENAME INVISIBLE
EMP PK_EMP VISIBLE
INVISIBLE 한다는 의미는 옵티마이져가 실행 계획을 세울 때 해당 인덱스를 보여주지 않는다는 의미이며,
즉, 옵티마이져는 인비저블로 설정한 인덱스는 없다고 생각하고 실행계획을 세우게 된다.
그러나 DML 작업시 인덱스 내용은 계속 반영되므로 인덱스가 지워진 것은 아니다.
따라서 INVISIBLE 로 설정한 후 점검하여 다른 SQL 문장에 영향을 주는 것이 없는 것으로 확인되면 해당 인덱스를 지우면 된다.
1. 다시 상태를 VISIBLE 로 변경하기
ALTER INDEX IDX_EMP_ENAME VISIBLE ;
SELECT table_name, index_name, visibility
FROM user_indexes
WHERE table_name = 'EMP' ;
2. SQL 힌트 구문에서 해당 인덱스를 사용하게 하기
SELECT /*+ index (emp idx_emp_ename) */ ename
FROM emp
WHERE ename > '0' ;
[출처] 다양한 예제로 쉽게 배우는 오라클 SQL과 PL/SQL 서진수 저