제2장 인덱스의 유형과 특징

• 인덱스란 말 그대로 전체 내용물 중에서 특정한 부분을 바로 찾을 수 있는 목차(目次)나 색인(索引)의 개념.
• 테이블에 저장되어있는 데이터를 좀 더 효율적이고 빠르게 찾기 위해 사용.
• 관계형 데이터베이스에서 적용하고 있는 인덱스 유형은 아래와 같음.
  • B-Tree 인덱스
  • 비트맵(Bitmap) 인덱스
  • B-tree 클러스트 인덱스
  • 해쉬(hash)클러스터 인덱스
  • 리버스 키(Reverse key) 인덱스
  • 비트맵 조인(Bitmap join) 인덱스
  • 함수기반(Function-based) 인덱스

• 생성 명령어 : CREATE INDEX 인덱스명 ON 테이블명(컬럼명)
• 나무구조(Tree)처럼 가지에 해당하는 브랜치 블럭과 잎사귀에 해당하는 리프 블럭이 있다.

:——————————————————————————————————

• Unique Scan일 경우

• range Scan일 경우
  1. 인덱스는 구성컬럼과 ROWID로 정렬되어 있다.
  2. ROWID에는 로우가 저장된 블록의 주소, 로우의 슬롯번호가 기록되어 있다.
  3. 처리할 범위의 첫번째 인덱스로우를 브랜체블록을 통해 리프블록을 액세스한 다음 거기의 ROWID로 블록을 찾는다.
  4. SGA부터 찾아보고 없으면 디스크에 액세스해서 복사본을 SGA에 복사한다.
  5. 새롭게 액세스한 데이터블록정보를 PGA로 가져와서 해당 SQL의 처리버퍼에 넣는다 (일명 원 버퍼).
  6. 인덱스블록을 액세스한 내용을 PGA버퍼에서 찾아 다른 조건까지 검증하여 성공한 로우만 운반단위로 보낸다 만약 클러스터링 팩터가 좋다면 여러건의 인덱스 스캔을 하나의 버퍼에서 처리할 수 있다.
  7. PGA버퍼에서 찾을 수 없다면 다시 다른 블록을 SGA에 액세스하여 새로운 블럭을 담고 위의 처리를 반복수행한다.
  8. 리프블록에서 액세스한 로우가 처리범위를 넘으면 처리를 종료한다.

• rowid라는 가상 컬럼에 저장된다.
• Single row access를 위한 row 고유한 주소
• 10자리로 구성

:——————————————————————————————————

1. 테이블을 액새스하여 정렬을 수행.
2. 정렬된 결과를 인덱스 세그먼트의 리프 블록에 기록하기 시작.
3. 리프블록이 PCTFREE에 도달하여 새로운 블록을 요구하게 되면 브랜치블록을 생성.
4. 리프블록이 증가함에 따라 브랜치블록도 채워져 가다 블랜치블록이 PCTFREE에 도달하면 새로운 리프블록과 새로운 브랜치블록을 생성할때 루트블록이 생성된다.
5. 새로운 브랜치블록이 만들어질 때마다 루트블록에 브랜치블록의 DBA정보를 기록한다.

• 위와 같은 방법으로 인덱스가 저장되기 때문에 인덱스 블럭에 많은 로우를 담게 될 경우 리프 블럭 감소, 브랜치 블럭 증가 둔화(블럭의 깊이 감소)

• 최대한 인덱스 컬럼의 수를 줄인다.
• 큰 블럭 사이즈(DB_BLOCK_SIZE)를 지정. (데이터베이스 전체, 혹은 테이블스페이스에 대한 정의이다.)
• PCTFREE를 최소로 정의. (0으로 해도 무관함)
• 압축을 활용하는 방법

CREATE INDEX ord_customer_idx
ON orders (customer_id, sales_id)
COMPRESS 1;

:——————————————————————————————————

