| Firebird Documentation Index → Firebird 1.5 Language Ref. Update → DDL statements → CREATE INDEX |
|
|
|
Available in: DSQL, ESQL
Changed in: 1.5
Description: In compliance with the SQL-99 standard, NULLs – even multiple – are now allowed in columns that have a UNIQUE index defined on them. For a full discussion, see CREATE TABLE :: UNIQUE constraints now allow NULLs. As far as NULLs are concerned, the rules for unique indices are exactly the same as those for unique keys.
Changed in: 1.0.3 and 1.5
Description: The maximum number of 64 indices per table has been removed in Firebird 1.0.3, and reintroduced at the higher level of 256 in Firebird 1.5.
Probably due to an off-by-one error in the code, the effective ceiling is 65 indices in Firebird 1.0 and 1.0.2, and 257 indices in Firebird 1.5.
The number of indices attainable in practice is further limited by the database page size and the number of columns per index, as shown in the table below.
Table 5.1. Maximum indices per table
| Page size | Firebird version(s) | ||||||||
|---|---|---|---|---|---|---|---|---|---|
| 1.0, 1.0.2 | 1.0.3 | 1.5.x | |||||||
| 1 col | 2 cols | 3 cols | 1 col | 2 cols | 3 cols | 1 col | 2 cols | 3 cols | |
| 1024 | 62 | 50 | 41 | 62 | 50 | 41 | 62 | 50 | 41 |
| 2048 | 65 | 65 | 65 | 126 | 101 | 84 | 126 | 101 | 84 |
| 4096 | 65 | 65 | 65 | 254 | 203 | 169 | 254 | 203 | 169 |
| 8192 | 65 | 65 | 65 | 510 | 408 | 340 | 257 | 257 | 257 |
| 16384 | 65 | 65 | 65 | 1022 | 818 | 681 | 257 | 257 | 257 |
Please be aware that under normal circumstances, even 64 indices is way too many and will drastically reduce mutation speeds. The maximum was raised to accommodate data-warehousing applications and the like, that do lots of bulk operations during which indices are temporarily switched off.
|
| Firebird Documentation Index → Firebird 1.5 Language Ref. Update → DDL statements → CREATE INDEX |