FirebirdSQL logo
 TABLEVIEW 

Examples Using CREATE INDEX

  1. Creating an index for the UPDATER_ID column in the SALARY_HISTORY table

    CREATE INDEX IDX_UPDATER
  ON SALARY_HISTORY (UPDATER_ID);

  2. 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);

  3. Creating a multi-segment index for the ORDER_STATUS, PAID columns in the SALES table

    CREATE INDEX IDX_SALESTAT
  ON SALES (ORDER_STATUS, PAID);

  4. 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);

  5. 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');

  6. 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))

  7. 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)

  8. 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)

  9. 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)
See also

[fblangref50-ddl-idx-altridx], [fblangref50-ddl-idx-dropidx]

ALTER INDEX

Activates or deactivates an index, and rebuilds an index

Available in

DSQL, ESQL

Syntax
ALTER INDEX indexname {ACTIVE | INACTIVE}
Table 1. ALTER INDEX Statement Parameter
Parameter Description

indexname

Index name

The ALTER INDEX statement activates or deactivates an index.There is no facility on this statement for altering any attributes of the index.

INACTIVE

With the INACTIVE option, the index is switched from the active to inactive state.The effect is similar to the DROP INDEX statement except that the index definition remains in the database.Altering a constraint index to the inactive state is not permitted.

An active index can be deactivated if there are no queries prepared using that index;otherwise, an “object in use” error is returned.

Activating an inactive index is also safe.However, if there are active transactions modifying the table, the transaction containing the ALTER INDEX statement will fail if it has the NOWAIT attribute.If the transaction is in WAIT mode, it will wait for completion of concurrent transactions.

On the other side of the coin, if our ALTER INDEX succeeds and starts to rebuild the index at COMMIT, other transactions modifying that table will fail or wait, according to their WAIT/NO WAIT attributes.The situation is the same for CREATE INDEX.

Note
How is it Useful?

It might be useful to switch an index to the inactive state whilst inserting, updating or deleting a large batch of records in the table that owns the index.
ACTIVE

Rebuilds the index (even if already active), and marks it as active.

Note
How is it Useful?

Even if the index is active when ALTER INDEX …​ ACTIVE is executed, the index will be rebuilt.Rebuilding indexes can be a useful piece of housekeeping to do, occasionally, on the indexes of a large table in a database that has frequent inserts, updates or deletes but is infrequently restored.