• 인덱스 로우는 정렬이 되어 저장 되어야 하는 이유 때문에 이미 생성된 구조에 새로운 로우가 삽입되면 기존의 위치에 파고 들어가는 문제 발생

• 마지막 값이 삽입되는 경우 : 그림처럼 PCTFREE에 도달한 블럭일 경우엔 새로운 블럭에 등록된다.
• 중간 값이 삽입되는 경우 : PCTFREE가 초과하게 되므로 분할이 발생하는데 어차피 2개의 블럭으로 수정되므로 2/3만 채우도록 하면서 양쪽을 모두 재편하게 됨(또 다른 중간 값이 들어와 계속 분할 되는 것을 방지하기 위함).
• 하지만 분할이 일어난 블럭에 새로운 값이 들어오지 않으면 저장공간 낭비를 초래하게 되고 이를 해결하기 위해선 인덱스를 재생성하는 방법뿐이다.

:——————————————————————————————————

• 데이터를 삭제했을 경우 테이블의 로우는 제거되지만 인덱스의 로우는 삭제되었다는 표시(flag)만 추가된다.(저장공간낭비와 스캔시 액세스 블럭 증가)
• 한 리프 블럭이 모두 삭제 되었을 경우 브랜치 블럭에 해당 리프 블럭을 가리키는 로우도 삭제 표시가 된다.
• 갱신할 경우 삭제와 삽입이 발생.
• 이러한 이유로 수정이 빈번하지 않는 컬럼을 인덱스로 사용권장.
• 데이터처리(DML)가 많이 수행되는 테이블은 정기적으로 재생성을 할 필요가 있다.

:——————————————————————————————————

• lmc는 브랜치 블럭의 첫 번째 로우의 값보다 적은 값을 갖는 하위의 블럭의 주소정보(dba:data block address)를 말한다.

 1.루트 블록을 찾는다.   
 2.주어진 값보다 같거나 최소값을 찾는다.(찾는값 >= 인덱스값)
    찾는값 < 인덱스 값 (Lmc에 있는 dba로 이동) 
    찾는 값 = 인덱스 값 (해당 dba로 이동) 
    찾는 값 > 인덱스 값 
       (검색 후 찾는 값 = 인데스 값이면 해당 dba로 이동 그렇지 않으면, 
       찾는 값 < 인데스 값을 만족하는 인덱스 최소값)
 3.리프 블록을 찾을 때까지 ② 의 단계를 반복해서 수행.
 4.리프 블록에서 주어진 값을 가진 키를 찾아 존재하면 ROWID를 이용해 테이블을 액세스하고,
   그렇지 않으면 'No Data found'로 결과 반환 만약, col2의 조건을 'ACC'가 아닌 'AC%'로
    바꾸면 Col1 = 'B'이면서 Col2 = 'AC'보다 같거나 큰 것에서 스캔을 시작, 'AD' 보다 
    작으면 테이블을 액세스하고, 그렇지 않으면 종료한다.

:——————————————————————————————————

• B-TREE인덱스에서는 실제 컬럼 값을 인덱스에도 보관하고 있어야 한다는 점이 대용량 데이터를 관리할 때 부담이 된다.
• B-TREE인덱스 컬럼값의 분포도가 좋아야 한다는 점
• 결합인덱스에서 조건을 사용하지 않는 컬럼이나 =조건이 아닌 컬럼이 결합인덱스 중간에 있으면 액세스효율성이 떨어진다는 점
• 다양한 액세스패턴을 수용하기 위해 많은 인덱스가 필요할 수 있다는 점
• NOT이나 NULL을 사용하거나 복잡한 OR조건에서는 인덱스의 성능을 보장받지 못한다는 점

• 컴퓨터에서 사용하는 최소의 단위인 비트를 사용하여 컬럼 값을 저장하고 이를 이용하여 rowid를 자동으로 생성하는 인덱스 방법으로 비트를 직접 관리하므로 저장공간이 감소하고 비트연산이 용이 하다는 장점이 있다.

