인덱스 분류

인덱스는 테이블에 있는 행을 직접 액세스할 수 있는 트리 구조로서 논리적 설계 또는 물리적 구현에 근거하여 분류할 수 있습니다. 논리적 분류는 인덱스를 응용 프로그램 관점에서 나눈 것이고 물리적 분류는 인덱스 저장 방법에 따라 나눈 것입니다.

단일 열 인덱스 및 연결된 인덱스

단일 열 인덱스는 해당 인덱스 키에 열이 하나만 있는데 예를 들면, 사원 테이블의 사원 번호 열에 대한 인덱스가 여기에 해당됩니다.

연결된 인덱스는 조합 인덱스라고도 하며 테이블의 여러 열에 대해 생성되는 이 열은 테이블의 열과 순서가 동일하거나 인접할 필요가 없습니다. 예를 들어, 사원 테이블의 부서 열과 직위 열에 대한 인덱스가 여기에 해당됩니다.

조합 키 인덱스의 최대 열 수는 32개지만 모든 열을 합친 크기가 해당 데이터 블록 크기의 3분의 1을 넘지 않아야 합니다.

고유 및 비고유 인덱스

고유 인덱스는 테이블의 두 행 값이 인덱스 정의 열에서 중복되지 않도록 하므로 고유 인덱스의 인덱스 키 하나는 테이블에서 한 행만을 가리킬 수 있습니다.

비고유 인덱스에서는 단일 키가 여러 행과 연관될 수 있습니다.

함수 기반 인덱스

함수 기반 인덱스는 인덱스화된 테이블의 열을 하나 이상 포함하는 함수 또는 표현식을 사용할 때 생성되며 함수 또는 표현식의 값을 미리 계산한 다음 인덱스에 저장합니다. 함수 기반 인덱스는 B 트리 인덱스 또는 비트맵 인덱스로 생성할 수 있습니다.

분할된 인덱스 및 분할되지 않은 인덱스

분할된 인덱스는 큰 테이블에서 하나의 인덱스에 해당하는 인덱스 항목을 여러 세그먼트에 저장하는 데 사용하며 이렇게 분할하면 하나의 인덱스를 여러 테이블스페이스에 분산시켜 인덱스 조회 경합을 줄이고 관리를 용이하게 할 수 있습니다. 분할된 인덱스는 대개 확장성 및 관리 용이성을 향상시키기 위해 분할된 테이블과 함께 사용하는데 각 테이블분할 영역마다 하나씩 인덱스 분할 영역을 생성할 수 있습니다.

이 단원은 분할되지 않은 B 트리 인덱스 및 비트맵 인덱스의 생성 및 유지 관리 방법을 설명합니다.

 

모든 인덱스가 B-트리 구조에 연관되어 있듯이 B-트리 인덱스는 보통 각 키에 대해 ROWID 값들을 저장하는 인덱스와 연관되어 있습니다.

B-트리 인덱스 구조
인덱스 제일 위는 root로 인덱스의 다음 레벨을 가리키는 엔트리를 포함하고 있습니다. 다음 레벨은 branch 블록으로 마찬가지로 다음 레벨의 인덱스 블록을 가리킵니다. 최하층 레벨은 leaf 노드로 테이블의 행을 가리키는 인덱스 엔트리를 포함하고 있습니다. leaf 블록은 키 값의 내림차순은 물론 오름차순의 키 값으로 인덱스를 검색하는데 편리하도록 양방향(doubling)으로 연결(link)되어 있습니다.

인덱스 Leaf  엔트리의 형식
인덱스 엔트리는 다음 요소들로 구성되어 있습니다.
   -  열 수와 잠금(locking) 정보를 저장하는 엔트리 헤더
   -  키의 열 길이와 열 값이 차례로 정의된 쌍(pair)으로 된 값(이들 쌍의 갯수가 인덱스에 들어갈 수 있는 열 수의 최대치입니다.)
   -  키 값을 포함하는 행의 ROWID

인덱스 Leaf 엔트리 특성
분할되지 않은 테이블의  B-트리 인덱스는 다음과 같습니다.
     -   같은 키 값을 갖는 행이 여러 개 있으면 키 값이 반복됩니다.
     -   모든 키 열의 값이 NULL인 행에 대응되는 인덱스 엔트리는 없습니다.
     -   모든 행이 같은 세그먼트에 속하므로 제한된 ROWID가 테이블의 행을  
     -   가리키는데 사용됩니다.

