openGauss每日一练第9天 | 普通表索引

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

su - omm
gsql -r

CREATE SCHEMA tpcds;
CREATE TABLE tpcds.products
(
pro_id INTEGER NOT NULL,
pro_name CHAR(30),
pro_code CHAR(10),
pro_loc CHAR(20)
);

CREATE UNIQUE INDEX ds_products_index1 ON tpcds.products(pro_id);
CREATE INDEX ds_products_index2 ON tpcds.products USING BTREE(pro_id);
CREATE INDEX ds_products_index3 ON tpcds.products (SUBSTR(pro_code, 1, 3));

–查看表信息
\d+ tpcds.products

–查看系统视图pg_indexes
SELECT * FROM pg_indexes WHERE tablename = 'products';

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

--设置索引1不可用
ALTER INDEX tpcds.ds_products_index1 UNUSABLE;

--修改索引2的表空间
CREATE TABLESPACE example0 RELATIVE LOCATION 'tablespace1/tablespace_0';
ALTER INDEX tpcds.ds_products_index2 SET TABLESPACE example0;

--重命名索引3
ALTER INDEX tpcds.ds_products_index3 RENAME TO ds_products_index4;

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

--重建索引2
REINDEX INDEX tpcds.ds_products_index2;

--重建products的所有索引
REINDEX TABLE tpcds.products;

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

–查看表信息
\d+ tpcds.products

–查看系统视图pg_indexes
SELECT * FROM pg_indexes WHERE tablename = 'products';

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

DROP INDEX tpcds.ds_products_index1;
DROP INDEX tpcds.ds_products_index2;
DROP INDEX tpcds.ds_products_index4;

DROP TABLESPACE example0;