• 생성 명령어 : CREATE BITMAP INDEX … ON …

• B-TREE인덱스가 가지는 문제점을 독립적으로 구성된 각각의 비트맵 인덱스가 조합되어 해결할 수 있다.

• 루트 블록이나 브랜치 블록은 B-tree인덱스와 같은 구조로 되어 있으나 리프블록은 비트맵으로 구성되어 있다.

• 비트를 저장할 때는 '선분 형태(Start rowid ~ End rowid)'로 저장을 하므로 일종의 압축 개념이 되며, 키 압축(Key Compression)이 적용되어 저장 공간이 매우 절약된다.
• 비트맵 연산을 통해 처리하므로 OR 연산이 가능하다.
• NULL 값도 BIT를 구성하고 있어 NULL 및 NOT 조건검색이 가능하다.
• 수정이 빈번하게 발생하는 컬럼은 인덱스의 크기가 크게 증가하고 블록레벨 잠금(Block Level Locking)으로 인해 많은 부하가 유발될 수 있다.
• 데이터 웨어하우스 업무에 주로 활용된다.

» 데이터 웨어하우스 http://100.naver.com/100.nhn?docid=717310

1. 인덱스를 생성하고자 하는 컬럼의 값들을 찾기 위해 테이블 스캔을 한 후
2. bitmap generator에 의해 컬럼값, start rowid, end rowid , bitmap을 갖는 인덱스 엔트리를 생성한다.
3. 2단계에서 생성된 Bitmap들을 B-tree구조에 넣기 쉽도록 key값과 start rowid 순으로 정렬한다.
4. 마지막 단계에서는 정렬된 인덱스 엔트리들을 단순히 B-tree구조로 삽입한다.

• Partition table에서 Global Index에는 Bitmap Index를 만들 수 없다.
• Local Index를 구성할 경우에는 Bitmap Index로 만들 수 있으나, 여러 파티션에 걸쳐있는 Global Index를 구성할 때는 Bitmap Index를 만들 수 없다.
• Bitmap Index는 RBO(rule base optimizer) mode에서는 사용될 수 없다.
• Bitmap Index는 통계치에 의하여 산출된 cost를 기반으로 Bitmap Index의 사용여부를 결정하기 때문에 Optimizer Mode는 반드시 CBO(cost based optimizer) Mode인 ALL_ROWS 또는 FIRST_ROWS로 지정되거나 , CHOOSE mode인 경우 해당 object는 ANALYZE 명령을 통하여 통계정보를 생성해 주어야 한다.
• IOT(Index Organized Table)에는 rowid를 가지고 있지 않기 때문에 rowid range로 표현되는 Bitmap Index를 사용할 수 없다. 그러나 Oracle9i부터는 ALTER TABLE .. MOVE MAPPING TABLE과 같이 mapping table을 이용하여 Bitmap Index를 사용할 수 있다.

1. COL1 인덱스에서 '123'인 비트맵을 엑세스하고,COL2인덱스에서 'ABC'인 비트맵을 엑세스하여 감산연산을 수행한다(BITMAP MINUS)
2. COL2에서 NULL인 비트맵과 1의 결과를 다시 감산연산을 수행한다(BITMAP MINUS)
3. COL3 < 100 인 비트맵을 읽어 머지(BITMAP MERGE)를 수행하여 하나의 비트맵을 만든다.
4. 2와 3에서 수행한 결과에 대해 OR연산(BITMAP OR)을 수행하여 조건을 만족하는 최종결과 비트맵을 만들고 ROWID로 변형하여 테이블을 엑세스 한다.

• OLTP : 네트워크상의 여러 이용자가 실시간으로 데이터베이스의 데이터를 갱신하거나 조회하는 등의 단위 작업을 처리하는 방식

• 어떤 컬럼값의 각 바이트의 위치를 역전시키는 것을 말한다.
• 리버스키 인덱스에서는 정렬이 크게 달라지므로 클러스터링 팩터가 나빠진다는 것을 의미한다.
• '='로만 액세스하는 경우에라면 크게 문제되것이 없다

