콘텐츠로 이동
≡

Administrator

Developer

Tools

External Tools

CREATE INDEX

CREATE INDEX#

구문#

create_index ::=#

create_index_image98

table_index_clause ::=, memory_index_clause ::=, disk_index_clause ::=, storage_clause ::=

table_index_clause ::=#

table_index_clause

memory_index_clause ::=#

memory_index_clause_image98_1

index_partitioning_clause ::=, domain_index_clause ::=, directkey_clause ::=, memory_index_attributes ::=

disk_index_clause ::=#

disk_index_clause_image98_2

index_partitioning_clause ::=, domain_index_clause ::, disk_index_attributes ::=, physical_attributes_clause ::=

domain_index_clause ::=#

domain_index_clause

directkey_clause ::=#

directkey_clause

memory_index_attributes ::=#

memory_index_attributes_image98_3

parallel_clause ::=

storage_clause ::=#

storage_clause

index_partitioning_clause ::=#

index_partitioning_clause

index_partition_definition ::=#

index_partition_def

disk_index_attributes ::=#

disk_index_attributes_image98_4

parallel_clause ::=#

parallel_clause_create_index

logging_clause ::=#

logging_clause_create_index

physical_attributes_clause ::=#

physical_attributes_clause_image98_5

전제 조건#

SYS 사용자, CREATE INDEX 시스템 권한을 가진 사용자 또는 인덱스가 생성될 테이블에 대한 INDEX 객체 권한을 가진 사용자만이 이 구문으로 인덱스를 생성할 수 있다.

함수 기반 인덱스(Function-based indexes)를 생성하려면, 일반 인덱스와 동일한 권한이 필요하다. 다만, 수식에 사용자 정의 함수가 포함될 경우, 해당 함수는 DETERMINISTIC으로 생성된 것이어야 한다. 또한, 사용자는 함수 기반 인덱스에 사용된 다른 사용자 소유의 사용자 정의 함수에 대해 EXECUTE 객체 권한을 가지고 있어야 한다.

설명#

테이블의 한 개 이상의 칼럼 또는 수식에 대해 인덱스를 생성하는 구문이다. 파티션드 인덱스(즉, 로컬 인덱스)를 생성할 때 LOCALUNIQUE 옵션을 사용해서 로컬 유니크 속성을 부여할 수 있다. 디스크 파티션드 테이블에 인덱스를 생성할 때 LOCALUNIQUE 옵션 또는 LOCAL 키워드를 사용하지 않으면, 논파티션드 인덱스를 생성한다.

파티션드 인덱스는 파티션 키 칼럼과 인덱스 키 칼럼의 관계에 따라 프리픽스드 인덱스(prefixed-index)와 논프리픽스드 인덱스(non-prefixed index)로 구분된다. 파티션 키 칼럼과 인덱스 키 칼럼의 가장 왼쪽 칼럼이 같은 경우 프리픽스드 인덱스이며, 같지 않은 경우 논프리픽스드 인덱스이다.

함수 기반 인덱스는 수식을 기반으로 생성된 인덱스이다. 이 수식은 내장 SQL 함수 또는 사용자 정의 함수를 포함할 수 있다.

user_name#

생성될 인덱스의 소유자 이름을 명시한다. 생략하면 Altibase는 현재 세션에 연결된 사용자의 스키마에 인덱스를 생성한다.

index_name#

생성될 인덱스 이름을 명시한다. 인덱스 이름은 2장의 "객체 이름 규칙"을 따라야 한다.

UNIQUE#

중복 값을 허용하지 않는다.

LOCALUNIQUE#

파티션드 인덱스 생성시 사용할 수 있다. 파티션드 인덱스의 각 인덱스 파티션별로 UNIQUE제약 조건을 만족해야 함을 가리킨다.

index_expr#

여기에는 인덱스가 기반할 칼럼 또는 수식을 명시한다.

