openGauss每日一练第9天 | 学习心得体会

学习openGauss普通表索引

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

omm=# CREATE TABLE tpcds.products
omm-# (
omm(# SM_SHIP_MODE_SK INTEGER NOT NULL,
omm(# SM_SHIP_MODE_ID CHAR(16) NOT NULL,
omm(# SM_TYPE CHAR(30),
omm(# SM_CODE CHAR(10),
omm(# SM_CARRIER CHAR(20),
omm(# SM_CONTRACT CHAR(20)
omm(# );
CREATE TABLE
omm=# 
omm=# 
omm=# CREATE UNIQUE INDEX ds_products_index1 ON tpcds.products(SM_SHIP_MODE_SK);
CREATE INDEX
omm=# CREATE INDEX ds_products_index2 ON tpcds.products USING btree(SM_SHIP_MODE_SK);
CREATE INDEX
omm=# CREATE INDEX ds_products_index3 ON tpcds.products(SUBSTR(SM_CODE,1 ,4));
CREATE INDEX
omm=# \d+ tpcds.products
                               Table "tpcds.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:
    "ds_products_index1" UNIQUE, btree (sm_ship_mode_sk) TABLESPACE pg_default
    "ds_products_index2" btree (sm_ship_mode_sk) TABLESPACE pg_default
    "ds_products_index3" btree (substr(sm_code::text, 1, 4)) TABLESPACE pg_default
Has OIDs: no
Options: orientation=row, compression=no

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

omm=# ALTER INDEX tpcds.ds_products_index1 UNUSABLE;
ALTER INDEX
omm=# CREATE TABLESPACE example0 RELATIVE LOCATION 'tablespace1/tablespace_0';
ERROR:  tablespace "example0" already exists
omm=# alter index tpcds.ds_products_index2 set tablespace example0;
ALTER INDEX
omm=# ALTER INDEX tpcds.ds_products_index3 RENAME TO ds_products_index5;
ALTER INDEX
omm=# \d+ tpcds.products
                               Table "tpcds.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:
    "ds_products_index1" UNIQUE, btree (sm_ship_mode_sk) TABLESPACE pg_default
    "ds_products_index2" btree (sm_ship_mode_sk) TABLESPACE example0, tablespace "example0"
    "ds_products_index5" btree (substr(sm_code::text, 1, 4)) TABLESPACE pg_default
Has OIDs: no
Options: orientation=row, compression=no

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

omm=# ALTER INDEX tpcds.ds_products_index2 REBUILD;
REINDEX
omm=# reindex table tpcds.products;
REINDEX
omm=# \d+ tpcds.products
                               Table "tpcds.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:
    "ds_products_index1" UNIQUE, btree (sm_ship_mode_sk) TABLESPACE pg_default
    "ds_products_index2" btree (sm_ship_mode_sk) TABLESPACE example0, tablespace "example0"
    "ds_products_index5" btree (substr(sm_code::text, 1, 4)) TABLESPACE pg_default
Has OIDs: no
Options: orientation=row, compression=no

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

omm=# \d+ tpcds.products
                               Table "tpcds.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:
    "ds_products_index1" UNIQUE, btree (sm_ship_mode_sk) TABLESPACE pg_default
    "ds_products_index2" btree (sm_ship_mode_sk) TABLESPACE example0, tablespace "example0"
    "ds_products_index5" btree (substr(sm_code::text, 1, 4)) TABLESPACE pg_default
Has OIDs: no
Options: orientation=row, compression=no

omm=# select * from pg_indexes where tablename = 'products';
 schemaname | tablename |     indexname      | tablespace |                                                      indexdef     
                                                  
------------+-----------+--------------------+------------+-------------------------------------------------------------------
--------------------------------------------------
 tpcds      | products  | ds_products_index1 |            | CREATE UNIQUE INDEX ds_products_index1 ON tpcds.products USING btr
ee (sm_ship_mode_sk) TABLESPACE pg_default
 tpcds      | products  | ds_products_index2 | example0   | CREATE INDEX ds_products_index2 ON tpcds.products USING btree (sm_
ship_mode_sk) TABLESPACE example0
 tpcds      | products  | ds_products_index5 |            | CREATE INDEX ds_products_index5 ON tpcds.products USING btree (sub
str((sm_code)::text, 1, 4)) TABLESPACE pg_default
(3 rows)

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

omm=# DROP INDEX tpcds.ds_products_index1;
DROP INDEX
omm=# DROP INDEX tpcds.ds_products_index2;
DROP INDEX
omm=# DROP INDEX tpcds.ds_products_index5;
DROP INDEX
omm=# drop TABLE tpcds.products;
DROP TABLE