# 오라클 인덱스 개요

오라클은 다양한 인덱스 유형을 제공한다.

    * B*Tree 인덱스 : 이것은 보편적으로 언급되는 인덱스며, 지금까지 오라클과 다른 데이터베이스에서 가장 자주 사용되는 일반적인 인덱스다.
       이진 트리와 유사한 구조인 B*Tree 인덱스는 키 값을 이용해서 개별적인 로우 또는 로우의 특정 범위를 빠르게 접근할 수 있도록 해주며, 몇 번의 읽는 과정만으로 정확한 로우를 찾는다.
       'B*Tree'에서 'B'는 binary가 아니라 balanced를 의미한다는 것이 중요하다.
       B*Tree 인덱스는 결코 binary 트리가 아니다.
       인덱스가 물리적으로 디스크에 어떻게 저장되는지를 보면 이해할 수 있을 것이다.
       B*Tree 인덱스를 기반으로 하는 다음과 같은 몇 가지 인덱스 구조가 존재한다.

        - 인덱스 구조 테이블(IOT, index organized table) : B*Tree 구조로 저장되는 테이블이다.
           힙 구조 테이블은 가용공간에 구조화되지 않는 방식으로 저장되는 반면, IOT의 데이터는 기본 키에 의해서 정렬되어 저장된다.
           IOT는 프로그램에서 일반적인 테이블처럼 사용되며, SQL은 일반 테이블에 접근하는 것처럼 사용한다.
           IOT는 특별히 정보 검색, 공간(spatial), OLAP 프로그램에 유용하다.

        - B*Tree 클러스터 인덱스(B*Tree cluster index) : 일반적인 B*Tree 인덱스를 약간 변형시킨 인덱스다.
           클러스터 키를 인덱싱하는 것으로, 10장 '인덱스 클러스터 테이블'절에서 언급하였기 때문에 이 장에서는 다시 다루지 않을 것이다.
           일반적인 B*Tree 인덱스는 로우를 가리키는 인덱스 키를 가지고 있는 반면, B*Tree 클러스터는 로우가 포함된 블록을 가리키는 클러스터 키를 가지고 있다.

        - 내림차순 인덱스(descending index) : 일반적인 인덱스는 작은 값부터 큰 값으로 오름차순으로 정렬되는 반면, 내림차순 인덱스는 큰 값부터 작은 값으로 내림차순으로 정렬된 데이터를 위한 인덱스다.
           내림차순 인덱스가 왜 중요하고 어떻게 작동되는지 살펴볼 것이다.

        - 리버스 키 인덱스(reverse key index) : 키 값을 뒤에서부터 역순으로 뒤집은 B*Tree 인덱스다.
           계속 증가하는 값에 대한 인덱스를 고르게 분산하기 위한 인덱스다.
           예를 들어 시퀀스 오브젝트로 값이 생성되는 기본 키가 있다면, 시퀀스는 987500, 987501, 987502, ... 처럼 생성될 것이다.
           시퀀스 값은 한쪽 방향으로 증가하기 때문에 일반적인 B*Tree 인덱스를 사용한다면 한결같이 오른쪽 블록이 증가할 것이며, 이 블록에 대한 경합이 발생하게 된다(옮긴이_최근에 생성된 데이터 블록을 읽으려는 트랜잭션이 많다면 경합이 발생한다).
           그러나 오라클에서 리버스 키 인덱스는 논리적으로 205789, 105789, 005789 등으로 생성된다.
           리버스 키 인덱스는 데이터의 바이트를 역으로 읽어서 저장하는 것으로, 리버스 인덱스가 아닌 경우에는 바로 옆에 근접해서 존재하던 값들이 멀리 떨어지게 되어 다수의 블록에 분산되어 저장되는 것이다.

    * 비트맵 인덱스(bitmap index) : 일반적인 B*Tree 인덱스는 인덱스 엔트리와 테이블 로우가 일대일 관계로 구성되는 것으로, 각 인덱스 엔트리는 하나의 로우를 가리킨다.
       이에 비해 비트맵 인덱스는 하나의 엔트리가 동시에 다수의 로우를 가리키는 비트맵으로 구성되며, 전체 테이블에서 서로 다른 값의 수가 적은 읽기 전용 데이터에 적합하다.
       예를 들어 100만 개 로우를 가진 테이블의 어떤 컬럼이 Y, N, NULL로 세 가지 값이 가능하다고 생각해보자.
       해당 컬럼이 'Y' 인 로우의 총 건수를 자주 추출한다면, 비트맵 인덱스로 구성해야 할 좋은 후보가 될 것이다.
       동일 테이블 해당 컬럼에 1,000개의 다른 값이 존재한다면, 비트맵 인덱스는 적절하지 않다.
       비트맵 인덱스는 동시성과 관련된 이슈가 존재하는 OLTP 환경에서는 사용하지 말아야 한다.
       비트맵 인덱스는 Enterprise 또는 Personal Edition에서 사용 가능하다.

    * 비트맵 조인 인덱스(bitmap join index) : 이 인덱스는 테이블 속성의 반정규화를 대신하여 인덱스 구조를 통해 반정규화하는 것과 같은 결과를 제공한다(옮긴이_비트맵 조인 인덱스는 두 개 이상의 테이블의 조인 결과에 대해 비트맵 인덱스를 구성하는 것이다).
       예를 들어 간단한 EMP, DEPT 테이블이 존재하는 경우, 다음과 같은 질문을 할 수 있다.
       "몇 명이 보스턴에 위치한 부서에서 근무하는가?"
       EMP 테이블은 DEPT 테이블의 부서코드 참조 키를 가지고 있기 때문에, LOC 컬럼이 보스턴인 부서에 근무하는 사원의 수를 계산하기 위해서 일반적으로 LOC 컬럼을 가진 DEPT 테이블과 EMP 로우를 가진 EMP 테이블을 조인해야만 한다.
       EMP 테이블에 비트맵 조인 인덱스를 생성하여 EMP 로우에 대응되는 LOC 컬럼의 인덱스를 대신할 수 있다.
       일반적인 비트맵 인덱스처럼 비트맵 조인 인덱스도 OLTP 환경에 적용하는 것은 주의해야 한다.

    * 함수 기반 인덱스(function-based index) : 컬럼값 자체가 아니라 컬럼에 대한 함수의 계산된 결과를 저장되는 B*Tree 또는 비트맵 인덱스를 말한다.
       즉, 물리적으로 저장되는 컬럼이 아니라 가상컬럼(또는 파생 컬럼)의 인덱스라고 생각할 수 있다.
       'SELECT * FROM T WHERE FUNCTION(DATABAE_COLUMN) = SOME_VALUE' 쿼리의 수행을 향상시키는 데 사용할 수 있다.
       FUNCTION(DATABASE_COLUMN)은 미리 계산되어 인덱스로 저장되기 때문이다.

    * 애플리케이션 도메인 인덱스(application domain index) : 오라클 내부 또는 외부에서 사용자가 만들어서 저장하는 인덱스다.
       사용자가 옵티마이저에 어떤 인덱스를 선택할 것인지, 실행하는 데 얼마나 비용이 드는지 지정해주고, 옵티마이저가 그 정보를 이용하여 인덱스를 사용할 것인지 말 것인지를 결정한다.
       오라클 텍스트 인덱스는 애플리케이션 도메인 인덱스의 예며(옮긴이_오라클 텍스트(Oracle text)는 모든 문서와 텍스트 내용을 인덱싱하여 표준 SQL 문으로 오라클 데이터베이스, 파일, 웹에 저장된 텍스트와 문서검색 애플리케이션을 쉽게 구현할 수 있게 한다), 사용자 정의 인덱스를 만드는 것과 같은 방법으로 생성한다(옮긴이_단순하게 CREATE INDEX 명령문으로 사용하여 생성한다).
       생성된 인덱스는 일반적인 인덱스 구조를 사용할 필요가 없다는 것을 기억해야 한다.
       예를 들어 오라클 텍스트 인덱스는 인덱스의 개념을 구현하기 위해 일련의 테이블을 이용한다.
       

