openGauss

开源数据库

openGauss社区官网

开源社区

openGauss每日一练(分区表索引)

云和恩墨2022-04-21openGauss每日一练(分区表索引)

openGauss 每日一练(分区表索引)

本文出处:https://www.modb.pro/db/222617

学习地址

https://www.modb.pro/course/133

学习目标

学习 openGauss 分区表索引

课后作业

1.创建范围分区表 products, 为表创建分区表索引 1,不指定索引分区的名称,创建分区表索引 2,并指定索引分区的名称,创建 GLOBAL 分区索引 3

--SQL文本:
create table products
( id integer,
  name char(8)
)
partition by range (id)
(partition products_p1 values less than (10),
 partition products_p2 values less than (20),
 partition products_p3 values less than (30),
 partition products_p4 values less than (40),
 partition products_p5 values less than (50)
);
create index products_index1 on products(id) local;
create index products_index2 on products(id) local
(
partition id_index1,
partition id_index2,
partition id_index3,
partition id_index4,
partition id_index5
);
create index products_index3 on products(name) global;

omm=# create table products
omm-# ( id integer,
omm(#   name char(8)
omm(# )
omm-# partition by range (id)
omm-# (partition products_p1 values less than (10),
omm(#  partition products_p2 values less than (20),
omm(#  partition products_p3 values less than (30),
omm(#  partition products_p4 values less than (40),
omm(#  partition products_p5 values less than (50)
omm(# );
CREATE TABLE
omm=# create index products_index1 on products(id) local;
CREATE INDEX
omm=# create index products_index2 on products(id) local
omm-# (
omm(# partition id_index1,
omm(# partition id_index2,
omm(# partition id_index3,
omm(# partition id_index4,
omm(# partition id_index5
omm(# );
CREATE INDEX
omm=# create index products_index3 on products(name) global;
CREATE INDEX
omm=#

2.在分区表索引 1 上,修改分区表索引的表空间,重命名分区表索引

--SQL文本:
alter index products_index1 move partition products_p1_id_idx tablespace pgtbs1;
alter index products_index1 move partition products_p2_id_idx tablespace pgtbs1;
alter index products_index1 move partition products_p3_id_idx tablespace pgtbs1;
alter index products_index1 move partition products_p4_id_idx tablespace pgtbs1;
alter index products_index1 move partition products_p5_id_idx tablespace pgtbs1;
alter index products_index1 rename partition products_p1_id_idx to products_p1_id_idx_new;
alter index products_index1 rename partition products_p2_id_idx to products_p2_id_idx_new;
alter index products_index1 rename partition products_p3_id_idx to products_p3_id_idx_new;
alter index products_index1 rename partition products_p4_id_idx to products_p4_id_idx_new;
alter index products_index1 rename partition products_p5_id_idx to products_p5_id_idx_new;

omm=# alter index products_index1 move partition products_p1_id_idx tablespace pgtbs1;
ALTER INDEX
omm=# alter index products_index1 move partition products_p2_id_idx tablespace pgtbs1;
ALTER INDEX
omm=# alter index products_index1 move partition products_p3_id_idx tablespace pgtbs1;
ALTER INDEX
omm=# alter index products_index1 move partition products_p4_id_idx tablespace pgtbs1;
ALTER INDEX
omm=# alter index products_index1 move partition products_p5_id_idx tablespace pgtbs1;
ALTER INDEX
omm=# alter index products_index1 rename partition products_p1_id_idx to products_p1_id_idx_new;
ALTER INDEX
omm=# alter index products_index1 rename partition products_p2_id_idx to products_p2_id_idx_new;
ALTER INDEX
omm=# alter index products_index1 rename partition products_p3_id_idx to products_p3_id_idx_new;
ALTER INDEX
omm=# alter index products_index1 rename partition products_p4_id_idx to products_p4_id_idx_new;
ALTER INDEX
omm=# alter index products_index1 rename partition products_p5_id_idx to products_p5_id_idx_new;
ALTER INDEX
omm=#

3.在分区表索引 2 上,重建单个索引分区和分区上的所有索引

--SQL文本:
reindex index products_index2 partition id_index1;
reindex index products_index2 partition id_index2;
reindex index products_index2 partition id_index3;
reindex index products_index2 partition id_index4;
reindex index products_index2 partition id_index5;

omm=# reindex index products_index2 partition id_index5;
REINDEX
omm=# reindex index products_index2 partition id_index1;
REINDEX
omm=# reindex index products_index2 partition id_index2;
REINDEX
omm=# reindex index products_index2 partition id_index3;
REINDEX
omm=# reindex index products_index2 partition id_index4;
REINDEX
omm=# reindex index products_index2 partition id_index5;
REINDEX
omm=#

4.使用\d+、系统视图 pg_indexes 和 pg_partition 查看索引信息

--SQL文本:
\d+ products
select * from pg_indexes where tablename='products';
select * from pg_partition where tablename='products';

omm=# \d+ products
                          Table "public.products"
 Column |     Type     | Modifiers | Storage  | Stats target | Description
--------+--------------+-----------+----------+--------------+-------------
 id     | integer      |           | plain    |              |
 name   | character(8) |           | extended |              |
Indexes:
    "products_index1" btree (id) LOCAL(PARTITION products_p1_id_idx_new TABLESPACE pgtbs1, PARTITION products_p2_id_idx_new TABLESPACE pgtbs1, PARTITION products_p3_id_idx_new TABLESPACE pgtbs1, PARTITION products_p4_id_idx_new TABLESPACE pgtbs1, PARTITION products_p5_id_idx_new TABLESPACE pgtbs1)  TABLESPACE pg_default
    "products_index2" btree (id) LOCAL(PARTITION id_index1, PARTITION id_index2, PARTITION id_index3, PARTITION id_index4, PARTITION id_index5)  TABLESPACE pg_default
    "products_index3" btree (name) TABLESPACE pg_default
Range partition by(id)
Number of partition: 5 (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 |                                                                                                                                                                          indexde
f
------------+-----------+-----------------+------------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
---------------------------------------------------------------------------------------------------------------------------------------------------------------------------
 public     | products  | products_index1 |            | CREATE INDEX products_index1 ON products USING btree (id) LOCAL(PARTITION products_p1_id_idx_new TABLESPACE pgtbs1, PARTITION products_p2_id_idx_new TABLESPACE pgtbs1, PARTITIO
N products_p3_id_idx_new TABLESPACE pgtbs1, PARTITION products_p4_id_idx_new TABLESPACE pgtbs1, PARTITION products_p5_id_idx_new TABLESPACE pgtbs1)  TABLESPACE pg_default
 public     | products  | products_index2 |            | CREATE INDEX products_index2 ON products USING btree (id) LOCAL(PARTITION id_index1, PARTITION id_index2, PARTITION id_index3, PARTITION id_index4, PARTITION id_index5)  TABLES
PACE pg_default
 public     | products  | products_index3 |            | CREATE INDEX products_index3 ON products USING btree (name) TABLESPACE pg_default
(3 rows)

omm=# select * from pg_partition;
        relname         | parttype | parentid | rangenum | intervalnum | partstrategy | relfilenode | reltablespace | relpages | reltuples | relallvisible | reltoastrelid | reltoastidxid | indextblid | indisusable | reldeltarelid | r
eldeltaidx | relcudescrelid | relcudescidx | relfrozenxid | intspnum | partkey | intervaltablespace | interval | boundaries | transit |                    reloptions                     | relfrozenxid64
------------------------+----------+----------+----------+-------------+--------------+-------------+---------------+----------+-----------+---------------+---------------+---------------+------------+-------------+---------------+--
-----------+----------------+--------------+--------------+----------+---------+--------------------+----------+------------+---------+---------------------------------------------------+----------------
 products               | r        |    33404 |        0 |           0 | r            |           0 |             0 |        0 |         0 |             0 |             0 |             0 |          0 | t           |             0 |
         0 |              0 |            0 | 0            |          | 1       |                    |          |            |         | {orientation=row,compression=no,wait_clean_gpi=n} |              0
 products_p1            | p        |    33404 |        0 |           0 | r            |       33408 |             0 |        0 |         0 |             0 |             0 |             0 |          0 | t           |             0 |
         0 |              0 |            0 | 213746       |          |         |                    |          | {10}       |         | {orientation=row,compression=no}                  |         213746
 products_p2            | p        |    33404 |        0 |           0 | r            |       33409 |             0 |        0 |         0 |             0 |             0 |             0 |          0 | t           |             0 |
         0 |              0 |            0 | 213746       |          |         |                    |          | {20}       |         | {orientation=row,compression=no}                  |         213746
 products_p3            | p        |    33404 |        0 |           0 | r            |       33410 |             0 |        0 |         0 |             0 |             0 |             0 |          0 | t           |             0 |
         0 |              0 |            0 | 213746       |          |         |                    |          | {30}       |         | {orientation=row,compression=no}                  |         213746
 products_p4            | p        |    33404 |        0 |           0 | r            |       33411 |             0 |        0 |         0 |             0 |             0 |             0 |          0 | t           |             0 |
         0 |              0 |            0 | 213746       |          |         |                    |          | {40}       |         | {orientation=row,compression=no}                  |         213746
 products_p5            | p        |    33404 |        0 |           0 | r            |       33412 |             0 |        0 |         0 |             0 |             0 |             0 |          0 | t           |             0 |
         0 |              0 |            0 | 213746       |          |         |                    |          | {50}       |         | {orientation=row,compression=no}                  |         213746
 id_index5              | x        |    33419 |        0 |           0 | n            |       33441 |             0 |        1 |         0 |             0 |             0 |             0 |      33412 | t           |             0 |
         0 |              0 |            0 | 0            |          |         |                    |          |            |         |                                                   |              0
 products_p1_id_idx_new | x        |    33413 |        0 |           0 | n            |       33427 |         33426 |        1 |         0 |             0 |             0 |             0 |      33408 | t           |             0 |
         0 |              0 |            0 | 0            |          |         |                    |          |            |         |                                                   |              0
 products_p2_id_idx_new | x        |    33413 |        0 |           0 | n            |       33428 |         33426 |        1 |         0 |             0 |             0 |             0 |      33409 | t           |             0 |
         0 |              0 |            0 | 0            |          |         |                    |          |            |         |                                                   |              0
 products_p3_id_idx_new | x        |    33413 |        0 |           0 | n            |       33429 |         33426 |        1 |         0 |             0 |             0 |             0 |      33410 | t           |             0 |
         0 |              0 |            0 | 0            |          |         |                    |          |            |         |                                                   |              0
 products_p4_id_idx_new | x        |    33413 |        0 |           0 | n            |       33430 |         33426 |        1 |         0 |             0 |             0 |             0 |      33411 | t           |             0 |
         0 |              0 |            0 | 0            |          |         |                    |          |            |         |                                                   |              0
 products_p5_id_idx_new | x        |    33413 |        0 |           0 | n            |       33431 |         33426 |        1 |         0 |             0 |             0 |             0 |      33412 | t           |             0 |
         0 |              0 |            0 | 0            |          |         |                    |          |            |         |                                                   |              0
 id_index1              | x        |    33419 |        0 |           0 | n            |       33437 |             0 |        1 |         0 |             0 |             0 |             0 |      33408 | t           |             0 |
         0 |              0 |            0 | 0            |          |         |                    |          |            |         |                                                   |              0
 id_index2              | x        |    33419 |        0 |           0 | n            |       33438 |             0 |        1 |         0 |             0 |             0 |             0 |      33409 | t           |             0 |
         0 |              0 |            0 | 0            |          |         |                    |          |            |         |                                                   |              0
 id_index3              | x        |    33419 |        0 |           0 | n            |       33439 |             0 |        1 |         0 |             0 |             0 |             0 |      33410 | t           |             0 |
         0 |              0 |            0 | 0            |          |         |                    |          |            |         |                                                   |              0
 id_index4              | x        |    33419 |        0 |           0 | n            |       33440 |             0 |        1 |         0 |             0 |             0 |             0 |      33411 | t           |             0 |
         0 |              0 |            0 | 0            |          |         |                    |          |            |         |                                                   |              0
(16 rows)

omm=#

5.删除索引、表和表空间

--SQL文本:drop index products_index1;
drop index products_index2;
drop index products_index3;drop table products;
drop tablespace pgtbs1;

omm=# drop index products_index1;
DROP INDEX
omm=# drop index products_index2;
DROP INDEX
omm=# drop index products_index3;
DROP INDEX
omm=# drop table products;
DROP TABLE
omm=# drop tablespace pgtbs1;
DROP TABLESPACEomm=#