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

> 准备工作

🍩连接openGauss

su - omm
gsql -r
复制

> 作业打卡

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

CREATE TABLE products
(product_id integer,product_name char(30),category char(20));
comment on column products.product_id is '产品编号';
comment on column products.product_name is '产品名';
comment on column products.category is '种类';
CREATE UNIQUE INDEX products_index1 ON products(product_id);
CREATE INDEX products_index2 ON products USING btree(product_id);
CREATE INDEX products_index3 ON products(SUBSTR(product_name,1 ,4));
复制

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

ALTER INDEX products_index1 UNUSABLE;
CREATE TABLESPACE example0 RELATIVE LOCATION 'tablespace1/tablespace_0';
ALTER INDEX products_index2 set tablespace example0;
\d+ products_index2;
ALTER INDEX products_index3 RENAME TO products_index33;
复制

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

ALTER INDEX products_index1 REBUILD;
REINDEX INDEX products_index2;
reindex table products;\di
复制

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

\d+
select * from pg_indexes where tablename = 'products';
复制

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

5.删除索引、表和表空间
\db
\di
\d public.products;
DROP INDEX products_index1; 
DROP INDEX products_index2; 
DROP INDEX products_index33; 
DROP TABLE products;
DROP TABLESPACE example0;
复制