# 내림차순 인덱스
내림차순 인덱스는 B*Tree 인덱스의 확장 기능으로서 오라클 8i 버전에서 소개되었다.
오름차순(작은 값에서 큰 값으로) 대신에 내림차순(큰 값에서 작은 값으로)으로 정렬되어 저장하는 인덱스를 의미한다.
오라클 8i 이전 버전에서는 DESC(내림차순) 키워드는 문법적으로는 제공되지만 데이터가 저장되고 사용하는 데는 아무런 영향이 없었다.
그러나 오라클 8i 버전 이상에서의 DESC 키워드는 인덱스의 생성 및 사용방식에 변화를 가져왔다.

원래 오라클은 인덱스를 역순으로 읽을 수 있기 때문에, 혹자는 왜 이 기능이 필요한지 의문이 생길 것이다.
아래의 예제를 통해 기능의 필요성에 대해 알아보기로 하겠다.
먼저 테이블 T를 다음과 같이 생성한다.
SUNSHINY@ORACLE11> create table t 
  2  as
  3  select * 
  4    from all_objects
  5  /

Table created.

SUNSHINY@ORACLE11> create index t_idx 
  2  on t(owner,object_type,object_name);

Index created.

SUNSHINY@ORACLE11> begin
  2     dbms_stats.gather_table_stats
  3     ( user, 'T', method_opt=>'for all indexed columns' );
  4  end;
  5  /

