[SQL] Index

◆ 인덱스의 정의
  - SQL명령문의 처리 속도를 향상시키기 위해 컬럼에 대해 생성하는 객체
  - 책 마지막 부분에 있는 색인과 동일한 기능을 한다.
  - 인덱스는 데이터가 어디 있는지 그 위치를 가지고 있는 주소록


◆ 인덱스의 특징
  - 컬럼 하나에는 하나의 인덱스만 생성 가능하다.(단, 결합인덱스 제외)
  - 인덱스는 사용자들이 많이 찾는 컬럼에 대해 만든다.
  - 인덱스는 주소값만 가지고 있지, 데이터값은 가지고 있지 않다.
  - 인덱스를 쓰면 한 블록만 DB캐시로 끌어올리게 되어 있다.(Single Block I/O)
  - 인덱스를 통해 주소를 찾았는데 해당 데이터들이 각기 한 블록에 하나씩 들어있다면 이때는 인덱스 사용이 더 늦다.
  - 인덱스를 만들면 자동으로 해당 테이블이 사는 같은 공간에 인덱스가 만들어지는데 성능상 테이블과 인덱스가 사는 테이블스페이스는 달리 두어야 한다.

create table test1
   (no number(5)
       constraint test1_no_pk primary key
       using index
       tablespace 인덱스가 들어갈 테이블스페이스명
   );
tablespace 테이블이 들어갈 테이블스페이스명;

◆ 인덱스는 왜 만드는가
  - 데이터가 어디 있는지 관리하려고 주소록을 만드는 것임(절대 SQL 빠르게 하자고 만드는 것이 아님)


◆ 인덱스는 언제 생성하는가
  - where절이나 조인 조건절에서 자주 사용되는 컬럼
  - 전체 데이터 중에서 10~15% 이내의 데이터를 검색하는 경우
  - 두개 이상의 컬럼이 where절이나 조인 조건에서 자주 사용되는 경우
  - 테이블에 저장된 데이터의 변경이 드문 경우


◆ 인덱스의 생성원리 / 작동원리
  1) Full Scan -> Lock 설정
  2) 정렬(시간이 오래 걸림. B*Tree 인덱스일 경우에만 해당. 비트맵 인덱스는 정렬과정 생략)
  3) Block에 기록


◆ 인덱스의 단점
  - DML에 취약하다.
  - 기존에 잘 돌고있던 쿼리에 악영향을 미칠 수 있다.


◆ 인덱스 생성

SQL> create [unique] index 인덱스명
   on 테이블명(컬럼명 [asc | desc] [, 컬럼명 [asc | desc], ...]);

UNIQUE : 고유 인덱스 지정시
ASC | DESC : 인덱스 키를 오름차순 또는 내림차순으로 정렬


◆ 인덱스 삭제

SQL> drop index 인덱스명;

◆ 인덱스 재구성(Index Rebuild)
테이블에 정의된 컬럼값에 대해 변경사항이 자주 발생할 경우 인덱스 키의 정렬 순서를 유지하기 위하여 노드값을 조정해 주는 작업을 의미함.

SQL> alter index 인덱스명 rebuild [online];

ONLINE : 해당 옵션을 안주면 리빌드를 하는 동안에는 해당 인덱스를 사용할 수 없지만, 이 옵션을 줄 경우 인덱스 리빌드를 하는 동안에도 해당 인덱스를 이용할 수 있다. 단, 시간이 많이 걸린다.
따라서 어느 정도 인덱스가 망가졌냐에 따라 리빌드를 하는 것이 좋을지 아니면 인덱스를 새로 만들지 고민해야 한다.


◆ 인덱스 정보 조회 관련 데이터 딕셔너리
  - USER_INDEXES : 어떤 테이블에 어떤 인덱스가 걸려있는지 조회.
  - USER_IND_COLUMNS : 어떤 컬럼에 어떤 인덱스가 걸려있는지 조회.


◆ 고유 인덱스(Unique Index)
유일한 값을 가지는 컬럼에 대해 생성하는 인덱스
만약 해당 컬럼의 값이 중복되는 경우가 있으면 Unique Index 생성이 되지 않는다.
오라클이 제일 좋아하는 인덱스 중의 하나가 유니크 인덱스이다. 왜냐하면 어떤 컬럼에 유니크 인덱스가 생성되었다는 것은 그 값은 유일하다는 것을 보장하기 때문이다.


◆ 비고유 인덱스(Non-Unique Index)
중복간 값을 가지는 컬럼에 대해 생성하는 인덱스


◆ 단일 인덱스
하나의 컬럼으로만 구성된 인덱스


