越学越觉得gauss功能比较强大，易用！！！老样子，练习步骤如下，今天来练习普通索引
1.登录环境
Welcome to 墨天轮.
This is Web Terminal of modb.pro; Good Good Study, Day Day Up.
root@modb:~# su - omm
omm@modb:~$ gsql -r
gsql ((openGauss 2.0.0 build 78689da9) compiled at 2021-03-31 21:03:52 commit 0 last mr )
Non-SSL connection (SSL connection is recommended when requiring high-security)
Type "help" for help.
2.创建schema,表
omm=# create schema tpcds;
CREATE SCHEMA
omm=# CREATE TABLE tpcds.ship_mode_t1
omm-# (
omm(# SM_SHIP_MODE_SK INTEGER NOT NULL,
omm(# SM_SHIP_MODE_ID CHAR(16) NOT NULL,
omm(# SM_TYPE CHAR(30),
omm(# SM_CODE CHAR(10),
omm(# SM_CARRIER CHAR(20),
omm(# SM_CONTRACT CHAR(20)
omm(# );
CREATE TABLE
3.创建唯一索引
omm=# CREATE UNIQUE INDEX ds_ship_mode_t1_index1 ON tpcds.ship_mode_t1(SM_SHIP_MODE_SK);
CREATE INDEX
创建btree索引
omm=# CREATE INDEX ds_ship_mode_t1_index4 ON tpcds.ship_mode_t1 USING btree(SM_SHIP_MODE_SK);
CREATE INDEX
创建表达式索引
omm=# CREATE INDEX ds_ship_mode_t1_index2 ON tpcds.ship_mode_t1(SUBSTR(SM_CODE,1 ,4));
CREATE INDEX
创建条件表达式索引 - 字段SM_SHIP_MODE_SK>10的部份创建索引
omm=# CREATE UNIQUE INDEX ds_ship_mode_t1_index3 ON tpcds.ship_mode_t1(SM_SHIP_MODE_SK) WHERE SM_SHIP_MODE_SK>10;
CREATE INDEX
4.查看表信息
omm=# \d+ tpcds.ship_mode_t1
Table "tpcds.ship_mode_t1"
Column | Type | Modifiers | Storage | Stats target | Description
-----------------+---------------+-----------+----------+--------------+-------------
sm_ship_mode_sk | integer | not null | plain | |
sm_ship_mode_id | character(16) | not null | extended | |
sm_type | character(30) | | extended | |
sm_code | character(10) | | extended | |
sm_carrier | character(20) | | extended | |
sm_contract | character(20) | | extended | |
Indexes:
"ds_ship_mode_t1_index1" UNIQUE, btree (sm_ship_mode_sk) TABLESPACE pg_default
"ds_ship_mode_t1_index3" UNIQUE, btree (sm_ship_mode_sk) TABLESPACE pg_default WHERE sm_ship_mode_sk > 10
"ds_ship_mode_t1_index2" btree (substr(sm_code::text, 1, 4)) TABLESPACE pg_default
"ds_ship_mode_t1_index4" btree (sm_ship_mode_sk) TABLESPACE pg_default
Has OIDs: no
Options: orientation=row, compression=no
5.查看系统视图pg_indexes
omm=# select * from pg_indexes where tablename = 'ship_mode_t1';select * from pg_indexes where tablename = 'ship_mode_t1';
schemaname | tablename | indexname | tablespace |
indexdef
------------+--------------+------------------------+------------+------------------------------------------------------------------
---------------------------------------------------------------------------------
tpcds | ship_mode_t1 | ds_ship_mode_t1_index1 | | CREATE UNIQUE INDEX ds_ship_mode_t1_index1 ON tpcds.ship_mode_t1
USING btree (sm_ship_mode_sk) TABLESPACE pg_default
tpcds | ship_mode_t1 | ds_ship_mode_t1_index4 | | CREATE INDEX ds_ship_mode_t1_index4 ON tpcds.ship_mode_t1 USING b
tree (sm_ship_mode_sk) TABLESPACE pg_default
tpcds | ship_mode_t1 | ds_ship_mode_t1_index2 | | CREATE INDEX ds_ship_mode_t1_index2 ON tpcds.ship_mode_t1 USING b
tree (substr((sm_code)::text, 1, 4)) TABLESPACE pg_default
tpcds | ship_mode_t1 | ds_ship_mode_t1_index3 | | CREATE UNIQUE INDEX ds_ship_mode_t1_index3 ON tpcds.ship_mode_t1
USING btree (sm_ship_mode_sk) TABLESPACE pg_default WHERE (sm_ship_mode_sk > 10)
(4 rows)
6.修改索引定义
omm=# ALTER INDEX tpcds.ds_ship_mode_t1_index1 RENAME TO ds_ship_mode_t1_index5;
ALTER INDEX
7.设置索引不可用
omm=# ALTER INDEX tpcds.ds_ship_mode_t1_index2 UNUSABLE;
ALTER INDEX
8.创建表空间以提供索引专用
omm=# CREATE TABLESPACE example0 RELATIVE LOCATION 'tablespace1/tablespace_0';
CREATE TABLESPACE
omm=# alter index tpcds.ds_ship_mode_t1_index4 set tablespace example0;
ALTER INDEX
9.查看表信息
omm=# \d+ tpcds.ship_mode_t1;
Table "tpcds.ship_mode_t1"
Column | Type | Modifiers | Storage | Stats target | Description
-----------------+---------------+-----------+----------+--------------+-------------
sm_ship_mode_sk | integer | not null | plain | |
sm_ship_mode_id | character(16) | not null | extended | |
sm_type | character(30) | | extended | |
sm_code | character(10) | | extended | |
sm_carrier | character(20) | | extended | |
omm=# sm_contract | character(20) | | extended | |
Indexes:
"ds_ship_mode_t1_index3" UNIQUE, btree (sm_ship_mode_sk) TABLESPACE pg_default WHERE sm_ship_mode_sk > 10
"ds_ship_mode_t1_index5" UNIQUE, btree (sm_ship_mode_sk) TABLESPACE pg_default
"ds_ship_mode_t1_index2" btree (substr(sm_code::text, 1, 4)) TABLESPACE pg_default
"ds_ship_mode_t1_index4" btree (sm_ship_mode_sk) TABLESPACE example0, tablespace "example0"
Has OIDs: no
Options: orientation=row, compression=no
10.重建索引
omm=# ALTER INDEX tpcds.ds_ship_mode_t1_index2 REBUILD;
omm=# REINDEX
REINDEX INDEX tpcds.ds_ship_mode_t1_index4;
REINDEX
omm=# reindex table tpcds.ship_mode_t1;
REINDEX
11.删除索引
omm=# DROP INDEX tpcds.ds_ship_mode_t1_index2;
DROP INDEX
omm=# DROP INDEX tpcds.ds_ship_mode_t1_index3;
DROP INDEX
omm=# DROP INDEX tpcds.ds_ship_mode_t1_index4;
DROP INDEX
omm=# DROP INDEX tpcds.ds_ship_mode_t1_index5;
DROP INDEX
omm=#
作业
omm@modb:~$ gsql -r
gsql ((openGauss 2.0.0 build 78689da9) compiled at 2021-03-31 21:03:52 commit 0 last mr )
Non-SSL connection (SSL connection is recommended when requiring high-security)
Type "help" for help.
omm=# create schema alex;
CREATE SCHEMA
omm=# CREATE TABLE alex.products
omm-# (
SM_SHIP_MODE_SK INTEGER NOT NULL,
omm(# omm(# SM_SHIP_MODE_ID CHAR(16) NOT NULL,
omm(# SM_TYPE CHAR(30),
omm(# SM_CODE CHAR(10),
omm(# SM_CARRIER CHAR(20),
omm(# SM_CONTRACT CHAR(20)
omm(# );
CREATE TABLE
分别创建唯一索引，B树索引，表达式索引
omm=# CREATE UNIQUE INDEX u_index1 ON alex.products(SM_SHIP_MODE_SK);
CREATE INDEX
omm=# CREATE INDEX b_index2 ON alex.products USING btree(SM_SHIP_MODE_SK);
CREATE INDEX
omm=# CREATE INDEX express_index3 ON alex.products(SUBSTR(SM_CODE,1 ,4));
CREATE INDEX
设置索引1不可用
omm=# ALTER INDEX alex.u_index1 UNUSABLE;
ALTER INDEX
omm=#
查看一下索引相关信息
select * from pg_indexes where tablename = 'products';
==>索引所在的表空间为pg_default
准备修改索引表空间
修改索引2的表空间
omm=# CREATE TABLESPACE index_tbs RELATIVE LOCATION 'tablespace1/tablespace_index_tbs';
CREATE TABLESPACE
omm=# alter index alex.b_index2 set tablespace index_tbs;
ALTER INDEX
重命名索引3
omm=# ALTER INDEX alex.express_index3 RENAME TO express_index3_new;
ALTER INDEX
重建索引2和products的所有索引
omm=# ALTER INDEX alex.b_index2 REBUILD;
REINDEX
omm=# reindex table alex.products;
REINDEX
使用\d+ 和系统视图pg_indexes查看索引信息
omm=# \d+ alex.products;
Table "alex.products"
Column | Type | Modifiers | Storage | Stats target | Description
-----------------+---------------+-----------+----------+--------------+-------------
sm_ship_mode_sk | integer | not null | plain | |
sm_ship_mode_id | character(16) | not null | extended | |
sm_type | character(30) | | extended | |
sm_code | character(10) | | extended | |
sm_carrier | character(20) | | extended | |
sm_contract | character(20) | | extended | |
Indexes:
"u_index1" UNIQUE, btree (sm_ship_mode_sk) TABLESPACE pg_default
"b_index2" btree (sm_ship_mode_sk) TABLESPACE index_tbs, tablespace "index_tbs"
"express_index3_new" btree (substr(sm_code::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
------------+-----------+--------------------+------------+-------------------------------------------------------------------------
-------------------------------------------
alex | products | u_index1 | | CREATE UNIQUE INDEX u_index1 ON alex.products USING btree (sm_ship_mode_
sk) TABLESPACE pg_default
alex | products | b_index2 | index_tbs | CREATE INDEX b_index2 ON alex.products USING btree (sm_ship_mode_sk) TAB
LESPACE index_tbs
alex | products | express_index3_new | | CREATE INDEX express_index3_new ON alex.products USING btree (substr((sm
_code)::text, 1, 4)) TABLESPACE pg_default
(3 rows)
omm=#
删除索引，表，表空间
omm=# select tablename,indexname,tablespace from pg_indexes where tablename='products';
(3 rows)
omm=# tablename | indexname | tablespace
-----------+--------------------+------------
products | b_index2 | index_tbs
products | u_index1 |
products | express_index3_new |
omm=# drop index alex.b_index2;
DROP INDEX
omm=# drop index alex.u_index1;
DROP INDEX
omm=# drop index alex.express_index3_new;
DROP INDEX
omm=# drop tablespace index_tbs;
DROP TABLESPACE
omm=#
索引相关操作到此，have a nice day!
