课程作业

1.创建表，在表中创建索引

root@modb:~# su - omm
omm@modb:~$ gsql -r
gsql ((openGauss 3.0.0 build 02c14696) compiled at 2022-04-01 18:12:00 commit 0 last mr  )
Non-SSL connection (SSL connection is recommended when requiring high-security)
Type "help" for help.
--创建表
omm=# create table yangkai(
omm(# id int,
omm(# name varchar(20) not null,
omm(# age int default 20
omm(# );
CREATE TABLE
--在表中创建索引                      ^
omm=# create index inx_name on yangkai(name);
CREATE INDEX
--查看索引
omm=# \di
               List of relations
Schema |   Name   | Type  | Owner |  Table  | Storage 
--------+----------+-------+-------+---------+---------
public | inx_name | index | omm   | yangkai | 
(1 row)

2.通过hint使用索引

--插入数据
omm=# insert into yangkai values (1,'yangkai1');
INSERT 0 1
omm=# insert into yangkai values (2,'zhangsan',33);
INSERT 0 1
omm=# insert into yangkai values (3,'lisi');
INSERT 0 1

--通过hint强制使用索引
omm=#  EXPLAIN SELECT /*+ indexscan(yangkai inx_name) */ name from yangkai
omm-# where name='zhangsan';
WARNING:  unused hint: IndexScan(yangkai inx_name)
                          QUERY PLAN                               
-----------------------------------------------------------------------
(4 rows)

 Bitmap Heap Scan on yangkai  (cost=4.28..12.74 rows=4 width=58)
  Recheck Cond: ((name)::text = 'zhangsan'::text)
  ->  Bitmap Index Scan on inx_name  (cost=0.00..4.28 rows=4 width=0)
     Index Cond: ((name)::text = 'zhangsan'::text)

3.rename索引

omm=# alter index inx_name RENAME TO idx_yangkai_name;
ALTER INDEX
omm=# \di
                   List of relations
Schema |       Name       | Type  | Owner |  Table  | Storage 
--------+------------------+-------+-------+---------+---------
public | idx_yangkai_name | index | omm   | yangkai | 
(1 row)

4.重建索引

--第一种方式
omm=# alter index idx_yangkai_name rebuild;
REINDEX
omm=# \di
                   List of relations
Schema |       Name       | Type  | Owner |  Table  | Storage 
--------+------------------+-------+-------+---------+---------
public | idx_yangkai_name | index | omm   | yangkai | 
(1 row)
--第二种方式
omm=# reindex index idx_yangkai_name;
REINDEX
--第三种方式
omm=# reindex table yangkai;
REINDEX

5.移动索引到其他表空间

--创建表空间yangkai_ts
omm=# CREATE TABLESPACE yangkai_ts RELATIVE LOCATION 'tablespace/yangkai_ts1';
CREATE TABLESPACE
--移动索引到yangkai_ts表空间
omm=# alter index idx_yangkai_name set tablespace yangkai_ts;
ALTER INDEX
--查看索引对应的表空间
omm=# select * from pg_indexes where tablename = 'yangkai';
schemaname | tablename |    indexname     | tablespace |                       
          indexdef                                      
------------+-----------+------------------+------------+-----------------------
------------------------------------------------------------
 public     | yangkai   | idx_yangkai_name | yangkai_ts | CREATE INDEX idx_yangk
ai_name ON yangkai USING btree (name) TABLESPACE yangkai_ts
(1 row)

或者

6.删除索引

omm=# drop index idx_yangkai_name;
DROP INDEX
omm=# \di;
No relations found.
omm=#