◆ 결합 인덱스
두개 이상의 컬럼을 결합하여 생성되는 인덱스.
where절의 조건 비교에서 두개 이상의 컬럼이 and로 연결되어 자주 사용되는 경우에 주로 생성함.

where deptno = 100
and grade > 2;

위와 같은 경우 and로 연결되어 있기 때문에 결합 인덱스를 사용함.
and 대신 or조건이면 결합인덱스보다는 컬럼별로 따로 인덱스를 생성해주는 것이 효율적임.
연산자에 따라 인덱스의 종류 및 컬럼이 오는 순서도 고려해주어야 한다.

where grade > 2
and deptno = 100;

이렇게 grade와 deptno의 순서가 바뀌었을지라도 결합인덱스의 컬럼순서는 on student (deptno, grade);로 해야 한다. 꼭 where절에 쓰인 순서대로 결합인덱스에 기술하는 것은 아니다. 왜 그런지 그 이유는 튜닝 때 좀 더 자세히 알아본다.


◆ Descending Index
인덱스를 만들면 기본적으로 컬럼의 정렬순서는 오름차순으로 고정되어 있다. 하지만 Descending Index는 컬럼별로 정렬 순서를 지정하여 생성할 수 있다.
문법은 인덱스 생성시 컬럼명 다음에 한칸을 띄고 ASC 또는 DESC를 써주면 된다.
큰 값을 먼저 조회하는 쿼리같은 경우에는 인덱스를 만들 때 큰 값부터 저장되도록 이 descending index로 만들어주면 된다. 아니면 아래 예시처럼 인덱스를 거꾸로부터 읽게끔 해주면 된다.
아래 예들은 학생 테이블의 이름 컬럼에 인덱스를 생성하고난 후, 쿼리성능에 영향을 주는 order by나 그룹함수를 쓰지 않고도 정렬 및 최대, 최소값을 구하는 방법이다.

select name from student;

그냥 테이블의 이름을 조회하면 정렬이 안된 결과 나옴.(현재 해당 컬럼에 인덱스가 없음)

create index idx_student_name
on student(name);

테이블의 이름 컬럼에 인덱스를 만든다.

select name from student
where name > '0';

order by절을 쓰지 않고도 이름에 오름차순 정렬된 결과 나옴.(인덱스 이용)

select /*+ index_desc (s idx_student_name) */
name
from student s
where name > '0';

order by 절을 쓰지 않고도 이름에 내림차순 정렬된 결과 나옴.(인덱스 이용)

select name
from student
where name > ;0'
and rownum = 1;

min이라는 그룹함수를 쓰지 않고도 최소값 하나 읽어옴.(인덱스 이용)

select /*+ index_desc (s idx_student_name) */
name
from student s
where name > '0'
and rownum = 1;

max라는 그룹함수를 쓰지 않고도 최대값 하나 읽어옴.(인덱스 이용)

◆ 함수 기반 인덱스(Function Based Index)
일반적으로 인덱스는 컬럼에 저장된 값에 의해 생성된다. 따라서 where절의 비교 조건에서 컬럼 값에 연산을 적용하면 인덱스를 사용하지 못한다. 하지만 함수 기반 인덱스는 컬럼에 대한 연산이나 함수의 계산 결과를 인덱스로 생성할 수 있다.(8i부터 지원)

create index idx_emp_sal
on emp(sal);

sal 컬럼에 인덱스를 생성한다.

where sal+100 > 500;

위 인덱스를 만든 후 이런 쿼리를 날렸을 경우 위에서 만든 인덱스를 이용하지 못한다.
왜냐하면 오라클은 where절과 부등호 사이를 하나의 컬럼으로 본다.
여기서 인덱스는 sal로만 만들어져 있기 때문에 못타는 것이다.
하지만 아래의 경우를 보자.

where sal > 400;

이런 경우는 인덱스를 탈 수 있다. where절과 부등호 사이에 있는 sal컬럼이 인덱스로 만들어져 있기 때문이다.

create index idx_emp_sal
on emp(sal+100);

위 경우는 이렇게 만들면 해결 가능하다. 이를 함수기반 인덱스라 한다.

◆ B*Tree Index
인덱스를 만들면 기본적으로 B*Tree 인덱스의 형태를 띄게 된다.
간단하게 예를 들어보자.
아래의 인덱스에서 강감찬을 찾으려면 어떤 식으로 값을 찾아가는가?

강감찬을 찾으려면 #9 -> #7 -> #1 블록으로 가서 그 블록에서 강감찬이 몇 번지에 있는지를 찾는다.