인덱스에의 DML 작업의 효과
오라클 서버는 테이블에 DML 작업이 수행될 때 모든 인덱스도 유지 보수합니다. 인덱스에의 DML 명령의 효과는 다음과 같습니다.
  -    삽입(insert) 작업은 적절한 블록에 인덱스 엔트리를 삽입합니다.
  -    행 삭제(delete)는 인덱스 엔트리의 논리적 삭제만을 하게 됩니다. 삭제된 행에  의해 사용되는 공간은 블록 내의 모든 엔트리가 삭제될 때까지 새 엔트리를
저장하는데 사용할 수 없습니다.
  -     키 열에 대한 갱신(update)은 인덱스에 논리적 삭제와 삽입을 하게 됩니다.
  -  PCTFREE 설정은 생성시를 제외하면 인덱스에 아무런 영향을 주지 않습니다.   -  인덱스 블록이 PCTFREE에 의해 지정된 것보다 작은 공간을 가지고 있더라도  새 엔트리가 추가될 수 있습니다. 

 

보통 B-트리 인덱스와는 반대로 Reverse 키 인덱스는 열 순서를 따라 인덱스가 지정되어(ROWID 제외) 있는 각 열의 바이트를 역으로 뒤집습니다. 시스템이 생성한 고용자 번호 같은 오름차순 키로 레코드를 삽입할 때 인덱스 트리의 한 곳에 모든 인덱스 갱신이 발생하게 되어 I/O 병목 현상이 발생할 수 있습니다. Reverse 키 인덱스는 인덱스 키의 데이터 값을 뒤집어 주어 인덱스 트리에 인덱스 갱신 분포를 분산시켜 줍니다.
예를 들자면 테이블에 사원 번호 7698를 삽입하면 8967의 키 값이 인덱스에 저장됩니다. 다음 사원 번호 7782가 입력되면 인덱스 엔트리는 2877이 되어 작업 부하(load)를 여러 개의 인덱스 블록으로 분산시켜 줍니다.
이러한 배열은 오라클 병렬 서버 환경에서 인덱스의 성능 저하를 막는데 매우 유용할 수 있습니다.
Reverse 키 인덱스는 동등 속성(equality predicate)을 갖는 질의에 대해서만 유용합니다. 사전적 의미로는 인접한 키가 reverse 키 인덱스에서는 서로 인접하여 저장되는 것이 아니기 때문에 이러한 인덱스를 사용하여 범위 검색(range search)을 수행할 수는 없을 것입니다.

함수 기반 인덱스

함수 기반 인덱스는 WHERE 절에 함수를 포함하는 명령문을 효율적으로 평가하는 방식을 제공하며 인덱스에 계산 집중 표현식을 구체화하기 위해 생성할 수 있어 SELECT 문 및 DELETE 문을 처리할 때 Oracle 서버가 표현식 값을 계산할 필요가 없습니다. 하지만 INSERT 문 및 UPDATE 문을 처리할 경우에는 Oracle 서버가 여전히 함수를 평가해야 합니다.

함수 기반 인덱스는 B 트리 또는 비트맵 인덱스 구조로 생성할 수 있습니다.


비트맵 인덱스는 다음과 같은 경우에 B-트리 인덱스보다 더 유용합니다.
   -  테이블이 많은 행을 가지며 키 열은 적은 분포도(cardinality)를 가질 때, 즉 열이 갖는 서로 다른 값이 몇 개 안 될 때. 예를 들어 여권 기록을 포함하고 있는 테이블의 성별 열이나 결혼 여부 열의 경우에는 B-트리 인덱스보다는 비트맵 인덱스가 더 유리할 것입니다.
   -  질의문이 OR 연산자를 포함하는 여러 개의 WHERE 조건을 자주 사용할 때
   -  키 열이 읽기 전용이거나 갱신 작업을 적게 할 때

비트맵 인덱스의 구조
비트맵도 B-트리처럼 조직되어 있습니다. 하지만 leaf 노드는 ROWID 값 들 대신 각 키 값에 대한 비트맵을 저장합니다. 비트맵의 각 비트는 가능한 ROWID에 대응되며 비트가 1이 되면(set), 대응되는 ROWID를 가진 행이 키 값을 가진다는 것을 의미합니다.

 그림에서처럼 비트맵 인덱스의 leaf 노드는 다음을 포함하고 있습니다.
  -   엔트리 헤더: 열 수와 잠금(lock) 정보를 포함하고 있습니다.
  -   키 값: 각 키 열에 대해 길이와 값이 쌍(value pair)으로 구성되어 있습니다.(
        예를 들어 키는 오직 하나의 열로 구성되어 있으며 첫번째 엔트리는 Blue의 키 값을 가집니다.)
  -  시작 ROWID: 이 예에서는 파일 번호 3, 블록 번호 10, 그리고 행 번호 0을
        포함하고 있습니다.
  -  끝 ROWID: 이 예에서는 블록 번호 12, 그리고 행 번호 8을 포함하고 있습니다.
  -   비트맵 세그먼트: 비트(bit)의 문자열을 가지고 있습니다.(대응되는 행이 키 값을 가질  때 비트가 1이 되고 행이 키 값을 갖지 않을 때 0이 됩니다. 오라클 서버는 비트맵 세그먼트를 저장할 때 특허받은 압축 기술을 사용합니다.)

시작 ROWID는 비트맵의 비트맵 세그먼트가 가리키고 있는 첫번째 행의 ROWID입니다. 즉, 비트맵의 첫번째 비트는 그 ROWID에 대응되며 두번째 비트는 블록의 다음 행에 대응됩니다. 끝 ROWID는 비트맵 세그먼트가 담당하는 테이블의 마지막 행을 가리킵니다. 비트맵 인덱스는 제한된 ROWID를 사용합니다.

비트맵 인덱스 사용
B-트리를 사용하여 주어진 키 값을 갖는 비트맵 세그먼트를 포함하는 leaf 노드의 위치를 찾습니다. 시작 ROWID와 비트맵 세그먼트는 키 값을 가진 행의 위치를 찾는데 사용됩니다.
테이블의 키 열이 변경되면 비트맵이 수정되어야 하며, 이 결과 관련된 비트맵 세그먼트가 잠기게(locking) 됩니다. 이 때 모든 비트맵 세그먼트가 잠기게 되므로 이 비트맵이 담당하는 행은 첫번째 트랜잭션이 끝날 때까지 다른 트랜잭션이 갱신할 수 없게 됩니다.

 


슬라이드의 표는 B-트리 인덱스와 비트맵 인덱스를 비교한 것입니다.
비트맵 인덱스는 적은 분포도를 갖는 열에 사용될 때 B-트리 인덱스보다 간결합니다.
비트맵은 비트맵 세그먼트 레벨의 잠금(locking)을 사용하므로 비트맵 인덱스에서 키 열을 갱신하는 비용이 매우 큽니다. 반면 B-트리 인덱스에서는 잠금이 테이블의 각 행에 대응되는 엔트리에 걸립니다.
비트맵 인덱스는 Bitmap OR 등의 작업을 수행하는데 사용될 수 있습니다. 즉, 오라클 서버는 두 개의 비트맵 세그먼트를 사용하여 비트 방식 OR를 수행하여 결과 비트맵을 얻어낼 수 있습니다. 이 방식은 OR을 사용하는 질의문에서 비트맵을 효율적으로 사용할 수 있게 해 줍니다.
요약하자면 B-트리 인덱스는 동적 테이블을 인덱스하는 OLTP 환경에 더 적합합니다. 반면 비트맵 인덱스는 크고 정적인 테이블에 복잡한 질의문이 사용되는 DSS 환경에 유용합니다.


인덱스는 테이블을 소유하고 있는 사용자 계정에서 생성할 수도 있고 다른 계정에서 생성할 수도 있습니다. 하지만 테이블과 같은 계정에서 생성하는 것이 일반적입니다.

구문
다음 명령을 사용하여 B-트리 인덱스를 생성하십시오.
   CREATE [ UNIQUE ] INDEX [schema.]index
   ON [schema.]table
   (column [ ASC | DESC ] [ , column [ASC | DESC ] ] . . .)
   [ TABLESPACE tablespace ]
     [ PCTFREE integer ]
     [ INITRANS integer ]
     [ MAXTRANS integer ]
     [ storage-clause ]
     [ LOGGING | NOLOGGING ]
     [ NOSORT ]

 구문에서:
UNIQUE            unique인덱스를 지정하는데 사용합니다.(NONUNIQUE가
                        기본값입니다.)
schema            인덱스/테이블의 소유자입니다.
index                인덱스 이름입니다.
table                 테이블 이름입니다.
column             열 이름입니다.
ASC/DESC        다른 데이터베이스와의 구문 호환성을 위해 제공됩니다.
TABLESPAC     인덱스가 생성될 테이블스페이스를 나타냅니다.
PCTFREE          생성시 새 인덱스 엔트리를 수용하기 위해 각 블록에 예약된                         공간의 총량입니다.(총 공간에서 블록 헤더를 뺀 것을 백분율로)
INITRANS         각 블록에 미리 할당된 트랜잭션 엔트리의 수를 지정합니다.
                       (기본값은 최소값인 2 입니다.)