• 테이블의 컬럼들을 가공한 논리적 컬럼을 인덱스로 생성한 것.

• 비용기준 옵티마이져(CBO)에서만 사용가능한다.
• 함수기반 인덱스를 생성한 후 반드시 통계정보를 생성해야 한다.
• 사용자지정함수는 반드시 DETERMINISTIC으로 선언되어야 한다.
• QUERY_REWRITE_ENABLE parameter 는 TRUE로 선언되어야 한다.
  • SQL>alter session set QUERY_REWRITE_ENABLE = TRUE;
• QUERY_REWRITE_INTEGRITY parameter는 TRUSTED로 선언되어야 한다.
  • SQL>alter session set QUERY_REWRITE_INTEGRITY=TRUSTED;
• 다음의 사용자 권한을 가져야한다.
  • 인덱스 생성권한 : INDEX CREATE / ANY INDEX CREATE
  • 쿼리 재생성권한 : QUERY REWRITE / GLOBAL QUERY REWRITE
• 함수나 수식의 결과가 NULL인 경우는 이 인덱스를 통해 액세스할 수 없다.
• 사용자 지정함수를 사용한 경우에는 종속성 유지에 주의해야 한다.
  • 인덱스 정의에 사용된 사용자 함수가 재정의되었을 때 사용불가 상태가 된다.
  • 인덱스 소유자의 실행 권한이 취소 되면 사용불가 상태가 된다.
• 옵티마이져가 사용불가 상태가 된 인덱스를 선택하면 SQL의 실행은 실패한다.
  • 인덱스를 가용상태로 만들거나 재구축해야 한다.
  • 인덱스를 미사용상태로 만든다 이때 SKIP_UNUSABLE_INDEXES파라미터가 TRUE로 선언되어 있어야 한다.
• 스칼라 서브쿼리로 표현된 컬럼은 함수기반 인덱스를 사용할 수 없다.
• 값이 상황에 따라 달라질 수 있는 SYSDATE, USER, ROWNUM 등의 가상컬럼이 포함되면 이 인덱스를 생성할 수 없다.
• 파티션을 적용한 경우에 파티션 키를 함수기반 인덱스에 사용할 수 없다.
• 숫자 컬럼을 문자 연산하거나 문자컬럼을 수치연산하는 수식의 경우에는 직접 기술하지 않았더라도 내부적으로 TO_CHAR, TO_NUMBER가 추가되어 처리된다.
• 함수기반 인덱스에서는 NLS파라메터를 현재 기준으로 적용하기 때문에 만약 세션 레벨에서 재정의를 한다면 잘못된 결과를 얻을 수도 있으므로 주의해야 한다.
• WHERE 절에 기술된 컬럼이 표현된 것과 인덱스에 지정된 표현이 다르더라도 논리적으로 교환법칙이 성립하는 경우라면 같은 결과를 얻을 수 있다.

• 컬럼의 분할 원칙을 준수하지 않았기 때문에 발생하는 문제에 대한 해결방안 .

 create index from_loc_idx on orders (substr(ship_id,5,3));
 create index repair_loc_idx on orders (substr(ship_id,3,2), ord_date);

• 정상적인 데이터 모델링을 수행했다면 나타날 수 없겠지만 현실에서는 가끔 등장하는 형태. 상위테이블의 품목분류테이블(itemgrop)에서는 상세한 관리를 위해 컬럼을 대분류(class1), 중분류(class2), 소분류(class3)으로 나누었으나 하위 테이블(items)에서는 컬럼이 늘어날 것을 염려하여 그룹코드(group_cd)라는 하나의 컬럼으로 생성한 예이다. 다음과 같이 조인을 할 경우 한쪽 연결고리에 이상이 발생하여 조인에 나쁜 영향을 미칠 수 있다.

  select ... from item_group x, items y
  where x.class1 || x.class2 || x.class3 = y.group_cd

