Indexes - Ocient Documentation

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

Parameter	Type	Description
`index_name`	string	An identifier for the index to create. The name must be distinct from the name of any existing index on the table.
`table`	string	The name of the table for the index.
`column_name`	string	The 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_value`	integer	Optional. 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 Type	Primary Data Types	Primary Usage Description
`INVERTED`	Fixed-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.
`HASH`	Variable-length character columns	Stores a hash of the indexed column value rather than the full value. Primarily used for exact comparisons. For details, see the HASH index section.
`NGRAM`	Variable-length character columns	Stores 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.
`SPATIAL`	Geospatial columns (`POINT`, `LINESTRING`, `POLYGON`)	Groups geographic objects for bounding-box filtering. For details, see the SPATIAL index section.
`ZONE_MAP`	Fixed-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

Parameter	Type	Description
`index_name`	string	An identifier for the index to drop.
`table_name`	string	The 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 Data Query Language (DQL) Statement Reference Database Password Security Settings System Catalog

​CREATE INDEX

​Index Types (<index_type>)

​DROP INDEX

​Related Links

CREATE INDEX

Index Types (`<index_type>`)

DROP INDEX

Related Links