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: