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

openGauss每日一练第9天课后作业

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

create schema example;

create table example.products (

prod_id int,

prod_name char(80),

prod_desc varchar(400),

prod_category_id int,

prod_price numeric(10,2)

);

create unique index example.products_index1 on example.products(prod_id);

create index example.products_index2 on example.products using btree(prod_category_id);

create index example.products_index3 on example.products(substr(prod_name,1,10));

\d+ example.products;

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

alter index example.products_index1 unusable;

create tablespace example relative location 'tablespace/example';

alter index example.products_index2 set tablespace example;

alter index example.products_index3 rename to products_index4;

\d+ example.products;

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

alter index example.products_index2 rebuild;

reindex table example.products;

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

\d+ example.products;

select * from pg_indexes where tablename='products';

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

drop index example.products_index1;

drop index example.products_index2;

drop index example.products_index4;

drop table example.products;

drop tablespace example;