数据库环境
openGauss:2.0.0 - 数据库实训平台
学习目标
学习openGauss分区表索引
学习笔记
- 创建分区表索引，不指定索引分区的名称、指定索引分区的名称、创建GLOBAL分区索引
omm=# CREATE INDEX ds_customer_address_p1_index1 ON tpcds.customer_address_p1(CA_ADDRESS_SK) LOCAL;
CREATE INDEX
omm=# CREATE INDEX ds_customer_address_p1_index2 ON
omm-# tpcds.customer_address_p1(CA_ADDRESS_SK) LOCAL
omm-# (
omm(# PARTITION CA_ADDRESS_SK_index1,
omm(# PARTITION CA_ADDRESS_SK_index2 TABLESPACE example3,
omm(# PARTITION CA_ADDRESS_SK_index3 TABLESPACE example4
omm(# );
CREATE INDEX
omm=# CREATE INDEX ds_customer_address_p1_index3 ON tpcds.customer_address_p1(CA_ADDRESS_ID) GLOBAL;
CREATE INDEX
课后作业
1.创建范围分区表products, 为表创建分区表索引1，不指定索引分区的名称，创建分区表索引2，并指定索引分区的名称，创建GLOBAL分区索引3
omm=# create table products
omm-# (
omm(# product_sk integer not null,
omm(# product_id char(30) not null,
omm(# product_name char(30)
omm(# )
omm-# partition by range(product_sk)
omm-# (
omm(# partition p1 values less than (1000),
omm(# partition p2 values less than (2000),
omm(# partition p3 values less than (MAXVALUE)
omm(# );
CREATE TABLE
omm=# \d+ products;
Table "public.products"
Column | Type | Modifiers | Storage | Stats target | Description
--------------+---------------+-----------+----------+--------------+-------------
product_sk | integer | not null | plain | |
product_id | character(30) | not null | extended | |
product_name | character(30) | | extended | |
Range partition by(product_sk)
Number of partition: 3 (View pg_partition to check each partition range.)
Has OIDs: no
Options: orientation=row, compression=no
omm=# create index products_sk_index1 on products(product_sk) local;
CREATE INDEX
omm=# create index products_sk_index2 on products(product_sk) local
omm-# (
omm(# partition products_sk_index2_p1,
omm(# partition products_sk_index2_p2,
omm(# partition products_sk_index2_p3
omm(# );
CREATE INDEX
omm=# create index products_sk_index3 on products(product_id) global;
CREATE INDEX
omm=# \d+ products;
Table "public.products"
Column | Type | Modifiers | Storage | Stats target | Description
--------------+---------------+-----------+----------+--------------+-------------
product_sk | integer | not null | plain | |
product_id | character(30) | not null | extended | |
product_name | character(30) | | extended | |
Indexes:
"products_sk_index1" btree (product_sk) LOCAL(PARTITION p1_product_sk_idx, PARTITION p2_product_sk_idx, PARTITION p3_product_sk_idx) TABLESPACE pg_default
"products_sk_index2" btree (product_sk) LOCAL(PARTITION products_sk_index2_p1, PARTITION products_sk_index2_p2, PARTITION products_sk_index2_p3) TABLESPACE pg_default
"products_sk_index3" btree (product_id) TABLESPACE pg_default
Range partition by(product_sk)
Number of partition: 3 (View pg_partition to check each partition range.)
Has OIDs: no
Options: orientation=row, compression=no
omm=#
2.在分区表索引1上，修改分区表索引的表空间，重命名分区表索引
omm=# create tablespace ts1 relative location 'tablespace/ts1';
CREATE TABLESPACE
omm=# alter index products_sk_index1 move partition p1_product_sk_idx tablespace ts1;
ALTER INDEX
omm=# alter index products_sk_index1 rename partition p1_product_sk_idx to p1_product_sk_idx_new;
ALTER INDEX
omm=# \d+ products;
Table "public.products"
Column | Type | Modifiers | Storage | Stats target | Description
--------------+---------------+-----------+----------+--------------+-------------
product_sk | integer | not null | plain | |
product_id | character(30) | not null | extended | |
product_name | character(30) | | extended | |
Indexes:
"products_sk_index1" btree (product_sk) LOCAL(PARTITION p1_product_sk_idx_new TABLESPACE ts1, PARTITION p2_product_sk_idx, PARTITION p3_product_sk_idx) TABLESPACE pg_default
"products_sk_index2" btree (product_sk) LOCAL(PARTITION products_sk_index2_p1, PARTITION products_sk_index2_p2, PARTITION products_sk_index2_p3) TABLESPACE pg_default
"products_sk_index3" btree (product_id) TABLESPACE pg_default
Range partition by(product_sk)
Number of partition: 3 (View pg_partition to check each partition range.)
Has OIDs: no
Options: orientation=row, compression=no
3.在分区表索引2上，重建单个索引分区和分区上的所有索引
omm=# reindex index products_sk_index2 partition products_sk_index2_p1;
REINDEX
omm=# reindex table products partition p1;
REINDEX
4.使用\d+、系统视图pg_indexes和pg_partition查看索引信息
omm=# \d+ products;
Table "public.products"
Column | Type | Modifiers | Storage | Stats target | Description
--------------+---------------+-----------+----------+--------------+-------------
product_sk | integer | not null | plain | |
product_id | character(30) | not null | extended | |
product_name | character(30) | | extended | |
Indexes:
"products_sk_index1" btree (product_sk) LOCAL(PARTITION p1_product_sk_idx_new TABLESPACE ts1, PARTITION p2_product_sk_idx, PARTITION p3_product_sk_idx) TABLESPACE pg_default
"products_sk_index2" btree (product_sk) LOCAL(PARTITION products_sk_index2_p1, PARTITION products_sk_index2_p2, PARTITION products_sk_index2_p3) TABLESPACE pg_default
"products_sk_index3" btree (product_id) TABLESPACE pg_default
Range partition by(product_sk)
Number of partition: 3 (View pg_partition to check each partition range.)
Has OIDs: no
Options: orientation=row, compression=no
omm=# select * from pg_indexes where tablename='products';
schemaname | tablename | indexname | tablespace | indexdef
------------+-----------+--------------------+------------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
public | products | products_sk_index1 | | CREATE INDEX products_sk_index1 ON products USING btree (product_sk) LOCAL(PARTITION p1_product_sk_idx_new TABLESPACE ts1, PARTITION p2_product_sk_idx, PARTITION p3_product_sk_idx) TABLESPACE pg_default
public | products | products_sk_index2 | | CREATE INDEX products_sk_index2 ON products USING btree (product_sk) LOCAL(PARTITION products_sk_index2_p1, PARTITION products_sk_index2_p2, PARTITION products_sk_index2_p3) TABLESPACE pg_default
public | products | products_sk_index3 | | CREATE INDEX products_sk_index3 ON products USING btree (product_id) TABLESPACE pg_default
(3 rows)
omm=# select relname, parttype, parentid, reltablespace from pg_partition;
relname | parttype | parentid | reltablespace
-----------------------+----------+----------+---------------
customer_address_p1 | r | 16394 | 0
p1 | p | 16394 | 0
p2 | p | 16394 | 16389
p3 | p | 16394 | 16390
products | r | 16416 | 0
p1 | p | 16416 | 0
p2 | p | 16416 | 0
p3 | p | 16416 | 0
p2_product_sk_idx | x | 16423 | 0
p3_product_sk_idx | x | 16423 | 0
products_sk_index2_p2 | x | 16427 | 0
products_sk_index2_p3 | x | 16427 | 0
p1_product_sk_idx_new | x | 16423 | 16432
products_sk_index2_p1 | x | 16427 | 0
(14 rows)
5.删除索引、表和表空间
omm=# drop index products_sk_index1,products_sk_index2,products_sk_index3;
DROP INDEX
omm=# drop table products;
DROP TABLE
omm=# drop tablespace ts1;
DROP TABLESPACE
学习体会
关于创建索引时，默认是local还是global，可以参考
学习资源
- openGauss SQL学习参考资料
- 每日一练：openGauss数据库在线实训课程
- openGauss每日一练 | 21期养成好习惯，提升技术能力！
- 墨天轮Markdown编辑器使用介绍
- 墨天轮数据库在线实训平台V1.0操作手册
- 墨天轮数据社区
欢迎各位同学一起来交流学习心得！
最后修改时间：2021-12-16 18:37:46
「喜欢这篇文章，您的关注和赞赏是给作者最好的鼓励」
关注作者
【版权声明】本文为墨天轮用户原创内容，转载时必须标注文章的来源（墨天轮），文章链接，文章作者等基本信息，否则作者和墨天轮有权追究责任。如果您发现墨天轮中有涉嫌抄袭或者侵权的内容，欢迎发送邮件至：contact@modb.pro进行举报，并提供相关证据，一经查实，墨天轮将立刻删除相关内容。
