함수 기반 인덱스

설명

함수 기반 인덱스(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) );

참고 사항

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