[Basic] 인덱스

1 인덱스 개념

• SQL의 검색 성능과 처리 속도를 향상시키기 위해 칼럼에 대해 생성되는 객체이다. 특정 열(혹은 여러 열의 조합)에 정렬된 키 값과 해당 키 값이 존재하는 테이블의 레코드 위치(ROWID)를 갖고 있다.
  ex) 그냥 table을 보면 full로 스캔할 걸, 인덱스가 있으면 그 인덱스를 따라 더 빠르게 접근.
• 일반적으로 B-트리 형식으로 구성되어 있다(Root Node, Internal Node, Leaf Node를 따라 ROWID를 찾아간다)
• 인덱스가 효율적인 경우
  • where절, join 조건절에 자주 사용되는 칼럼
  • 전체 데이터 중 적은 데이터(10~15%)를 데이터를 자주 검색(6~70%를 찾을 거면 full로 스캔하는 게 낫다)
  • null 값이 많이 포함되어 있거나, 값이 광범위할 경우
  • 데이터 변경이 드문 경우(데이터 변경이 잦으면 인덱스 매번 업데이트)

2 인덱스 종류

2-1 고유 인덱스(Unique index) vs 비고유 인덱스(Non unique index)

• 유일한 값을 가지는 칼럼에 생성하는 인덱스. 모든 인덱스 키를 테이블의 하나의 행과 연결.
  ex) 대학 학과 이름은 모두 고유.

CREATE UNIQUE INDEX idx_dept_name (인덱스 이름)
ON department(dname); (테이블(칼럼))

• 중복된 값을 가지는 칼럼에 생성하는 인덱스. 하나의 인덱스키는 테이블의 여러 행과 연결(하나의 인덱스키에, 여러 개의 ROWID)
  ex) 학생 명부의 생일

CREATE INDEX idx_stud_birthdate
ON student(birthdate);

2-2 결합 인덱스 vs 단일 인덱스

• 위의 2가지는 하나의 칼럼으로만 구성된 인덱스
• 결합 인덱스는 2개 이상으 ㅣ칼럼을 결합해서 생성하는 인덱스
  ex) 학생 명부에서 학과별 학년

CREATE INDEX idx_stud_dno_grade
ON student(deptno, grade);

2-3 DESCENDING INDEX

• 칼럼별로 정렬순서를 별도로 지정해서 결합 인덱스 생성
  DESC는 내림차순, ASC는 오름차순.

CREATE INDEX fidx_stud_no_name 
ON student(deptno DESC, name ASC);

2-4 함수 기반 인덱스(function based index)

• 칼럼에 대한 연산이나 함수의 계산 결과를 인덱스로 생성
  ex) upper 함수를 쓰면 이후에 대소문자 구분 없이 대문자로만 해서 검색 가능. 키와 체중으로 BMI 구하기.

CREATE INDEX uppercase_idx 
ON emp(UPPER(ename));

SELECT *
FROM emp 
WHERE UPPER(ename) = 'KING';

CREATE INDEX idx_standard_weight 
ON student((height-100)*0.9);

3 인덱스 실행 경로 관리

• sql command line의 경우

(권한 양도)
conn system/manager
grant plustrace to hr 
(autotrace 설정)
conn hr/hr
set autot on
(sql 명령문 실행하면 맨 아래 실행계획(execution plan) 나온다)
select *
from emp;
(autotrace 끄기)
set autot off

• sql oracel developer에서 F10을 누르면 계획 설명 나온다.
• 인덱스가 없는 경우 실행 계획에 전체 테이블을 검색했다고 뜨지만, 인덱스가 있는 경우 인덱스를 통해 랜덤 엑세스.

4 인덱스 관리

4-1 인덱스 정보 조회

SELECT index_name, uniqueness (인덱스 이름, 유일성 여부)
FROM user_indexes (유저 인덱스)
WHERE table_name = 'STUDENT'; (테이블 조건)

SELECT index_name, column_name (인덱스 이름, 칼럼 이름)
FROM user_ind_columns (유저 인덱스 칼럼)
WHERE table_name = 'STUDENT'; (테이블 조건)

4-2 인덱스 삭제

DROP INDEX fidx_stud_no_name; (인덱스 이름)

4-3 인덱스 재구성

• 칼럼값이 변했을 떄, 불필요하게 생성된 인덱스 노드 정리.

ALTER INDEX [schema.] stud_no_pk REBUILD
[TABLESPACE tablespace]