学习感受
继续学习，坚持就是胜利！
渐入佳境，有些上瘾的感觉，学习起来一点也不枯燥！！
学习目标
学习openGauss分区表索引
课后作业
连接openGauss
#第一次进入等待15秒
#数据库启动中…
su - omm
gsql -r
1.创建范围分区表products, 为表创建分区表索引1，不指定索引分区的名称，创建分区表索引2，并指定索引分区的名称，创建GLOBAL分区索引3
CREATE TABLESPACE example1 RELATIVE LOCATION 'tablespace1/tablespace_1';
CREATE TABLESPACE example2 RELATIVE LOCATION 'tablespace2/tablespace_2';
CREATE TABLESPACE example3 RELATIVE LOCATION 'tablespace3/tablespace_3';
CREATE TABLESPACE example4 RELATIVE LOCATION 'tablespace4/tablespace_4';
create schema tpcds;
CREATE TABLE tpcds.products
( product_id integer,
product_name char(30),
category char(20)
)
PARTITION BY RANGE(product_id)
(
PARTITION p1 VALUES LESS THAN (100),
PARTITION p2 VALUES LESS THAN (500) TABLESPACE example1,
PARTITION p3 VALUES LESS THAN (MAXVALUE) TABLESPACE example2
);
CREATE INDEX products_index1 ON tpcds.products(product_id) LOCAL;
CREATE INDEX products_index2 ON
tpcds.products (product_id) LOCAL
(
PARTITION products_id_index1,
PARTITION products_id_index2 TABLESPACE example3,
PARTITION products_id_index3 TABLESPACE example4
);
CREATE INDEX products_index3 ON tpcds.products(product_name) GLOBAL;
2.在分区表索引1上，修改分区表索引的表空间，重命名分区表索引
ALTER INDEX tpcds.products_index2 MOVE PARTITION
products_id_index3 TABLESPACE example2;
ALTER INDEX tpcds.products_index2 RENAME PARTITION
products_id_index1 TO products_id_index4;
3.在分区表索引2上，重建单个索引分区和分区上的所有索引
reindex index tpcds.products_index2 PARTITION products_id_index3;
reindex table tpcds.products PARTITION p2;
4.使用\d+、系统视图pg_indexes和pg_partition查看索引信息
\d+ tpcds.products;
select * from pg_indexes where tablename = 'products';
select * from pg_partition;
5.删除索引、表和表空间
DROP INDEX tpcds.products_index1;
DROP INDEX tpcds.products_index2;
DROP INDEX tpcds.products_index3;
\d+ tpcds.products
drop table tpcds.products;
DROP SCHEMA tpcds CASCADE;
drop tablespace example1;
drop tablespace example2;
drop tablespace example3;
drop tablespace example4;
执行演示代码如下：
omm=# CREATE TABLESPACE example1 RELATIVE LOCATION 'tablespace1/tablespace_1';
CREATE TABLESPACE
omm=# CREATE TABLESPACE example2 RELATIVE LOCATION 'tablespace2/tablespace_2';
CREATE TABLESPACE
omm=# CREATE TABLESPACE example3 RELATIVE LOCATION 'tablespace3/tablespace_3';
CREATE TABLESPACE
omm=# CREATE TABLESPACE example4 RELATIVE LOCATION 'tablespace4/tablespace_4';
CREATE TABLESPACE
omm=#
omm=#
omm=# create schema tpcds;
CREATE SCHEMA
omm=# CREATE TABLE tpcds.products
omm-# ( product_id integer,
omm(# product_name char(30),
omm(# category char(20)
omm(# )
omm-# PARTITION BY RANGE(product_id)
omm-# (
omm(# omm(# PARTITION p1 VALUES LESS THAN (100),
PARTITION p2 VALUES LESS THAN (500) TABLESPACE example1,
omm(# PARTITION p3 VALUES LESS THAN (MAXVALUE) TABLESPACE example2
omm(# );
CREATE TABLE
omm=#
omm=#
omm=# CREATE INDEX products_index1 ON tpcds.products(product_id) LOCAL;
CREATE INDEX
omm=# omm-# CREATE INDEX products_index2 ON
tpcds.products (product_id) LOCAL
omm-# (
omm(# PARTITION products_id_index1,
omm(# PARTITION products_id_index2 TABLESPACE example3,
omm(# omm(# PARTITION products_id_index3 TABLESPACE example4
);
CREATE INDEX
omm=# CREATE INDEX products_index3 ON tpcds.products(product_name) GLOBAL;
CREATE INDEX
omm=#
omm=#
omm=# omm=#
omm=# products_id_index3 TABLESPACE example2;
ALTER INDEX tpcds.products_index2 MOVE PARTITION
omm-# ALTER INDEX
omm=#
omm=# ALTER INDEX tpcds.products_index2 RENAME PARTITION
omm-# products_id_index1 TO products_id_index4;
ALTER INDEX
omm=#
omm=# reindex index tpcds.products_index2 PARTITION products_id_index3;
REINDEX
omm=#
omm=# reindex table tpcds.products PARTITION p2;
REINDEX
omm=#
omm=# \d+ tpcds.products;
Table "tpcds.products"
Column | Type | Modifiers | Storage | Stats target | Description
--------------+---------------+-----------+----------+--------------+-------------
product_id | integer | | plain | |
product_name | character(30) | | extended | |
category | character(20) | | extended | |
Indexes:
"products_index1" btree (product_id) LOCAL(PARTITION p1_product_id_idx, PARTITION p2_product_id_idx, PARTITION p3_product_id_idx) TABLESPACE pg_default
"products_index2" btree (product_id) LOCAL(PARTITION products_id_index4, PARTITION products_id_index2 TABLESPACE example3, PARTITION products_id_index3 TABLESPACE example2) TABLESPACE pg_default
"products_index3" btree (product_name) TABLESPACE pg_default
Range partition by(product_id)
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
------------+-----------+-----------------+------------+-----------------------------------------
-------------------------------------------------------------------------------------------------
----------------------------------------------------------------------------------------------
tpcds | products | products_index1 | | CREATE INDEX products_index1 ON tpcds.pr
oducts USING btree (product_id) LOCAL(PARTITION p1_product_id_idx, PARTITION p2_product_id_idx, P
ARTITION p3_product_id_idx) TABLESPACE pg_default
tpcds | products | products_index2 | | CREATE INDEX products_index2 ON tpcds.pr
oducts USING btree (product_id) LOCAL(PARTITION products_id_index4, PARTITION products_id_index2
TABLESPACE example3, PARTITION products_id_index3 TABLESPACE example2) TABLESPACE pg_default
tpcds | products | products_index3 | | CREATE INDEX products_index3 ON tpcds.pr
oducts USING btree (product_name) TABLESPACE pg_default
(3 rows)
omm=# select * from pg_partition;
relname | parttype | parentid | rangenum | intervalnum | partstrategy | relfilenode |
reltablespace | relpages | reltuples | relallvisible | reltoastrelid | reltoastidxid | indextbli
d | indisusable | reldeltarelid | reldeltaidx | relcudescrelid | relcudescidx | relfrozenxid | in
-------+---------+--------------------+----------+------------+---------+------------------------
---------------------------+----------------
products | r | 16479 | 0 | 0 | r | 0 |
0 | 0 | 0 | 0 | 0 | 0 |
0 | t | 0 | 0 | 0 | 0 | 0 |
tspnum | partkey | intervaltablespace | interval | boundaries | transit | relo
ptions | relfrozenxid64
--------------------+----------+----------+----------+-------------+--------------+-------------+
---------------+----------+-----------+---------------+---------------+---------------+----------
--+-------------+---------------+-------------+----------------+--------------+--------------+---
ssion=no,wait_clean_gpi=n} | 0
p1 | p | 16479 | 0 | 0 | r | 16483 |
| 1 | | | | | {orientation=row,compre
0 | 0 | 0 | 0 | 0 | 0 |
0 | t | 0 | 0 | 0 | 0 | 9383 |
| | | | {100} | | {orientation=row,compre
ssion=no} | 9383
p2 | p | 16479 | 0 | 0 | r | 16484 |
16474 | 0 | 0 | 0 | 0 | 0 |
0 | t | 0 | 0 | 0 | 0 | 9383 |
| | | | {500} | | {orientation=row,compre
ssion=no} | 9383
p3 | p | 16479 | 0 | 0 | r | 16485 |
16475 | 0 | 0 | 0 | 0 | 0 |
0 | t | 0 | 0 | 0 | 0 | 9383 |
| | | | {NULL} | | {orientation=row,compre
ssion=no} | 9383
p1_product_id_idx | x | 16486 | 0 | 0 | n | 16487 |
0 | 1 | 0 | 0 | 0 | 0 | 1648
3 | t | 0 | 0 | 0 | 0 | 0 |
| | | | | |
| 0
p3_product_id_idx | x | 16486 | 0 | 0 | n | 16489 |
0 | 1 | 0 | 0 | 0 | 0 | 1648
5 | t | 0 | 0 | 0 | 0 | 0 |
| | | | | |
| 0
products_id_index4 | x | 16490 | 0 | 0 | n | 16491 |
0 | 1 | 0 | 0 | 0 | 0 | 1648
3 | t | 0 | 0 | 0 | 0 | 0 |
| | | | | |
| 0
products_id_index3 | x | 16490 | 0 | 0 | n | 16496 |
16475 | 1 | 0 | 0 | 0 | 0 | 1648
5 | t | 0 | 0 | 0 | 0 | 0 |
| | | | | |
| 0
p2_product_id_idx | x | 16486 | 0 | 0 | n | 16497 |
0 | 1 | 0 | 0 | 0 | 0 | 1648
4 | t | 0 | 0 | 0 | 0 | 0 |
| | | | | |
| 0
products_id_index2 | x | 16490 | 0 | 0 | n | 16498 |
16476 | 1 | 0 | 0 | 0 | 0 | 1648
4 | t | 0 | 0 | 0 | 0 | 0 |
| | | | | |
| 0
(10 rows)
omm=#
omm=#
omm=# DROP INDEX tpcds.products_index1;
DROP INDEX
omm=# DROP INDEX tpcds.products_index2;
DROP INDEX
omm=# DROP INDEX tpcds.products_index3;
DROP INDEX
omm=#
omm=# \d+ tpcds.products
Table "tpcds.products"
Column | Type | Modifiers | Storage | Stats target | Description
--------------+---------------+-----------+----------+--------------+-------------
product_id | integer | | plain | |
product_name | character(30) | | extended | |
category | character(20) | | extended | |
Range partition by(product_id)
Number of partition: 3 (View pg_partition to check each partition range.)
Has OIDs: no
Options: orientation=row, compression=no
omm=#
omm=# drop table tpcds.products;
DROP TABLE
omm=# DROP SCHEMA tpcds CASCADE;
DROP SCHEMA
drop tablespace example1;
omm=# DROP TABLESPACE
omm=# drop tablespace example2;
DROP TABLESPACE
omm=# drop tablespace example3;
DROP TABLESPACE
omm=# drop tablespace example4;
DROP TABLESPACE
最后修改时间：2021-12-22 14:48:49
「喜欢这篇文章，您的关注和赞赏是给作者最好的鼓励」
关注作者
【版权声明】本文为墨天轮用户原创内容，转载时必须标注文章的来源（墨天轮），文章链接，文章作者等基本信息，否则作者和墨天轮有权追究责任。如果您发现墨天轮中有涉嫌抄袭或者侵权的内容，欢迎发送邮件至：contact@modb.pro进行举报，并提供相关证据，一经查实，墨天轮将立刻删除相关内容。
