SQL_INDEX

INDEX

데이터에 빠르게 접근 가능하게 함으로써
데이터베이스의 성능 향상에 도움을 주는 객체

자주 검색되는 필드에 부여하며 책갈피와 같은 기능을 함

INDEX 종류

종류	설명
unique index	primary key가 자동으로 부여 primary key = not null + unique + unique index ※ primary key가 부여된 필드 먼저 검색
non-unique index	INDEX의 기본값, 중복을 허용하는 필드에 부여

INDEX 장점

1. 검색 속도 향상
   Oracle에서는 인덱스가 부여된 필드를 먼저 검색
2. 시스템 부하를 줄여서 시스템 전체 성능 향상

INDEX 단점

1. INDEX를 위한 추가적인 공간이 필요
2. INDEX 생성에 시간이 소요됨
3. 데이터 변경 작업(DML)이 자주 일어날 경우에는 오히려 성능이 저하됨
   ※ 자주 수정되는 필드에는 INDEX를 부여하지 않음
   INDEX를 검색할 때마다 내부적으로 정렬을 실행하는데
   자주 수정되는 필드는 인덱스를 자꾸 바꿔줘야 해서 검색 속도가 느려짐

INDEX 생성

§ 형식

[ SQL> create [unique] index 인덱스명 on 테이블명(필드명); ]

# 예제

▶ b_emp5테이블 ename필드에 인덱스 생성
[ SQL> create index b_emp5_ename_idx on b_emp5(ename);
인덱스가 생성되었습니다. ]

INDEX_Data Dictioary

• user_indexes
  인덱스 이름, 유니크 종류(uniqueness),,,
• user_ind_columns
  인덱스 이름, 인덱스 적용 테이블명, 필드명,,,

※ 두 개의 Oracle 자체 테이블이 있으며 두 테이블을 JOIN해서 검색해야 함

※ 구조

{ user_indexes }
▷ [ SQL> desc user_indexes ]

이름	널?	유형
INDEX_NAME	NOT NULL	VARCHAR2(30)
TABLE_NAME	NOT NULL	VARCHAR2(30)
UNIQUENESS		VARCHAR2(9)
...	...	...

{ user_ind_columns }
▷ [ SQL> desc user_ind_columns ]

이름	널?	유형
INDEX_NAME		VARCHAR2(30)
TABLE_NAME		VARCHAR2(30)
COLUMN_NAME		VARCHAR2(4000)
...	...

{ JOIN }
▷ &테이블의 인덱스 이름, 컬럼 이름, 컬럼 위치, 유니크 종류를 검색하는 SQL 문장 작성
[ SQL> select c.index_name, c.column_name, c.column_position, i.uniqueness
from user_indexes i, user_ind_columns c
where c.index_name=i.index_name and c.table_name='&table_name'; ]

 

{ EMP }

INDEX_NAME	COLUMN_NAME	COLUMN_POSITION	UNIQUENESS
PK_EMP	EMPNO	1	UNIQUE

※ EMPNO 필드에 Primary Key가 부여되어 있기 때문에
UNIQUE INDEX가 자동으로 설정되어 있음

 

{ B_EMP5}

INDEX_NAME	COLUMN_NAME	COLUMN_POSITION	UNIQUENESS
B_EMP5_ENAME_IDX	ENAME	1	NONUNIQUE

※ 기본 INDEX를 부여했기 때문에 NONUNIQUE가 설정되어 있음

INDEX 삭제

§ 형식

[ SQL> drop index index명; ]

# 예제

▶ b_emp5_ename_idx 인덱스 삭제
[ SQL> drop index b_emp5_ename_idx;
인덱스가 삭제되었습니다. ]

 

▷ b_emp5 테이블의 INDEX 이름 조회
[ SQL> select i.index_name from user_indexes i, user_ind_columns c
where i.index_name = c.index_name and c.table_name='B_EMP5';
→ 선택된 레코드가 없습니다. ]

---

자주 수정되지 않는 필드에 인덱스를 부여하면 Oracle에서의 검색 속도를 향상시킬 수 있습니다.
자주 수정되는 필드의 경우 내부 정렬 과정이 복잡해지기 때문에 오히려 성능을 저하시킬 가능성이 있습니다.