수식에는 테이블의 칼럼, 상수, SQL 함수, 및 사용자 정의 함수가 포함될 수 있다. 칼럼이 아니라 수식을 명시하는 경우, 함수 기반 인덱스가 생성된다.

함수 기반 인덱스의 제약 사항:#
  • 수식에는 함수 기반 인덱스가 생성될 테이블의 칼럼만 포함될 수 있다. 또한, 칼럼 이름 앞에 스키마와 테이블 이름을 지정할 수 없다.
  • 수식에는 상수(문자열 또는 숫자)가 포함될 수 있다.
  • 수식에 SUM 같은 집계 함수를 사용할 수 없다.
  • 수식에 포함되는 저장 프로시저에는 커서 및 %ISOPEN 등의 커서 속성, SQLCODE, SQLERRM 등을 사용할 수 없다.
  • 글로벌 파티션드 인덱스의 경우, 수식은 파티셔닝 키가 될 수 없다.
  • 함수 인자가 없더라도 모든 함수에 괄호를 명시해야 한다. 그렇지 않으면 데이터베이스 서버가 칼럼 이름으로 인식한다.
  • 수식에 완전하게 기술되지 않는 DATE 상수를 허용한다. 즉, DATE 타입 값에 년도를 지정하지 않으면 현재 년도가 기본값이 되며, 월을 지정하지 않으면 현재 월이 기본값이 된다. 타임 존 값은 Altibase의 DATE 타입에 지정할 수 없다.
  • 수식에는 항상 동일한 값을 반환하는 내장 SQL 함수만 포함될 수 있다. 예를 들어, SYSDATE 함수는 사용할 수 없다.
  • 수식에 포함되는 사용자 정의 함수는 DETERMINISTIC으로 선언되어야 한다.
  • 수식에는 부질의(subquery)가 포함될 수 없다.
  • 수식에는 시퀀스가 포함될 수 없다.
  • 수식에는 어떠한 의사 칼럼(pseudo column)도 포함될 수 없다.
  • 수식에 PRIOR 연산자를 사용할 수 없다.
  • 수식에 LOB 데이터를 사용할 수 없다.
  • QUERY_REWRITE_ENABLE 프로퍼티가 1인 경우에만, 쿼리 최적화 과정에서 함수 기반 인덱스가 선택될 수 있다.

ASC/DESC#

각각의 인덱스 키 칼럼이 오름차순인지 내림차순인지를 지정한다.

index_partitioning_clause#

생성될 인덱스가 파티션드 인덱스임을 명시하고, 인덱스 파티션들이 저장될 테이블스페이스를 지정하는 절이다. 이 절을 생략하면, 인덱스 파티션들은 테이블의 기본 테이블스페이스에 저장될 것이다.

파티션드 인덱스를 생성하는 가장 쉬운 방법은 인덱스 생성시 LOCAL 키워드를 명시하는 것이다. 아니면, 각 테이블 파티션별로 생성될 인덱스 파티션에 대한 속성을 상세히 명시해야 한다.

LOCAL 키워드만 명시하면, 각 테이블 파티션별로 인덱스 파티션이 자동으로 생성되며 각 인덱스 파티션의 이름은 시스템에 의해 자동으로 부여된다. 인덱스 파티션 이름은 SYS_IDX1, SYS_IDX2, ... 와 같은 형태로 순차적으로 붙여진다.

모든 테이블 파티션 또는 그 일부에 인덱스 파티션을 직접 명시할 수 있다. 일부에만 명시하는 경우 나머지 테이블 파티션에 대한 인덱스 파티션은 자동적으로 생성되며, 생성 방식은 상기와 동일하다.

파티션드 인덱스 생성시 테이블스페이스를 명시하지 않으면, 각 인덱스 파티션이 저장될 테이블스페이스는 아래 도표의 과정을 거쳐서 결정된다.

create_index_expl

