PREFIX 인덱스로 성능을 보장하라

파티션 테이블의 사용이 많아지면서 파티션 테이블의 인덱스에 관심이 높아지는 것은 당연한 일일 것이다. 테이블만 파티션 테이블로 생성한다면 이는 생각만큼의 효용가치는 없게 된다. 테이블을 생성하면서 해당 테이블에 인덱스가 존재하지 않는 경우는 그렇게 많지 않을 것이다. 대부분 테이블에 인덱스를 하나 이상 생성하여 조회 속도의 향상을 기대할 것이다. 이런 이유에서 파티션 테이블에서 파티션 인덱스의 생성은 매우 중요한 역할을 수행하게 된다. 지난 호에서는 로컬 인덱스와 글로벌 인덱스에 대해 언급을 하였다. 파티션 테이블의 인덱스는 로컬 인덱스와 글로벌 인덱스만이 존재하는 것은 아니다. 로컬 인덱스와 글로벌 인덱스가 가용성 등의 관리의 목적이었다면 PREFIX 인덱스와 NONPREFIX 인덱스는 성능 관점에서의 파티션 인덱스의 종류이다.

PREFIX 인덱스와 NONPREFIX 인덱스를 생성하자

PREFIX 인덱스 또는 NONPREFIX 인덱스 또한 로컬 인덱스 또는 글로벌 인덱스와 마찬가지로 가장 기본적인 사항이 인덱스의 생성일 것이다. 가장 먼저 PREFIX 인덱스와 NONPREFIX 인덱스를 생성하는 방법을 확인해 보자.

===============================
CREATE TABLE TEST
(YYYYMMDD VARCHAR2(6) NOT NULL.
ID CHAR(12) NOT NULL,
PHONE_NUMBER CHAR(11))
PARTITION BY RANGE(YYYYMM)
(PARTITION PART_200801 VALUES LESS THAN (‘200802’),
PARTITION PART_200802 VALUES LESS THAN (‘200803’));
CREATE INDEX TEST_IDX ON TEST(YYYYMM, ID)
LOCAL
(PARTITION PK_200801 VALUES LESS THAN (‘200802’),
PARTITION PK_200802 VALUES LESS THAN (‘200803’));
===============================

와 같이 인덱스를 생성한 경우를 확인해 보자. 위의 인덱스는 PREFIX 인덱스가 된다. 물론, 해당 인덱스는 로컬 인덱스이기도 한다. 위의 SQL을 수행한다면 로컬 인덱스이면서 PREFIX 인덱스가 생성이 될 것이다. 이미 로컬 인덱스는 지난 호에서 자세히 언급하였다. 그렇다면 위의 인덱스는 어떤 이유에서 PREFIX 인덱스가 되는가? 그 이유는 간단하다. PREFIX 인덱스는 파티션 인덱스의 파티션을 구분하는 컬럼이 인덱스의 첫번째 컬럼으로 생성하는 경우이다. 위의 예제에서 인덱스는 로컬 인덱스이므로 파티션 테이블을 구성한 파티션 키 컬럼인 YYYYMM 컬럼이 인덱스의 파티션 키 컬럼이 된다. 또한, 해당 인덱스의 첫 번째 컬럼이 YYYYMM 컬럼이므로 해당 인덱스를 PREFIX 인덱스라고 부르게 된다. 따라서, PREFIX 인덱스는 아래와 같이 정의할 수 있을 것이다.

PREFIX 인덱스 - 인덱스의 파티션 키 컬럼과 인덱스를 구성하는 첫 번째 컬럼이 동일한 파티션 인덱스

그렇다면 위와 같이 PREFIX 인덱스가 아닌 경우를 우리는 NONPREFIX 인덱스라고 부르게 될 것이다. 아래에서 NONPREFIX 인덱스를 생성하는 SQL을 확인해 보자.

