ORACLE|인덱스(Index)
※ Index를 생성하는 것이 좋은 Column
① WHERE절이나 join조건 안에서 자주 사용되는 컬럼
② null 값이 많이 포함되어 있는 컬럼
③ WHERE절이나 join조건에서 자주 사용되는 두 개이상의 컬럼들
◈ 다음과 같은 경우에는 index 생성이 불필요 합니다.
① table이 작을 때
③ 테이블이 자주 갱신될 때
※ B-tree(binary search tree)
-. 오라클 인덱스는 B-tree(binary search tree)에 대한 원리를 기반으로 하고 있습니다.
-. B-tree인덱스는 컬럼안에 독특한 데이터가 많을 때 가장 좋은 효과를 냅니다.
이 알고리즘 원리는
① 주어진 값을 리스트의 중간점에 있는 값과 비교합니다.
만약 그 값이 더 크면 리스트의 아래쪽 반을 버립니다. 만약 그 값이 더 작다면 위쪽 반을 버립니다.
② 하나의 값이 발견될 때 까지 또는 리스트가 끝날 때까지 그와 같은 작업을 다른 반쪽에도 반복합니다.
(1) Bitmap 인덱스
■비트맵 인덱스는 각 컬럼에 대해 적은 개수의 독특한 값이 있을 경우에 가장 잘 작동합니다.
그러므로 비트맵 인덱스는 B-tree 인덱스가 사용되지 않을 경우에서 성능을 향상 시킵니다.
■테이블이 매우 크거나 수정/변경이 잘 일어나지 않는 경우에 사용할 수 있습니다.
예를 들어 여권 기록을 포함하고 있는 테이블의 성별 열이나 결혼 여부 열의 경우에는 B-트리 인덱스 보다는 비트맵 인덱스가 더 유리할 것입니다.
■질의문이 OR 연산자를 포함하는 여러 개의 WHERE 조건을 자주 사용할 때 유리합니다
(2) Unique 인덱스
■Unique 인덱스는 인덱스를 사용한 컬럼의 중복값들을 포함하지 않고 사용할 수 있는 장점이 있습니다.
■프라이머리키 와 Unique 제약 조건시 생성되는 인덱스는 Unique 인덱스입니다.
(3) Non-Unique 인덱스
■Non-Unique 인덱스는 인덱스를 사용한 컬럼에 중복 데이터 값을 가질 수 있습니다.
[B*Tree index] B*Tree인덱스
오라클 인덱스는 B-tree(binary search tree)를 기반으로 하며, 인덱스의 물리적 구조가 좌우 대칭 구조를 이루고 있어 Balance-tree라 한다. B-tree 인덱스는 컬럼안에 독특한 데이터가 많을 때 효과적이다.
B-tree 인덱스는 먼저 주어진 값을 리스트의 중간점에 있는 값과 비교하여 그 값이 크면 작은쪽 리스트의 절반을 버리고, 그 값이 작으면 큰쪽 리스트의 절반을 버린다. 하나의 값이 발견될때까지 또는 리스트가 끝날 때까지 그와 같은 작업을 다른 반쪽에도 반복하여 검색이 이루어진다.
인덱스란 빠른 검색을 위해서 사용하는 독립된 객체이다. 인덱스란 18자리의 ROWID(16진수값)와 열 값으로 구성되어 있다.
【생성형식】
CREATE [UNIQUE] INDEX [schema.]인덱스명
ON [schema.]테이블명 (컬럼 [ASC|DESC] [,컬럼 [ASC|DESC]]...)
[TABLESPACE 테이블스페이스명]
[PCTFREE 값]
[INITRANS 숫자]
[MAXTRANS 숫자]
[storage 절]
[LOGGING | NOLOGGING]
[NOSORT]
[REVERSE];
밸런스 트리 인덱스를 만드는 기준은 다음과 같다.
· 인덱스를 만들어야 하는 열은 DML(insert, update, delete) 문의 WHERE 절에 자주 나오는 열이다.
· 조인되는 테이블의 조인 열에는 인덱스를 만들어야 한다.
· 인덱스에 NULL 값이 저장되지 않기 때문에, NULL 값이 많은 열에 인덱스를 만들면 유리하다.
· DML 문장의 WHERE 절에 의해 검색되는 데이터의 테이블 전체의 10～15%의 범위에 속할 때 인덱스를 만들어야 한다.
· 하나의 테이블에 인덱스를 많이 만들면 update, delete할 때 실행속도가 늦다.(하나의 테이블에 3～4개의 인덱스가 적당하다)
【삭제형식】
DROP INDEX <인덱스명>;
index는 생성 이후, Oracle Server가 자동으로 유지 보수하기 때문에 수정할 수 없다. 따라서 index를 수정하는 대신 이전의 INDEX를 삭제하고 새로 만들어야 한다.
【예제】SQL> select rowid from emp where ename='MILLER';
ROWID
------------------
AAAAsfAABAAAETBAAN
SQL>예제의 출력에서 AAAAsf는 object 번호, AAB은 파일 번호, AAAETB은 블록번호, AAN은 row 번호를 가리킨다.
【예제】
SQL> select * from dba_indexes;
2141 rows selected.
SQL> select index_name,index_type,table_name,table_owner,uniqueness
2 from dba_indexes
3 where table_owner='SCOTT';
INDEX_NAME INDEX_TYPE TABLE_NAME TABLE_OWNER UNIQUENES
----------- ---------- ---------- -------------- ---------
PK_DEPT NORMAL DEPT SCOTT UNIQUE
PK_EMP NORMAL EMP SCOTT UNIQUE
B*Tree 인덱스 CREATE INDEX 인덱스명 ON 테이블명(컬럼명, ...);
Bitmap 인덱스 CREATE BITMAP INDEX 인덱스명 ON 테이블명(컬럼명, ...);
함수기반 인덱스 CREATE INDEX 인덱스명 ON 테이블명(함수식(컬럼명)| 산술식);
