Open Source RDBMS - Seamless, Scalable, Stable and Free

한국어 | Login |Register

Versions available for this page: CUBRID 9.0.0 | 

Function-based Index

Description

Function-based index is used to sort or find the data based on the combination of values of table rows by using a specific function. For example, to find the space-ignored string, it can be used to optimize the query by using the function that provides the feature. In addition, it is useful to search the non-case-sensitive names.

Syntax

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;

Example

After the following indexes have been created, the SELECT query automatically uses the function-based index.

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

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

If a function-based index is created by using the LOWER function, it can be used to search the non-case-sensitive names.

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

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

To make an index selected while creating a query plan, the function used for the index should be used for the query condition in the same way. The SELECT query above uses the last_name_lower index created above.

However, this index is not used for the following condition:

SELECT * FROM clients_table

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

In addition, to make the function-based index used by force, use the USING INDEX syntax.

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;

Constraints

Arguments of functions which can be used in the function-based indexes, only column names and constants are allowed; nested expressions are not allowed. For example, a statement below will cause an error.

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

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

However, implicit cast is allowed. In the example below, the first argument type of the LEFT() function should be VARCHAR and the second argument type should be INTEGER; it works normally.

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

Function-based indexes cannot be used with filtered indexes. The example will cause an error.

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

Function-based indexes cannot become multiple-columns indexes. The example will cause an error.

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

Remark

Functions with the function-based indexes are as follows:

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