Open Source RDBMS - Seamless, Scalable, Stable and Free

한국어 | Login |Register

Versions available for this page: CUBRID 9.0.0 | 

함수 기반 인덱스

설명

함수 기반 인덱스(function-based index)는 특정 함수를 이용하여 테이블 행들로부터 값의 조합에 기반한 데이터를 정렬하거나 찾고 싶을 때 사용한다. 예를 들어, 공백을 무시한 문자열을 찾는 작업을 하고 싶을 때 이러한 기능을 수행하는 함수를 이용하게 되는데, 함수를 통해 칼럼 값을 변경하게 되면 일반 인덱스를 통해서 인덱스 스캔을 할 수 없다. 이러한 경우에 함수 기반 인덱스를 생성하면 이를 통해 해당 질의 처리를 최적화할 수 있다. 다른 예로, 대소문자를 구분하지 않는 이름을 검색할 때 활용할 수 있다.

구문

CREATE /* hints */ [REVERSE] [UNIQUE] INDEX index_name

        ON table_name (function_name (argument_list));

ALTER /* hints */ [REVERSE] [UNIQUE] INDEX index_name

        [ ON table_name (function_name (argument_list)) ]

        REBUILD;

예제

다음 인덱스가 생성된 이후 SELECT 질의는 자동으로 함수 기반 인덱스를 사용한다.

CREATE INDEX idx_trim_post ON posts_table(TRIM(keyword));

SELECT * FROM posts_table WHERE TRIM(keyword) = 'SQL';

LOWER 함수로 함수 기반 인덱스를 생성하면, 대소문자 구분을 안 하는 이름을 검색할 때 사용될 수 있다.

CREATE INDEX idx_last_name_lower ON clients_table(LOWER(LastName));

SELECT * FROM clients_table WHERE LOWER(LastName) = LOWER('Timothy');

질의 계획을 생성할 때 인덱스가 선택되게 하기 위해서는, 이 인덱스에서 사용되는 함수가 질의 조건에서 같은 방법으로 사용되어야 한다. 위의 SELECT 질의는 위에서 생성된 last_name_lower 인덱스를 사용한다.

하지만 다음과 같은 조건에서는 함수 기반 인덱스 형태와 다른 표현식이 주어졌기 때문에 인덱스가 사용되지 않는다.

SELECT * FROM clients_table

WHERE LOWER(CONCAT('Mr. ', LastName)) = LOWER('Mr. Timothy');

함수 기반 인덱스의 사용을 강제하려면 USING INDEX 구문을 사용할 수 있다.

CREATE INDEX i_tbl_first_four ON tbl(LEFT(col, 4));

SELECT * FROM clients_table

WHERE LEFT(col, 4) = 'CAT5'

USING INDEX i_tbl_first_four;

제약 사항

함수 기반 인덱스에서 사용할 함수의 인자는 테이블의 칼럼 이름 혹은 상수인 경우만 허용하며, 복잡한 중첩된 표현식은 허용하지 않는다. 예를 들어 아래의 문장은 오류를 발생한다.

CREATE INDEX my_idx ON tbl (TRIM(LEFT(col, 3)));

CREATE INDEX my_idx ON tbl (LEFT(col1, col2 + 3));

그러나, 묵시적인 타입 변환(implicit cast)은 허용된다. 아래의 예에서 LEFT() 함수는 첫 번째 인자 타입이 VARCHAR이고 두 번째 인자 타입이 INTEGER여야 하지만 정상 동작한다.

CREATE INDEX my_idx ON tbl (LEFT(int_col, str_col));

함수 기반 인덱스는 필터링된 인덱스와 함께 사용될 수 없다. 아래의 예는 오류를 발생한다.

CREATE INDEX my_idx ON tbl ( TRIM(col) ) WHERE col > 'SQL';

함수 기반 인덱스는 다중 칼럼 인덱스가 될 수 없다. 아래의 예는 오류를 발생한다.

CREATE INDEX my_idx ON tbl ( TRIM(col1), col2, LEFT(col3, 5) );

참고 사항

함수 기반 인덱스로 사용할 수 있는 함수는 다음과 같다.

MOD

LEFT

RIGHT

REPEAT

SPACE

MID

STRCMP

REVERSE

BIT_COUNT

MODULUS

FLOOR

CEIL

ABS

POWER

ROUND

LOG

EXP

SQRT

SIN

COS

TAN

COT

ACOS

ASIN

ATAN

ATAN2

DEGREES

DATEF

TIMEF

RADIANS

LN

LOG2

LOG10

TRUNC

CHR

INSTR

LEAST

GREATEST

POSITION

LOWER

UPPER

CHAR_LENGTH

LTRIM

RTRIM

FROM_UNIXTIME

SUBSTRING_INDEX

MD5

LPAD

RPAD

REPLACE

TRANSLATE

ADD_MONTHS

LAST_DAY

UNIX_TIMESTAMP

STR_TO_DATE

TIME_FORMAT

TIMESTAMP

YEARF

MONTHF

DAYF

DAYOFMONTH

HOURF

MINUTEF

SECONDF

QUARTERF

 

WEEKDAY

DAYOFWEEK

DAYOFYEAR

TODAYS

FROMDAYS

TIMETOSEC

SECTOTIME

MAKEDATE

MAKETIME

WEEKF

MONTHS_BETWEEN

FORMAT

DATE_FORMAT

ADDDATE

DATE_ADD

DATEDIFF

TIMEDIFF

SUBDATE

DATE_SUB

FUNCTION_HOLDER

BIT_LENGTH

OCTET_LENGTH

IFNULL

LOCATE

SUBSTRING

SUBSTR

NVL

NVL2

NULLIF

TO_CHAR

TO_DATE

TO_DATETIME

TO_TIMESTAMP

TO_TIME

TO_NUMBER

TRIM

INET_ATON

INET_NTOA