Working with Databases-Creating and Managing Indexes
Tags: opengauss, database
Published: 2023-03-14 04:28:26

**Background**
Indexes accelerate data access but increase the processing time of insertion, update, and deletion operations. Therefore, before creating an index, consider whether it is necessary and determine the columns where the index will be created. You can determine whether to add an index for a table by analyzing the service processing and data use of applications, as well as columns that are frequently used as search criteria or need to be collated.

Indexes are created based on columns in database tables. Therefore, you must correctly identify which columns require indexes. You are advised to create indexes for any of the following columns:

**·** Columns that are often searched and queried. This speeds up searches.

**·** Columns that function as primary keys. This enforces the uniqueness of the columns and the data collation structures in organized tables.

**·** Columns that are often searched by range. The index helps collate data, and therefore the specified ranges are contiguous.

**·** Columns that often need to be collated. The index helps collate data, reducing the time for a collation query.

**·** Columns where the WHERE clause is executed frequently. This speeds up condition judgment.

**·** Columns that often appear after the keywords ORDER BY, GROUP BY, and DISTINCT.

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

**Procedure**
For details about how to create a partitioned table, see Creating and Managing Partitioned Tables.

**·** Creating an index

**·** Create the LOCAL index **tpcds_web_returns_p2_index1** without specifying the partition name for a partitioned table.

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

If the following information is displayed, the table has been created:

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

**·** Create the LOCAL index tpcds_web_returns_p2_index2 with the partition name specified for a partitioned table.

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

If the following information is displayed, the table has been created:

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

**·** Create the GLOBAL index **tpcds_web_returns_p2_global_index** for a partitioned table.

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

**·** Modifying the tablespace of an index partition

**·** Change the tablespace of index partition **web_returns_p2_P2_index** to example1.

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

If the following information is displayed, the tablespace of the index partition has been modified:

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

**·** Change the tablespace of index partition **web_returns_p2_P3_index** to example2.

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

If the following information is displayed, the tablespace of the index partition has been modified:

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

**·** Renaming an index partition

Rename the name of index partition web_returns_p2_P8_index to web_returns_p2_P8_index_new.

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

If the following information is displayed, the index partition has been renamed:

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

**·** Querying indexes

**·** Run the following command to query all indexes defined by the system and users:

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

**·** Run the following command to query information about a specified index:

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

**·** Dropping an index

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

If the following information is displayed, the tables have been deleted:

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

openGauss supports four methods for creating indexes. For details, see Table 1.

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

Table 1 Indexing methods

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

**·** Create an ordinary table.

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

**·** Create an ordinary index.

For the **tpcds.customer_address_bak** table, you need to perform the following operations frequently:

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

Generally, the database system needs to scan the **tpcds.customer_address_bak** table row by row to find all matched tuples. If the size of the **tpcds.customer_address_bak** table is large but only a few (possibly zero or one) of the **WHERE** conditions are met, the performance of this sequential scan is low. If the database system uses an index to maintain the **ca_address_sk** attribute, the database system only needs to search a few tree layers for the matched tuples. This greatly improves data query performance. Furthermore, indexes can improve the update and deletion operation performance in the database.

Run the following command to create an index:

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

**·** Create a multi-column index.

Assume you need to frequently query records with **ca_address_sk** being 5050 and **ca_street_number** smaller than 1000 in the **tpcds.customer_address_bak** table. Run the following commands:

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

Run the following command to define a composite index on **ca_address_sk** and **ca_street_number** columns:

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

**·** Create a partial index.

If you only want to find records whose **ca_address_sk** is 5050, you can create a partial index to facilitate your query.

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

**·** Create an expression index.

Assume that you need to frequently query records with **ca_street_number** smaller than 1000, run the following command:

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

The following expression index can be created for this query task:

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

· Delete the **tpcds.customer_address_bak** table.

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