[Oracle] index

 index 

- 많은 양의 레코드에서 필요한 값을 빠르게 검색하기 위해 사용 (검색속도 향상)

- 레코드 수가 적으면 index를 사용하지 않는 것이 더 빠름

- 자동 인덱스, 수동 인덱스를 제공

• 자동 인덱스 : 테이블을 생성할 때 primary key, unque를 생성하면 자동으로 생성되는 인덱스
• 수동 인덱스 : 개발자가 생성하는 인덱스

- unique index, non-unique index, bitmap index, composite index 4가지를 제공

- 실제 테이블과 생성된 index는 별개로 동작함

   (테이블에 DML이 발생하더라도, index에는 반영되지 않음)

   -> 시간이 지날수록 실제 데이터와 인덱스 간의 정보차가 발생

    => 인덱스를 주기적으로 rebuild 해야 함!

- 테이블 삭제 시 인덱스도 함께 삭제 됨

 

- 인덱스로 설정해야하는 컬럼의 선정기준

• where절에서 자주 사용되는 컬럼
• where절에서 사용되더라도 함수를 사용하지 않고 조회하는 컬럼
• 자주 변경되지 않는 컬럼

- 인덱스는 전체 레코드 중 10~15% 정도의 결과가 조회될 때 가장 속도가 빠름

  (인덱스가 항상 빠른 것은 아님)

- 인덱스는 테이블의 주소에 대한 참조 (DBMS는 내용에 의한 참조)

     => Oracle에서는 record의 주소를 rowid 컬럼(유일함)으로 사용

- 인덱스 생성) create [인덱스종류] index on 테이블명 (컬럼명,,,);

     => 해당 테이블의 컬럼을 조회 조건으로 사용하여 검색

- 인덱스 삭제) drop 인덱스 인덱스명;

- 인덱스 갱신) alter index 인덱스명 rebuild;

   -> 실제 테이블과 인덱스의 데이터를 일치시키는 것!

 

* index 종류별 생성문법 

1) unique 인덱스

 - 컬럼의 값이 유일할 때 사용하는 인덱스

 - 컬럼의 값이 null이거나 유일해야 함

 - 테이블을 생성할 때 PK를 설정하거나 unique를 설정하면 자동생성되는 자동 인덱스

- 수동 생성도 가능

 - 문법) create unique index 인덱스명 on 테이블명 (컬럼명);

2) non-unique 인덱스

 - 컬럼의 값이 중복될 때 사용하는 인덱스

 - 문법) create index 인덱스명 on 테이블명 (컬럼명);

3) bitmap 인덱스

 - 컬럼의 값이 키는 아니지만 독특한 형태일 때 사용하는 인덱스

     Ex) LOT 번호

 - 문법) create bitmap index 인덱스명 on 테이블명(컬럼명);

4) composite 인덱스

 - 여러 개의 컬럼이 조합되어 사용되는 인덱스

 - 문법) create index 인덱스명 on 테이블명(컬럼명 ,,, )

 

* 인덱스를 사용한 검색

- index hint를 기술함 : 여러 줄 주석과 한 줄 주석 방식 사용

       /*+ */ : 여러 줄 주석방식

       --+  : 한 줄 주석 방식

- index hint를 사용하면 정렬의 효과를 얻을 수 있음

- select 쿼리에 가장 처음에 기술함 : selelct /*+ 인덱스힌트 */ 컬럼명,,,

  > 인덱스 힌트 : 인덱스 종류 (테이블명(alias) 인덱스명)

       Ex) index (emp PK_EMP)  : 순방향 정렬의 효과

              index_desc (emp PK_EMP)  : 역방향 정렬 // 단, record가 많다면 정렬 안 되고  where절을 사용했을 때만 정렬됨

 

* index 관련 data dictionary

- 테이블에 설정된 인덱스 조회 : user_indexes data dictionary

    > last_analyzed column은 마지막으로 rebuild된 시간을 보여줌

- index로 설정된 컬럼을 조회 : user_ind_columns data dictionary

 

---

# 오늘의 코딩 #

-  인덱스 생성 / 조회를 해보자

 

#인덱스 생성

--사원번호로 non-unique index 생성
create unique index empno_ind on test_emp(empno);

select *
from user_ind_columns
where table_name='TEST_EMP';

#출력결과#

=> 딕셔너리를 조회하면 생성한 인덱스를 확인할 수 있다!

 

---

#인덱스를 이용한 조회

--인덱스를 사용한 조회, select hint 부여
select  /*+ index (test_emp EMPNO_IND)*/ empno, name, sal, hiredate
from    test_emp
where   empno =7788;

#출력결과#

=> 특정 사원번호를 인덱스를 통해 조회 가능!!