(30)[ORACLE] 13일차 : 인덱스(INDEX)

 

 

인덱스란?
인덱스는 일반 테이블이나 클러스터에서 쓰여지는 선택적인 객체로서, 오라클
데이터베이스 테이블내의 원하는 레코드를 빠르게 찾아갈 수 있도록 만들어진 데이터 구조이다.
SQL 명령문의 처리 속도를 향상시키기 위해서 컬럼에 대해서 생성하는 오라클 객체
인덱스는 디스크 I/O를 줄이는 많은 방법 중 하나.
옵티마이저가 최적의 경로를 결정하기 위해 사용하는 요소
옵티마이저는  SQL을 가장 빠르고 효율적으로 수행할 최적(최저비용)의 처리경로를 생성해주는
DBMS 내부의 핵심엔진이다.

 

 

인덱스 특성

1. 인덱스는 연관된 객체의 데이터와 논리적 및 물리적으로 독립적인 스키마 객체
2. 테이블에 물리적으로 영향을 주지 않고 인덱스를 삭제하거나 만들 수 있다.
3. 클러스터링 인덱스는 하나만 생성할 수 있다.
4. 클러스터는 디스크로부터 데이터를 읽어오는 시간을 줄이기 위해서 조인이나 자주 사용되는 테이블의 데이터를 디스크의 같은 위치에 저장시키는 방법

 

인덱스 고려사항 

• 인덱스가 항상 나은 성능을 보장하진 않는다.
• 조건절에 사용하는 컬럼 순서에 따라 인덱스의 사용이 달라질 수 있다.(결합 인덱스는 인덱스 컬럼 순서에 유의해야 한다.)
• INSERT,DELETE,UPDATE가 얼마나 자주 일어나는지 고려해야 한다.
• 인덱스는 타 SQL 실행에 악영향을 줄 수 있다. 테이블에 하나의 인덱스가 존재하는 경우 다른 인덱스를 만들면 쿼리 실행 시간이 느려질 수 있다. 즉, 잘 실행하던 SQL이 새로운 인덱스로 인하여 느려지는 것이다.
• 인데스는 추가적인 저장 공간을 필요로 한다.

 

인덱스를 사용해야 하는 경우	인덱스를 사용하지 말아야 하는 경우
WHERE 절이나 JOIN조건절에서 자주 사용되는 컬럼	테이블에 관리되는 데이터 양이 적은 경우
값이 아주 드물게 존재하는 컬럼(NULL을 포함하는 컬럼이 많은 경우)	WHERE문에 해당 컬럼이 자주 사용되지 않을때
테이블에 관리되는 데이터 양이 많고, 질의할 때 전체 행의 2~4% 검색의 대상이 되는 컬럼	검색 결과가 전체 데이터의 10~15% 이상일때
자주 조합되어 사용되는 컬럼은 결합 인덱스로 생성	테이블에 DML 작업이 많은 경우. 즉 입력, 수정, 삭제 등이 자주 일어날 때

 

▶ 인덱스가 사용되지 않는 경우

• 인덱스 컬럼이 비교되기 전에 변형이 일어난 경우
• 부정형(NOT)으로 조건을 기술한 경우
• 인덱스 컬럼이 NULL로 비교되는 경우 

 

인덱스 종류

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

-- 단일 인덱스(Single Index) : 하나의 컬럼을 사용하여 인덱스를 만드는 것
CREATE INDEX 인덱스명 ON 테이블명(컬럼명);

-- 결합 인덱스(Composite Index) : 두개 이상의 컬럼을 사용하여 인덱스를 만드는 것
CREATE INDEX 인덱스명 ON 테이블명(컬럼명, 컬럼명, ...);

-- 고유 인덱스(Unique INdex) : 유일한 값을 갖는 컬럼에 대해서만 인덱스를 설정
CREATE UNIQUE INDEX 인덱스명 ON 테이블명(컬럼명, ...);

-- Bitmap 인덱스(Express는 지원하지 않음)
CREATE BITMAP INDEX 인덱스명 ON 테이블명(컬럼명, ...);

-- 함수기반 인덱스
CREATE INDEX 인덱스명 ON 테이블명(함수식(컬럼명) | 산술식);

-- 역방향 인덱스
CREATE INDEX 인덱스명 ON 테이블명(컬럼명1,컬럼명2, ...) REVERSE;

