openGauss SQL syntax - index
Published: 2022-10-10 07:56:41

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;