PL/SQL procedure successfully completed.

-- 플랜 추출
SUNSHINY@ORACLE11> set autotrace traceonly explain
SUNSHINY@ORACLE11> select owner, object_type
  2    from t
  3   where owner between 'T' and 'Z'
  4     and object_type is not null
  5   order by owner DESC, object_type DESC;

Execution Plan
----------------------------------------------------------
Plan hash value: 2685572958

-------------------------------------------------------------------------------------
| Id  | Operation                   | Name  | Rows  | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |       |   792 | 11880 |     8   (0)| 00:00:01 |
|*  1 |  INDEX RANGE SCAN DESCENDING| T_IDX |   792 | 11880 |     8   (0)| 00:00:01 |
-------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - access("OWNER">='T' AND "OWNER"<='Z')
       filter("OBJECT_TYPE" IS NOT NULL)

오라클은 단지 인덱스를 역순으로 읽을 뿐이다.
데이터가 정렬되어 있기 때문에 실행계획 마지막에 정렬 단계가 없다.
그러나 내림차순 인덱스의 역할이 필요한 경우는 ASC(오름차순) 컬럼과 DESC(내림차순) 컬럼을 복합적으로 수행해야 하는 경우이다.
SUNSHINY@ORACLE11> select owner, object_type
  2    from t
  3   where owner between 'T' and 'Z'
  4     and object_type is not null
  5   order by owner DESC, object_type ASC;

Execution Plan
----------------------------------------------------------
Plan hash value: 2813023843

---------------------------------------------------------------------------
| Id  | Operation         | Name  | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |       |   792 | 11880 |     9  (12)| 00:00:01 |
|   1 |  SORT ORDER BY    |       |   792 | 11880 |     9  (12)| 00:00:01 |
|*  2 |   INDEX RANGE SCAN| T_IDX |   792 | 11880 |     8   (0)| 00:00:01 |
---------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - access("OWNER">='T' AND "OWNER"<='Z')
       filter("OBJECT_TYPE" IS NOT NULL)

오라클은 (OWNER, OBJECT_TYPE, OBJECT_NAME)으로 구성된 인덱스를 더 이상 정렬 작업에 사용할 수 없다.
OWNER는 내림차순으로 읽어야 하고 OBJECT_TYPE은 오름차순으로 읽어야 한다.
따라서 모든 로우를 읽은 후에 정렬할 수밖에 없을 것이다.
여기에 내림차순 인덱스를 생성해보자.
SUNSHINY@ORACLE11> create index desc_t_idx on t(owner desc,object_type asc);

Index created.

SUNSHINY@ORACLE11> select owner, object_type
  2    from t
  3   where owner between 'T' and 'Z'
  4     and object_type is not null
  5   order by owner DESC, object_type ASC;

Execution Plan
----------------------------------------------------------
Plan hash value: 2494308350

