상상플러스 :: ORACLE 인덱스(INDEX)

블로그 > The Secret To Success Is To Never Give Up

http://blog.naver.com/nkmin80/140032103146

※ 인덱스란?

인덱스는 테이블이나 클러스트에서 쓰여지는 선택적인 객체로서, 오라클 데이터베이스 테이블내의
원하는 레코드를 빠르게 찾아갈 수 있도록 만들어진 데이터 구조입니다.

자동 인덱스 : 프라이머리 키 또는 uinque 제한 규칙에 의해 자동적으로 생성되는 인덱스 입니다.

수동 인덱스 : CREATE INDEX 명령을 실행해서 만드는 인덱스들 입니다.

※ Index를 생성하는 것이 좋은 Column

① WHERE절이나 join조건 안에서 자주 사용되는 컬럼
② null 값이 많이 포함되어 있는 컬럼
③ WHERE절이나 join조건에서 자주 사용되는 두 개이상의 컬럼들

※ 다음과 같은 경우에는 index 생성이 불필요 합니다.
① table이 작을 때
③ 테이블이 자주 갱신될 때

※ 오라클 인덱스는 B-tree(binary search tree)에 대한 원리를 기반으로 하고 있습니다.

B-tree인덱스는 컬럼안에 독특한 데이터가 많을 때 가장 좋은 효과를 냅니다.

이 알고리즘 원리는

① 주어진 값을 리스트의 중간점에 있는 값과 비교합니다.
만약 그 값이 더 크면 리스트의 아래쪽 반을 버립니다.
만약 그 값이 더 작다면 위쪽 반을 버립니다.

② 하나의 값이 발견될 때 까지 또는 리스트가 끝날 때까지 그와 같은 작업을 다른 반쪽에도
반복합니다.

※ 인덱스는 B-tree 구조를 가지며 크게 다음 네 가지로 분류될수 있습니다.

① Bitmap 인덱스

비트맵 인덱스는 각 컬럼에 대해 적은 개수의 독특한 값이 있을 경우에 가장 잘 작동합니다.
그러므로 비트맵 인덱스는 B-tree 인덱스가 사용되지 않을 경우에서 성능을 향상 시킵니다.
테이블이 매우 크거나 수정/변경이 잘 일어나지 않는 경우에 사용할수 있습니다.

SQL>CREATE BITMAP INDEX emp_deptno_indx
ON emp(deptno);

② Unique 인덱스

Unique 인덱스는 인덱스를 사용한 컬럼의 중복값들을 포함하지 않고 사용할 수 있는 장점이 있습니다.
프라이머리키 와 Unique 제약 조건시 생성되는 인덱스는 Unique 인덱스입니다.

SQL>CREATE UNIQUE INDEX emp_ename_indx
ON emp(ename);

③ Non-Unique 인덱스

Non-Unique 인덱스는 인덱스를 사용한 컬럼에 중복 데이터 값을 가질수 있습니다.

SQL>CREATE INDEX dept_dname_indx
ON dept(dname);

④ 결합 (Concatenated(=Composite)) 인덱스

복수개의 컬럼에 생성할 수 있으며 복수키 인덱스가 가질수 있는 최대 컬럼값은 16개입니다

SQL>CREATE UNIQUE INDEX emp_empno_ename_indx
ON emp(empno, ename);

※ 인덱스의 삭제

- 인덱스의 구조는 테이블과 독립적이므로 인덱스의 삭제는 테이블의 데이터에는 아무런 영향도 미치지 않습니다.

- 인덱스를 삭제하려면 INDEX의 소유자이거나 DROP ANY INDEX권한을 가지고 있어야 합니다.

- INDEX는 ALTER를 할 수 없습니다.

SQL>DROP INDEX emp_empno_ename_indx ;

※ 인덱스에 대한 정보는 USER_INDEXES 뷰 또는 USER_IND_COLUMNS뷰를 통해 검색할 수
있습니다.

※ 인덱스 생성

Oralce 9i에서 인덱스는 Primary Key와 Unique Key와는 별도로 CREATE TABLE의 USING INDEX CREATE INDEX 문법을 이용해서 정의하는 것이 가능해 졌습니다.

아마도 대부분의 사용자들은 다음과 같은 방식을 알고 계실텐데…

예제를 보시면서 어떤 것이 바뀌었는지 확인토록 해보세요~~

SQL> create table test (
c1 varchar2(4) not null,
c2 number(10) not null,
constrint pk_test primary key(c1) using index );

테이블이 생성되었습니다.

위의 create table문은 pk_test라는 이름을 가지는 primary key 제약 조건을 만들며 아울러 pk_test라는 이름을 가진 인덱스를 만듭니다. 이 두가지는 user_ind_colums 뷰와 user_constraints 뷰에서 table_name = ‘TEST’라는 조건을 주시면 확인이 가능합니다.

그러나 9i이후에서는 인덱스에 대해 명시적으로 이름을 주는 것이 가능해 졌는데…

우선 아래의 예제를 참고 하도록 하죠…

SQL> create table test (

c1 varchar2(4) not null,
c2 number(7),
constraint pk_test primary key(c1)
using index
(create index idx_test_c1 on test(c1))
);

테이블이 생성되었습니다.

이 경우는 테이블을 만들면서 primary key를 만드는데 (원래는 default로 pk를 만들게 되면 그 컬럼으로 인덱스를 만듭니다.) 인덱스의 이름은 primary key 이름과 다르게 주기 위해 using index 구안에 create index문을 이용해서 인덱스를 생성했습니다.

--------------------------------------------------------------------
아래의 SQL문중 하나를 이용해 인덱스는 놔두고 PK만 삭제할 수 있습니다.
--------------------------------------------------------------------

SQL> alter table test drop primary key keep index;

테이블이 변경되었습니다.

또는

SQL> alter table test drop constraint pk_test;

테이블이 변경되었습니다.

일	월	화	수	목	금	토
1	2	3	4	5	6	7
8	9	10	11	12	13	14
15	16	17	18	19	20	21
22	23	24	25	26	27	28
29	30	31

상상플러스 - 최근 공지