FirebirdSQL logo
 TABLEVIEW 

Computed (Expression) Indexes

In creating an index, you can use the COMPUTED BY clause to specify an expression instead of one or more columns.Computed indexes are used in queries where the condition in a WHERE, ORDER BY or GROUP BY clause exactly matches the expression in the index definition.The expression in a computed index may involve several columns in the table.

Note

Expression indexes can also be used as a workaround for indexing computed columns: use the name of the computed column as the expression.

Limits on Indexes

Certain limits apply to indexes.

The maximum length of a key in an index is limited to a quarter of the page size.

Maximum Indexes per Table

The number of indexes that can be accommodated for each table is limited.The actual maximum for a specific table depends on the page size and the number of columns in the indexes.

Table 1. Maximum Indexes per Table

Page Size

Number of Indexes Depending on Column Count

Single

2-Column

3-Column

4096

203

145

113

8192

408

291

227

16384

818

584

454

32768

1637

1169

909

Character Index Limits

The maximum indexed string length is 9 bytes less than the maximum key length.The maximum indexable string length depends on the page size, the character set, and the collation.

Table 1. Maximum indexable (VAR)CHAR length

Page Size

Maximum Indexable String Length by Charset Type

1 byte/char

2 byte/char

3 byte/char

4 byte/char

4096

1015

507

338

253

8192

2039

1019

679

509

16384

4087

2043

1362

1021

32768

8183

4091

2727

2045

Note

Depending on the collation, the maximum size can be further reduced as case-insensitive and accent-insensitive collations require more bytes per character in an index.See also Character Indexes in Chapter Data Types and Subtypes.