opengauss学习的第9天

#opengauss的第九天打卡
创建表products, 分别为表创建一个unique索引1，指定b-tree索引2和表达式索引3
omm=# create table products(id int unique,num int);
NOTICE: CREATE TABLE / UNIQUE will create implicit index “products_id_key” for table “products”
CREATE TABLE
omm=# omm=#

omm=# alter table products add column name char(40);
ALTER TABLE
omm=# \d products
Table “public.products”
Column | Type | Modifiers
--------±--------------±----------
id | integer |
num | integer |
name | character(40) |
Indexes:
“products_id_key” UNIQUE CONSTRAINT, btree (id) TABLESPACE pg_default

omm=# create index idx_name on products(upper(name));
CREATE INDEX

omm=# \d products
Table “public.products”
Column | Type | Modifiers
--------±--------------±----------
id | integer |
num | integer |
name | character(40) |
Indexes:
“products_id_key” UNIQUE CONSTRAINT, btree (id) TABLESPACE pg_default
“idx_name” btree (upper(name::text)) TABLESPACE pg_default
“idx_num” btree (num) TABLESPACE pg_default

设置索引1不可用，修改索引2的表空间，重命名索引3
omm=# \d products
Table “public.products”
Column | Type | Modifiers
--------±--------------±----------
id | integer |
num | integer |
name | character(40) |
Indexes:
“products_id_key” UNIQUE CONSTRAINT, btree (id) TABLESPACE pg_default
“idx_name” btree (upper(name::text)) TABLESPACE pg_default
“idx_num” btree (num) TABLESPACE pg_default

omm=# alter index products_id_key UNUSABLE;
ALTER INDEX

omm=# create tablespace tbs1 RELATIVE LOCATION ‘tbs1’;
CREATE TABLESPACE

omm=# alter index idx_name SET TABLESPACE tbs1;
ALTER INDEX

omm=# alter index idx_num rename to idx_numrename;
ALTER INDEX

omm=# \d products
Table “public.products”
Column | Type | Modifiers
--------±--------------±----------
omm=# id | integer |
num | integer |
name | character(40) |
Indexes:
“products_id_key” UNIQUE CONSTRAINT, btree (id) TABLESPACE pg_default
“idx_name” btree (upper(name::text)) TABLESPACE tbs1, tablespace “tbs1”
“idx_numrename” btree (num) TABLESPACE pg_default

重建索引2和products的所有索引
omm=# alter index products_id_key rebuild;
REINDEX

omm=# alter index idx_name rebuild;
REINDEX

omm=# alter index idx_numrename rebuild;
REINDEX

omm=# drop index idx_name;
DROP INDEX

omm=# create index idx_name on products(name);
CREATE INDEX

使用\d+和系统视图pg_indexes查看索引信息
omm=# \d+ products
Table “public.products”
Column | Type | Modifiers | Storage | Stats target | Description
--------±--------------±----------±---------±-------------±------------
id | integer | | plain | |
num | integer | | plain | |
name | character(40) | | extended | |
Indexes:
“products_id_key” UNIQUE CONSTRAINT, btree (id) TABLESPACE pg_default
“idx_name” btree (name) TABLESPACE pg_default
“idx_numrename” btree (num) TABLESPACE pg_default
Has OIDs: no
Options: orientation=row, compression=no

omm=# select * from pg_indexes where tablename like ‘products’;
schemaname | tablename | indexname | tablespace | indexdef

------------±----------±----------------±-----------±----------------------------------------------------------

public | products | products_id_key | | CREATE UNIQUE INDEX products_id_key ON products USING btre
e (id) TABLESPACE pg_default
public | products | idx_numrename | | CREATE INDEX idx_numrename ON products USING btree (num) TABLESPACE pg_default
public | products | idx_name | | CREATE INDEX idx_name ON products USING btree (name) TABLESPACE pg_default

删除索引、表和表空间
omm=# \d products
Table “public.products”
Column | Type | Modifiers
--------±--------------±----------
id | integer |
num | integer |
name | character(40) |
Indexes:
“products_id_key” UNIQUE CONSTRAINT, btree (id) TABLESPACE pg_default
“idx_name” btree (name) TABLESPACE pg_default
“idx_numrename” btree (num) TABLESPACE pg_default

omm=# alter table products drop constraint products_id_key;
ALTER TABLE
omm=# drop index idx_name;
DROP INDEX
omm=# drop index idx_numrename;
DROP INDEX
omm=# drop tablespace tbs1;
DROP TABLESPACE
omm=# \d products
Table “public.products”
Column | Type | Modifiers
--------±--------------±----------
id | integer |
num | integer |
name | character(40) |

omm=# \db
List of tablespaces
Name | Owner | Location
------------±------±---------
pg_default | omm |
pg_global | omm |