openGauss每日一练第10天|分区表索引

学习openGauss分区表索引

体会：和oracle一样有全局分区索引和本地分区索引。全局分区索引即在整个分区表上的索引。本地分区索引即在每个分区上创建一个小索引。


连接数据库
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.


1.创建范围分区表products, 为表创建分区表索引1，不指定索引分区的名称，创建分区表索引2，并指定索引分区的名称，创建GLOBAL分区索引3
omm=# create schema test;
omm=# CREATE SCHEMA

omm=# CREATE TABLE test.products
omm-# (
omm(# id int,pro_name varchar(20),pro_city varchar(30),pro_c1 varchar(20)
omm(# )
omm-# (
omm-# PARTITION BY RANGE(id)
omm(# PARTITION p1 VALUES LESS THAN (100),
omm(# PARTITION p2 VALUES LESS THAN (200),
omm(# PARTITION p3 VALUES LESS THAN (MAXVALUE)
omm(# );
CREATE TABLE
omm=# create index idx_pro_1 on test.products(id) local;
CREATE INDEX
omm=# CREATE INDEX idx_pro_2 ON test.products(pro_name) LOCAL
omm(# (PARTITION name_1,
omm(# PARTITION name_2 ,
omm(# PARTITION name_3
omm(# );
CREATE INDEX
omm=# CREATE INDEX idx_pro_3 ON test.products(pro_c1) GLOBAL;
CREATE INDEX


2.在分区表索引1上，修改分区表索引的表空间，重命名分区表索引
omm=# CREATE TABLESPACE tt RELATIVE LOCATION 'test_tablespace/tt';
CREATE TABLESPACE
omm=# ALTER INDEX test.idx_pro_1 MOVE partition p1_id_idx TABLESPACE tt;
ALTER INDEX
omm=# ALTER INDEX test.idx_pro_1 MOVE partition p2_id_idx TABLESPACE tt;
ALTER INDEX
omm=# ALTER INDEX test.idx_pro_1 MOVE partition p3_id_idx TABLESPACE tt;
ALTER INDEX
omm=# ALTER INDEX test.idx_pro_1 RENAME PARTITION p1_id_idx TO p11_id_idx;
ALTER INDEX
omm=# ALTER INDEX test.idx_pro_1 RENAME PARTITION p2_id_idx TO p22_id_idx;
ALTER INDEX
omm=# ALTER INDEX test.idx_pro_1 RENAME PARTITION p3_id_idx TO p33_id_idx;
ALTER INDEX


3.在分区表索引2上，重建单个索引分区和分区上的所有索引
reindex index test.idx_pro_2 PARTITION name_2;
reindex table test.products partition p1;


4.使用\d+、系统视图pg_indexes和pg_partition查看索引信息
omm=# \d+ test.products;
Table "test.products"
Column | Type | Modifiers | Storage | Stats target | Description
----------+-----------------------+-----------+----------+--------------+-------------
id | integer | | plain | |
pro_name | character varying(20) | | extended | |
pro_city | character varying(30) | | extended | |
pro_c1 | character varying(20) | | extended | |
Indexes:
"idx_pro_1" btree (id) LOCAL(PARTITION p11_id_idx TABLESPACE tt, PARTITION p22_id_idx TABLESPACE tt, PARTITION p33_id_idx TABLESPACE tt) TABLESPACE pg_default
"idx_pro_2" btree (pro_name) LOCAL(PARTITION name_1, PARTITION name_2, PARTITION name_3) TABLESPACE pg_default
"idx_pro_3" btree (pro_c1) TABLESPACE pg_default
Range partition by(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

------------+-----------+-----------+------------+-------------------------------------------------------------
---------------------------------------------------------------------------------------------------------------
-----------------------
test | products | idx_pro_1 | | CREATE INDEX idx_pro_1 ON test.products USING btree (id) LOC

test | products | idx_pro_2 | | CREATE INDEX idx_pro_2 ON test.products USING btree (pro_nam
e) LOCAL(PARTITION name_1, PARTITION name_2, PARTITION name_3) TABLESPACE pg_default
test | products | idx_pro_3 | | CREATE INDEX idx_pro_3 ON test.products USING btree (pro_c1)
TABLESPACE pg_default
AL(PARTITION p11_id_idx TABLESPACE tt, PARTITION p22_id_idx TABLESPACE tt, PARTITION p33_id_idx TABLESPACE tt)
TABLESPACE pg_default(3 rows)

omm=# select * from pg_partition;
relname | parttype | parentid | rangenum | intervalnum | partstrategy | relfilenode | reltablespace | relpa
ges | reltuples | relallvisible | reltoastrelid | reltoastidxid | indextblid | indisusable | reldeltarelid | re
ldeltaidx | relcudescrelid | relcudescidx | relfrozenxid | intspnum | partkey | intervaltablespace | interval |
boundaries | transit | reloptions | relfrozenxid64
------------+----------+----------+----------+-------------+--------------+-------------+---------------+------
----+-----------+---------------+---------------+---------------+------------+-------------+---------------+---
----------+----------------+--------------+--------------+----------+---------+--------------------+----------+
------------+---------+---------------------------------------------------+----------------
products | r | 16390 | 0 | 0 | r | 0 | 0 |
0 | 0 | 0 | 0 | 0 | 0 | t | 0 |
| | {orientation=row,compression=no,wait_clean_gpi=n} | 0
p1 | p | 16390 | 0 | 0 | r | 16394 | 0 |
0 | 0 | 0 | 0 | | 1 | | |
0 | 0 | 0 | 0 | 0 | 0 | t | 0 |
0 | 0 | 0 | 9320 | | | | |
{100} | | {orientation=row,compression=no} | 9320
p2 | p | 16390 | 0 | 0 | r | 16395 | 0 |
p3 | p | 16390 | 0 | 0 | r | 16396 | 0 |
{200} | | {orientation=row,compression=no} | 9320
0 | 0 | 0 | 0 | 0 | 0 | t | 0 |
0 | 0 | 0 | 9320 | | | | |
0 | 0 | 0 | 0 | 0 | 0 | t | 0 |
0 | 0 | 0 | 9320 | | | | |
{NULL} | | {orientation=row,compression=no} | 9320
name_3 | x | 16401 | 0 | 0 | n | 16404 | 0 |
1 | 0 | 0 | 0 | 0 | 16396 | t | 0 |
0 | 0 | 0 | 0 | | | | |
| | | 0
p22_id_idx | x | 16397 | 0 | 0 | n | 16408 | 16406 |
1 | 0 | 0 | 0 | 0 | 16395 | t | 0 |
0 | 0 | 0 | 0 | | | | |
| | | 0
p33_id_idx | x | 16397 | 0 | 0 | n | 16409 | 16406 |
1 | 0 | 0 | 0 | 0 | 16396 | t | 0 |
0 | 0 | 0 | 0 | | | | |
| | | 0
name_2 | x | 16401 | 0 | 0 | n | 16410 | 0 |
1 | 0 | 0 | 0 | 0 | 16395 | t | 0 |
0 | 0 | 0 | 0 | | | | |
| | | 0
p11_id_idx | x | 16397 | 0 | 0 | n | 16411 | 16406 |
1 | 0 | 0 | 0 | 0 | 16394 | t | 0 |
0 | 0 | 0 | 0 | | | | |
| | | 0
name_1 | x | 16401 | 0 | 0 | n | 16412 | 0 |
1 | 0 | 0 | 0 | 0 | 16394 | t | 0 |
0 | 0 | 0 | 0 | | | | |
| | | 0
(10 rows)



5.删除索引、表和表空间
omm=# drop index test.idx_pro_1;
DROP INDEX
omm=# drop index test.idx_pro_2;
DROP INDEX
omm=# drop index test.idx_pro_3;
DROP INDEX
omm=# drop table test.products;
DROP TABLE
omm=# drop schema test;
DROP SCHEMA
omm=# drop tablespace tt;
DROP TABLESPACE