DB

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

1223gogo 2023. 4. 3. 10:10

Oracle에서 사용되는 인덱스의 종류

  • B-TREE(Balanced Tree, 균형트리)
  • BITMAP
  • 함수기반(Function-Based)
  • 어플리케이션 도메인(Application domain)
  • Index-Organized

자동으로 생성되는 인덱스

- 테이블 생성 시에 제약조건(Primary Key or Unique)을 사용하면 자동으로 인덱스가 생성된다.

    * INDEX_TYPE : NORMAL로 생성된다.

    * NORMAL 인 INDEX_TYPE은 오라클에서 B-Tree 구조를 의미한다.(Oracle 기본은 B-Tree)

- 이때, 자동으로 생성된 인덱스는 DROP INDEX 구문으로 삭제할 수 없다.(인덱스를 삭제하고 싶다면 제약조건을 제거해야한다.)

 

오라클의 기본 인덱스

  • 오라클의 기본 인덱스 구조는 B-Tree 구조이다.
  • 오라클의 기본 블록크기는 8Kbyte이다.
    • 블록(block) : 트리구조에서 사용하는 노드라는 용어에 해당되는 말이다. (노드는 트리구조에서 데이터가 존재하는 공간을 말한다.)
    • 블록크기(블록당 들어갈 수 있는 데이터 양)는 SHOW PARAMETER DB_BLOCK_SIZE 구문으로 확인할 수 있다.
    • 블록크기는 2KB, 4KB, 8KB, 16KB, 32KB 등으로 변경 가능하다.

인덱스의 특징

  • 인덱스 생성 시, 데이터 블록은 그냥 둔 상태에서 별도의 블록에 인덱스를 구성한다.
  • 인덱스의 리프 블록은 데이터가 아닌, 주소값(ROWID)이다.
  • INSERT/UPDATE/DELETE 시에는 인덱스가 존재하는 것이 인덱스가 존재하지 않는 것보다 성능 측면에서 느리다.
  • 인덱스는 여러개 생성 가능하지만 성능을 떨어뜨릴 수도 있으니 필요한 상황에서 생성하는 것이 좋다.
  • 인덱스를 검색하기 위한 필수조건으로는 WHERE 절에 해당 인덱스를 생성한 열의 이름이 나와야한다.

오라클 인덱스 검색

SELECT *
  FROM USER_INDEXES;
  
SELECT * 
  FROM USER_IND_COLUMNS;
  
SELECT I.*, C.*
  FROM USER_INDEXES I
      INNER JOIN USER_IND_COLUMNS C
      ON I.INDEX_NAME = C.INDEX_NAME
 WHERE I.TABLE_NAME = '테이블명';

오라클 힌트

SELECT 문을 수행하면 기본적으로 오라클의 옵티마이저(Optimizer)가 효율적으로 계획을 세워 수행한다. 이러한 옵티마이저의 작동을 하지 않고 직접 처리하는 방법을 지정하는 것을 오라클 힌트라고 부른다. 

--힌트의 형식
SELECT /*+ 힌트 */ 
열이름 FROM ~~
;
SELECT --+ 힌트 
열이름 FROM ~~
;

힌트의 종류

ALL_ROWS, FIRST_ROWS(n), CHOOSE, RULE, USE_CONCAT, NOREWRITE, MERGE, FACT, FULL, INDEX, NO_INDEX, ORDERED, USE_NL, USE_HASH 등이 있다.

--INDEX 힌트를 사용하는 방법
SELECT /*+ INDEX([테이블이름] [인덱스이름]) */
열이름 FROM ~~

비트맵(BITMAP) 인덱스와 B-Tree 인덱스

B-TREE 인덱스는 데이터 값의 중복도가 낮을 때 사용한다.(다양한 데이터 값이 존재할 때) (ex 사원번호, 이름, 입사일 등)

비트맵 인덱스는 비트(bit)라는 최소 단위를 이용해서 데이터 값을 저장하기 때문에, 데이터의 중복도가 높은 경우에 사용하면 효율적이다.(ex 성별 등)

비트맵 인덱스는 OLTP보다는 OLAP에서 더욱 활용된다.

OLTP와 OLAP

OLTP(Online Transaction Processing) : 1개의 트랜잭션을 처리하는 과정, 무수히 발생하는 각각의 요청을 오류없이 처리하고 무결성을 보장한다. 1개의 트랜잭션 내에서 INSERT/UPDATE/DELETE 과정의 무결성을 보장하고 그 결과를 SELECT 하는 과정을 OLTP라고 부른다.

OLAP(Online Analytical Processing) : 데이터를 기반으로 분석하는 과정, 기존에 저장되어 있는 데이터를 사용자의 요구와 목적에 맞게 분석하여 정보를 제공하는 개념이다. 데이터 집계(COUNT, SUM) 등 으로 사용자의 의사결정에 도움을 줄 수 있는 데이터를 제공함.