-------------------------------------------------------------------------------
| Id  | Operation        | Name       | Rows  | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------------
|   0 | SELECT STATEMENT |            |   792 | 11880 |     3   (0)| 00:00:01 |
|*  1 |  INDEX RANGE SCAN| DESC_T_IDX |   792 | 11880 |     3   (0)| 00:00:01 |
-------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - access(SYS_OP_DESCEND("OWNER")>=HEXTORAW('A5FF')  AND
              SYS_OP_DESCEND("OWNER")<=HEXTORAW('ABFF') )
       filter(SYS_OP_UNDESCEND(SYS_OP_DESCEND("OWNER"))>='T' AND
              SYS_OP_UNDESCEND(SYS_OP_DESCEND("OWNER"))<='Z' AND "OBJECT_TYPE" IS NOT NULL)

정렬된 데이터를 읽을 수 있게 되었고, 실행계획에 추가적인 정렬 단계는 더 이상 필요하지 않게 되었다.
주의할 점은 오라클 8.1.0과 그 이상 버전에 init.ora에 compatible 변수를 설정하지 않으면 CREATE INDEX 문의 DESC 절은 경고나 오류 없이 무산된다는 점이다.
이것을 설정하지 않으면 이전 버전의 기본 설정과 같이 처리된다.
설정 예) compatible ='11.2.0'

-Note---------------------------------------------------------------------
쿼리에서 ORDER BY 절을 제거하지 않도록 한다.
실행계획에 인덱스를 포함한다고 해서 해당 인덱스의 순서로 정렬된 결과를 얻을 수 있는 것은 아니다.
데이터베이스에서 어떤 순서로 정렬된 결과를 얻을 수 있는 유일한 방법은 ORDER BY 절을 포함하는 것이다.
ORDER BY를 대체할 수 있는 것은 없다.
---------------------------------------------------------------------------


출처 : 전문가를 위한 오라클 데이터베이스 아키텍처 - 토마스 카이트


※ 위 내용은, 여러 자료를 참고하거나 제가 주관적으로 정리한 것입니다.
   잘못된 정보나 보완이 필요한 부분을, 댓글 또는 메일로 보내주시면 많은 도움이 되겠습니다.
05 31, 2012 17:26 05 31, 2012 17:26


Trackback URL : http://develop.sunshiny.co.kr/trackback/780

  1. # 사랑은 2018年 08月 01日 09時 51分 Delete Reply

    정말 큰 도움이 되었습니다.. 감사합니다...

    1. Re: # sunshiny 2018年 08月 03日 23時 39分 Delete

      안녕하세요^^
      도움이 되셨다니, 저도 기분이 좋아지네요.
      답글 감사합니다.

Leave a comment

« Previous : 1 : ... 206 : 207 : 208 : 209 : 210 : 211 : 212 : 213 : 214 : ... 648 : Next »

Recent Posts

  1. HDFS - Python Encoding 오류 처리
  2. HP - Vertica ROS Container 관련 오류...
  3. HDFS - Hive 실행시 System Time 오류
  4. HP - Vertica 사용자 쿼리 이력 테이블...
  5. Client에서 HDFS 환경의 데이터 처리시...

Recent Comments

  1. 안녕하세요^^ 배그핵
  2. 안녕하세요^^ 도움이 되셨다니, 저... sunshiny
  3. 정말 큰 도움이 되었습니다.. 감사합... 사랑은
  4. 네, 안녕하세요. 댓글 남겨 주셔서... sunshiny
  5. 감사합니다 많은 도움 되었습니다!ㅎㅎ 프리시퀸스

Recent Trackbacks

  1. chatbots chatbots %M
  2. london relocation agents london relocation agents %M
  3. how to build chatbot how to build chatbot %M
  4. advanced chatbot advanced chatbot %M
  5. facebook ai chatbot facebook ai chatbot %M

Calendar

«   08 2019   »
        1 2 3
4 5 6 7 8 9 10
11 12 13 14 15 16 17
18 19 20 21 22 23 24
25 26 27 28 29 30 31

Bookmarks

  1. 위키피디아
  2. MysqlKorea
  3. 오라클 클럽
  4. API - Java
  5. Apache Hadoop API
  6. Apache Software Foundation
  7. HDFS 생태계 솔루션
  8. DNSBL - Spam Database Lookup
  9. Ready System
  10. Solaris Freeware
  11. Linux-Site
  12. 윈디하나의 솔라나라

Site Stats

TOTAL 2660959 HIT
TODAY 1275 HIT
YESTERDAY 1540 HIT