주의: 메모리 인덱스는 테이블스페이스에 저장되지 않으므로, 각 파티션이 저장될 테이블스페이스를 지정하더라도 이는 무시된다.

BTREE#

B+-Tree 인덱스를 생성하도록 지시한다. 이는 범위 검색 시 유용하다. INDEXTYPE IS 절 생략 시 기본으로 B+-Tree 인덱스가 생성된다.

RTREE#

R-Tree 인덱스를 생성하도록 지시한다. 이는 다차원 데이터 처리 시 유용하다.

directkey_clause#

이 절은 Direct Key 인덱스 생성 시 사용할 수 있다. Direct Key란 인덱스 노드에 저장되는 레코드이며, Direct Key 인덱스에는 레코드 포인터와 함께 실제 레코드도 저장되기 때문에 인덱스 스캔 비용을 줄일 수 있다.

MAXSIZE integer#

Direct Key의 최대 크기를 설정할 수 있으며, 명시하지 않을 경우 기본값은 8바이트이다. 지원하는 데이터 타입의 MAXSIZE 값은 아래의 표를 참조한다.
Partial Key를 지원하는 문자형 타입은 MAXSIZE 값보다 설정 값이 큰 경우에는 MAXSIZE만큼 prefix가 되어 Direct Key로 저장된다. 그러나 Partial Key를 지원하지 않는 타입은 MAXSIZE 값보다 설정 값이 큰 경우 인덱스 생성에 실패한다.

Direct Key 인덱스의 제약사항#
  • 복합 키 인덱스(Composite Index)에 Direct Key 인덱스를 생성하는 경우 첫 번째 칼럼만 Direct Key로 설정된다.
  • 압축 칼럼과 암호화 칼럼은 Direct Key 인덱스를 설정할 수 없다.
  • 디스크 인덱스에는 Direct Key 인덱스를 설정할 수 없다.

아래는 Direct Key 인덱스를 지원하는 데이터 타입을 나타내는 표이다.

분류

자료형

MAXSIZE

(Full Key 지원)

Partial Key 지원

Native 숫자형

BINGINT

8

X

DOUBLE

8

X

INTEGER

4

X

REAL

4

X

SMALLINT

2

X

Non-Native 숫자형

FLOAT

FLOAT(p)

23

3 + ( ( p + 2 ) / 2 )

X

NUMBER

NUMBER(p, s)

NUMBER(p)

23

3 + ( ( p + 2 ) / 2 )

X

NUMERIC

NUMERIC(p, s)

NUMERIC(p)
 (=DECIMAL)

23

3 + ( ( p + 2 ) / 2 )

X

문자형

CHAR(M)

M + 2

O

VARCHAR(M)

M + 2

O

NCHAR(M)

(M * 2) + 2 <UTF16>

(M * 3) + 2 <UTF8

O

NVARCHAR(M)

(M * 2) + 2 <UTF16>

(M * 3) + 2 <UTF8>

O

날짜형

DATE

8

X

이진 데이터형

BIT(M)

(M / 8) + 4

X

VARBIT(M)

(M / 8) + 4

 

BYTE(M)

M + 2

X

NIBBLE(M)

(M / 2) + 1

 

physical_attributes_clause#

INITRANS 절#

TTS(Touched Transaction Slot)의 초기 개수를 지정한다. 기본값은 8개이다.

MAXTRANS 절#

TTS의 최대 개수를 지정한다. 기본값은 50개이다.

TABLESPACE 절#

인덱스가 저장될 테이블스페이스 이름을 명시한다. 이 절을 생략하면 Altibase는 인덱스를 그 인덱스가 속하는 스키마 소유자의 기본 테이블스페이스에 생성한다. 그러나 메모리 테이블에 인덱스를 생성하는 구문에는 테이블스페이스를 지정하더라도 메모리 인덱스는 테이블스페이스에 저장되지 않으므로 이는 무시된다.

parallel_clause#

