|
|
|
![]()
| [일반] 오라클 인덱스 작성 규칙 ORACLE |
|
| 호석 님께서 쓴 글입니다 - 211.♡.74.31 |
읽음:4383 |
|
|
오라클 인덱스 작성 규칙 ORACLE
2007/02/06 17:02
http://blog.naver.com/visualjd/40034127888
오라클 인덱스 작성 규칙 조회 (258)
Database | 2005/07/31 (일) 20:32 공감 (0) 스크랩 (0)
* 인덱스를 작성해야 하는 경우
대형테이블이나 where 절에서 단순 일치성 검사를 하는 경우 작성해야 하며 where name='설렘이' and blog='설레는 마음으로'와 같은 경우에도 효과적이다. 또한 join시에도 적용이 되며 인덱스가 지정된 열에 대해 is not null과 is null을 사용하는 경우를 제외하고는 where 절에서 사용한 경우 검색 속도가 더욱 빠르다. 그러나 크기가 작은 테이블은 primary key 제약이 아니라면 인덱스가 없는 것이 유리하다. 크기가 작은 테이블은 행이 30개 이하이며 경우에 따라서는 100개 이하도 소규모로 취급하기도 한다.
* 인덱스 자동 생성 조건
primary key 제약이나 unique 제약이 있는 column을 생성하는 경우 자동으로 유일인덱스를 작성한다.
* 인덱스 작성문
create index indexSample on tableSample( columnSample1 [, columnSample2........] );
- indexSample: 인덱스 명칭, 유일한 명칭이며 오라클의 열에 대한 명명 규칙을 지켜야 한다. 인덱스 명칭은 data dictionary view, user_indexes 테이블에도 들어있으며 여기에서 조회가 가능하다.
- 인덱스 명칭에 대한 하나의 예시는 테이블 명과 컬럼명의 조합이다. concert 테이블에 대해 singer와 title의 열에 인덱스를 작성한다면 CONCERT_SINGER_TITLE 이라고 작성할 수 있다.
- tableSample: 테이블 명칭
- columnSample1, columnSample2: 인덱스가 작성되는 해당 열 명
- 인덱스 작성문 예시
create index CONCERT_SINGER_TITLE on concert(singer, title);
* 인덱스가 사용되는 경우
where 절에서 비교 대상이 되는 열에 인덱스가 지정되어 있는 경우 해당 열을 검색 시에 인덱스를 사용한다. 그러므로 중요한 열에 어떻게 인덱스를 지정하는가에 따라 조회 시 응답속도를 단축시킬 수 있다. 또한 관련 열에 인덱스가 지정되어 있으면 join된 조회의 경우에도 조회속도를 높여 줄 수 있다. order by도 인덱스를 사용하지만 distinct와 group by는 모두 인덱스를 사용하지 않는다.
* 인덱스가 무시되는 경우
인덱스의 유무를 탐지할 수 있는 단순 열에 대해 적용되어야만 인덱스의 장점이 활용된다. 즉 substr이나 || 와 같은 함수에 의해 변형되어 있다면 오라클은 인덱스를 사용하지 않는다. 그러나 min과 max도 열 자체와 함께 사용된다면 인덱스의 장점을 활용할 수 있다. 또한 클러스터 인덱스를 제외하고는 null인 열은 인덱스 상에 나타나지 않는다. 하나 이상의 열에 대해 지정된 인덱스는 전체 열 중에 어느 하나라도 null이 아니면 엔트리를 가진다. 모든 열이 null이면 인덱스에 이와 관련된 엔트리는 생기지 않는다.
* 인덱스가 지정된 열의 다양함
인덱스는 많은 양의 data가 다양하게 작성된 열에 대해 가장 유용하다. 즉 'Y'나 'N' 두 개의 값을 갖는 열에는 좋지 못한 선택이다. 실제로 조회를 느려지게 만들 수 있다. 전화번호는 훌륭한 후보이며 지역번호 열은 특히 효과적이다. 기본키가 하나 이상의 열을 포함하고 있는 경우에는 변화가 많은 것을 기본키 제약 조건에 먼저 위치시키는 것이 좋을 것이다. 다양성이 비슷하다면 가장 자주 접근되는 쪽을 먼저 위치시키는 것이 좋다.
설렘이 생각)
실제 인덱스는 다음과 같이 생겼다.
singer title 위치
김장훈 김장훈 스런 콘서트 0000204C.0001.0001
이소은 은소리 콘서트 0000204C.0002.0001
이승환 난리 0000204C.0004.0001
이승환 이승환이 꿈꾸는 음악회 0000204C.0003.0001
그러나 'Y' 나 'N'을 가지는 열에 대한 인덱스는 다음과 같다.
enableYN 위치
N 0000204C.0004.0001
N 0000204C.0001.0001
Y 0000204C.0003.0001
Y 0000204C.0002.0001
위의 인덱스에 비해 무슨 의미가 있겠는가!! 가나다 순으로 정렬이 된다해도 단 N과 Y의 차이일 뿐이다. 또한 전체 엔트리 중에서 하나의 엔트리의 위치를 찾아 접근하는 것과 전체 엔트리의 반 정도의 위치를 찾아 접근하는 것도 다를 것이다. 순서대로 전부 읽는 것과 반의 위치를 찾아 읽는 것이 그리 다를 것 같지 않다.
* 테이블에 사용할 수 있는 인덱스의 수
1개의 테이블에 최고 16개까지의 열에 대해 단일 인덱스를 작성하는 것이 가능하다. O.S에 따라서는 인덱스 당 최고 1000~2000 byte의 용량 점유가 가능하다. 그러나 인덱스를 너무 많은 열에 대해 지정하는 경우 새로운 행 삽입 시 속도가 느려진다. 이것은 행이 insert될 때 인덱스에도 하나의 entry가 삽입되어야 하기 때문이다.
* 인덱스 저장 위치
인덱스는 물리적으로 data tablespace와 분리된 별도의 tablespace에 저장하는 것이 좋다. tablespace 파일 간의 경합을 줄일 수 있기 때문이다. 인덱스에 tablespace를 지정하는 명령문은 다음과 같다.
create index CONCERT_SINGER_TITLE on concert(singer, title)
tablespace GBTALBOT;
이것은 주로 최적화나 공간사용량이 중요한 대형 데이터베이스의 경우에만 필요하다. 기본키나 유일성 제약조건을 만들 때 오라클은 자동으로 유일성을 보장하기 위해 인덱스를 작성한다. 별도로 지정하지 않는다면 이 인덱스는 테이블과 동일한 tablespace에 작성된다. 기본키와 유일성 제약조건을 작성할 때에는 using index 절을 활용하여야 한다.
create table coffeeMix(
coffee_name varchar2(20),
coffee number(6),
sugar number(6),
milk number(6),
cream number(6),
syrup number(6),
cocoa_powder number(6),
cinnamon_powder number(6),
water number(6) ),
constraint PK_COFFEE primary key(coffee_name)
using index tablespace INDEXES
storage (initial 20K next 20K) )
위의 예제에서 기본키 제약조건은 PK_COFFEE라는 명칭을 갖으며 이 기본키와 관련된 인덱스는 특정 영역 파라메터와 함께 INDEXES라는 테이블스페이스를 가리킨다.
덧) 위의 내용은 "Oracle 완벽해설"을 기반으로 한 자료입니다. 이 책에는 불행하게도 covers version 7.X 라는 문구가 있더군요. 명령문까지 변하지는 않았더라도 테이블에 사용할 수 있는 인덱스의 수 같은 것은 현재 버전과 약간 차이가 있을지도 모르겠습니다. ^^
|
|
|
|
|
|
