#opengauss的第10天
创建范围分区表products, 为表创建分区表索引1，不指定索引分区的名称，创建分区表索引2，并指定索引分区的名称，创建GLOBAL分区索引3
create table products(id bigint,name char(40),crt_time timestamp without time zone) partition by range(crt_time)
omm-# (partition p1 values less than(‘2021-12-01 00:00:00’),
omm(# partition p2 values less than (‘2022-01-01 00:00:00’),
omm(# partition p3 values less than (‘2022-02-01 00:00:00’),
omm(# partition p4 values less than (maxvalue));
CREATE TABLE
omm=# CREATE INDEX products_p1_index1 ON products(crt_time) LOCAL;
CREATE INDEX
omm=# CREATE INDEX products_p1_index2 ON
omm-# products(crt_time) LOCAL
omm-# (
omm(# PARTITION crt_time_index1,
omm(# PARTITION crt_time_index2 ,
omm(# PARTITION crt_time_index3,
omm(# PARTITION crt_time_index4);
CREATE INDEX
omm=# CREATE INDEX products_p1_index3 ON products(name) GLOBAL;
CREATE INDEX
在分区表索引1上，修改分区表索引的表空间，重命名分区表索引
omm=# create tablespace tbs1 relative location ‘tbs1’;
CREATE TABLESPACE
omm=# \dn
List of schemas
Name | Owner
-------------±------
cstore | omm
dbe_perf | omm
pkg_service | omm
public | omm
snapshot | omm
omm=# ALTER INDEX products_p1_index2 MOVE PARTITION
omm-# crt_time_index1 TABLESPACE tbs1;
ALTER INDEX
omm=# ALTER INDEX products_p1_index2 RENAME PARTITION
omm-# crt_time_index1 TO crt_time_index5;
ALTER INDEX
omm=# \d products
Table “public.products”
Column | Type | Modifiers
----------±----------------------------±----------
id | bigint |
name | character(40) |
crt_time | timestamp without time zone |
Indexes:
“products_p1_index1” btree (crt_time) LOCAL(PARTITION p1_crt_time_idx, PARTITION p2_crt_time_idx, PARTITION p3_crt_time_idx, PARTITION p4_crt_time_idx) TABLESPACE pg_default
“products_p1_index2” btree (crt_time) LOCAL(PARTITION crt_time_index5 TABLESPACE tbs1, PARTITION crt_time_index2, PARTITION crt_time_index3, PARTITION crt_time_index4) TABLESPACE pg_default
“products_p1_index3” btree (name) TABLESPACE pg_default
Range partition by(crt_time)
Number of partition: 4 (View pg_partition to check each partition range.)
在分区表索引2上，重建单个索引分区和分区上的所有索引
omm=# reindex index products_p1_index2 PARTITION crt_time_index2;
REINDEX
omm=# reindex table products PARTITION p1;
REINDEX
使用\d+、系统视图pg_indexes和pg_partition查看索引信息
omm=# \d+ products
Table “public.products”
Column | Type | Modifiers | Storage | Stats target | Description
omm=# \d+ products
Table “public.products”
Column | Type | Modifiers | Storage | Stats target | Description
----------±----------------------------±----------±---------±-------------±------------
id | bigint | | plain | |
name | character(40) | | extended | |
crt_time | timestamp without time zone | | plain | |
Indexes:
Number of partition: 4 (View pg_partition to check each partition range.)
Has OIDs: no
Options: orientation=row, compression=no
omm=# “products_p1_index1” btree (crt_time) LOCAL(PARTITION p1_crt_time_idx, PARTITION p2_crt_time_idx, PARTITION p3_crt_time_idx, PARTITION p4_crt_time_idx) TABLESPACE pg_default
“products_p1_index2” btree (crt_time) LOCAL(PARTITION crt_time_index5 TABLESPACE tbs1, PARTITION crt_time_index2, PARTITION crt_time_index3, PARTITION crt_time_index4) TABLESPACE pg_default
“products_p1_index3” btree (name) TABLESPACE pg_default
Range partition by(crt_time)
omm=# \x
Expanded display is on.
omm=# select * from pg_indexes where tablename=‘products’;
-[ RECORD 1 ]---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
schemaname | public
tablename | products
indexname | products_p1_index1
tablespace |
indexdef | CREATE INDEX products_p1_index1 ON products USING btree (crt_time) LOCAL(PARTITION p1_crt_time_idx, PARTITION p2_crt_time_idx, PARTITION p3_crt_time_idx, PARTITION p4_crt_time_idx) TABLESPACE pg_default
-[ RECORD 2 ]---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
schemaname | public
tablename | products
indexname | products_p1_index2
tablespace |
indexdef | CREATE INDEX products_p1_index2 ON products USING btree (crt_time) LOCAL(PARTITION crt_time_index5 TABLESPACE tbs1, PARTITION crt_time_index2, PARTITION crt_time_index3, PARTITION crt_time_index4) TABLESPACE pg_default
-[ RECORD 3 ]---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
schemaname | public
tablename | products
indexname | products_p1_index3
tablespace |
omm=# indexdef | CREATE INDEX products_p1_index3 ON products USING btree (name) TABLESPACE pg_default
select * from pg_partition where relname =‘products’;
-[ RECORD 1 ]------±-------------------------------------------------
relname | products
parttype | r
parentid | 16397
rangenum | 0
intervalnum | 0
relfrozenxid | 0
intspnum |
partkey | 3
–More–partstrategy | r
relfilenode | 0
reltablespace | 0
relpages | 0
reltuples | 0
relallvisible | 0
reltoastrelid | 0
reltoastidxid | 0
indextblid | 0
indisusable | t
reldeltarelid | 0
reldeltaidx | 0
relcudescrelid | 0
relcudescidx | 0
intervaltablespace |
interval |
boundaries |
transit |
reloptions | {orientation=row,compression=no,wait_clean_gpi=n}
删除索引、表和表空间
omm=# \d products
Table “public.products”
Column | Type | Modifiers
----------±----------------------------±----------
id | bigint |
“products_p1_index1” btree (crt_time) LOCAL(PARTITION p1_crt_time_idx, PARTITION p2_crt_time_idx, PARTITION p3_crt_time_idx, PARTITION p4_crt_time_idx) TABLESPACE pg_default
“products_p1_index2” btree (crt_time) LOCAL(PARTITION crt_time_index5 TABLESPACE tbs1, PARTITION crt_time_index2, PARTITION crt_time_index3, PARTITION crt_time_index4) TABLESPACE pg_default
“products_p1_index3” btree (name) TABLESPACE pg_default
Range partition by(crt_time)
Number of partition: 4 (View pg_partition to check each partition range.)
omm=#
name | character(40) |
crt_time | timestamp without time zone |
Indexes:
omm=#
omm=#
omm=#
omm=# drop index products_p1_index1;
DROP INDEX
omm=# drop index products_p1_index2;
DROP INDEX
omm=# drop index products_p1_index3;
DROP INDEX
omm=# drop tablespace tbs1;
DROP TABLESPACE
omm=# \d products
Table “public.products”
Column | Type | Modifiers
----------±----------------------------±----------
id | bigint |
name | character(40) |
crt_time | timestamp without time zone |
Range partition by(crt_time)
Number of partition: 4 (View pg_partition to check each partition range.)
