openGauss SQL syntax - index

define index

⚫ An index is a structure that sorts the values ​​of one or more columns in a database table. An index can be used to quickly access specific information in a database table. The SQL statements involved are shown in the table below.

⚫ Category:

 The index is divided into single-column index and multi-column index according to the number of index columns;

 According to the index usage method, it can be divided into ordinary index, unique index, function index, and partition index.

create index

⚫ Function description

 Creates an index on the specified table. Indexes can be used to improve database query performance, but improper use will lead to performance degradation.

⚫ Note

 The user who executes this statement needs to have the CREATE INDEX and CREATE ANY INDEX system privileges. Ordinary users cannot create system user objects.

⚫ Syntax

CREATE [ UNIQUE ] INDEX [ CONCURRENTLY ] [ [schema_name.]index_name ] ON table_name

[ USING method ]

({ { column_name | ( expression ) } [ COLLATE collation ] [ opclass ] [ ASC | DESC ] [ NULLS { FIRST | LAST } ] }[, ...] )

[ WITH ( {storage_parameter = value} [, ... ] ) ]

[ TABLESPACE tablespace_name ]

[ WHERE predicate ];

⚫ Create an index on the ordinary table bank_card.

--Create a normal table bank_card.

postgres=# CREATE TABLE bank_card( b_number NCHAR(30) PRIMARY KEY, b_type NCHAR(20),b_c_id INT NOT NULL);

--Create index idx_bank_card.

postgres=# CREATE INDEX idx_bank_card ON bank_card(b_number ASC, b_type);

⚫ Create a partitioned index on the partitioned table education.

--Create a partition table education.

postgres=# CREATE TABLE education(staff_id INTEGER NOT NULL, higest_degree CHAR(8), graduate_school VARCHAR(64), graduate_date DATETIME, education_note VARCHAR(70))

PARTITION BY LIST(higest_degree) ( PARTITION doctor VALUES ('PhD'), PARTITION master VALUES ('Master'), PARTITION undergraduate VALUES ('Bachelor') );

--Create a partitioned index and specify the name of the index partition.

postgres=# CREATE INDEX idx_education ON education(staff_id ASC, higest_degree) LOCAL (PARTITION doctor, PARTITION master, PARTITION undergraduate);

Modify index properties

⚫ Syntax format:

ALTER INDEX [ schema_name. ]index_name { rebuild_clauses | rename_clauses | modify_clauses }

⚫ Description:

 rebuild_clauses

◼ REBUILD [ PARTITION index_partition_name ], rebuild the table index or index partition.

 rename_clauses

◼ RENAME TO [schema_name.] index_name_new, the name of the index to be renamed.

 modify_clauses

◼ SET TABLESPACE tablespace_name, modify the space to which the table index belongs.

⚫ Example:

 Rebuild the index.

postgres=# ALTER INDEX idx_bank_card REBUILD;

 Rename an existing index.

postgres=# ALTER INDEX idx_bank_card RENAME TO idx_bank_card_temp;

 Setting index is not available.

postgres=# ALTER INDEX idx_bank_card UNUSABLE;

drop index

⚫ Syntax

DROP INDEX [ CONCURRENTLY ] [ IF EXISTS ] index_name [, ...] [ CASCADE | RESTRICT ];

⚫ Parameter description

 IF EXISTS

◼ If the index does not exist, return success directly.

 index_name

◼ The name of the index to be deleted.

 CASCADE | RESTRICTON

◼ CASCADE: Indicates that cascading deletion of objects that depend on this index is allowed.

◼ RESTRICT (default): Indicates that there are objects that depend on this index, and the index cannot be deleted.

⚫ Example

postgres=# DROP INDEX idx_bank_card;