Skip to main content
This group of DDL SQL statements allows database administrators to manage indexes. Database administrators can create and drop indexes. You can view information about indexes using the sys.indexes system catalog table. For information on other database components, see the pages on Databases, Schemas, Tables, and Views.

​
CREATE INDEX

CREATE INDEX creates a new secondary index. Indexes help optimize database queries when created on columns that are frequently referenced. For more information on how indexes operate, see Secondary Indexes. Creating an index does not trigger re-indexing of existing segments. Only segments generated after the CREATE INDEX is issued contain the new index. Indexes can be created on columns containing various different data types as long as the requirements are met. Please note that depending on the data type, the system can assign different index types by default if you decline to specify which index type to use. The name must be distinct from the name of any existing index on the table.
You can apply indexes regardless of whether they have GDC compression.
Syntax
SQL
CREATE INDEX [ IF NOT EXISTS ] index_name ON table (column_name) [ USING <index_type> ]

<index_type> ::=
   INVERTED | HASH | NGRAM [ (n_value) ] | SPATIAL | ZONE_MAP
ParameterTypeDescription
index_namestringAn identifier for the index to create.
The name must be distinct from the name of any existing index on the table.
tablestringThe name of the table for the index.
column_namestringThe name of the column for the index.
Identical indexes on the same column are not allowed. A column can only have multiple indexes if they are of different types or parameters.
n_valueintegerOptional.
When used with an NGRAM index, this numeric value specifies the character length of the substrings to be indexed.
If unspecified, this value defaults to 3.

​
Index Types (<index_type>)

Ocient supports four index types alongside the clustering index: INVERTED, HASH, NGRAM, and SPATIAL. An index notionally stores a mapping of a column value to the rows that contain that value, and the index type differentiates the format in which the column values are stored and accessed. Unless an index type is explicitly specified with a USING clause, the data type of a column determines a default index type that the system creates. For information on index type defaults, see Index Type Requirements and Defaults. For container data types (e.g., arrays and tuples), the index stores the internal elements of the container, and is used on predicates that target the internal values. However, a mapping of NULL column values is generally stored for both scalar and container data types, so the index can always be used for column IS NULL predicates.
Index TypePrimary Data TypesPrimary Usage Description
INVERTEDFixed-length numeric columns.Stores whole column value internally, meaning its storage size is approximately the same as the width of the data type.
Supports lookups using strict equality or range comparisons.
For details, see the INVERTED index section.
HASHVariable-length character columnsStores a hash of the indexed column value rather than the full value.
Primarily used for exact comparisons.
For details, see the HASH index section.
NGRAMVariable-length character columnsStores substrings equal in size to its n_value.
Storage requirements can greatly vary depending on column data size, width and cardinality.
Supports exact string comparison and filters including LIKE, NOT LIKE, SIMILAR TO and NOT SIMILAR TO.
For details, see the NGRAM index section.
SPATIALGeospatial columns (POINT, LINESTRING, POLYGON)Groups geographic objects for bounding-box filtering.
For details, see the SPATIAL index section.
ZONE_MAPFixed-length columns
(INT, BIGINT, SMALLINT, FLOAT, DOUBLE, BINARY, DECIMAL, HASH, CHAR, IP / IPV4, TIME, TIMESTAMP, DATE, UUID)		Supports range and exact matches on a whole segment level.
The system stores the minimum and maximum values for the column per-segment.
For further description and examples of the index types, see Secondary Indexes. Examples This example creates an index named new_idx on the address column of the table. Because address is a VARCHAR column, this index defaults to the HASH index type.
SQL
CREATE INDEX new_idx ON employees (address);
This example creates an index of type NGRAM on the address column. As the NGRAM has no specified n_value, it defaults to indexing substrings of three characters long.
SQL
CREATE INDEX ngram_address_idx ON employees (address) USING NGRAM;
This example creates an index on a component of the tuple_col column. As this column is of data type INT, the index defaults to using the INVERTED type.
SQL
CREATE INDEX tuple_index ON employees (tuple_col[1]);
This example creates an index on a component of the point_col column. As this column is of data type POINT, the index defaults to using the SPATIAL type.
SQL
CREATE INDEX spatial_index ON employees (point_col)

​
DROP INDEX

DROP INDEX drops a secondary index on a table. After an index is dropped, new segments that are generated do not contain the new index. However, no existing segments will be altered. This means that until a segment is rebuilt, you can still use the removed index internally, and the system does not reclaim the storage space the removed index occupied. Syntax
SQL
DROP INDEX [ IF EXISTS ] index_name ON table_name
ParameterTypeDescription
index_namestringAn identifier for the index to drop.
table_namestringThe name of the table with the index to drop.
Example This example drops the index named new_idx on the employees table.
SQL
DROP INDEX new_idx ON employees;
Core Elements of an Ocient System General SQL Syntax Database Password Security Settings System Catalog
Last modified on May 27, 2026