Indexes
Tags: opengauss
Published: 2023-03-06 10:32:43

An index is a pointer to data in a table. The index in a database is very similar to the index directory in a book.

Indexes are primarily used to enhance database performance (though inappropriate use can result in database performance deterioration). You are advised to create indexes on:

**·** Columns that are often queried
**·** Join conditions. For a query on joined columns, you are advised to create a composite index on the columns. For example, select * from t1 join t2 on t1. a=t2. a and t1. b=t2.b. You can create a composite index on the a and b columns of table t1.
**·** Columns having filter criteria (especially scope criteria) of a **WHERE** clause
**·** Columns that are frequently used after **ORDER BY**, **GROUP BY**, and **DISTINCT**

**Syntax**

**·** Single-column index

A single-column index is created based on only one column in a table.

![Image description](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/emn2hhlhs6hmj8vaie3c.png)

**·** Composite index

A composite index is created based on multiple columns in a table.

![Image description](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/de3um4hogljceulh8q8k.png)

**·** Unique index

Duplicate values cannot be inserted into a column with a unique index.

![Image description](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/mv6cvmt2y511jv0ft3ka.png)

**·** Local index

A Local index is created on a subset of a table. The subset is defined by a condition expression.

![Image description](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/8e145deuhbbboy1thkw3.png)

**·** Partial index

A partial index contains entries for only a portion of a table, usually a portion that is more useful for indexing than the rest of the table.

![Image description](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/y41vshvv7ik3nztu703w.png)

**·** Index deletion

![Image description](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/4r0szcybwxsfwu674196.png)

**Parameter Description**

**· UNIQUE**

Creates a unique index. In this way, the system checks whether new values are unique in the index column. Attempts to insert or update data which would result in duplicate entries will generate an error.

Currently, only the B-tree index supports unique indexes.

**· schema_name**

Specifies the schema name.

Value range: an existing schema name

**· index_name**

Specifies the name of the index to be created. The schema of the index is the same as that of the table.

Value range: a string.

**· table_name**

Specifies the name of the table for which an index is to be created (optionally schema-qualified).

Value range: an existing table name

**· column_name**

Specifies the name of the column for which an index is to be created.

Multiple columns can be specified if the index method supports multi-column indexes. A global index supports a maximum of 31 columns, and other indexes support a maximum of 32 columns.

**· expression**

Specifies an expression index constraint based on one or more columns of the table. It must be written in parentheses. However, the parentheses can be omitted if the expression has the form of a function call.

The expression index can be used to obtain fast access to data based on some transformation of the basic data. For example, an index computed on **upper(col)** would allow the **WHERE upper(col) = 'JIM'** clause to use an index.

If an expression contains the **IS NULL** clause, the index for this expression is invalid. In this case, you are advised to create a partial index.

**· WHERE predicate**

Creates a partial index. A partial index contains entries for only a portion of a table, usually a portion that is more useful for indexing than the rest of the table. For example, if you have a table that contains both billed and unbilled orders where the unbilled orders take up a small portion of the total table and yet that is an often used portion, you can improve performance by creating an index on just that portion. In addition, the **WHERE** clause with a UNIQUE constraint can be used to enforce uniqueness over a subset of a table.

Value range: The predicate expression can only refer to columns of the underlying table, but it can use all columns, not just the ones being indexed. Currently, subqueries and aggregate expressions are forbidden in the **WHERE** clause.

**Examples**

Create the tpcds.ship_mode_t1 table.

![Image description](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/cr5q860le3p07ss9d8ey.png)

Create a single-column index on the SM_SHIP_MODE_ID column in the tpcds.ship_mode_t1 table.

![Image description](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/ynkk4jom4393k4vmn8qk.png)

Create a common unique index on the SM_SHIP_MODE_SK column in the tpcds.ship_mode_t1 table.

![Image description](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/qf0leoflj4mowlftvaxa.png)

Create an expression index on the SM_CODE column in the tpcds.ship_mode_t1 table.

![Image description](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/o3lvnkby5bcodru47qnc.png)

Create a partial index on the SM_SHIP_MODE_SK column where SM_SHIP_MODE_SK is greater than 10 in the tpcds.ship_mode_t1 table.

![Image description](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/5sa8m8sml6fdh60kvk5x.png)

Delete the created index.

![Image description](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/4vl1s54b1vwstn5lwnfy.png)