openGauss每日一练第9天 加油

1.创建索引

create schema tpcds;
CREATE TABLE tpcds.ship_mode_t1
(
SM_SHIP_MODE_SK INTEGER NOT NULL,
SM_SHIP_MODE_ID CHAR(16) NOT NULL,
SM_TYPE CHAR(30),
SM_CODE CHAR(10),
SM_CARRIER CHAR(20),
SM_CONTRACT CHAR(20)
);
– SM_SHIP_MODE_SK字段上创建普通的唯一索引

CREATE UNIQUE INDEX ds_ship_mode_t1_index1 ON tpcds.ship_mode_t1(SM_SHIP_MODE_SK);
– SM_SHIP_MODE_SK字段上创建指定B-tree索引。

CREATE INDEX ds_ship_mode_t1_index4 ON tpcds.ship_mode_t1 USING btree(SM_SHIP_MODE_SK);
–SM_CODE字段上创建表达式索引

CREATE INDEX ds_ship_mode_t1_index2 ON tpcds.ship_mode_t1(SUBSTR(SM_CODE,1 ,4));
– SM_SHIP_MODE_SK字段上创建SM_SHIP_MODE_SK大于10的部分索引

CREATE UNIQUE INDEX ds_ship_mode_t1_index3 ON tpcds.ship_mode_t1(SM_SHIP_MODE_SK) WHERE SM_SHIP_MODE_SK>10;
–查看表信息

\d+ tpcds.ship_mode_t1
–查看系统视图pg_indexes

select * from pg_indexes where tablename = ‘ship_mode_t1’;

2.修改索引定义

–重命名索引

ALTER INDEX tpcds.ds_ship_mode_t1_index1 RENAME TO ds_ship_mode_t1_index5;
–设置索引不可用

ALTER INDEX tpcds.ds_ship_mode_t1_index2 UNUSABLE;
–修改索引表空间

CREATE TABLESPACE example0 RELATIVE LOCATION ‘tablespace1/tablespace_0’;
alter index tpcds.ds_ship_mode_t1_index4 set tablespace example0;
\d+ tpcds.ship_mode_t1;

3.重建索引

–重建一个单独索引

ALTER INDEX tpcds.ds_ship_mode_t1_index2 REBUILD;
REINDEX INDEX tpcds.ds_ship_mode_t1_index4;
–重建所有索引

reindex table tpcds.ship_mode_t1;

4.删除索引

DROP INDEX tpcds.ds_ship_mode_t1_index2;
DROP INDEX tpcds.ds_ship_mode_t1_index3;
DROP INDEX tpcds.ds_ship_mode_t1_index4;
DROP INDEX tpcds.ds_ship_mode_t1_index5;

课后作业

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

