SQL_INDEX
INDEX
데이터에 빠르게 접근 가능하게 함으로써
데이터베이스의 성능 향상에 도움을 주는 객체자주 검색되는 필드에 부여하며 책갈피와 같은 기능을 함
INDEX 종류
종류 | 설명 |
---|---|
unique index | primary key가 자동으로 부여 primary key = not null + unique + unique index ※ primary key가 부여된 필드 먼저 검색 |
non-unique index | INDEX의 기본값, 중복을 허용하는 필드에 부여 |
INDEX 장점
- 검색 속도 향상
Oracle에서는 인덱스가 부여된 필드를 먼저 검색 - 시스템 부하를 줄여서 시스템 전체 성능 향상
INDEX 단점
- INDEX를 위한 추가적인 공간이 필요
- INDEX 생성에 시간이 소요됨
- 데이터 변경 작업(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에서의 검색 속도를 향상시킬 수 있습니다.
자주 수정되는 필드의 경우 내부 정렬 과정이 복잡해지기 때문에 오히려 성능을 저하시킬 가능성이 있습니다.