openGauss每日一练第10天 | 学习心得体会

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

create table products (product_id int,product_name char(50),sale_date date)
partition by range(sale_date)
(partition p1 values less than (to_date('2021-03-01','yyyy-mm-dd')),
partition p2 values less than (to_date('2021-06-01','yyyy-mm-dd')),
partition p3 values less than (to_date('2021-09-01','yyyy-mm-dd')),
partition p4 values less than (to_date('2021-12-01','yyyy-mm-dd')));

create index products_sale_date_idx1 on products(sale_date)  local;

create index products_sale_date_idx2 on products(sale_date)  local
(
partition idx1_p1,
partition idx1_p2,
partition idx1_p3,
partition idx1_p4);

create index products_sale_date_idx3 on products(product_id)  global;

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

create tablespace example relative location 'tablespace/example';
alter index products_sale_date_idx1 move partition p1_sale_date_idx tablespace example;
alter index products_sale_date_idx1  rename partition p2_sale_date_idx to p5_sale_date_idx;

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

alter index products_sale_date_idx2 rebuild partition idx1_p1;
reindex table products partition p1;

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

\d+ products
                             Table "public.products"
    Column    |     Type      | Modifiers | Storage  | Stats target | Description 
--------------+---------------+-----------+----------+--------------+-------------
 product_id   | integer       |           | plain    |              | 
 product_name | character(50) |           | extended |              | 
 sale_date    | date          |           | plain    |              | 
Indexes:
    "products_sale_date_idx1" btree (sale_date) LOCAL(PARTITION p1_sale_date_idx TABLESPACE example, PARTITION p5_sale_date_idx, PARTITION p3_sale_date_idx, PARTITION p4_sale_date_idx)  TABLESPACE pg_default
    "products_sale_date_idx2" btree (sale_date) LOCAL(PARTITION idx1_p1, PARTITION idx1_p2, PARTITION idx1_p3, PARTITION idx1_p4)  TABLESPACE pg_default
    "products_sale_date_idx3" btree (product_id) TABLESPACE pg_default
Range partition by(sale_date)
Number of partition: 4 (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                                                      
                                                           
------------+-----------+-------------------------+------------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
-----------------------------------------------------------
 public     | products  | products_sale_date_idx1 |            | CREATE INDEX products_sale_date_idx1 ON products USING btree (sale_date) LOCAL(PARTITION p1_sale_date_idx TABLESPACE example, PARTITION p5_sale_date_idx, PARTITION p3_sale_da
te_idx, PARTITION p4_sale_date_idx)  TABLESPACE pg_default
 public     | products  | products_sale_date_idx2 |            | CREATE INDEX products_sale_date_idx2 ON products USING btree (sale_date) LOCAL(PARTITION idx1_p1, PARTITION idx1_p2, PARTITION idx1_p3, PARTITION idx1_p4)  TABLESPACE pg_defa
ult
 public     | products  | products_sale_date_idx3 |            | CREATE INDEX products_sale_date_idx3 ON products USING btree (product_id) TABLESPACE pg_default
(3 rows)

omm=# select relname,parttype,reltablespace from pg_partition;
     relname      | parttype | reltablespace 
------------------+----------+---------------
 products         | r        |             0
 p1               | p        |             0
 p2               | p        |             0
 p3               | p        |             0
 p4               | p        |             0
 p3_sale_date_idx | x        |             0
 p4_sale_date_idx | x        |             0
 idx1_p2          | x        |             0
 idx1_p3          | x        |             0
 idx1_p4          | x        |             0
 p5_sale_date_idx | x        |             0
 p1_sale_date_idx | x        |         16409
 idx1_p1          | x        |             0
(13 rows)

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

drop index products_sale_date_idx1;
drop index products_sale_date_idx2;
drop index products_sale_date_idx3;
drop table products;
drop tablespace example;