openGuass每日一练第9天 | 普通表索引 from seven
学习目标
学习openGauss普通表索引
索引是对数据库表中一列或多列的值进行排序的一种结构，使用索引可快速访问数据库表中的特定信息
课程学习
#第一次进入等待15秒#数据库启动中...
su - omm
gsql -r
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);
– M_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 table products
omm-# (
omm(# p_id int primary key,
omm(# p_name char(30) not null,
omm(# p_type char(30) not null,
omm(# p_num int,
omm(# p_loc char(50));
NOTICE: CREATE TABLE / PRIMARY KEY will create implicit index "products_pkey" for table "products"
CREATE TABLE
omm=# create unique index ind_p_name on products(p_name);
CREATE INDEX
omm=# create index ind_p_type on products using btree(p_type);
CREATE INDEX
omm=# create index ind_p_loc on products(substr(p_loc,1,5));
CREATE INDEX
2. 设置索引1不可用，修改索引2的表空间，重命名索引3
omm=# alter index ind_p_name unusable;
omm=# alter index ind_p_type set tablespace indtbs0;
ALTER INDEX
omm=# alter index ind_p_loc rename to ind_p_location;
ALTER INDEX
3. 重建索引2和products的所有索引
omm=# reindex index ind_p_type;
REINDEX
omm=# reindex table products;
REINDEX
4. 使用\d+和系统视图pg_indexes查看索引信息
omm=# \d+ products;
Table "public.products"
Column | Type | Modifiers | Storage | Stats target | Description
--------+---------------+-----------+----------+--------------+-------------
p_id | integer | not null | plain | |
p_name | character(30) | not null | extended | |
p_type | character(30) | not null | extended | |
p_num | integer | | plain | |
p_loc | character(50) | | extended | |
Indexes:
"products_pkey" PRIMARY KEY, btree (p_id) TABLESPACE pg_default
"ind_p_name" UNIQUE, btree (p_name) TABLESPACE pg_default
"ind_p_location" btree (substr(p_loc::text, 1, 5)) TABLESPACE pg_default
"ind_p_type" btree (p_type) TABLESPACE indtbs0, tablespace "indtbs0"
Has OIDs: no
Options: orientation=row, compression=no
omm=# select * from pg_indexes where tablename='products';
schemaname | tablename | indexname | tablespace | indexdef
------------+-----------+----------------+------------+---------------------------------------------------------------------------------------------------------
public | products | products_pkey | | CREATE UNIQUE INDEX products_pkey ON products USING btree (p_id) TABLESPACE pg_default
public | products | ind_p_name | | CREATE UNIQUE INDEX ind_p_name ON products USING btree (p_name) TABLESPACE pg_default
public | products | ind_p_type | indtbs0 | CREATE INDEX ind_p_type ON products USING btree (p_type) TABLESPACE indtbs0
public | products | ind_p_location | | CREATE INDEX ind_p_location ON products USING btree (substr((p_loc)::text, 1, 5)) TABLESPACE pg_default
(4 rows)
5.删除索引、表和表空间
omm=# drop index ind_p_location;
DROP INDEX
omm=# drop index ind_p_type;
DROP INDEX
omm=# drop index ind_p_name;
omm=# DROP INDEX