MAXTRANS      각 블록에 할당될 수 있는 트랜잭션 엔트리의 수를 제한합니다.
                       (기본값은 255 입니다.)
STORAGE        익스텐트가 인덱스에 할당되는 방법을 결정하는 스토리지 절을                         나타냅니다.
LOGGING         인덱스의 생성과 이후의 인덱스에 대한 작업이 리두 로그 파일에                        기록되도록 명시합니다.(기본값입니다.)
NOLOGGING     인덱스 생성과 특정 유형의 데이터 로드가 리두 로그 파일에                         기록되지 않도록 명시합니다.
NOSORT          행이 데이터베이스에 오름차순으로 저장되어 있음을 명시합니다.                         이 경우 오라클 서버는 생성시 행을 정렬하지 않습니다

주
  -   테이블스페이스에 MINIMUM EXTENT가 정의되어 있다면 인덱스에 대한
      익스텐트 크기는 다음으로 큰 MINIMUM EXTENT 값의 정수배로 반올림될
      것입니다.
  -  [NO]LOGGING 절이 생략되면 인덱스의 기록(logging) 속성은 인덱스가
      상주하고 있는 테이블스페이스의 기록 속성을 따르게 됩니다.
  -   인덱스에는 PCTUSED가 지정될 수 없습니다. 왜냐하면 엔트리가 올바른
      순서대로 저장되어 있어야만 하기 때문에 사용자는 인덱스 블록이 삽입에 사       용될 때 제어할 수 없습니다.
  -   데이터가 키에 대해 정렬되지 않은 상태에서 NOSORT 키워드를 사용하면       명 령문은 에러를 출력하고 종료하게 됩니다.  
  -  오라클 서버는 가능하다면 기존 인덱스를 사용하여 새 인덱스를 생성합니다.
  -   이러한 일은 새 인덱스에 대한 키가 기존 인덱스의 키의 주요 부분에 대응할        때 발생합니다.

OEM
 1  Oracle Schema Manager를 사용 하십시오.
 2  Object->Create를 선택하십시오.
 3  나열된 값들 중에서 Index를 선택하고 OK를 클릭 하십시오.
 4  Property sheet에서 General, Storage, 그리고 Option 정보를 입력 하십시오.
 5  Create를 클릭 하십시오
또는 내비게이터에서 기존 인덱스를 선택한 후 Object->Create Like를 사용하여 기존 인덱스와 유사한 성질을 갖는 새 인덱스를 생성할 수도 있습니다.
Oracle Schema Manager를 사용할 때 초기 크기(volume), 성장률(growth rate), 테이블에 대한 삽입 작업, 그리고 행이 삽입된 순서에 기초하여 스토리지 파라미터와 공간 활용 파라미터를 자동으로 정의하도록 할 수도 있습니다.

 

인덱스를 생성할 때 다음을 고려하십시오.
 -  인덱스는 질의문의 성능을 향상 시켜주고 DML 작업을 느리게 합니다. 자주 변하는 테이블에   필요한 인덱스 숫자는 항상 최소화 하십시오.
 -  인덱스를 롤백 세그먼트, 임시 세그먼트, 그리고 테이블과는 별도의 테이블스페이스에  위치시키십시오.
 -  단편화를 최소화하려면 5*DB_BLOCK_SIZE의 정수배인 표준 익스텐트 크기를 사용하십시오.
 -  리두 생성을 피함으로써 큰 인덱스에 대해 현저한 성능 향상이 있을 것입니다.
 -  큰 인덱스를 생성할 때는 NOLOGGING 절을 사용하는 것을 고려해 보십시오.
 -  인덱스 엔트리는 그들이 인덱스하는 행에 비교해 볼 때 작기 때문에 인덱스 블록이 블록당 더  많은 엔트리를 가질 수 있습니다. 이런 이유로 INITRANS는 일반적으로 대응되는 테이블  에서보다는 인덱스에서 더 커야 합니다. 

 


인덱스와 PCTFREE
인덱스에 대한 PCTFREE는 테이블에 대한 PCTFREE와는 달라서 동일 인덱스 블록에 삽입되어야 할 인덱스 엔트리에 대비한 공간을 예비해 두도록 인덱스 생성시에만 사용됩니다. 인덱스 엔트리는 갱신되지 않습니다. 키 열이 갱신될 때는 인덱스 엔트리가 논리적으로 삭제된 후 삽입됩니다.

