学习openGauss的第十天。
主要内容是openGauss分区表索引
连接数据库
su - omm
gsql -r
1.创建范围分区表products, 为表创建分区表索引1，不指定索引分区的名称，创建分区表索引2，并指定索引分区的名称，创建GLOBAL分区索引3
omm=# create table products (id integer ,name char(20),price integer)
omm-# partition by range(id)
omm-# (
omm(# omm(# partition pro_p1 values less than(10),
omm(# partition pro_p2 values less than(20),
partition pro_p3 values less than(30)
omm(# );
CREATE TABLE
omm=# create index idx1_pro on products(id) local ;
CREATE INDEX
omm(# create index idx2_pro on products(name) local
partition name_idx1,
omm(# partition name_idx2,
omm(# partition name_idx3
omm(# ) ;
CREATE INDEX
omm=# create index idx3_pro on products(price) global ;
CREATE INDEX
2.在分区表索引1上，修改分区表索引的表空间，重命名分区表索引
omm=# create tablespace tps1 relative location 'tablespace/tps1' ;
CREATE TABLESPACE
omm=# alter index idx1_pro rename to idx1_pro1 ;
ALTER INDEX
omm=# \d
List of relations
Schema | Name | Type | Owner | Storage
--------+----------+-------+-------+----------------------------------
public | products | table | omm | {orientation=row,compression=no}
(1 row)
3.在分区表索引2上，重建单个索引分区和分区上的所有索引
omm=# alter index idx2_pro rebuild;
REINDEX
omm=# reindex table products ;
REINDEX
4.使用\d+、系统视图pg_indexes和pg_partition查看索引信息
omm=# \d+
List of relations
Schema | Name | Type | Owner | Size | Storage | Description
--------+----------+-------+-------+---------+----------------------------------+-------------
public | products | table | omm | 0 bytes | {orientation=row,compression=no} |
(1 row)
omm=# Select * from pg_indexes where tablename='products';
schemaname | tablename | indexname | tablespace |
indexdef
------------+-----------+-----------+------------+---------------------------------------------------
-----------------------------------------------------------------------------------------------------
-----
public | products | idx1_pro1 | | CREATE INDEX idx1_pro1 ON products USING btree (id
) LOCAL(PARTITION pro_p1_id_idx, PARTITION pro_p2_id_idx, PARTITION pro_p3_id_idx) TABLESPACE pg_def
ault
public | products | idx2_pro | | CREATE INDEX idx2_pro ON products USING btree (nam
e) LOCAL(PARTITION name_idx1, PARTITION name_idx2, PARTITION name_idx3) TABLESPACE pg_default
public | products | idx3_pro | | CREATE INDEX idx3_pro ON products USING btree (pri
ce) TABLESPACE pg_default
(3 rows)
omm=# Select * from pg_partition ;
relname | parttype | parentid | rangenum | intervalnum | partstrategy | relfilenode | reltable
space | relpages | reltuples | relallvisible | reltoastrelid | reltoastidxid | indextblid | indisusab
le | reldeltarelid | reldeltaidx | relcudescrelid | relcudescidx | relfrozenxid | intspnum | partkey
| intervaltablespace | interval | boundaries | transit | reloptions
| relfrozenxid64
---------------+----------+----------+----------+-------------+--------------+-------------+---------
------+----------+-----------+---------------+---------------+---------------+------------+----------
---+---------------+-------------+----------------+--------------+--------------+----------+---------
+--------------------+----------+------------+---------+---------------------------------------------
0 | 0 | 0 | 0 | 0 | 0 | 0 | t
| 0 | 0 | 0 | 0 | 0 | | 1
| | | | | {orientation=row,compression=no,wait_clean_g
pi=n} | 0
pro_p1 | p | 16389 | 0 | 0 | r | 16393 |
0 | 0 | 0 | 0 | 0 | 0 | 0 | t
| 0 | 0 | 0 | 0 | 9034 | |
| | | {10} | | {orientation=row,compression=no}
| 9034
------+----------------
products | r | 16389 | 0 | 0 | r | 0 | pro_p2 | p | 16389 | 0 | 0 | r | 16394 |
0 | 0 | 0 | 0 | 0 | 0 | 0 | t
| 0 | 0 | 0 | 0 | 9034 | |
| | | {20} | | {orientation=row,compression=no}
| 9034
pro_p3 | p | 16389 | 0 | 0 | r | 16395 |
0 | 0 | 0 | 0 | 0 | 0 | 0 | t
| 0 | 0 | 0 | 0 | 9034 | |
| | | {30} | | {orientation=row,compression=no}
| 9034
pro_p3_id_idx | x | 16396 | 0 | 0 | n | 16409 |
0 | 1 | 0 | 0 | 0 | 0 | 16395 | t
| 0 | 0 | 0 | 0 | 0 | |
| | | | |
| 0
pro_p2_id_idx | x | 16396 | 0 | 0 | n | 16410 |
0 | 1 | 0 | 0 | 0 | 0 | 16394 | t
| 0 | 0 | 0 0 | 1 | 0 | 0 | 0 | 0 | 16393 | t
| 0 | 0 | 0 | 0 | 0 | |
| | | | |
| 0
| 0 | 0 | |
| | | | |
| 0
pro_p1_id_idx | x | 16396 | 0 | 0 | n | 16411 |
name_idx3 | x | 16400 | 0 | 0 | n | 16412 |
0 | 1 | 0 | 0 | 0 | 0 | 16395 | t
| 0 | 0 | 0 | 0 | 0 | |
| | | | |
| 0
name_idx2 | x | 16400 | 0 | 0 | n | 16413 |
0 | 1 | 0 | 0 | 0 | 0 | 16394 | t
| 0 | 0 | 0 | 0 | 0 | |
| | | | |
| 0
name_idx1 | x | 16400 | 0 | 0 | n | 16414 |
0 | 1 | 0 | 0 | 0 | 0 | 16393 | t
| 0 | 0 | 0 | 0 | 0 | |
| | | | |
| 0
(10 rows)
5.删除索引、表和表空间
omm=# drop index idx1_pro1 ;
DROP INDEX
omm=# drop index idx2_pro ;
omm=# DROP INDEX
omm=# drop index idx3_pro ;
DROP INDEX
omm=# Drop table products ;
DROP TABLE
omm=# drop tablespace tps1 ;
DROP TABLESPACE
