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

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

create schema tpcds;
CREATE TABLE tpcds.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 UNIQUE INDEX sk_products_index1 ON tpcds.products(SM_SHIP_MODE_SK);

CREATE INDEX sk_products_index2 ON tpcds.products USING btree(SM_SHIP_MODE_SK);

CREATE INDEX sm_code_products_index3 ON tpcds.products(SUBSTR(SM_CODE,1 ,4));

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

ALTER INDEX tpcds.sk_products_index1 UNUSABLE;

CREATE TABLESPACE example0 RELATIVE LOCATION 'tablespace1/tablespace_0';
alter index tpcds.sk_products_index2 set tablespace example0;
\d+ tpcds.products;

ALTER INDEX tpcds.sm_code_products_index3 RENAME TO sm_code_products_index5;

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

ALTER INDEX tpcds.sk_products_index2 REBUILD;

REINDEX INDEX tpcds.sk_products_index2;


reindex table tpcds.products;

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

\d+ tpcds.products

select * from pg_indexes where tablename = 'products';

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

drop index tpcds.sk_products_index2;

drop index tpcds.sk_products_index1;

drop index tpcds.sm_code_products_index5;

drop table  tpcds.products;


drop tablespace example0