시스템 생성 송장 번호(system-generated invoice number)같이 차례대로 증가하는 열에 대한 인덱스에는 PCTFREE를 낮게 잡으십시오. 이 경우 새 인덱스 엔트리는 항상 기존 엔트리에 추가되며 기존의 두 인덱스 엔트리 간에 새 엔트리를 삽입할 필요는 없습니다.

삽입될 행의 인덱스 열 값이 어떤 값이라도 가질 수 있다면, 즉 현재 가지고 있는 값의 범위 내의 어느 값이라도 가질 수 있다면 높은 값의 PCTFREE를 제공해야만 합니다. 높은 PCTFREE 값을 요구하는 인덱스의 한 예는 송장 테이블의 소비자 코드 열에 대한 인덱스입니다. 이 경우 다음 식이 가리키는 대로 PCTFREE 값을 지정하는 것이 유리합니다.

                          최대 행의 갯수 - 초기 행의 갯수
         --------------------------------------------------- *100
                                      최대 행의 갯수

최대 값은 일년 같은 특정 기간에 맞춘 값일 수 있습니다. 

 


데이터 딕셔너리 뷰 DBA_INDEXES와 DBA_IND_COLUMNS은 인덱스와 인덱스된 열에 대한 정보를 제공합니다.

인덱스와 그 유효성 검사
다음 명령을 이용하여 사용자 SCOTT이 소유한 인덱스의 이름, 유형, 그리고 상태를 확인하십시오.
SVRMGR> SELECT index_name, tablespace_name, index_type,
             2> uniqueness, status
             3> FROM dba_indexes
             4> WHERE owner=‘SCOTT’;
INDEX_NAME        TABLESPACE_NAME INDEX_TYPE UNIQUENESS  STATUS
---------------   --------------- ---------- ---------   ------
EMP_LNAME_IDX     INDX01          NORMAL     NONUNIQUE   VALID
ORD_ORD_NO_IDX    INDX01          NORMAL     UNIQUE      VALID
ORD_REGION_ID_IDX INDX02          BITMAP     NONUNIQUE   VALID
3 rows selected.

INDEX_TYPE 열은 인덱스가 비트맵인지 보통 인덱스인지를 가리킵니다. 모든 reverse 키 인덱스의 이름을 보고자 할 때는 다음 질의문을 사용하십시오.
        SVRMGR> SELECT o.object_name
                2> FROM dba_objects o
                3> WHERE owner=‘SCOTT’
                4> AND o.object_id IN (SELECT I.obj#
                5>                  FROM ind$ I
                6>                  WHERE BITAND (I.property, 4) = 4);
        OBJECT_NAME
        -----------------------
        ORD_ORD_NO_IDX
        1 row selected.

인덱스가 정의된 열 찾기
다음 질의문은 사용자 SCOTT이 소유한 인덱스 전부를 보여주며 인덱스가 구축된 테이블과 열을 보여 줍니다.
        SVRMGR> SELECT index_name, table_owner, table_name, column_name
                2> FROM dba_ind_columns
                3> WHERE index_owner = ‘SCOTT’
                4> ORDER BY index_name, column_position;
        INDEX_NAME          TABLE_OWNER   TABLE_NAME   COLUMN_NAME
        ------------------- ------------- ------------ -----------
        EMP_LNAME_IDX       SCOTT         EMP          LAST_NAME
        ORD_ORD_NO_IDX      SCOTT         ORD          ORD_NO
        ORD_REGION_ID_IDX   SCOTT         ORD          REGION_ID
        3 rows selected. 

 

구문
Reverse 키 인덱스를 생성하려면 다음 명령을 사용하십시오.
  CREATE [ UNIQUE ] INDEX [schema.]index
  ON [schema.]table
  (column [ ASC | DESC ] [ , column [ASC | DESC] ] . . .)
  [ TABLESPACE tablespace ]
     [ PCTFREE integer ]
     [ INITRANS integer ]
     [ MAXTRANS integer ]
     [ storage-clause ]
     [ LOGGING | NOLOGGING ]
      REVERSE
Reverse 키 인덱스 생성을 위한 명령은 REVERSE 키워드가 사용된다는 점을 제외하면 정규 인덱스를 생성하는 명령과 비슷합니다. Reverse 키 인덱스에는 NOSORT 키워드가 사용될 수 없다는 것을 명심하십시오


