坚持学习openGauss数据库，坚持每天打卡。第十天学习openGauss分区表索引的创建，修改，重建，删除。
连接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=#复制
1.创建范围分区表products, 为表创建分区表索引1，不指定索引分区的名称，创建分区表索引2，并指定索引分区的名称，创建global分区索引3
omm=# create tablespace tbs1 relative location 'tablespace1/tbs1';
CREATE TABLESPACE
omm=# create tablespace tbs2 relative location 'tablespace1/tbs2';
CREATE TABLESPACE
omm=# create tablespace tbs3 relative location 'tablespace1/tbs3';
CREATE TABLESPACE
omm=# create tablespace tbs4 relative location 'tablespace1/tbs4';
CREATE TABLESPACE
omm=# create schema schema1;
CREATE SCHEMA
omm=# create table schema1.products
omm-# (
omm(# ca_address_sk integer not null,
omm(# ca_address_id char(16) not null,
omm(# ca_street_number char(10) ,
omm(# ca_street_name varchar(60) ,
omm(# ca_street_type char(15) ,
omm(# ca_suite_number char(10) ,
omm(# ca_city varchar(60) ,
omm(# ca_county varchar(30) ,
omm(# ca_state char(2) ,
omm(# ca_zip char(10) ,
omm(# ca_country varchar(20) ,
omm(# ca_gmt_offset decimal(5,2) ,
omm(# ca_location_type char(20)
omm(# )
omm-# partition by range(ca_address_sk)
omm-# (
omm(# partition p1 values less than (3000),
omm(# partition p2 values less than (5000) tablespace tbs1,
omm(# partition p3 values less than (maxvalue) tablespace tbs2
omm(# );
CREATE TABLE
–创建分区表索引idx_products_index1，不指定索引分区的名称
omm=# create index idx_products_index1 on schema1.products(ca_address_sk) local;
CREATE INDEX
–创建分区表索引idx_products_index2，并指定索引分区的名称
omm=# create index idx_products_index2 on schema1.products(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
–创建global分区索引
omm=# create index idx_products_index3 on schema1.products(ca_address_id) global;
CREATE INDEX
–不指定关键字，默认创建global分区索引
omm=# create index idx_products_index4 on schema1.products(ca_address_id);
CREATE INDEX
复制2.在分区表索引1上，修改分区表索引的表空间，重命名分区表索引
omm=# alter index schema1.idx_products_index1 move partition p1_ca_address_sk_idx tablespace tbs1;
ALTER INDEX
omm=# alter index schema1.idx_products_index1 rename partition p1_ca_address_sk_idx to p1_ca_address_sk_idx1;
ALTER INDEX
复制3.在分区表索引2上，重建单个索引分区和分区上的所有索引
–重建单个索引分区
omm=# reindex index schema1.idx_products_index2 partition ca_address_sk_index1;
REINDEX
–重建分区上的所有索引
omm=# reindex table schema1.products partition p1;
REINDEX
复制4.使用\d+、系统视图pg_indexes和pg_partition查看索引信息
omm=# \d+ schema1.products;
Table "schema1.products"
Column | Type | Modifiers | Storage | Stats target | Description
------------------+-----------------------+-----------+----------+--------------+-------------
ca_address_sk | integer | not null | plain | |
ca_address_id | character(16) | not null | extended | |
ca_street_number | character(10) | | extended | |
ca_street_name | character varying(60) | | extended | |
ca_street_type | character(15) | | extended | |
ca_suite_number | character(10) | | extended | |
ca_city | character varying(60) | | extended | |
ca_county | character varying(30) | | extended | |
ca_state | character(2) | | extended | |
ca_zip | character(10) | | extended | |
ca_country | character varying(20) | | extended | |
ca_gmt_offset | numeric(5,2) | | main | |
ca_location_type | character(20) | | extended | |
Indexes:
"idx_products_index1" btree (ca_address_sk) LOCAL(PARTITION p1_ca_address_sk_idx1 TABL--More--ESPACE tbs1, PARTITION p2_ca_address
_sk_idx, PARTITION p3_ca_address_sk_idx) TABLESPACE pg_default
"idx_products_index2" btree (ca_address_sk) LOCAL(PARTITION ca_address_sk_index1, PARTITION ca_address_sk_index2 TABLESPAC
E example3, PARTITION ca_address_sk_index3 TABLESPACE example4) TABLESPACE pg_default
"idx_products_index3" btree (ca_address_id) TABLESPACE pg_default
"idx_products_index4" btree (ca_address_id) TABLESPACE pg_default
Range partition by(ca_address_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
------------+-----------+---------------------+------------+------------------------------------------------------------------
------------------------------------------------------------------------------------------------------------------------------
-------------------------------------------------------
schema1 | products | idx_products_index1 | | CREATE INDEX idx_products_index1 ON schema1.products USING btree
(ca_address_sk) LOCAL(PARTITION p1_ca_address_sk_idx1 TABLESPACE tbs1, PARTITION p2_ca_address_sk_idx, PARTITION p3_ca_address
_sk_idx) TABLESPACE pg_default
schema1 | products | idx_products_index2 | | CREATE INDEX idx_products_index2 ON schema1.products USING btree
(ca_address_sk) LOCAL(PARTITION ca_address_sk_index1, PARTITION ca_address_sk_index2 TABLESPACE example3, PARTITION ca_address
_sk_index3 TABLESPACE example4) TABLESPACE pg_default
schema1 | products | idx_products_index3 | | CREATE INDEX idx_products_index3 ON schema1.products USING btree
(ca_address_id) TABLESPACE pg_default
schema1 | products | idx_products_index4 | | CREATE INDEX idx_products_index4 ON schema1.products USING btree
(ca_address_id) TABLESPACE pg_default
(4 rows)
omm=# select * from pg_partition;
relname | parttype | parentid | rangenum | intervalnum | partstrategy | relfilenode | reltablespace | relpages
| reltuples | relallvisible | reltoastrelid | reltoastidxid | indextblid | indisusable | reldeltarelid | reldeltaidx | relcude
screlid | relcudescidx | relfrozenxid | intspnum | partkey | intervaltablespace | interval | boundaries | transit |
reloptions | relfrozenxid64
-----------------------+----------+----------+----------+-------------+--------------+-------------+---------------+----------
+-----------+---------------+---------------+---------------+------------+-------------+---------------+-------------+--------
--------+--------------+--------------+----------+---------+--------------------+----------+------------+---------+-----------
----------------------------------------+----------------
products | r | 16420 | 0 | 0 | r | 0 | 0 | 0
| 0 | 0 | 0 | 0 | 0 | t | 0 | 0 |
0 | 0 | 0 | | 1 | | | | | {orientati
on=row,compression=no,wait_clean_gpi=n} | 0
p1 | p | 16420 | 0 | 0 | r | 16424 | 0 | 0
| 0 | 0 | 0 | 0 | 0 | t | 0 | 0 |
0 | 0 | 9337 | | | | | {3000} | | {orientati
on=row,compression=no} | 9337
p2 | p | 16420 | 0 | 0 | r | 16425 | 16389 | 0
| 0 | 0 | 0 | 0 | 0 | t | 0 | 0 |
0 | 0 | 9337 | | | | | {5000} | | {orientati
on=row,compression=no} | 9337
p3 | p | 16420 | 0 | 0 | r | 16426 | 16390 | 0
| 0 | 0 | 0 | 0 | 0 | t | 0 | 0 |
0 | 0 | 9337 | | | | | {NULL} | | {orientati
on=row,compression=no} | 9337
p2_ca_address_sk_idx | x | 16427 | 0 | 0 | n | 16429 | 0 | 1
| 0 | 0 | 0 | 0 | 16425 | t | 0 | 0 |
0 | 0 | 0 | | | | | | |
| 0
p3_ca_address_sk_idx | x | 16427 | 0 | 0 | n | 16430 | 0 | 1
| 0 | 0 | 0 | 0 | 16426 | t | 0 | 0 |
0 | 0 | 0 | | | | | | |
ca_address_sk_index2 | x | 16431 | 0 | 0 | n | 16433 | 16403 | 1
| 0 | 0 | 0 | 0 | 16425 | t | 0 | 0 |
0 | 0 | 0 | | | | | | |
| 0
ca_address_sk_index3 | x | 16431 | 0 | 0 | n | 16434 | 16404 | 1
| 0 | 0 | 0 | 0 | 16426 | t | 0 | 0 |
| 0
0 | 0 | 0 | | | | | | |
| 0
p1_ca_address_sk_idx1 | x | 16427 | 0 | 0 | n | 16439 | 16389 | 1
| 0 | 0 | 0 | 0 | 16424 | t | 0 | 0 |
0 | 0 | 0 | | | | | | |
| 0
ca_address_sk_index1 | x | 16431 | 0 | 0 | n | 16440 | 0 | 1
| 0 | 0 | 0 | 0 | 16424 | t | 0 | 0 |
0 | 0 | 0 | | | | | | |
| 0
(10 rows)
复制5.删除索引、表和表空间
omm=# drop index schema1.idx_products_index1;
DROP INDEX
omm=# drop index schema1.idx_products_index2;
DROP INDEX
omm=# drop index schema1.idx_products_index3;
DROP INDEX
omm=# drop index schema1.idx_products_index4;
DROP INDEX
omm=# drop table schema1.products;
DROP TABLE
omm=# drop tablespace tbs1;
DROP TABLESPACE
omm=# drop tablespace tbs2;
DROP TABLESPACE
omm=# drop tablespace tbs3;
DROP TABLESPACE
omm=# drop tablespace tbs4;
DROP TABLESPACE
复制通过学习和作业，更加深入的了解了openGauss分区表索引的相关操作。了解了索引是对数据库表中一列或多列的值进行排序的一种结构，使用索引可快速访问数据库表中的特定信息。
「喜欢这篇文章，您的关注和赞赏是给作者最好的鼓励」
关注作者
【版权声明】本文为墨天轮用户原创内容，转载时必须标注文章的来源（墨天轮），文章链接，文章作者等基本信息，否则作者和墨天轮有权追究责任。如果您发现墨天轮中有涉嫌抄袭或者侵权的内容，欢迎发送邮件至：contact@modb.pro进行举报，并提供相关证据，一经查实，墨天轮将立刻删除相关内容。
文章被以下合辑收录
评论
相关阅读
2025年3月国产数据库大事记
墨天轮编辑部
727次阅读
2025-04-03 15:21:16
内蒙古公司成功完成新一代BOSS云原生系统割接上线
openGauss
211次阅读
2025-03-24 09:40:40
openGauss 7.0.0-RC1 版本正式发布!
Gauss松鼠会
167次阅读
2025-04-01 12:27:03
openGauss 7.0.0-RC1 版本体验：一主一备快速安装指南
孙莹
149次阅读
2025-04-01 10:30:07
MogDB 发布更新，解决 openGauss 数据库在长事务情况下Ustore表膨胀问题
云和恩墨
125次阅读
2025-04-16 09:52:02
从数据库源码比较 PostgreSql和OpenGauss的启动过程
maozicb
92次阅读
2025-03-24 15:55:04
一文快速上手openGauss
进击的CJR
88次阅读
2025-03-26 16:12:54
openGauss HASH JOIN原理
lbsswhu
66次阅读
2025-03-18 10:45:01
openGauss 学习之路：集群部署实战探索
openGauss
65次阅读
2025-03-21 10:34:13
opengauss使用gs_probackup进行增量备份恢复
进击的CJR
54次阅读
2025-04-09 16:11:58
