暂无图片
暂无图片
暂无图片
暂无图片
暂无图片

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

原创 手机用户2761 2021-12-20
513

分区索引由一组索引分区构成,每个索引分区都包含单一数据分区的索引条目。
每个索引分区都只包含对相应数据分区中的数据的引用。
系统生成的索引和用户生成的索引都可以是分区索引。

0. 进入系统

su - omm
gsql -r

1.创建范围分区表products,

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 TABLESPACE

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

create table products (
product_id integer,
product_name char(30),
product_sk char(20),
inventory integer
)
PARTITION BY RANGE(product_id)
(
PARTITION p1 VALUES LESS THAN (3000),
PARTITION p2 VALUES LESS THAN (5000) TABLESPACE example1,
PARTITION p3 VALUES LESS THAN (MAXVALUE) TABLESPACE example2
);
  • 回显
CREATE TABLE
CREATE INDEX p1_index_1 ON products(product_id) LOCAL;

CREATE INDEX index_2 ON products(product_id) LOCAL
(
PARTITION p1_index2,
PARTITION p2_index2 TABLESPACE example3,
PARTITION p3_index2 TABLESPACE example4
);

CREATE INDEX p1_index3 ON products(product_name) GLOBAL;
  • 回显
CREATE INDEX

GLOBAL LOCAL index 不能在同一列上, 不然会出错!

CREATE INDEX p1_index3 ON products(product_id) GLOBAL;
  • 回显
ERROR:  Global and local partition index should not be on same column

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

alter index index_2 MOVE PARTITION p2_index2 tablespace example3;
  • 回显
ALTER INDEX

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

reindex index index_2 partition p2_index2 ;
reindex table products PARTITION p2;
  • 回显
REINDEX

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

\d+
  • 回显
                                      List of relations
 Schema |   Name   | Type  | Owner |  Size   |             Storage              | Description
--------+----------+-------+-------+---------+----------------------------------+-------------
 public | products | table | omm   | 0 bytes | {orientation=row,compression=no} |
(1 row)
select * from pg_indexes;
  • 回显
 public     | products                      | p1_index_1                                    |            | CREATE INDEX p1_ind
ex_1 ON products USING btree (product_id) LOCAL(PARTITION p1_product_id_idx, PARTITION p2_product_id_idx, PARTITION p3_product
_id_idx)  TABLESPACE pg_default
2 ON products USING btree (product_id) LOCAL(PARTITION p1_index2, PARTITION p2_index2 TABLESPACE example3, PARTITION p3_index2
 TABLESPACE example4)  TABLESPACE pg_default
 public     | products                      | index_2                                       |            | CREATE INDEX index_
 public     | products                      | p1_index3                                     |            | CREATE INDEX p1_ind
ex3 ON products USING btree (product_name) TABLESPACE pg_default
select * from pg_partition;
  • 回显
 products          | r        |    16393 |        0 |           0 | r            |           0 |             0 |        0 |
      0 |             0 |             0 |             0 |          0 | t           |             0 |           0 |
----+--------------+--------------+----------+---------+--------------------+----------+------------+---------+---------------
------------------------------------+----------------
  0 |            0 | 0            |          | 1       |                    |          |            |         | {orientation=r
ow,compression=no,wait_clean_gpi=n} |              0
 p1                | p        |    16393 |        0 |           0 | r            |       16397 |             0 |        0 |
      0 |             0 |             0 |             0 |          0 | t           |             0 |           0 |
 p2                | p        |    16393 |        0 |           0 | r            |       16398 |         16389 |        0 |
      0 |             0 |             0 |             0 |          0 | t           |             0 |           0 |
  0 |            0 | 9320         |          |         |                    |          | {5000}     |         | {orientation=r
ow,compression=no}                  |           9320
  0 |            0 | 9320         |          |         |                    |          | {3000}     |         | {orientation=r
ow,compression=no}                  |           9320
 p3                | p        |    16393 |        0 |           0 | r            |       16399 |         16390 |        0 |
  0 |            0 | 9320         |          |         |                    |          | {NULL}     |         | {orientation=r
-      0 |             0 |             0 |             0 |          0 | t           |             0 |           0 |
ow,compression=no}                  |           9320
 p1_product_id_idx | x        |    16400 |        0 |           0 | n            |       16401 |             0 |        1 |
      0 |             0 |             0 |             0 |      16397 | t           |             0 |           0 |
  0 |            0 | 0            |          |         |                    |          |            |         |
                                    |              0
 p2_product_id_idx | x        |    16400 |        0 |           0 | n            |       16402 |             0 |        1 |
      0 |             0 |             0 |             0 |      16398 | t           |             0 |           0 |
  0 |            0 | 0            |          |         |                    |          |            |         |
                                    |              0
 p3_product_id_idx | x        |    16400 |        0 |           0 | n            |       16403 |             0 |        1 |
      0 |             0 |             0 |             0 |      16399 | t           |             0 |           0 |
  0 |            0 | 0            |          |         |                    |          |            |         |
                                    |              0
 p3_index2         | x        |    16404 |        0 |           0 | n            |       16409 |         16392 |        1 |
      0 |             0 |             0 |             0 |      16399 | t           |             0 |           0 |
  0 |            0 | 0            |          |         |                    |          |            |         |
                                    |              0
 p1_index2         | x        |    16404 |        0 |           0 | n            |       16411 |             0 |        1 |
      0 |             0 |             0 |             0 |      16397 | t           |             0 |           0 |
  0 |            0 | 0            |          |         |                    |          |            |         |
                                    |              0
 p2_index2         | x        |    16404 |        0 |           0 | n            |       16412 |         16391 |        1 |
      0 |             0 |             0 |             0 |      16398 | t           |             0 |           0 |
  0 |            0 | 0            |          |         |                    |          |            |         |

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

DROP INDEX index_1;
DROP INDEX index_2;
DROP INDEX index_3;
DROP TABLE products;
DROP TABLESPACE example1;
DROP TABLESPACE example2;
DROP TABLESPACE example3;
DROP TABLESPACE example4;
「喜欢这篇文章,您的关注和赞赏是给作者最好的鼓励」
关注作者
【版权声明】本文为墨天轮用户原创内容,转载时必须标注文章的来源(墨天轮),文章链接,文章作者等基本信息,否则作者和墨天轮有权追究责任。如果您发现墨天轮中有涉嫌抄袭或者侵权的内容,欢迎发送邮件至:contact@modb.pro进行举报,并提供相关证据,一经查实,墨天轮将立刻删除相关内容。

评论