OR

 select ...
   from item_group x, items y
  where x.class1 = substr(y.group_cd,1,2) 
    and x.class2 = substr(y.group_cd,3,2)
    and x.class3 = substr(y.group_cd,5,3)

위와 같은 경우 다음과 같은 함수기반 인덱스로 해결할 수 있다.

 create index group_cd_idx on item_group(x.class1 || x.class2 || x.class3 );

• 일자컬럼이 함부로 년,월,일로 분할 되어 관리되는 경우 년,월,일을 결하여 비교할 수 밖에 없으므로 조인 연결고리 컬럼이 대응하지 않는 경우과 같아진다.

  create index sal_date_idx on sales (sal_yyyy || sal_mm || sal_dd);

• 표준화를 준수하고 도메인을 사전에 체계적으로 관리하였다면 발생하지 않을 확률이 높겠지만 어떻한 이유든 조인되어야 하는 테이블간의 조인키 컬럼의 데이터타입이 일치하지 않는 경우 컬럼의 내부적 변형이 발생하여 어느 한쪽의 인덱스가 사용될 수 없는 상태가 될 수 있다. 이런 경우 함수기반인덱스에서 한쪽 컬럼의 데이터형을 미리 가공하여 인덱스를 생성하면 해결할 수 있다.

 create index deptno_idx on emp (to_number(deptno));

• 아래와 같이 업무규칙에 따라 배타적으로 조인하는 경우가 발생했다면 조인시 컬럼이 달라질 수 있다.
• 이때 드라이빙을 원하는대로 하기 위해 식에 함수기반 인덱스를 사용하면 가능하게 유도할 수 있다.

 select ... from sales s, departments d
 where d.deptno = (case when sal_type = 1 then 
                           sal_dept
                      else 
                           agent_no
                 end)
   and d.location = 'PUSAN';
 
 create index deptno_idx on sales  
 (case when sal_type = 1 then sal_dept
                         else 
                              agent_no
                         end);

• 인덱스의 사용 시 두개의 인덱스를 동시에 쓰기어려운 제한 조건이 있었는데 하나의 선행인덱스가 사용된다면 다른 인덱스는 체크조건으로 사용된다는 점이다. (인덱스머지는 제외)

 select ...
from movement x, movement_trans y
 where x.mov_order = y.mov_order
 and x.deptno = '12310'
 and y.mov_date like '200512%'

위와 같은 sql이 있다면 x.deptno = '12310'이 처리 주관 조건이 된다면 move_date like '200512%'는 체크기능의 역할만 하게 된다. 이때 두개의 조건이 함께 처리 주관조건이 된다면 아주 양호한 수행속도를 얻을 수 있다면 다음과 같은 함수기반인덱스를 이용하여 해결 할 수 있다.

 create or replace function get_deptno(v_mov_order in number)
return varchar2 DETERMINISTIC is
ret_val varchar2(5);
 begin 
select deptno into ret_val
  from movement
 where mov_order = v_mov_order;
return ret_val;
 end get_deptno;
 
 create index dept_date_idx on movement_trans(get_deptno(mov_order),mov_date);

• 단 함수내에 참조 테이블이 있을 경우 버전 별로 차이가 있으므로 주의해서 사용해야한다.

 create index ename_upper_idx on employees (upper (ename));

== 불필요한 공백을 제거한 후 비교를 해야하는 경우

 create index ename_trim_idx on employees(replace(ename,' '));

• 전화번호 앞번호가 018이면 그냥 두고 아니면 016을 붙여서 비교할 때

 create index call_number_idx 
 on call_data (decode(substr(call_number,1,3),'018','','016')||call_number);

