ALTER TABLE COLUMN ADD INDEX

Indexes an existing symbol column.

Syntax​

Flow chart showing the syntax of the ALTER TABLE ALTER COLUMN ADD INDEX keyword

Adding an index is an atomic, non-blocking, and non-waiting operation. Once complete, the SQL optimizer will start using the new index for SQL executions.

Examples​

Adding a bitmap index (default)​

ALTER TABLE trades ALTER COLUMN instrument ADD INDEX;

Adding a posting index​

ALTER TABLE trades ALTER COLUMN instrument ADD INDEX TYPE POSTING;

Adding a posting index with covering columns​

The INCLUDE clause stores additional column values in the index sidecar files, enabling covering queries that bypass column file reads:

ALTER TABLE trades
  ALTER COLUMN symbol ADD INDEX TYPE POSTING INCLUDE (price, quantity, timestamp);

After this, queries that only select columns from the INCLUDE list (plus the indexed symbol column) are served from the index sidecar:

-- This query reads from the index sidecar, not from column files
SELECT timestamp, price FROM trades WHERE symbol = 'AAPL';

See Posting index and covering index for supported column types and performance details.