openGauss每日一练 第9天 索引

学习感受

继续学习，坚持就是胜利！
渐入佳境，有些上瘾的感觉，学习起来一点也不枯燥！！

学习目标

学习openGauss普通表索引

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

课后作业

连接openGauss

#第一次进入等待15秒
#数据库启动中…

su - omm
gsql -r

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

create schema tpcds;
CREATE TABLE tpcds.products 
( product_id             integer,   
  product_name           char(30),    
  category               char(20) 
);

CREATE UNIQUE INDEX product_idx1 ON tpcds.products(product_id);
CREATE INDEX product_idx2 ON tpcds.products USING btree(product_name);
CREATE INDEX product_idx3 ON tpcds.products(SUBSTR(category,1 ,4));

执行结果如下：

omm=# create schema tpcds;
ERROR:  schema "tpcds" already exists
omm=# CREATE TABLE tpcds.products 
omm-# ( product_id             integer,   
omm(#   product_name           char(30),    
omm(#   category               char(20) 
omm(# );
CREATE TABLE
omm=# 
omm=# CREATE UNIQUE INDEX product_idx1 ON tpcds.products(product_id);
CREATE INDEX
omm=# CREATE INDEX product_idx2 ON tpcds.products USING btree(product_name);
CREATE INDEX
omm=# CREATE INDEX product_idx3 ON tpcds.products(SUBSTR(category,1 ,4));
CREATE INDEX

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

ALTER INDEX tpcds.product_idx1 UNUSABLE;
CREATE TABLESPACE example0 RELATIVE LOCATION 'tablespace1/tablespace_0';
alter index tpcds.product_idx2 set tablespace example0;
ALTER INDEX tpcds.product_idx3 RENAME TO product_idx_3;


\d+ tpcds.PRODUCTS;

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

ALTER INDEX tpcds.product_idx2 REBUILD;
REINDEX INDEX tpcds.product_idx2;
reindex table tpcds.products;

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

\d+ tpcds.products
select * from pg_indexes where tablename = 'products';

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

DROP INDEX tpcds.product_idx1;
DROP INDEX tpcds.product_idx2;
DROP INDEX tpcds.product_idx_3;
\d+ tpcds.products

drop table tpcds.products;
drop tablespace tpcds;

执行演示代码如下：

omm=# ALTER INDEX tpcds.product_idx1 UNUSABLE;
ALTER INDEX
omm=# CREATE TABLESPACE example0 RELATIVE LOCATION 'tablespace1/tablespace_0';
ERROR:  tablespace "example0" already exists
omm=# alter index tpcds.product_idx2 set tablespace example0;
ALTER INDEX
omm=# ALTER INDEX tpcds.product_idx3 RENAME TO product_idx_3;
ALTER INDEX

omm=# ALTER INDEX tpcds.product_idx2 REBUILD;
REINDEX INDEX tpcds.product_idx2;
REINDEX
omm=# REINDEX
omm=# reindex table tpcds.products;
REINDEX
omm=# 
omm=# 
omm=# 
omm=# \d+ tpcds.products
                              Table "tpcds.products"
    Column    |     Type      | Modifiers | Storage  | Stats target | Description 
--------------+---------------+-----------+----------+--------------+-------------
 product_id   | integer       |           | plain    |              | 
 product_name | character(30) |           | extended |              | 
 category     | character(20) |           | extended |              | 
Indexes:
    "product_idx1" UNIQUE, btree (product_id) TABLESPACE pg_default
    "product_idx2" btree (product_name) TABLESPACE example0, tablespace "example0"
    "product_idx_3" btree (substr(category::text, 1, 4)) TABLESPACE pg_default
Has OIDs: no
Options: orientation=row, compression=no

omm=# select * from pg_indexes where tablename = 'products';
 schemaname | tablename |   indexname   | tablespace |                                                    indexdef                    
                                 
------------+-----------+---------------+------------+--------------------------------------------------------------------------------
---------------------------------
 tpcds      | products  | product_idx1  |            | CREATE UNIQUE INDEX product_idx1 ON tpcds.products USING btree (product_id) TAB
LESPACE pg_default
 tpcds      | products  | product_idx2  | example0   | CREATE INDEX product_idx2 ON tpcds.products USING btree (product_name) TABLESPA
CE example0
 tpcds      | products  | product_idx_3 |            | CREATE INDEX product_idx_3 ON tpcds.products USING btree (substr((category)::te
xt, 1, 4)) TABLESPACE pg_default
(3 rows)

omm=# 
omm=# 
omm=# DROP INDEX tpcds.product_idx1;
DROP INDEX
omm=# DROP INDEX tpcds.product_idx2;
DROP INDEX
omm=# DROP INDEX tpcds.product_idx_3;
DROP INDEX
omm=# 
omm=# \d+ tpcds.products
                              Table "tpcds.products"
    Column    |     Type      | Modifiers | Storage  | Stats target | Description 
--------------+---------------+-----------+----------+--------------+-------------
 product_id   | integer       |           | plain    |              | 
 product_name | character(30) |           | extended |              | 
 category     | character(20) |           | extended |              | 
Has OIDs: no
Options: orientation=row, compression=no