• 상품별로 데이터량이 많을 때 주문단가에서 할인금액을 뺀 실제 적용단가에 주문수량을 곱하여 금액별로 100개의 상품을 조회할때 복잡한 계산식을 함수기반인덱스로 만들어 결과값을 논리컬럼으로 저장해두면 힌트에서 인덱스를 사용할 수 있다.

 create index order_amount_idx 
 on order_items(item_cd, (order_price-nvl(order_discount,0),0) * order_count));

 select /*+ index_desc(x order_amount_idx) */ *
    from order_items x
 where item_cd = :b1
       and rownum <= 100

• 말일, 단가, 율의 검색 : 해당월에 가장 높은 sal_amount가 발생한 건을 찾는 경우

 create index sal_amount_idx on sales (last_day(sal_date), sal_amount);

• 기간을 검색하는 경우

 create index term_idx on activities (expire_date - start_date)

• 길이를 검색하는 경우 .

 create index source_length_idx on print_media(text_length(source_text));

• 육면체(cube)를 오브젝트타입으로 생성하오 이것을 이용하여 부피를 구하는 예

길이,폭,높이 세가지 컬럼을 이용하여 부피를 구하는 volume()메소드를 정의

 create type cube AS object
(
length number,
width  number,
heigth number,
member function volume return number DETERMINISTIC
);

create or replace type body cube as 
member function volume return number is
   begin
     return (length * width * height);
   end;
end;

cube유형(TYPE)으로된 CUBE_TAB 테이블을 생성하고, volumn() 메소드로된 함수기반 인덱스 생성

 create table cube_tab of cube;
 create index volume_idx on cube_tab x (x.volume());
 

인덱스를 경유하여 엑세스를 수행

 select * from cube_tab x where x.volume() > 100

• 여기서 말하는 배타적(Exclusive)관계에 있는 컬럼이란 데이터 모델링 상에서 서로 다른 서브타입(Subtype)에 있는 컬럼들 중에서 동일한 속성으로 통합할 수 있는 컬럼들의 관계를 말한다.

• 어떤 배타적 관계에 있는 속성들을 관찰하면 동일한 속성(subtype)으로 볼 수 있으며 이들을 결합했을 경우 반드시 존재해야한다거나 유일해야 한다는 원칙이 있을 수 있다. 이런경우 함수기반 인덱스를 생성하여 처리의 단순화 및 시스템을 통한 제약조건의 검증까지 보장받을 수 있다.

• 배타적 관계에 있는 주민등록번호와 사업자등록번호를 결합하여 유일성을 체크할 수 있을 뿐 아니라 검색의 단순화를 얻을수 있는 예

 create unique index official_id_idx 
 on customers(case when cust_type =1 then resident_id else business_id end);
 
 select * from customers
 where (case when cust_type =1 then resident_id else business_id end) = :b1;

• 특정한 범위의 데이터에 대해서만 유일성을 체크할도록 할 수 있는 사례.
• 예를들어 'A01'의 보험상품에서는 고객이 한 번만 가입할 수 있다면 다음과 같은 방법으로 유일성을 체크할 수 있다.

 create unique index contract_idx on insurance (
 case when then ins_type = 'A01' then customer_id else null end, 
 case when then ins_type = 'A01' then ins_type     else null end
 );

• 이 인덱스는 INS_TYPE ='A01'인 경우가 아니면 모두 NULL 값이 되므로 유일성을 체크하지 않는다.
• 다음과 같은 입력이 발생하면 에러가 나타단다.

 insert into contact_person 
 insuran_id, ..., customer_id, ins_type) values (122101, ..., 2101, 'A01');
 
 1row created.
 
 insert into contact_person 
 (insuran_id, ..., customer_id, ins_type) values (122102, ..., 2101, 'A01');
 
 ERROR at line 1;
 unique constraint (OE.CONTRACT_IDX) violated

• 어떤 구분값을 기준으로 결합인덱스의 구성이 달라진다면 함수기반인덱스로 해결할 수 있다.

 create index order_deliver_idx1 on order_delivery(
 order_dept, //고정된 선행컬럼
 case when ord_type=1 then delivery_date else shipping_date end), // 중간 컬럼인덱스
 item_type 
 )