omm=# create schema product_schema;
CREATE SCHEMA
omm=# create table product_schema.products
omm-# (
omm(# product_sk integer not null,
omm(# product_id char(20) not null,
omm(# product_name char(30)
omm(# );
CREATE TABLE
omm=# \d+ product_schema.products;
Table “product_schema.products”
Column | Type | Modifiers | Storage | Stats target | Description
--------------±--------------±----------±---------±-------------±------------
product_sk | integer | not null | plain | |
product_id | character(20) | not null | extended | |
product_name | character(30) | | extended | |
Has OIDs: no
Options: orientation=row, compression=no

omm=# select * from pg_indexes where tablename = ‘products’;
schemaname | tablename | indexname | tablespace | indexdef
------------±----------±----------±-----------±---------
(0 rows)

omm=# create unique index products_index1 on product_schema.products(product_sk);
CREATE INDEX
omm=# create index products_index2 on product_schema.products using btree(product_sk);
CREATE INDEX
omm=# create index products_index3 on product_schema.products(substr(product_name,1 ,4));
CREATE INDEX
omm=# \d+ product_schema.products;
omm=# Table “product_schema.products”
Column | Type | Modifiers | Storage | Stats target | Description
--------------±--------------±----------±---------±-------------±------------
product_sk | integer | not null | plain | |
product_id | character(20) | not null | extended | |
product_name | character(30) | | extended | |
Indexes:
“products_index1” UNIQUE, btree (product_sk) TABLESPACE pg_default
“products_index2” btree (product_sk) TABLESPACE pg_default
“products_index3” btree (substr(product_name::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
----------------±----------±----------------±-----------±-------------------------------------------------------------------------------------------------------------------------------
product_schema | products | products_index1 | | CREATE UNIQUE INDEX products_index1 ON product_schema.products USING btree (product_sk) TABLESPACE pg_default
product_schema | products | products_index2 | | CREATE INDEX products_index2 ON product_schema.products USING btree (product_sk) TABLESPACE pg_default
product_schema | products | products_index3 | | CREATE INDEX products_index3 ON product_schema.products USING btree (substr((product_name)::text, 1, 4)) TABLESPACE pg_default
(3 rows)

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

omm=# alter index product_schema.products_index1 unusable;
ALTER INDEX
omm=# create tablespace products_ts1 relative location ‘tablespace/products_ts1’;
CREATE TABLESPACE
omm=# alter index product_schema.products_index2 set tablespace products_ts1;
ALTER INDEX
omm=# alter index product_schema.products_index3 rename to products_index3_new;
ALTER INDEX
omm=# \d+ product_schema.products;
Table “product_schema.products”
Column | Type | Modifiers | Storage | Stats target | Description
--------------±--------------±----------±---------±-------------±------------
product_sk | integer | not null | plain | |
product_id | character(20) | not null | extended | |
product_name | character(30) | | extended | |
Indexes:
“products_index1” UNIQUE, btree (product_sk) TABLESPACE pg_default
“products_index2” btree (product_sk) TABLESPACE products_ts1, tablespace “products_ts1”
“products_index3_new” btree (substr(product_name::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
----------------±----------±--------------------±-------------±-----------------------------------------------------------------------------------------------------------------------------------
product_schema | products | products_index1 | | CREATE UNIQUE INDEX products_index1 ON product_schema.products USING btree (product_sk) TABLESPACE pg_default
product_schema | products | products_index2 | products_ts1 | CREATE INDEX products_index2 ON product_schema.products USING btree (product_sk) TABLESPACE products_ts1
product_schema | products | products_index3_new | | CREATE INDEX products_index3_new ON product_schema.products USING btree (substr((product_name)::text, 1, 4)) TABLESPACE pg_default
(3 rows)

omm=#

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

omm=# reindex index product_schema.products_index2;
REINDEX
omm=# reindex table product_schema.products;
REINDEX
两种重建索引方法的帮助信息
REINDEX
ALTER INDEX

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

omm=# \d+ product_schema.products;
Table “product_schema.products”
Column | Type | Modifiers | Storage | Stats target | Description
--------------±--------------±----------±---------±-------------±------------
product_sk | integer | not null | plain | |
product_id | character(20) | not null | extended | |
product_name | character(30) | | extended | |
Indexes:
“products_index1” UNIQUE, btree (product_sk) TABLESPACE pg_default
“products_index2” btree (product_sk) TABLESPACE products_ts1, tablespace “products_ts1”
“products_index3_new” btree (substr(product_name::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
----------------±----------±--------------------±-------------±-----------------------------------------------------------------------------------------------------------------------------------
product_schema | products | products_index1 | | CREATE UNIQUE INDEX products_index1 ON product_schema.products USING btree (product_sk) TABLESPACE pg_default
product_schema | products | products_index2 | products_ts1 | CREATE INDEX products_index2 ON product_schema.products USING btree (product_sk) TABLESPACE products_ts1
product_schema | products | products_index3_new | | CREATE INDEX products_index3_new ON product_schema.products USING btree (substr((product_name)::text, 1, 4)) TABLESPACE pg_default
(3 rows)
omm=#

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

omm=# drop index product_schema.products_index1;
DROP INDEX
omm=# drop index product_schema.products_index2;
DROP INDEX
omm=# drop index product_schema.products_index3;
ERROR: index “products_index3” does not exist
omm=# drop index product_schema.products_index3_new;
DROP INDEX
omm=# drop table product_schema.products;
DROP TABLE
omm=# drop schema product_schema;
DROP SCHEMA
omm=# drop tablespace products_ts1;
DROP TABLESPACE