===============================
CREATE INDEX TEST_IDX ON TEST( ID)
LOCAL
(PARTITION PK_200801 VALUES LESS THAN (‘200802’),
PARTITION PK_200802 VALUES LESS THAN (‘200803’));
===============================

위와 같이 파티션 인덱스를 생성한다면 어떻게 되겠는가? 파티션 테이블은 앞서 예제와 동일한 테이블이라고 가정하자. 해당 인덱스는 로컬 인덱스이기 때문에 인덱스는 YYYY MM 컬럼으로 분리되어 있을 것이다. 인덱스 컬럼은 ID 컬럼으로 구성되므로 인덱스의 파티션 키 컬럼과 인덱스를 구성하는 첫 번째 컬럼이 다르게 된다. 이와 같은 구조로 인덱스가 생성된다면 해당 인덱스를 NONPREFIX 인덱스라고 한다.

NONPREFIX 인덱스 - 인덱스의 파티션 키 컬럼과 인덱스를 구성하는 첫 번째 컬럼이 동일하지 않은 파티션 인덱스

결국, 위와 같은 형태의 인덱스를 NONPREFIX 인덱스라고 하게 된다. 그렇다면 이와 같은 PREFIX 인덱스와 NONPREFIX 인덱스의 차이는 무엇인가? 보기에는 단지 인덱스를 구성하는 컬럼의 위치에 따라 인덱스의 종류가 결정되는 형태이다. 이제부터 PREFIX 인덱스와 NONPREFIX 인덱스의 차이를 확인해 보자.

PREFIX 인덱스는 성능을 위한 보호 장치를 가지고 있다

PREFIX 인덱스가 성능에 대해 보호 장치를 가지고 있다는 것은 무엇을 의미하는가? 그렇다면 NONPREFIX 인덱스는 성능을 위한 보호 장치를 가지고 있지 않은 것인가? NONPREFIX 인덱스는 성능에 대해 보호 장치를 가지고 있지 않게 된다. 그렇다고 NONPREFIX 인덱스가 반드시 성능 문제를 발생시킨다는 의미는 아니다.
앞서 언급한 예제에서 PREFIX 인덱스의 경우를 확인해 보자. 해당 인덱스를 이용하기 위해서는 아래와 같은 SQL이 수행되어야 할 것이다.

===============================
SELECT * FORM TEST
WHERE YYYYMM = ‘200708’
AND ID = ‘200’;
===============================

위와 같이 SQL을 수행한다면 해당 SQL은 테이블이 YYYYMM 컬럼으로 분리되어 있기 때문에 YYYYMM 컬럼의 값이 ‘200708’인 데이터만 저장되어 있는 파티션만을 엑세스하면 원하는 데이터를 모두 추출할 수 있게 된다. WHERE 조건에서 ID 컬럼이 생략되더라도 YYYYMM 컬럼의 값이 ‘200708’인 데이터가 저장되어 있는 파티션만을 엑세스하게 될 것이다.
이처럼 PREFIX 인덱스의 첫 번째 컬럼에 대해 WHERE 조건에서 동일 조건(=)로 조회를 수행한다면 하나의 파티션만 엑세스하여 원하는 결과를 모두 추출할 수 있게 되므로 테이블을 전체 엑세스하는 최악의 경우를 방지할 수 있을 것이다. 이와 같기 때문에 PREFIX 인덱스는 성능을 위한 보호 장치를 가지고 있다고 하게 된다.
하지만, NONPREFIX 인덱스를 이용하는 SQL의 경우에는 이와 같이 하나의 테이블 파티션만을 엑세스하여 원하는 데이터를 모두 추출할 수는 없게 된다. 이 뜻은 NOPREFIX 인덱스를 이용하는 SQL은 전체 인덱스 파티션을 엑세스하여야만 원하는 데이터를 모두 추출할 수 있다는 의미가 된다. 이에 대한 자세한 내용은 다음 호에서 언급하도록 하겠다.

제공 : DB포탈사이트 DBguide.net

출처 : 마이크로소프트웨어