Parallelized Index Creation

Since Firebird 5.0, index creation can be parallelized.Parallelization happens automatically if the current connection has two or more parallel workers — configured through ParallelWorkers in firebird.conf or isc_dpb_parallel_workers — and the server has parallel workers available.

Examples Using `CREATE INDEX`

Creating an index for the UPDATER_ID column in the SALARY_HISTORY table
```
CREATE INDEX IDX_UPDATER
  ON SALARY_HISTORY (UPDATER_ID);
```
Creating an index with keys sorted in the descending order for the CHANGE_DATE column in the SALARY_HISTORY table
```
CREATE DESCENDING INDEX IDX_CHANGE
  ON SALARY_HISTORY (CHANGE_DATE);
```
Creating a multi-segment index for the ORDER_STATUS, PAID columns in the SALES table
```
CREATE INDEX IDX_SALESTAT
  ON SALES (ORDER_STATUS, PAID);
```
Creating an index that does not permit duplicate values for the NAME column in the COUNTRY table
```
CREATE UNIQUE INDEX UNQ_COUNTRY_NAME
  ON COUNTRY (NAME);
```

Creating a computed index for the PERSONS table

CREATE INDEX IDX_NAME_UPPER ON PERSONS
  COMPUTED BY (UPPER (NAME));

An index like this can be used for a case-insensitive search:

SELECT *
FROM PERSONS
WHERE UPPER(NAME) STARTING WITH UPPER('Iv');

Creating a partial index and using its condition:

CREATE INDEX IT1_COL ON T1 (COL) WHERE COL < 100;
SELECT * FROM T1 WHERE COL < 100;
-- PLAN (T1 INDEX (IT1_COL))

Creating a partial index which excludes NULL

CREATE INDEX IT1_COL2 ON T1 (COL) WHERE COL IS NOT NULL;
SELECT * FROM T1 WHERE COL > 100;
PLAN (T1 INDEX IT1_COL2)

Creating a partial index with ORed conditions

CREATE INDEX IT1_COL3 ON T1 (COL) WHERE COL = 1 OR COL = 2;
SELECT * FROM T1 WHERE COL = 2;
-- PLAN (T1 INDEX IT1_COL3)

Using a partial index to enforce uniqueness for a subset of rows

create table OFFER (
  OFFER_ID bigint generated always as identity primary key,
  PRODUCT_ID bigint not null,
  ARCHIVED boolean default false not null,
  PRICE decimal(9,2) not null
);

create unique index IDX_OFFER_UNIQUE_PRODUCT
  on OFFER (PRODUCT_ID)
  where not ARCHIVED;

insert into OFFER (PRODUCT_ID, ARCHIVED, PRICE) values (1, false, 18.95);
insert into OFFER (PRODUCT_ID, ARCHIVED, PRICE) values (1, true, 17.95);
insert into OFFER (PRODUCT_ID, ARCHIVED, PRICE) values (1, true, 16.95);
-- Next fails due to second record for PRODUCT_ID=1 and ARCHIVED=false:
insert into OFFER (PRODUCT_ID, ARCHIVED, PRICE) values (1, false, 19.95);
-- Statement failed, SQLSTATE = 23000
-- attempt to store duplicate value (visible to active transactions) in unique index "IDX_OFFER_UNIQUE_PRODUCT"
-- -Problematic key value is ("PRODUCT_ID" = 1)

Parallelized Index Creation

Parallelized Index Creation

Examples Using CREATE INDEX

Examples Using `CREATE INDEX`