暂无图片
暂无图片
暂无图片
暂无图片
暂无图片
首页 / (openGauss每日一练第 9天)：openGauss普通表索引、创建、修改、重建、删除

(openGauss每日一练第 9天)：openGauss普通表索引、创建、修改、重建、删除

原创 junzibuyuantian 恩墨学院 2021-12-09
374

1.创建索引

--1、使用gsql登录openGauss
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.

omm=# 

--2、创建schema tpcds
omm=# create schema tpcds;
CREATE SCHEMA

--3、在模式tpcds下创建ship_mode_t1表
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


--4、SM_SHIP_MODE_SK字段上创建普通的唯一索引
omm=# CREATE UNIQUE INDEX ds_ship_mode_t1_index1 ON tpcds.ship_mode_t1(SM_SHIP_MODE_SK);
CREATE INDEX

--5、 SM_SHIP_MODE_SK字段上创建指定B-tree索引。
omm=# CREATE INDEX ds_ship_mode_t1_index4 ON tpcds.ship_mode_t1 USING btree(SM_SHIP_MODE_SK);
CREATE INDEX

--6、SM_CODE字段上创建表达式索引
omm=# CREATE INDEX ds_ship_mode_t1_index2 ON tpcds.ship_mode_t1(SUBSTR(SM_CODE,1 ,4));
CREATE INDEX

--7、SM_SHIP_MODE_SK字段上创建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

--8、查看表信息
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

--9、查看系统视图pg_indexes
omm=# 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 btree (sm_shi
p_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 btree (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)

2.修改索引定义

--1、重命名索引
omm=# ALTER INDEX tpcds.ds_ship_mode_t1_index1 RENAME TO ds_ship_mode_t1_index5;
ALTER INDEX

--2、验证索引名是否更改
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_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 pg_default
Has OIDs: no
Options: orientation=row, compression=no

--3、设置索引不可用
omm=# ALTER INDEX tpcds.ds_ship_mode_t1_index2 UNUSABLE;
ALTER INDEX

--4、修改索引表空间
--创建表空间
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

--查询表信息验证索引表空间是否更改
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:
omm=#     "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
omm=#

3.重建索引

--1、重建一个单独索引
omm=# ALTER INDEX tpcds.ds_ship_mode_t1_index2 REBUILD;
REINDEX

omm=# REINDEX INDEX tpcds.ds_ship_mode_t1_index4;
REINDEX

--2、重建所有索引
omm=# reindex table tpcds.ship_mode_t1;
REINDEX

4.删除索引

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

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

omm=# CREATE SCHEMA TEST1;
CREATE SCHEMA

omm=# CREATE TABLE test1.products
(
XUEHAO INTEGER NOT NULL,
SHENFENZHENG CHAR(18) NOT NULL,
NAME CHAR(50),
BANJIE CHAR(10),
NIANJI CHAR(20),
XUEXIAO CHAR(50)
);
CREATE TABLE

omm=# CREATE UNIQUE INDEX products_index1 ON test1.products(XUEHAO);
CREATE INDEX

omm=# CREATE INDEX products_index2 ON test1.products USING btree(XUEHAO);
CREATE INDEX

omm=# CREATE INDEX products_index3 ON test1.products(SUBSTR(BANJIE,1 ,4));
CREATE INDEX

omm=# \d+ test1.products
                              Table "test1.products"
    Column    |     Type      | Modifiers | Storage  | Stats target | Description 
--------------+---------------+-----------+----------+--------------+-------------
 xuehao       | integer       | not null  | plain    |              | 
 shenfenzheng | character(18) | not null  | extended |              | 
 name         | character(50) |           | extended |              | 
 banjie       | character(10) |           | extended |              | 
 nianji       | character(20) |           | extended |              | 
 xuexiao      | character(50) |           | extended |              | 