이는 인덱스를 생성하는 쓰레드의 개수를 결정하는 데 사용되는 힌트(hint)로 인덱스 생성시 성능 향상을 목적으로 사용한다. Altibase는 사용자가 힌트로 지정한 parallel_factor, 인덱스 생성 대상 테이블의 크기 및 인덱스 생성 시 사용가능한 메모리 양을 고려하여 최적화된 인덱스 생성 쓰레드의 개수를 결정한다.

parallel_factor의 입력 가능한 값의 범위는 0 ~ 512이고, 기본값은 Altibase가 동작중인 호스트 장비의 CPU 개수이다. 인덱스 생성 쓰래드 개수는 위에서 설명한 최적화 방법으로 결정되기 때문에 parallel_factor는 생략해도 무방하다.

이 구문을 생략하거나 0으로 설정한 경우, parallel_factor와 동일한 의미를 갖는 altibase.properties 파일에 설정된 INDEX_BUILD_THREAD_COUNT 프로퍼티 값이 대신 사용된다. INDEX_BUILD_THREAD_COUNT프로퍼티도 명시되어 있지 않은 경우, CPU 개수에 기반하여 최적화된 인덱스 생성 쓰레드 개수가 결정된다.

parallel_factor의 값을 실제 CPU 개수보다 큰 값으로 설정하는 경우, 혹은 최대값인 512를 초과해 설정하는 경우, CPU 개수를 힌트로 사용하여 최적화된 인덱스 생성 쓰레드 개수가 결정된다.

logging_clause#

로깅(LOGGING) 또는 노로깅(NOLOGGING) 옵션을 사용하면 디스크 테이블을 위한 인덱스를 생성할 때 발생하는 로그를 기록하거나 기록하지 않게 할 수 있다. 기본 값은 로깅을 하는 것이며, 이는 인덱스 생성시 관련 로그가 기록됨을 의미한다.

생성된 디스크 인덱스를 강제로 디스크에 저장할 것인지 여부를 지정하려면 FORCE 또는 NOFORCE 옵션을 사용한다.

logging_clause에 대한 자세한 설명은 Administrator's Manual의 "데이터베이스 객체 및 권한" 장의 "인덱스"를 참고한다.

storage_clause#

사용자가 세그먼트 내의 익스텐트를 관리하는 파라미터를 지정할 수 있는 구문이다.

INITEXTENTS 절#

세그먼트 생성 시 기본으로 할당되는 익스텐트의 개수를 지정한다. 기본값은 1이다.

NEXTEXTENTS 절#

세그먼트 크기 확장시마다 추가될 익스텐트 개수를 명시한다. 기본값은 1이다.

MINEXTENTS 절#

세그먼트의 최소 익스텐트 개수를 지정한다. 기본값은 1이다.

MAXEXTENTS 절#

세그먼트의 최대 익스텐트 개수를 지정한다. 명시하지 않을 경우 세그먼트 내의 익스텐트 최대 개수는 제한이 없다.

주의 사항#

  • 파티션드 테이블에 인덱스(즉, 파티션드 인덱스)를 생성하는 경우, 각 로컬 인덱스가 저장될 테이블스페이스는 index_partitioning_clause 에 따로따로 지정할 수 있다. disk_index_attributes는 파티션드 인덱스 전체를 위한 테이블스페이스를 지정하는데 사용될 수 없다. 즉, 파티션드 인덱스 전체를 위한 테이블스페이스는 지정할 수 없다. 또한 로컬 인덱스는 B+-tree 인덱스일 수만 있다.

  • 시스템이나 미디어 고장시 NOLOGGING(FORCE/NOFORCE) 옵션으로 생성된 인덱스의 일관성은 보장되지 않을 수 있다. 이 경우 'The index is inconsistent.'라는 오류 메시지가 발생할 것이다. 이러한 오류를 해결하려면 일관성이 깨어진 인덱스를 찾아 삭제한 후에 해당 인덱스를 다시 생성하도록 한다. 인덱스의 일관성은 V$DISK_BTREE_HEADER 성능 뷰로 확인할 수 있다.

  • LOB 칼럼은 인덱스 키 칼럼이 될 수 없다.

