目标

学习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);

– 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

-- 创建 schema

create schema bingo;

-- 创建表

CREATE TABLE bingo.products
(
	product_id INTEGER NOT NULL,
	product_name CHAR(20) NOT NULL,
	product_type CHAR(20),
	code CHAR(30),
	address CHAR(200)
);

-- 创建 unique 索引

create unique index idx_products_id on bingo.products (product_id);

-- 创建 b-tree 索引

create index idx_products_name on bingo.products using btree(product_name);

-- 创建 表达式索引

create index idx_products_code on bingo.products (substr(code,1,4));

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

alter index bingo.idx_products_id unusable;
create tablespace example0 relative location 'tablespace1/tablespace_0';
alter index bingo.idx_products_name set tablespace example0;
alter index bingo.idx_products_code rename to idx_products_code1;
select * from pg_indexes where tablename = 'products';

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

alter index bingo.idx_products_name rebuild;
reindex index bingo.idx_products_name;
reindex table bingo.products;

备注：2/3个重建索引命令，是oracle没有的，get到了。

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

\d+ bingo.products
select * from pg_indexes where tablename = 'products';

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

drop index bingo.idx_products_id;
drop index bingo.idx_products_name;
drop index bingo.idx_products_code1;
drop table bingo.products;
drop schema bingo;
drop tablespace example0;