출처 : http://naver.kaist.ac.kr/oracle_sql/s15.htm
1. 인덱스 생성 개요
◈인덱스는 포인터를 써서 행을 빠르게 검색할 수 있는 데이터베이스 객체이다.
◈인덱스는 명시적 또는 자동적으로 생성 할 수 있으며 생성후 바로 사용된다.
◈Column에 대한 인덱스가 없으면 한 테이블 전체를 검색하게 된다.
♣ 인덱스란?
◈인덱스는 테이블의 값을 빠르게 액세스 하도록 하는 데이터베이스 객체이다.
◈데이터를 빠르게 찾기 위한 B*트리를 써서 디스크 입출력 횟수를 줄인다.
◈인덱스를 만들면 사용자가 직접 조작할 필요가 없게 된다.
◈인덱스는 논리적으로도 물리적으로도 테이블과는 독립적이다.
◈언제든지 생성하거나 삭제할 수 있으며 테이블이나 다른 인덱스에 영향을 주지 않는다는 의미이다.
♣ 인덱스 생성방법
1.자동생성
◈테이블 정의에 PRIMARY KEY나 UNIQUE 제약조건을 정의할 때 unique 인덱스가 자동적으로 생성
2.사용자가 생성
◈행에 대한 액세스 속도를 빠르게 하기 위해 Coulmn에 non_unique 인덱스를 생성
♣ 인덱스의 구조(B*Tree)
◈각 인덱스는 페이지로 구성된 포인터(또는 ROWID)와 Column값으로 구성된다.
◈서버는 포인터를 갖는 값이 있는 노드에 이를 때까지 트리를 탐색한다.
◈B*Tree인덱스 구조를 이용한다.
◈이 트리는 어떤 행에 대한 액세스 횟수도 동일하게 한 이진의 균형탐색 구조이다.
◈행이 테이블의 시작이나 중간, 또는 끝에 있어도 거의 같은 횟수 내에 지정된 값을 액세스하는
효율적인 방법이다.
◈인덱스는 트리에 정렬된 여러 개의 저장장소 페이지로 구성된다.
◈각 페이지는 키 값이 데이터 자체의 위치를 가리킬 때까지 구조의 아래 쪽으로 향하는 페이지에 대한
포인터와 일련의 키값을 갖고 있다.
♣ 인덱스의 유형
| 유형 | 설명 |
| Unique인덱스 | 지정된 Column의 값이 고유함을 보장 |
| Non_Unique인덱스 | 데이터를 질의할 때 가장 빠른 결과를 보장 |
| 단일 Column인덱스 | 오직 한 개의 Column만이 인덱스에 있음 |
| 연결 인덱스 또는 조합 인덱스 | 성능이나 고유함 검사를 목적으로 인덱스에 16개까지의 Column을 포함 |
2. 인덱스 생성
◈Syntax
CREATE INDEX 인덱스명
ON 테이블(Column[,Column]...);
◈ 인덱스 생성:Guideline
1. 인덱스를 만드는 때
◈ WHERE절이나 조인 조건에서 Column을 자주 이용할 때
◈ Column이 넓은 범위의 값을 가질때
◈ 많은 NULL 값을 갖는 Column일 때
◈ 테이블의 Data가 많고 그 테이블에서 Query되는 행의 수가 전체의 10-15%정도 일때
2. 인덱스를 만들지 않아야 할 때
◈ 테이블이 작을때
◈ Column이 질의의 조건으로 사용되는 경우가 별로 없을 때
◈ 대부분의 질의가 행의 10-15% 이상을 검색한다고 예상될때
◈ 테이블이 자주 변경될때
♠실습 : s_emp테이블의 last_name column에 Query엑세스 속도를 빠르게 하기 위한 인덱스를 만드시오.
| SQL> CREATE INDEX s_emp_last_name_idx ON s_emp(last_name); |
♣ 인덱스 확인
◈ USER_INDEXES Dictionary 뷰는 인덱스의 이름과 Unique 여부를 가지고 있다.
◈ USER_IND_COLUMNS 뷰는 인덱스 명, 테이블 명과 Column 명을 가지고 있다.
◈ USER_INDEXES Dictionary 뷰에 인덱스가 있는지 확인하시오.
◈ USER_IND_COLUMNS 뷰를 Query 하여 인덱스를 갖고 있는 Column을 확인할 수도 있다.
♠실습 : S_EMP 테이블에서 이미 생성된 인덱스, 관련된 Column 명, Unique 여부를 Display 하라.
| SQL> SELECT ic.index_name, ic.column_name, ic.column_position, ix.uniqueness |
3. 인덱스 삭제
◈인덱스를 수정할 수는 없다.
◈인덱스를 변경하려면 삭제한 다음 다시 만들어야 한다.
◈INDEX 명령을 써서 인덱스를 삭제하라.
◈인덱스를 삭제하려면 그 인덱스의 소유자이거나 DROP ANY INDEX 권한을 가지고 있어야 한다.
DROP INDEX 인덱스명;
♠실습 : S_EMP_LAST_NAME_IDX 인덱스를 삭제하고 Display 하라.
| SQL> DROP INDEX s_emp_last_name_idx; |