openGauss每日一练 | 9.1 学习openGauss普通表索引

索引是对数据库表中一列或多列的值进行排序的一种结构，使用索引可快速访问数据库表中的特定信息

课程作业

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

create schema tpcds;
create table tpcds.products(id int not null, name varchar(30) not null, price int, type varchar(30), region varchar(30));

create unique index ds_products_index1 on tpcds.products(id);
create index ds_products_index2 on tpcds.products(name);
create index ds_products_index3 on tpcds.products(substr(price, 1, 4));

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

omm=# alter index tpcds.ds_products_index1 unusable;
ALTER INDEX
omm=# create tablespace example0 relative location 'tablespace1/tablespace_0';
CREATE TABLESPACE
omm=# alter index tpcds.ds_products_index2 set tablespace example0;
ALTER INDEX
omm=# \d+ tpcds.products;
                               Table "tpcds.products"
 Column |         Type          | Modifiers | Storage  | Stats target | Description 
--------+-----------------------+-----------+----------+--------------+-------------
 id     | integer               | not null  | plain    |              | 
 name   | character varying(30) | not null  | extended |              | 
 price  | integer               |           | plain    |              | 
 type   | character varying(30) |           | extended |              | 
 region | character varying(30) |           | extended |              | 
Indexes:
    "ds_products_index1" UNIQUE, btree (id) TABLESPACE pg_default
    "ds_products_index2" btree (name) TABLESPACE example0, tablespace "example0"
    "ds_products_index3" btree (substr(price::text, 1, 4)) TABLESPACE pg_default
Has OIDs: no
Options: orientation=row, compression=no

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

alter index tpcds.ds_products_index2 rebuild;
reindex table tpcds.products;

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

omm=# \d+ tpcds.products;
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 btree (id) TABLESPACE pg_default
 tpcds      | products  | ds_products_index2 | example0   | CREATE INDEX ds_products_index2 ON tpcds.products USING btree (name) TABLESPACE example0
 tpcds      | products  | ds_products_index3 |            | CREATE INDEX ds_products_index3 ON tpcds.products USING btree (substr((price)::text, 1, 4)) TABLESP
ACE pg_default
(3 rows)

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

drop index tpcds.ds_products_index1;
drop index tpcds.ds_products_index2;
drop index tpcds.ds_products_index3;

drop table tpcds.products;

drop tablespace example0;

openGauss SQL学习参考资料