구문
비트맵 인덱스를 생성하려면 다음 명령을 사용하십시오.
 CREATE BITMAP INDEX [schema.]index
 ON [schema.]table
 (column [ ASC | DESC ] [ , column [ASC | DESC ] ] . . .]
 [ TABLESPACE tablespace ]
      [ PCTFREE integer ]
      [ INITRANS integer ]
      [ MAXTRANS integer ]
      [ storage-clause ]
      [ LOGGING | NOLOGGING ]
      [ NOSORT ]
비트맵 인덱스는 unique 인덱스가 될 수 없음을 명심하십시오.

CREATE_BITMAP_AREA_SIZE
초기화 파라미터 CREATE_BITMAP_AREA_SIZE는 메모리 내에 비트맵 세그먼트를 저장하기 위해 사용될 공간의 총량을 결정합니다. 기본 값은 8 MB입니다. 이 값을 크게 하면 더 빨리 인덱스를 생성할 수 있을 것입니다. 분포도가 매우 작다면 작은 값으로 설정될 수 있습니다. 예를 들어 분포도(cardinality)가 2라면 파라미터 값은 메가바이트 단위 보다는 킬로바이트 단위가 될 수 있습니다. 일반적인 규칙 대로 분포도가 커질수록 최적화된 성능을 위해서는 더 많은 메모리가 필요하게 됩니다.

OEM
1       Oracle Schema Manager를 사용 하십시오.
2       Object->Create를 사용 하십시오.
3       나열된 값 중에서 Index를 선택하고 OK를 클릭 하십시오.
4       Property sheet에 General, Storage, 그리고 Options 정보를 입력하고 General  
        페이지에 Bitmap 옵션이 선택되었는지 확인 하십시오.
5       Create를 클릭 하십시오. 

 

스토리지 파라미터와 공간 활용 파라미터 일부는 ALTER INDEX 명령을 사용하여 수정할 수 있습니다.
 구문
   ALTER INDEX [schema.]index
   [ storage-clause ]
   [ INITRANS integer ]
   [ MAXTRANS integer ]
인덱스에 대한 스토리지 파라미터의 변경의 의미는 테이블에 대한 이 파라미터들이 변경되는 것과 마찬가지입니다. 일반적으로 인덱스에 대한 변경은MAXEXTENTS를 증가시키는 것입니다.
공간 활용 파라미터는 인덱스 블록에 대한 높은 수준의 동시성을 보장하기 위해 변경될 수도 있습니다.

 OEM
1      Oracle Schema Manager를 사용 하십시오.
2      Index 노드를 확장 하십시오.
3      사용자 이름(또는 스키마)을 확장 하십시오.
4      인덱스를 선택 하십시오.
5      Property sheet의 Storage tab의 값을 수정 하십시오. 익스텐트의 최소값은         이 방법으로  변경될 수 없다는 것을 명심하십시오.
6      Apply를 클릭 하십시오.

 

 인덱스에 대한 수동 공간 할당
테이블에 많은 삽입 작업을 하기 전에 인덱스에 익스텐트를 추가하여야 할 수 있습니다. 익스텐트를 미리 추가하면 수행중의 인덱스의 동적 확장과 그로 인한 성능 저하를 막아 줍니다.

인덱스로부터의 수동 공간 해제
인덱스의 high water mark 이상의 사용되지 않는 공간을 해제하려면 ALTER INDEX 명령의 DEALLOCATE 절을 사용하십시오.
구문
인덱스 공간을 할당하거나 해제하려면 다음 명령을 사용하십시오.
  ALTER INDEX [schema.]index
  {ALLOCATE EXTENT ([SIZE integer [K|M] ]
              [ DATAFILE ‘filename’ ])
  | DEALLOCATE UNUSED [ KEEP integer [ K|M ] ] }
인덱스에 수동으로 공간을 할당하거나 해제하는 것은 테이블에 대해 이러한 명령을 사용할 때와 같습니다.

주
인덱스 공간은 인덱스가 구축된 테이블이 잘려질(truncate) 때 해제됩니다. 테이블을 잘라 버리면 관련 인덱스도 잘려 집니다.

 

인덱스 재구축

인덱스 재구축에는 다음 특성이 있습니다.

재구축이 필요한 상황

다음 상황일 때 인덱스를 재구축합니다.

구문

