2013. 1. 3. 10:31

클러스터인덱스와 비클러스터인덱스 차이

[인덱스] - 색인


책에서 원하는 내용을 빨리 찾으려면 인덱스를 이용(책의 인덱스와 비슷한 개념)
DB도 사용자가 원하는 내용을 빨리 찾으려면 색인이란 정보를 미리 만들어서 원하는 데이터를 빨리 찾을 수 있게 할 수 있다.

데이터베이스내의 테이블에서 원하는 정보를 좀더 빨리 찾아줄수 있게  데이터의 위치 정보를 모아놓은 데이터베이스내의 객체 object이다.


 
* 인덱스는 정렬되어 있다.
  예1) 사진에서 원하는 데이터를 찾을때
  예2) 책에서 책뒷부분의 인덱스 페이지
 

table scan  -- 전부다 검색
index seek  -- 원하는 페이지만 검색
===========================================
* 포인트쿼리   point query
  조회되는 데이터가 한두개
  select * from tb_member where uid = 'kim'    -- 값이 하나
 
* 범위쿼리   range query
  조회되는 데이터가 다수
  select * from tb_member where regdt = '2008/06/19'    -- 값이 다수
 
* 커버드쿼리  covered query
  조회의 대상과 조회의 결과가 컬럼이 일치하는 상태
  인덱스 측면에서 제일 빠른 성능을 냄
 
select * from tb_member

 where uid = 'kim' and upw = '1234'    -- 커버드 쿼리 아님 포인트쿼리
 
select uid from tb_member
 where uid = 'kim' and upw = '1234'    -- 커버드 쿼리


1. clustered index 클러스터 인덱스
형식) create clustered index 인덱스명
        on 테이블명(칼럼명 오름/내림)
해당 컬럼을 기준으로 정렬, 테이블당 1개씩만 허용.
데이터를 여러개 조회하는 범위 쿼리이건, 하나만 조회하는 포인트 쿼리건 둘다 성능 발휘.
primary key  설정시 그 컬럼에 자동으로 클러스터드 인덱스가 만들어진다.
이 컬럼은 데이터 입력, 수정, 삭제시 항상 정렬을 유지한다.
기본적으로 인덱스는 정렬되어 있다. <<-- 이자체가 인덱스
즉 그 인덱스가 사전식으로 정렬 된다.


2. nonclustered index 넌클러스터 인덱스 

인덱스 페이지 따로 만든다. 용량이 더 차지 한다.(로그파일에 저장)
기존의 테이블 + 넌클러스트 인덱스테이블
형식) create nonclustered index 인덱스명
        on 테이블명(칼럼명 정렬)
인덱스 페이지 따로 만든다.
레코드 원본은 정렬 안된다.
테이블당 240개 정도 만들수 있다.
포인트 쿼리는 성능발휘/ 범위 쿼리는 장담 할 수 없다.
 

예) 클러스터 인덱스는 책을 비유하자면 페이지를 알기 때문에 바로 그 페이지를 펴는 것과 비교되는것이고
넌클러스터 인덱스는 뒤에 목차에서 찾고자 하는 내용의 페이지를 찾고 그 페이지로 이동 하는것과 같다.
테이블스캔은 처음부터 한장씩 넘기면서 내용을 찾는것과 같다.
 
-- 포인트 쿼리일때 비교 : 포인트 쿼리에서는 별다른 속도 차이가 없다.

-- 인덱스 추가 범위 쿼리일때 비교

select * from w_zipcode where dong like '당산%'
select * from c_myzip     where dong like '당산%'
select * from n_myzip     where dong like '당산%'    -- %당산% 일때 처음에 %가 있으면 table scan 으로 된다. 따라서 속도시간이 늘어난다
 
create clustered index dong인덱스
on c_myzip(dong asc)

create nonclustered index dong인덱스2
on n_myzip(dong asc)

 


어떤 컬럼에 인덱스를 걸어야 하는지??
1. where절에서 자주 사용되는 컬럼 (예 : dong 컬럼 -> 자주 검색하기 때문에)
2. like '%~~~' 조심. %는 뒤에만 오게 해야 속도가 빨라진다.
3. between A and B (클러스터인덱스가 유리)

범위 쿼리문에서는 클러스터드인덱스가 유리하지만 클러스터드인덱스는 그 테이블에서 한번만 사용되는 단점을 가지고 있다.
4. order by가 항상 사용되는 컬럼
5. join으로 자주 사용되는 컬럼
FK( 1:1 대응이 많을 때 -- >  둘다 상관 없음(상황에 따라 넌클러스터드 인덱스를 사용)
1:N 대응이 많을 때 -- > 클러스터드 인덱스 유리
6. 100만건 중에 10개 조회/1000개 조회. 찾는 것이 적은 수에 주로 인덱스를 걸어주는 것이 상책이다.
주의)  중복 데이터가 많은 컬럼 (성별) --> 인덱스를 거는게 아님
조회되는것이 많으면 그냥 처음부터 찾는것이 나은편.
7. not 연산자 -> 긍정문을 바꿔서...
8. insert, delete가 빈번한 컬럼은 인덱스에 좋은 영향은 아님

 


* 인덱스로 인해 얻는 손해
1. 만드는데 시간과 많은 공간이 필요하고, 만들고 난 후에도 추가적인 공간이 필요한다.
2. 데이타를 수정(insert, delete, update)하는 시간, 특히 insert작업은 오히려 더 많이 걸린다.


 

2009. 4. 15. 14:55

[Oracle] 오라클 객체(1) [인덱스(Index)]

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

 

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

 

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

 

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

- WHERE절이나 join조건 안에서 자주 사용되는 컬럼

- null값이 많이 포함되어 있는 컬럼

- WHERE절이나 join조건에서 자주 사용되는 두 개이상의 컬럼들

 

※ 다음과 같은 경우에는 index 생성이 불필요 합니다.

- table이 작을 때

- 테이블이 자주 갱신될 때

 

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

 

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

 

이 알고리즘 원리는

 

- 주어진 값을 리스트이 중간점에 있는 값과 비교합니다.

  만약 그 값이 더 크면 리스트의 아래쪽 반을 버립니다.

  만약 그 값이 더 작다면 위쪽 반을 버립ㄴ디ㅏ.

 

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

 

                                                                                                                                  

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

 

1) Bitmap 인덱스

 

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

 

SQL> CREATE BITMAP INDEX emp_deptno_indx

          ON emp(deptno);

 

 

2) Unique 인덱스

 

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

 

SQL> CREATE UNIQUE INDEX emp_ename_indx

          ON emp(ename);

 

3) Non-Unique 인덱스

 

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

 

SQL> CREATE INDEX dept_dname_indx

          ON dept(dname);

 

4) 결합(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_INDEXS 뷰 또는 USER_IND_COLUMNS 뷰를 통해 검색할 수 있습니다.

 

SQL> SELECT index_name, index_type

          FROM USER_INDEXS

          WHERE table_name = 'EMP';

 

INDEX_NAME                        INDEX_TYPE
---------------------------     -----------
EMP_DEPTNO_INDX                BITMAP
EMP_PK_EMPNO                    NORMAL

 

                                                                                                                                  

 


  ================================================
    * Oracle Community OracleClub.com
    * http://www.oracleclub.com
    * http://www.oramaster.net
    * 운영자 : 김정식 (oramaster _at_ empal.com)
  ================================================
※ 강좌를 다른 홈페이지에 기재할 경우에는 출처를 꼭 밝혀 주시면 고맙겠습니다.~^^