-- 내림차순 인덱스
CREATE INDEX 인덱스명 ON 테이블명(컬럼명1,컬럼명2, ... DESC);

 

 

B-Tree indexes

• 일반적인 유형의 데이터베이스 인덱스
• 기본 키 및 선택성이 높은 인덱스에 적합
• 연결된 인덱스로 사용되는 B-Tree 인덱스는 인덱스 된 컬럼별로 정렬된 데이터를 검색할 수 있다.

--B-Tree 인덱스 작성
CREATE INDEX idx_emp_name ON emp(name);
            --NONUINQUE
            
--인덱스 확인
SELECT * FROM user_indexes WHERE table_name='EMP';
SELECT * FROM user_ind_columns WHERE table_name='EMP';            

--인덱스 삭제
DROP INDEX idx_emp_name;

 

 

결합 인덱스

• 두 개 이상의 컬럼을 사용하여 인덱스를 만드는 것

--결합 인덱스 : 두개 이상의 컬럼으로 인덱스 작성

SELECT empNO,name,sal,dept
FROM emp
WHERE name = '이상한' AND dept = '개발부';
--AND 조건으로 검색하는 경우 결합 인덱스를 만들면 성능에 중요한 역할을 한다.
--OR 연산은 인덱스를 만들지 않는다 

--결합 인덱스 작성
CREATE INDEX idx_emp_comp ON emp(name,dept);

--CREATE INDEX idx_emp_comp ON emp(dept,name);
--부서에서 해당 이름을 검색하므로 
--이름에서 부서를 검색하는 것보다 속도가 느리다. 
--즉, 부서를 만족하는 행이 이름을 만족하는 행보다 많으므로

 

 

함수 기반 인덱스

• 함수 기반 인덱스는 표현식을 기반으로 한다.
• B-Tree와 Bitmap 인덱스는 함수 기반 일 수 있다. 

--함수 기반 인덱스

SELECT empNO,name,rrn
FROM emp
WHERE MOD(SUBSTR(rrn,8,1),2)=1;

--함수 기반 인덱스
CREATE INDEX idx_emp_fin ON emp (MOD(SUBSTR(rrn,8,1),2));

--인덱스 삭제 
DROP INDEX idx_emp_fin;

 

 

 

인덱스 정보 조회 

--인덱스 상태 조회
ANALYZE INDEX idx_test_num VALIDATE STRUCTURE; --분석
SELECT * FROM index_stats WHERE name = 'IDX_TEST_NUM'; -- 상태 확인

SELECT (del_lf_rows_len / lf_rows_len)*100
FROM INDEX_stats
WHERE name='IDX_TEST_NUM'; --0에 가까우면 좋은 상태

 

 

INDEX Rebuild

• 테이블은 추가 및 삭제가 되지만, 인덱스는 입력만 되고 삭제 되지 않는다.
• 연속적인 데이터가 다 지워져서 인덱스 블럭이 비게되면 사용 공간을 삭제하고 재사용하는 경우도 있다. 

--인덱스REBUILD

ALTER INDEX idx_test_num REBUILD;

ANALYZE INDEX idx_test_num VALIDATE STRUCTURE; --분석

 

 

 힌트(hint)

-- ◎ 힌트(hint)
---------------------------------------------------
--옵티마이저에게 SQL문 실행을 위한 데이터 스캐닝 경로, 조인 방법등을 알려주기 위해 사용
--형식
SELECT /*_hint_name)param)*/컬럼,컬럼 FROM 테이블명;


--페이징 처리
SELECT * FROM(
    SELECT ROWNUM rnum,empNo,name FROM(
        SELECT empNo,name
        FROM emp
        ORDER BY empNo DESC
        ) WHERE ROWNUM <= 30
    )WHERE rnum >=21;


--힌트(hint)를 이용한 페이징 처리
SELECT rnum, empno, NAME FROM (
    SELECT /*+ INDEX_DESC(emp PK_EMP_EMPNO)*/ ROWNUM rnum, empno, NAME
    FROM emp
    WHERE ROWNUM <= 30
)
WHERE rnum >= 21;

--INDEX+ASC,INDEX+DESC :가장 많이 사용하는 힌트로 인덱스를 순서,역순으로 이용할지 지정