0201 국비 수업 오라클 index(unique non unique composite) 인덱스 재구축 rebuild 인덱스 힌트 index hint

*index

- 레코드의 양이 많을 때 빠른 검색을 하기 위해 사용되는 객체 (CPU cost가 줄어든다)

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

• 자동인덱스: 테이블을 생성했을 때 자동으로 PK,UX에 의해 자동으로 생성되는 인덱스
                       unique 인덱스가 자동 생성
• 수동인덱스: 개발자가 필요에 의해서 생성하는 인덱스(unique,non unique, bitmap,composite)

-모든 계정은 index를 생성할 수 있음

- user_indexes DD에서 만들어진 인덱스 확인 가능

-모든 레코드는 rowid(레코드를 식별하기 위한 물리적 주소, 겹치지 않음)를 가짐

  AAAShCAAHAAAAKEAAA ( AAAShC 데이터객체번호 AAH 파일번호 AAAAKE 블록번호 AAA 레코드번호)
  DB는 내용에 의한 참조이지만 (같은 값을 식별 시) 주소에 의한 참조를 하기 위해 rowid를 써서 관리함

 

 

문법)

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

 

- unique,non unique, bitmap은 하나, composite은 n개의 컬럼으로 인덱스가 구성됨

 

-인덱스로 선정하는 컬럼의 기준:

•  where절에서 자주 사용되는 컬럼이 인덱스에 반영되면 좋음 (주로 pk컬럼, 완전함수 종속)
•  자주 변경되지 않는 컬럼 (설계의 부재로 부분함수 종속 시) 
•  주의 : 인덱스 컬럼은 함수를 사용한 조회를 하지 않음

---

-인덱스는 테이블에서 도출된 객체이지만, 테이블과는 별도로 동작함 (cf.뷰는 같이 동작)

 시간이 지날수록 테이블과 인덱스 간의 데이터의 차이,괴리가 발생함 > 

 일정 주기로 index를 rebuilding 재구축해야 함 

 

 

• b-tree index를 생성

rowid	컬럼명
실제 레코드 위치	1
	2
	.
	.
	100

where 컬럼명 = 값;

컬럼의 값을 위에서부터 아래로 훑는 full scan하여 검색함

>찾고자 하는 값이 99번째일 때 99번째 레코드를 검색하려면 순차적으로 검색, 99번 비교해서 결과 나옴

 

 

• 인덱스 사용

 인덱스에서 데이터의 구성을 검색하고, 실제 레코드의 위치로 가서 값을 가져오는 실제 검색을 수행함

  > 적은 횟수로 레코드를 검색할 수 있음

 

---

*unique index

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

- 자동 인덱스 중 하나

- 테이블을 생성할 때 PK나 Unique 제약을 설저하면 자동으로 생성됨

 

문법)

-인덱스 생성

create unique index 인덱스명 on 테이블명(컬럼명); // 컬럼의 값이 중복없이 유일해야함, pk컬럼

 

-인덱스 삭제

drop index 인덱스명;

 

-인덱스가 설정된 컬럼을 검색 DD

 user_ind_columns

---

*non unique index

-컬럼의 값이 중복될 때 사용하는 인덱스 (일반컬럼에 주로 적용하는 인덱스)

 

-개발자가 수동으로 생성하는 인덱스

 

문법)

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

---

*bitmap

-컬럼의 값이 중복되나, 독특한 형태의 값일 때 사용하는 인덱스 (ex. 코드값 ICD_0001)

-12c부터 생성 안됨

 

문법)

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

 

---

*composite index

-여러 개의 컬럼으로 인덱스를 구성해야 할 때

 

문법)

create index 인덱스명 on 테이블명(컬럼명,,,);

 

---

*인덱스 재구축(갱신)

-테이블의 변경사항을 적용하여 인덱스를 재구축

 

문법)

alter index 인덱스명 rebuild;

 

---

index hint :

-Oracle DBMS에게 지정한 인덱스를 사용하여 검색하도록 설정함

-조회 컬럼에 /*+  index(컬럼명) 인덱스명  */

 

index를 사용한 검색 > H/W의 자원을 적게 사용하므로, 검색 결과를 빠르게 가져올 수 있음 

  -index가 적용되지 않은 컬럼을 사용하여 검색하면 CPU Cost와 IO Cost 증가

  -index가 적용된 컬럼을 사용하여 검색하면 CPU Cost와 IO Cost 감소