[ORACLE] INDEX(인덱스, 색인)

 

인덱스 생성해서 사용하는 이유

• where 절이 있는 select 명령문의 속도를 높이기 위함

 

인덱스 생성 원칙

1. where 절에서 자주 사용되어진 컬럼에 만들어야 한다.

 

2. 선택도(selectivity)가 높은 컬럼에 만들어야 한다.

• 고유한 데이터일수록 선택도가 높다.
• 선택도가 낮다 => 성별은 선택의 여지가 아주 낮기 때문에 선택도가 낮은 편이다.
• 선택도가 높다 => 학번은 고유하고 골라야할 대상이 매우 많으므로 선택도가 높은 것이다.

3. 카디널리티(cardinality)가 높은 컬럼에 만들어야 한다.

• 카디널리티는 집합원의 개수이다.
• 카디널리티가 높다 : '상대적으로' 중복도가 낮아서 고유한 데이터이다.
• 카디널리티가 낮다 : '상대적으로' 중복도가 높아 중복된 데이터가 많다.
• 예) 주민번호는 성명에 비해 '상대적으로' 카디널리티가 높다.

 

 

UNIQUE 한 INDEX 생성하기

- 어떤 컬럼에 unique 한 인덱스를 생성하면, 그 컬럼에는 중복되지 않는 고유한 값만 들어올 수 있다.

- non-unique 한 인덱스보다 검색 속도가 조금 더 빠르다.

[ 문법 ] 
create unique index 인덱스명
on 해당테이블명 (컬럼명 asc | desc);

 

 

1. 인덱스 생성

create unique index idx_tbl_student_1_hakbun
on tbl_student_1(hakbun);

 

2. 행 삽입 :  insert ~ value ~

 - 행이 삽입될 때 hakbun 컬럼에 중복된 값이 있으면 오류가 난다.

 

3. commit;

 

4. 테이블에 생성되어진 index 조회하기

(여기서는 tbl_student_1 테이블에 생성되어진 index 를 조회하겠다.)

select *
from user_indexes
where table_name = 'TBL_STUDENT_1';

select *
from user_ind_columns
where table_name = 'TBL_STUDENT_1';

  위 두개를 JOIN 시켜준다.

                 ↓ ↓ ↓

select A.index_name, A.uniqueness, B.column_name, B.descend -- 이건 임의로 선택한 것임
from user_indexes A JOIN user_ind_columns B
on A.index_name = B.index_name
where A.table_name = 'TBL_STUDENT_1';

 

 

 

NON - UNIQUE 한 INDEX 생성하기

- 어떤 컬럼에 non-unique 한 인덱스를 생성하면 그 컬럼에는 중복된 값이 들어올 수 있다.

- unique 한 인덱스보다 검색 속도가 다소 느린 편이다.

[ 문법 ]
create index 인덱스명
on 해당테이블명 (컬럼명 asc | desc);

 

 

1. 인덱스 생성

create index idx_tbl_student_1_name
on tbl_student_1(name);

 - 동명이인이 있기 때문에 name 컬럼에 중복값이 허용되어야 한다.

 

2. 행 삽입 : insert ~ value ~

 - 행이 삽입될 때 name 컬럼에 중복된 값이 있어도 허용된다.

 

3. commit;

 

4. 테이블에 생성되어진 index 조회하기

select A.index_name, A.uniqueness, B.column_name, B.descend
from user_indexes A JOIN user_ind_columns B
on A.index_name = B.index_name
where A.table_name = 'TBL_STUDENT_1';

 

 ※ 만들어진 인덱스의 형태는 아래와 같다.

-------------------------------------------------------------------------------------------------------------------------
 INDEX_NAME                                      UNIQUENESS       COLUMN_NAME      DESCEND
-------------------------------------------------------------------------------------------------------------------------
 IDX_TBL_STUDENT_1_HAKBUN       UNIQUE                 HAKBUN                   ASC
 IDX_TBL_STUDENT_1_NAME            NONUNIQUE         NAME                       ASC
-------------------------------------------------------------------------------------------------------------------------

 

 

 

인덱스 활용

select *
from tbl_student_1
where hakbun = '2'; 

select *
from tbl_student_1
where name = '이미자';

• unique 한 hakbun 컬럼 / non-unique 한 name 컬럼은 각각 생성되어진 인덱스인
• IDX_TBL_STUDENT_1_HAKBUN, IDX_TBL_STUDENT_1_NAME 인덱스를 사용하여 빠르게 조회해온다.
•  
• 그러나, address 컬럼의 경우에는 인덱스가 없으므로 tbl_student_1 테이블에 있는 모든 데이터를 조회해서 address 컬럼의 값이 일치하는 데이터를 가져온다. 
• Table Full-scan : 인덱스를 사용하지 않고 데이터 조회(테이블 전체 조회) => 속도가 가장 느리다.

 

 

채번