인덱스 정의
인덱스 생성 전후 실행계획과 버퍼 비교, USING INDEX와 컬럼 가공 및 함수 기반 인덱스
#핵심 요약
CREATE INDEX 이름 ON 테이블(컬럼)
책의 목차처럼 찾아가므로 full table scan보다 빠르게 검색할 수 있음
실습: sal = 3000 조회의 메모리 읽기(consistent gets)가 인덱스 생성 전 7, 생성 후 3이었음
WHERE 절에서 인덱스 컬럼을 함수나 연산으로 가공하면 인덱스를 쓰기 어려움
가공을 상수 쪽으로 옮기거나 함수 기반 인덱스를 만듦
인덱스를 많이 만들면 입력 속도가 느려지고 저장 공간을 많이 차지함
#개념과 동작 원리
실습 용어:
| 항목 | 설명 |
|---|---|
db block gets, consistent gets |
Oracle 메모리에서 읽은 버퍼 개수 |
physical reads |
데이터베이스(디스크)에서 읽은 블록 개수 |
| buffer | SQL을 실행할 때 DB에서 읽어들인 블록 개수 |
인덱스를 만드는 방법은 두 가지임CREATE INDEX로 직접 만들거나, primary key, unique 제약을 걸 때 자동으로 만들어짐
자동 인덱스 이름은 제약 이름과 같으므로(제약조건), 인덱스 이름 규칙(예: emp_empno_idx)을 지키고 싶으면 USING INDEX (CREATE INDEX...)로 함께 정함
인덱스 사용과 실행 계획의 자세한 내용은 성능 분석과 튜닝 분야에서 다룸
#필수 명령어 또는 SQL
#인덱스 생성과 효과 확인
CREATE INDEX emp_sal ON emp (sal);SELECT ename, sal
FROM emp
WHERE sal = 3000;결과 해석:
실습 기록: 인덱스 없을 때 db block gets 0, consistent gets 7, physical reads 0
인덱스 만든 뒤 consistent gets 3
실습 테이블 14행 기준임
SELECT /*+ index(emp emp_ename_un) */ ename, sal
FROM emp
WHERE ename = 'SCOTT';결과 해석:
실습에서는 /*+ full(emp) */ 힌트와 비교해 인덱스 힌트(/*+ INDEX(테이블 인덱스명) */)를 보였음
#가공된 조건 고치기
| 튜닝 전 | 튜닝 후 |
|---|---|
WHERE SUBSTR(job, 1, 5) = 'SALES' |
WHERE job LIKE 'SALES%' |
WHERE TO_CHAR(hiredate, 'DD/MM/RR') = '17/11/81' |
WHERE hiredate = TO_DATE('17/11/81', 'DD/MM/RR') |
WHERE sal * 12 = 36000 |
WHERE sal = 36000 / 12 |
WHERE job || sal = 'SALESMAN1250' |
WHERE job = 'SALESMAN' AND sal = 1250 |
결과 해석: 튜닝 후 문장은 인덱스 컬럼을 가공하지 않고 그대로 비교함
마지막 예는 수업 자료에서 이수자 평가 문제로 쓰였음
#USING INDEX로 인덱스 이름 정하기
CREATE TABLE emp50
( empno NUMBER(10)
CONSTRAINT emp50_empno_un UNIQUE
USING INDEX (CREATE INDEX emp50_empno_idx ON emp50 (empno)),
ename VARCHAR2(20) );결과 해석:
수업 자료 메모에는 첫 컬럼 정의까지만 있어 ename 컬럼과 닫는 괄호는 문장을 완성하려고 붙였음
#함수 기반 인덱스
실습 상황: 이름을 대소문자 구분 없이 찾으려고 UPPER(ename)을 쓰면 ename 인덱스를 쓰지 못해 full table scan이 됨
연습 문제(계산식을 미리 계산해 인덱스로 구성하기):
SELECT ename, sal * (12 + NVL(comm, 0)) 소득
FROM emp
WHERE sal * (12 + NVL(comm, 0)) >= 5600;결과 해석:
같은 식으로 인덱스를 만들면 다음과 같음(답 확인 불가)
CREATE INDEX emp_income_idx ON emp (sal * (12 + NVL(comm, 0)));결과 해석: 개별 실행 출력은 기재 없음
#사용 조건과 주의사항
연습 문제 "scott이 가진 인덱스를 모두 삭제"는 답이 없음USER_INDEXES로 삭제 문장을 만드는 방식은 뷰의 일괄 삭제와 같음
제약이 만든 인덱스는 DROP INDEX로 지울 수 없을 수 있음(확인 불가)