课程作业

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 zouyang(
omm(# id int,
omm(# name varchar(20) not null,
omm(# age int default 20
omm(# );
CREATE TABLE
--在表中创建索引 ^
omm=# create index inx_name on zouyang(name);
CREATE INDEX
--查看索引
omm=# \di
List of relations
Schema | Name | Type | Owner | Table | Storage
--------+----------+-------+-------+---------+---------
public | inx_name | index | omm | zouyang |
(1 row)

2.通过hint使用索引

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

select  * from  zouyang;

\di 

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

Bitmap Heap Scan on zouyang (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索引

\di

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

4.重建索引

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

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

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

或者

6.删除索引

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