Indexes:
    "products_index1" UNIQUE, btree (xuehao) TABLESPACE pg_default
    "products_index2" btree (xuehao) TABLESPACE pg_default
    "products_index3" btree (substr(banjie::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                            
                         
------------+-----------+-----------------+------------+----------------------------------------------------------------------------------------(3 rows)


-------------------------
 test1      | products  | products_index1 |            | CREATE UNIQUE INDEX products_index1 ON test1.products USING btree (xuehao) TABLESPACE p
g_default
 test1      | products  | products_index2 |            | CREATE INDEX products_index2 ON test1.products USING btree (xuehao) TABLESPACE pg_defau
lt
 test1      | products  | products_index3 |            | CREATE INDEX products_index3 ON test1.products USING btree (substr((banjie)::text, 1, 4
)) TABLESPACE pg_default

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

omm=# ALTER INDEX test1.products_index1 UNUSABLE;
ALTER INDEX

omm=# CREATE TABLESPACE tbs_01 RELATIVE LOCATION 'tablespace1/tbs_01';
CREATE TABLESPACE

omm=# alter index test1.products_index2 set tablespace tbs_01;
ALTER INDEX

omm=# ALTER INDEX test1.products_index3 RENAME TO products_index4;
ALTER INDEX

omm=# \d+ test1.products
                              Table "test1.products"
    Column    |     Type      | Modifiers | Storage  | Stats target | Description 
--------------+---------------+-----------+----------+--------------+-------------
 xuehao       | integer       | not null  | plain    |              | 
 shenfenzheng | character(18) | not null  | extended |              | 
 name         | character(50) |           | extended |              | 
 banjie       | character(10) |           | extended |              | 
 nianji       | character(20) |           | extended |              | 
Has OIDs: no
Options: orientation=row, compression=no

omm=#  xuexiao      | character(50) |           | extended |              | 
Indexes:
    "products_index1" UNIQUE, btree (xuehao) TABLESPACE pg_default
    "products_index2" btree (xuehao) TABLESPACE tbs_01, tablespace "tbs_01"
    "products_index4" btree (substr(banjie::text, 1, 4)) TABLESPACE pg_default

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

omm=# REINDEX INDEX test1.products_index2;
REINDEX

omm=# reindex table test1.products;
REINDEX

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

omm=# \d+ test1.products
                              Table "test1.products"
    Column    |     Type      | Modifiers | Storage  | Stats target | Description 
--------------+---------------+-----------+----------+--------------+-------------
 xuehao       | integer       | not null  | plain    |              | 
 shenfenzheng | character(18) | not null  | extended |              | 
 name         | character(50) |           | extended |              | 
 banjie       | character(10) |           | extended |              | 
 nianji       | character(20) |           | extended |              | 
 xuexiao      | character(50) |           | extended |              | 
Indexes:
    "products_index1" UNIQUE, btree (xuehao) TABLESPACE pg_default
    "products_index2" btree (xuehao) TABLESPACE tbs_01, tablespace "tbs_01"
    "products_index4" btree (substr(banjie::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                            
                         
------------+-----------+-----------------+------------+----------------------------------------------------------------------------------------
-------------------------
 test1      | products  | products_index1 |            | CREATE UNIQUE INDEX products_index1 ON test1.products USING btree (xuehao) TABLESPACE p
g_default
 test1      | products  | products_index2 | tbs_01     | CREATE INDEX products_index2 ON test1.products USING btree (xuehao) TABLESPACE tbs_01
 test1      | products  | products_index4 |            | CREATE INDEX products_index4 ON test1.products USING btree (substr((banjie)::text, 1, 4
)) TABLESPACE pg_default
(3 rows)

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

omm=# DROP INDEX test1.products_index1;
DROP INDEX

omm=# DROP INDEX test1.products_index2;
DROP INDEX

omm=# DROP INDEX test1.products_index4;
DROP INDEX

omm=# DROP TABLE TEST1.PRODUCTS;
DROP TABLE

omm=# DROP TABLESPACE TBS_01;
DROP TABLESPACE
opengaussopengauss训练营
最后修改时间：2021-12-09 23:15:31
「喜欢这篇文章，您的关注和赞赏是给作者最好的鼓励」
关注作者
【版权声明】本文为墨天轮用户原创内容，转载时必须标注文章的来源（墨天轮），文章链接，文章作者等基本信息，否则作者和墨天轮有权追究责任。如果您发现墨天轮中有涉嫌抄袭或者侵权的内容，欢迎发送邮件至：contact@modb.pro进行举报，并提供相关证据，一经查实，墨天轮将立刻删除相关内容。

评论