예제 1: 인덱스 생성 기본#

<질의 1> 사원 테이블의 eno 칼럼 (오름차순)과 dno칼럼 (내림차순)에 인덱스 emp_idx2를 생성하라.

iSQL> CREATE INDEX emp_idx2
    ON employees (eno ASC, dno DESC);
Create success.

예제 2: 유니크 인덱스 생성#

<질의 2> 사원 테이블의 dno 칼럼에 내림차순의 유니크 인덱스 emp_idx2를 생성하라. (이는 사원 테이블에 레코드가 전혀 없거나 칼럼 dno에 unique한 값들만 존재 할 때 가능하다.)

iSQL> CREATE UNIQUE INDEX emp_idx2
    ON employees (dno DESC);
Create success.

예제 3: B+tree 인덱스 생성#

<질의 3>테이블 employees의 eno 칼럼에 오름차순으로 B+tree 인덱스 emp_idx3를 생성하라. 이미 사원 테이블의 eno칼럼을 오름차순으로 정렬한 PRIMARY KEY가 존재하기 때문에 인덱스 emp_idx3를 생성하기 전에 기본키 제약을 삭제해야 한다. 그렇지 않으면, 다음 오류가 발생할 것이다.

ERR-3104C: Duplicate key columns in an index

iSQL> ALTER TABLE employees
    DROP PRIMARY KEY;
Alter success.
iSQL> CREATE INDEX emp_idx3
    ON employees (eno ASC)
    INDEXTYPE IS BTREE;
Create success.

예제 4: 테이블스페이스 지정#

<질의 4> user_data 테이블스페이스에 table_user 테이블의 i1칼럼에 인덱스 idx1을 생성하라.

iSQL> CREATE INDEX idx1
    ON table_user (i1)
    TABLESPACE user_data;
Create success.

예제 5: 병렬 옵션 사용#

<질의 5> user_data 테이블스페이스에 table_user 테이블의 i1 칼럼에 인덱스 idx2을 병렬 옵션으로 생성하라.

iSQL> CREATE INDEX idx1
    ON table_user (i1)
    TABLESPACE user_data PARALLEL 4;
Create success.

예제 6: 로컬 인덱스 생성#

<질의 6> 로컬 인덱스, 즉 product_id를 기준으로 각 테이블 파티션에 대응하는 파티션별로 인덱스를 생성하라. 파티션의 이름은 자동으로 부여되도록 하라.

CREATE INDEX prod_idx ON products(product_id) LOCAL;

예제 7: 로컬 인덱스 생성 - 인덱스 파티션 지정#

<질의 7> 각각의 인덱스 파티션을 지정해서 로컬 인덱스를 생성하라.

CREATE INDEX prod_idx ON products(product_id) 
LOCAL
( 
  PARTITION p_idx1 ON p1 TABLESPACE tbs_disk1,
  PARTITION p_idx2 ON p2 TABLESPACE tbs_disk2,
  PARTITION p_idx3 ON p3 TABLESPACE tbs_disk3 
);

예제 8: 로컬 인덱스 생성 - 인덱스 파티션 일부 지정#

<질의 8> 인덱스 파티션 일부만 지정해서 로컬 인덱스를 생성하라. 지정하지 않은 파티션은 자동으로 결정된다.

CREATE INDEX prod_idx ON products(product_id) 
LOCAL
( 
  PARTITION p_idx1 ON p1 TABLESPACE tbs_disk1,
  PARTITION p_idx3 ON p3 TABLESPACE tbs_disk3 
);

예제 9: LOGGING 옵션 사용#

