0820_Oracle : INDEX

인덱스 (INDEX) ?

- 일반 테이블이나 클러스에서 쓰여지는 선택적인 객체로서, 오라클 데이터베이스 테이블내의 원하는 레코드를 빠르게 찾아갈 수 있도록 만들어진 데이터 구조이다.

- SQL 명령문의 처리 속도를 향상시키기 위해서 컬럼에 대해서 생성하는 오라클 객체이다.

- 인덱스는 디스크 I/O를 줄이는 많은 방법 중 하나이다.

 

-- 인덱스 확인

SELECT * FROM user_indexes;
SELECT * FROM user_indexes WHERE table_name = 'EMP';
SELECT * FROM user_ind_columns WHERE table_name = 'EMP';

PRIMARY KEY, UNIQUE 컬럼은 자동으로 인덱스가 만들어지며 인덱스 이름은 제약조건의이름이다.

 

-- B-Tree 인덱스 만들기

SELECT empNo, name, sal FROM emp WHERE name = '심심해';

실행하려는 SQL의 범위를 잡고 F10을 누르면 밑에 계획 설명을 보여준다. COST를 보면 SQL문을 실행해서 걸리는 여러 요인들을 계산해서 대략적인 수치를 보여준다. (원래는 3인데, 지금은 INDEX를 만들어 놓은 상태라서 2입니다.)

 

-- B-Tree 인덱스 작성

CREATE INDEX idx_emp_name ON emp ( name ); -- NON UNIQUE

이름은 중복의 값이 있을 수 있기 때문에 UNIQUE 값이 아닙니다.

CREATE INDEX 인덱스명 ON 테이블명 (컬럼) ;

 

여러 예들 ;

SELECT empNo, name, sal FROM emp WHERE SUBSTR(name, 1, 1) = '심';

SELECT empNo, name, sal FROM emp WHERE NOT name = '심심해';

위의 쿼리의 경우 인덱스를 사용하지 않습니다. 비교전에 SUBSTR를 통해 변형되기 때문입니다. 또 NOT을 사용한 문장에서도 인덱스가 사용되지 않습니다.

 

-- 인덱스 삭제

DROP INDEX idx_emp_name;

DROP INDEX 인덱스명; 을 통해 삭제할 수 있습니다.

 

-- 결합 인덱스

CREATE INDEX idx_emp_comp ON emp(name, dept);

AND 조건으로 검색하는 경우 결합 인덱스를 만들면 성능에 중요한 역할을 합니다. OR연산은 인덱스를 만들지 않습니다.

CREATE INDEX 인덱스명 ON 테이블명(컬럼명, 컬럼명);

 

※ 주의

CREATE INDEX idx_emp_comp ON emp(dept, name);

위 쿼리처럼 INDEX를 만들면 dept 에서 name 을 검색하므로, (순서가 다르므로) name을 검색하고 dept을 검색하는 것보다 속도가 느립니다. name을 만족하는 행보다 dept을 만족하는 행이 더 많기 때문입니다. (name이 중복값이 있을 수 있지만 dept보다 행의 개수가 적다.) 따라서 결합 인덱스를 만들 때, 괄호 안의 위치도 신경을 써야합니다.

 

-- 함수 기반 인덱스 만들기

CREATE INDEX idx_emp_fun ON emp(MOD(SUBSTR(rrn, 8, 1), 2 ) ) ;

 

-- 인덱스 관리

-- 모니터링

-- 인덱스 생성
CREATE INDEX idx_emp_name ON emp(name);

-- 모니터링 시작
ALTER INDEX idx_emp_name MONITORING USAGE;

-- 인덱스 사용 유무 확인
SELECT * FROM v$object_usage; -- used :  no

-- 모니터링 중단
ALTER INDEX idx_emp_name NOMONITORING USAGE;

-- REBUILD

 

실습을 위해 테이블을 작성하고 테이터를 10000개를 넣어주었습니다.

CREATE TABLE demo (
    num NUMBER
);

BEGIN
    FOR n IN 1 .. 10000 LOOP
        INSERT INTO demo VALUES (n);
    END LOOP;
    COMMIT;
END;
/

SELECT COUNT(*) FROM demo;

 

-- 위 테이블에 인덱스 추가하기.

CREATE INDEX idx_demo_num ON demo(num);

 

-- 인덱스 분석

ANALYZE INDEX idx_demo_num VALIDATE STRUCTURE;

SELECT * FROM index_stats;

SELECT (del_lf_rows_len / lf_rows_len ) * 100
FROM index_stats
WHERE name = 'IDX_DEMO_NUM'; -- 0에 가까우면 좋은 상태(인덱스가 깨지지 않은 상태)

DELETE FROM demo WHERE num <= 4000;
COMMIT;

ANALYZE INDEX idx_demo_num VALIDATE STRUCTURE;

SELECT (del_lf_rows_len / lf_rows_len ) * 100
FROM index_stats
WHERE name = 'IDX_DEMO_NUM'; -- 40% 정도 밸런싱이 깨짐

세 번째 쿼리를 처음 실행하면 0이 나오는데, 이는 인덱스가 깨지지 않은 상태를 의미합니다. 후 에, 10000개의 데이터에서 4000개를 삭제하면 IDX_DEMO_NUM 이 39.xxxxx 로 나옵니다. 인덱스가 40%정도 깨져있는 것을 확인할 수 있습니다.

 

-- REBUILD

ALTER INDEX idx_demo_num REBUILD; -- 밸런싱에 문제가 있으면 관리자가 다시 조절한다.

-- 인덱스 다시 분석
ANALYZE INDEX idx_demo_num VALIDATE STRUCTURE;

SELECT (del_lf_rows_len / lf_rows_len ) * 100
FROM index_stats
WHERE name = 'IDX_DEMO_NUM' -- 인덱스를 REBUILD 했기 때문에 다시 밸런싱이 좋아짐.

밸런싱에 문제가 있어서 ALTE INDEX 인덱스명 REBUILD; 를 통해 REBUILD하고 나면 다시 0으로 되어있는 것을 확인할 수 있습니다.

 

인덱스는 데이터가 삭제되도 남아있기 때문에 밸런싱이 깨지면 나중에 REBUILD를 하는 것이 필요해보입니다.