다음 명령을 사용하여 인덱스를 재구축합니다.

   ALTER INDEX [schema.] index REBUILD

     [ TABLESPACE tablespace]

     [ PCTFREE integer ]

     [ INITRANS integer ]

     [ MAXTRANS integer ]

     [ storage-clause ]

     [ LOGGING | NOLOGGING ]

     [ REVERSE | NOREVERSE ]

ALTER INDEX ... REBUILD 명령은 비트맵 인덱스를 B 트리 인덱스로 바꾸거나 또는 그 반대인 경우 사용할 수 없으며 REVERSE 키워드 또는 NOREVERSE 키워드는 B 트리 인덱스에만 지정할 수 있습니다.

 

온라인으로 인덱스 재구축

인덱스 구축 또는 재구축 작업은 테이블이 아주 큰 경우 시간이 많이 걸리는 작업이며 Oracle8i 이전에는 인덱스를 생성 또는 재구축할 경우 테이블을 잠구어야 했고 동시 DML 작업을 할 수 없었습니다.

Oracle8i는 인덱스를 생성 또는 재생성하면서 기본 테이블에 대한 동시 작업을 수행할 수 있지만 이러한 절차 중에는 큰 DML 작업을 수행하지 않는 것이 좋습니다.

참고 : 계속해서 DML 문이 잠겨 있으면 온라인 인덱스 구축 중에 다른 DDL 작업을 수행할 수 없음을 의미합니다.

제한 사항

 

인덱스 병합

인덱스 단편화가 있는 경우 해당 인덱스를 재구축 또는 병합할 수 있으며 이러한 작업을 수행하기 전에 먼저 각 옵션의 비용 및 이익을 고려하여 자신의 상황에 가장 적합한 작업을 선택해야 합니다.

재사용을 위해 공간을 늘릴 수 이는 B 트리 인덱스 최하위 블록이 있는 상황에서는 다음 SQL 문을 사용하여 이 최하위 블록을 병합할 수 있습니다.

   SQL> ALTER INDEX summit.orders_id_idx COALESCE;

다음을 수행하려면 인덱스를 분석하십시오.
   -   모든 인덱스 블록을 검사하여 블록 훼손을 조사합니다. 인덱스 엔트리가 테이블의 데이터에 대응되는지 여부는 검증하지 않는다는 것을 명심하십시오.
   -   인덱스에 대한 정보를 가지고 있는 INDEX_STATS 뷰에 기록합니다.

구문
   ANALYZE INDEX [schema.]inex VALIDATE STRUCTURE
위 명령을 수행한 후 INDEX_STATS를 질의하여 다음 예에 보여지는 것처럼 인덱스에 대한 정보를 얻으십시오.
  SVRMGR> SELECT blocks, pct_used,
       2> lf_rows, del_lf_rows
       3> FROM index_stats;
BLOCKS            PCT_USED           LF-ROWS       DEL_LF_ROWS
-------           --------           -------       ------------                 
     25                 11                14                  0           
1 row selected.
인덱스가 삭제된 행을 많이 가지고 있다면 인덱스를 재구축하십시오. 예를 들어 LF_ROWS에 대한 DEL_LF_ROWS의 비가 30%를 초과하면 인덱스를 재구축하십시오.


다음과 같은 경우 인덱스를 삭제할 필요가 있습니다.
    ?  응용 프로그램에서 더 이상 필요하지 않은 인덱스는 제거할 수 있습니다.
    ?  대량으로 로드하기 전에 인덱스를 삭제할 수도 있습니다. 큰 데이터를 로드하기 전에 인덱스를 삭제하고 로드한 후 재생성하여:
        -       로드 성능을 향상시키십시오.
        -       인덱스 공간을 보다 효율적으로 사용하십시오.
    ? 정기적으로만 사용되는 인덱스는 불필요하게 유지 보수될 필요는 없습니다. 특히 잠깐씩  사용하는(volatile) 테이블에 기초한 경우에는 더욱 그렇습니다. 일반적으로 회의용 자료를 모으기 위해 연말, 또는 분기 말에 질의문이 만들어지는 OLTP 시스템의 경우가 그렇습니다.
    ?  인덱스는 로드 같은 특정 유형의 작업 중 인스턴스가 비정상적으로 종료할 때 인덱스가   INVALID라고 표시될 수도 있습니다. 이 경우 인덱스는 삭제되거나 재생성되어야 합니다.
    ?  인덱스가 훼손되었을 때

 구문
다음 명령을 사용하여 인덱스를 삭제하십시오.
        DROP INDEX [schema.]index