<질의 9> 테이블 employees의 사원번호(eno)에 인덱스 idx1을 생성하되 시스템 고장이나 미디어 고장이 발생하더라도 사용할 수 있게 LOGGING 옵션을 사용하라. Employees 테이블이 디스크 테이블스페이스에 있다고 가정한다.

iSQL> CREATE INDEX idx1
    ON employees (eno);
Create success.

또는

iSQL> CREATE INDEX idx1
    ON employees (eno) LOGGING ;
Create success.

예제 10: NOLOGGING 및 NOLOGGING FORCE 옵션 사용#

<질의 10> 테이블 employees의 eno 칼럼(오름차순)과 dno칼럼(오름차순)에 인덱스 idx1을 NOLOGGING 옵션으로 생성하라. 단, 인덱스 생성 후 시스템 고장이 발생하더라도 인덱스가 사용가능하도록 FORCE옵션을 사용하라. Employees 테이블이 디스크 테이블스페이스에 있다고 가정한다.

iSQL> CREATE INDEX idx1
    ON employees (eno ASC, dno ASC)
    NOLOGGING;
Create success.

또는
사원번호(eno): ASC
부서번호(dno): ASC
iSQL> CREATE INDEX idx1
    ON employees (eno ASC, dno ASC)
    NOLOGGING FORCE;
Create success.

예제 11: NOLOGGING NOFORCE 옵션 사용#

<질의 11> 테이블 employees의 eno 칼럼(오름차순)과 dno칼럼(오름차순)에 인덱스 idx1을 NOLOGGING 옵션으로 생성하고, 디스크에 반영하지 않게 NOFORCE옵션을 사용하라. Employees 테이블이 디스크 테이블스페이스에 있다고 가정한다.

iSQL> CREATE INDEX idx1
    ON employees (eno ASC, dno ASC)
    NOLOGGING NOFORCE;
Create success.

예제 12: 익스텐트 관리 파라미터 지정#

<질의 12> 디스크 테이블스페이스 USERTBS의 LOCAL_TBL 테이블에 인덱스 LOCAL_IDX를 생성하라. 단, 인덱스 생성시 익스텐트 1개가 할당되고 인덱스 세그먼트 크기 확장시마다 2개씩 증가되며, 세그먼트의 총 익스텐트 개수는 제한하지 않는다.

iSQL> CREATE INDEX LOCAL_IDX ON LOCAL_TBL ( I1 ) 
 TABLESPACE USERTBS
 STORAGE ( INITEXTENTS 1 NEXTEXTENTS 2 MAXEXTENTS UNLIMITED );
Create success.

예제 13: 함수 기반 인덱스 생성#

<질의 13> employees 테이블의 salary 칼럼을 사용해서 연봉에 기반한 함수 기반 인덱스를 생성하라.

iSQL> CREATE INDEX income_idx ON employees (salary*12);
Create success.

예제 14: 함수 기반 인덱스 생성 - 사용자 정의 함수 이용#

<질의 14> 사용자 정의 함수를 사용해서 <질의 13>과 동일한 인덱스를 생성하라.

CREATE OR REPLACE FUNCTION get_annual_salary
(salary in integer) 
RETURN integer
DETERMINISTIC
AS
BEGIN
    RETURN salary*12;
END;
/

iSQL> CREATE INDEX income_idx ON employees(sys.get_annual_salary(salary));
Create success.

예제 15: Direct Key 인덱스 생성#

<질의 15> 테이블 employees의 eno 칼럼에 Direct Key 인덱스를 생성하라.

iSQL> CREATE INDEX direct_idx ON employees ( eno ) DIRECTKEY ;
Create success.

예제 16: Direct Key 인덱스 생성 시 최대 크기 지정#

<질의 16> 테이블 tab1의 name 칼럼(varchar(100))에 32바이트의 레코드를 저장할 수 있는 Direct Key 인덱스를 생성하라.

iSQL> CREATE INDEX idx1 ON tab1 ( name ) DIRECTKEY MAXSIZE 32;
Create success.