openGauss每日一练第9天｜openGauss普通表的索引操作

坚持学习openGauss数据库，坚持每天打卡。第九天学习openGauss普通表的索引的操作。

连接openGauss

root@modb:~# su - omm
omm@modb:~$ gsql -r
gsql ((openGauss 2.0.0 build 78689da9) compiled at 2021-03-31 21:03:52 commit 0 last mr  )
Non-SSL connection (SSL connection is recommended when requiring high-security)
Type "help" for help.
omm=#
复制

1.创建表products, 分别为表创建一个unique索引1，指定b-tree索引2和表达式索引3

omm=# create schema schema1;
CREATE SCHEMA
omm=# CREATE TABLE schema1.products
(
SM_SHIP_MODE_SK INTEGER NOT NULL,
SM_SHIP_MODE_ID CHAR(16) NOT NULL,
SM_TYPE CHAR(30),
SM_CODE CHAR(10),
SM_CARRIER CHAR(20),
SM_CONTRACT CHAR(20)
);
CREATE TABLE
–SM_SHIP_MODE_SK字段上创建普通的唯一索引
omm=# CREATE UNIQUE INDEX idx_products_index1 ON schema1.products(SM_SHIP_MODE_SK);
CREATE INDEX
–SM_SHIP_MODE_SK字段上创建指定B-tree索引
omm=# CREATE INDEX idx_products_index2 ON schema1.products USING btree(SM_SHIP_MODE_SK);
CREATE INDEX
–SM_CODE字段上创建表达式索引
omm=# CREATE UNIQUE INDEX idx_products_index3 ON schema1.products(SM_SHIP_MODE_SK) WHERE SM_SHIP_MODE_SK>10;
CREATE INDEX
复制

2.设置索引1不可用，修改索引2的表空间，重命名索引3

omm=# ALTER INDEX schema1.idx_products_index1 UNUSABLE;
ALTER INDEX
omm=# CREATE TABLESPACE example0 RELATIVE LOCATION 'tablespace1/tablespace_0';
CREATE TABLESPACE
omm=# alter index schema1.idx_products_index2 set tablespace example0;
ALTER INDEX
omm=# ALTER INDEX schema1.idx_products_index3 RENAME TO idx_products_index33;
ALTER INDEX
复制

3.重建索引2和products的所有索引

omm=# ALTER INDEX schema1.idx_products_index2 REBUILD;
REINDEX
omm=# REINDEX INDEX schema1.idx_products_index2;
REINDEX
omm=# reindex table schema1.products;
REINDEX
复制

4.使用\d+和系统视图pg_indexes查看索引信息

omm=# \d+ schema1.products;
                              Table "schema1.products"
     Column      |     Type      | Modifiers | Storage  | Stats target | Description 
-----------------+---------------+-----------+----------+--------------+-------------
 sm_ship_mode_sk | integer       | not null  | plain    |              | 
 sm_ship_mode_id | character(16) | not null  | extended |              | 
 sm_type         | character(30) |           | extended |              | 
 sm_code         | character(10) |           | extended |              | 
 sm_carrier      | character(20) |           | extended |              | 
 sm_contract     | character(20) |           | extended |              | 
Indexes:
    "idx_products_index1" UNIQUE, btree (sm_ship_mode_sk) TABLESPACE pg_default
    "idx_products_index33" UNIQUE, btree (sm_ship_mode_sk) TABLESPACE pg_default WHERE sm_ship_mode_sk > 10
    "idx_products_index2" btree (sm_ship_mode_sk) TABLESPACE example0, tablespace "example0"
Has OIDs: no
Options: orientation=row, compression=no

omm=# select * from pg_indexes where tablename = 'products';
 schemaname | tablename |      indexname       | tablespace |                                                                 
  indexdef                                                                    
------------+-----------+----------------------+------------+-----------------------------------------------------------------
------------------------------------------------------------------------------
 schema1    | products  | idx_products_index1  |            | CREATE UNIQUE INDEX idx_products_index1 ON schema1.products USIN
G btree (sm_ship_mode_sk) TABLESPACE pg_default
 schema1    | products  | idx_products_index2  | example0   | CREATE INDEX idx_products_index2 ON schema1.products USING btree
 (sm_ship_mode_sk) TABLESPACE example0
 schema1    | products  | idx_products_index33 |            | CREATE UNIQUE INDEX idx_products_index33 ON schema1.products USI
NG btree (sm_ship_mode_sk) TABLESPACE pg_default WHERE (sm_ship_mode_sk > 10)
(3 rows)
复制

5.删除索引、表和表空间

omm=# DROP INDEX schema1.idx_products_index1;
DROP INDEX
omm=# DROP INDEX schema1.idx_products_index2;
DROP INDEX
omm=# DROP INDEX schema1.idx_products_index33;
DROP INDEX
omm=# drop table schema1.products;
DROP TABLE
omm=# drop TABLESPACE example0;
DROP TABLESPACE
复制

通过学习和作业，更加深入的了解了openGauss普通表上索引的相关操作。了解了索引是对数据库表中一列或多列的值进行排序的一种结构，使用索引可快速访问数据库表中的特定信息。