OEM
1   Oracle Schema Manager를 사용 하십시오.
2   Index 노드를 확장 하십시오.
3   사용자 이름(또는 스키마)를 확장 하십시오.
4   인덱스를 선택 하십시오.
5   Object->Remove 선택
6   다이얼로그 박스에서 Yes를 선택 하십시오.

주
인덱스가 enable된 무결성 제약 조건 구현에 사용되었을 경우 삭제될 수 없습니다. 제약 조건은 “데이터 무결성 관리” 장에서 다루어 집니다.

 

요약참조

 관련내용

 참조

 초기 파라미터

 CREATE_BITMAP_AREA_SIZE

 동적 성능 뷰

 없슴

 데이터 딕셔너리 테이블/뷰

 DBA_INDEXS
 DBA_IND_COLUMNS
 IND$
 INDEX_STATS

 명령어

 CREATE INDEX
 CREATE UNIQUE INDEX
 CREATE BITMAP INDEX
 CREATE INDEX...REVERSE
 ALTER INDEX...STORAGE
 ALTER INDEX...INITRANS...MAXTRANS
 ALTER INDEX...ALLOCATE EXTENT
 ALTER INDEX...DEALLOCATE UNUSDE
 ALTER INDEX...REBUILD
 ALTER INDEX...REBUILD...REVERSE
 ALTER INDEX...REBUILD...NOREVERSE
 ANALYZEINDEX...VALIDATE STRUCTURE
 DROPINDEX

 패키지 프로시저와 함수

 없슴

 

  1. Analyze를 해 보니 1주일 전보다 인덱스 엔트리가 현저하게 줄었다. 어떤 조치를 취해야 하나?
    A. 필요없으므로 Drop한다.
    B. Deallocate unused를 사용하여 필요없는 공간을 반납한다.
    C. 엔트리의 저하는 성능을 크게 좌우하지 않으므로 그대로 사용한다.
    D. 인덱스를 rebuild한다.
    답
    X 정답:D


  2. Bitmap Index를 사용하면 좋은 경우는?
    A. OLTP 환경
    B. Where절의 조건에 OR를 사용
    C. High cardinality column
    D. Update가 자주 일어나는 column
    답
    O


  3. Index segment의 특징으로 맞는 것을 모두 고르시오.
    A. nologging으로 생성될 수 있다.
    B. 대개 관련 테이블보다 크기가 작다.
    C. 항상 오라클 서버에 의해 자동으로 생성되고 관리된다.
    D. 테이블과는 다른 테이블스페이스에 저장될 수 있다.
    답
    X 정답:ABD


  4. index를 가진 table 의 data가 많이 삭제된 경우, 다음 중 가장 좋은 처리 방법은?
    A. index의 PCTUSED를 20으로 감소
    B. index의 PCTUSED를 80으로 증가
    C. index의 NEXT를 증가
    D. index를 drop하고 재생성
    답
    X 정답:D


  5. 다음 중 index에 대한 설명이 틀린 것은?
    A. column의 cardinality가 높으면 B*tree index가 좋다.
    B. Bitmap index는 OR 연산에 대해서 효율적이다.
    C. B*tree index가 Bitmap index보다 Update 비용이 더 크다.
    D. B*tree index는 OLTP 업무에, Bitmap index는 DSS 업무에 효과적이다.
    답
    O


  6. 다음 중 적은 key cardinality를 가지고 OR 연산자 수행 시 적합한 인덱스 구조는?
    A. Reverse Index
    B. Bitmap Index
    C. B-tree Index
    D. Index organized table
    답
    O


  7. 다음과 같은 문장이 수행되었다. 다음 중 맞는 것은?
    create index i_payroll on payroll ( last_name );

    A. tablespace를 지정하지 않았기 때문에 실패한다.
    B. user의 default tablespace에 생성될 것이다.
    C. application이 정의 될 때, application manager에 의해 정의된 tablespace에 생성될 것이다.
    D. tablespace를 지정하지 않았기 때문에 SYSTEM tablespace에 생성될 것이다.
    답
    X 정답:B


  8. 비트맵 인덱스(Bitmap Index)가 가장 유용한 경우는?
    A. 테이블이 크고 키 컬럼이 높은 cardinality를 가지는 경우
    B. 테이블이 작고 키 컬럼이 낮은 cardinality를 가지는 경우
    C. 테이블이 크고 키 컬럼이 낮은 cardinality를 가지는 경우
    D. 테이블이 작고 키 컬럼이 높은 cardinality를 가지는 경우
    답
    X 정답:C