云数据库 GAUSSDB-分区表相关内置工具函数:前置建表相关信息

时间：2024-11-02 18:49:17

云数据库 GAUSSDB 分区表系统视图&DFX

前置建表相关信息

前置建表：

CREATE TABLE test_range_pt (a INT, b INT, c INT)
PARTITION BY RANGE (a)
(
    PARTITION p1 VALUES LESS THAN (2000),
    PARTITION p2 VALUES LESS THAN (3000),
    PARTITION p3 VALUES LESS THAN (4000),
    PARTITION p4 VALUES LESS THAN (5000),
    PARTITION p5 VALUES LESS THAN (MAXVALUE)
)ENABLE ROW MOVEMENT;

查看分区表oid

SELECT oid FROM pg_class WHERE relname = 'test_range_pt';
  oid
-------
 49290
(1 row)

查看分区信息

SELECT oid,relname,parttype,parentid,boundaries FROM pg_partition WHERE parentid = 49290;
  oid  |    relname    | parttype | parentid | boundaries
-------+---------------+----------+----------+------------
 49293 | test_range_pt | r        |    49290 |
 49294 | p1            | p        |    49290 | {2000}
 49295 | p2            | p        |    49290 | {3000}
 49296 | p3            | p        |    49290 | {4000}
 49297 | p4            | p        |    49290 | {5000}
 49298 | p5            | p        |    49290 | {NULL}
(6 rows)

创建索引

CREATE INDEX idx_range_a ON test_range_pt(a) LOCAL;
CREATE INDEX
--查看分区索引oid
SELECT oid FROM pg_class WHERE relname = 'idx_range_a';
  oid
-------
 90250
(1 row)

查看索引分区信息

SELECT oid,relname,parttype,parentid,boundaries,indextblid FROM pg_partition WHERE parentid = 90250;
  oid  | relname  | parttype | parentid | boundaries | indextblid
-------+----------+----------+----------+------------+------------
 90255 | p5_a_idx | x        |    90250 |            |      49298
 90254 | p4_a_idx | x        |    90250 |            |      49297
 90253 | p3_a_idx | x        |    90250 |            |      49296
 90252 | p2_a_idx | x        |    90250 |            |      49295
 90251 | p1_a_idx | x        |    90250 |            |      49294
(5 rows)

上一篇：云数据库 GAUSSDB-分区表相关内置工具函数:工具函数示例

下一篇：云数据库 GAUSSDB-DQL/DML-DDL并发:DQL/DML-DDL同分区并发