> ## Documentation Index
> Fetch the complete documentation index at: https://docs.ocient.com/llms.txt
> Use this file to discover all available pages before exploring further.

# Indexes

export const Ocient = "Ocient®";

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](/databases), [Schemas](/schemas), [Tables](/tables), and [Views](/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 {Ocient} indexes operate, see [Secondary Indexes](/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.

<Info>
  You can apply indexes regardless of whether they have GDC compression.
</Info>

**Syntax**

```sql SQL theme={null}
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. <br />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.<br />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. <br />When used with an NGRAM index, this numeric value specifies the character length of the substrings to be indexed. <br />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](/secondary-indexes#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. <br />Supports lookups using strict equality or range comparisons.<br />For details, see the [INVERTED index](/secondary-indexes#inverted-index-type) section.                                                                       |
| `HASH`         | Variable-length character columns                                                                                                     | Stores a hash of the indexed column value rather than the full value. <br />Primarily used for exact comparisons.<br />For details, see the [HASH index](/secondary-indexes#hash-index-type) section.                                                                                                                                                        |
| `NGRAM`        | Variable-length character columns                                                                                                     | Stores substrings equal in size to its `n_value`. <br />Storage requirements can greatly vary depending on column data size, width and cardinality.<br />Supports exact string comparison and filters including `LIKE`, `NOT LIKE`, `SIMILAR TO` and `NOT SIMILAR TO`.<br />For details, see the [NGRAM index](/secondary-indexes#ngram-index-type) section. |
| `SPATIAL`      | Geospatial columns (`POINT`, `LINESTRING`, `POLYGON`)                                                                                 | Groups geographic objects for bounding-box filtering. <br />For details, see the [SPATIAL index](/secondary-indexes#spatial-index-type) section.                                                                                                                                                                                                             |
| `ZONE_MAP`     | Fixed-length columns<br />(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.<br />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](/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 SQL theme={null}
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 SQL theme={null}
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 SQL theme={null}
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 SQL theme={null}
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 SQL theme={null}
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 SQL theme={null}
DROP INDEX new_idx ON employees;
```

## Related Links

[Core Elements of an Ocient System](/core-elements-of-an-ocient-system)

[General SQL Syntax](/general-sql-syntax)

[Database Password Security Settings](/database-password-security-settings)

[System Catalog](/system-catalog)