暂无图片
暂无图片
2
暂无图片
暂无图片
暂无图片
首页 / openGauss每日一练第 10 天 | 分区表索引管理及课后作业

openGauss每日一练第 10 天 | 分区表索引管理及课后作业

原创 JiekeXu All China Database Union 2021-12-13
1234

openGauss 每日一练第 10 天学习打卡，巩固 openGauss 数据库分区表索引管理基本操作！

学习目标

学习 openGauss 分区表的索引管理

前面每日一练链接：

openGauss每日一练第1天 | 数据库和表的基本操作（一）
openGauss每日一练第2天 | 数据库和表的基本操作（二）
openGauss每日一练第3天 | 前三课作业实操练习
openGauss每日一练第4天 | 角色管理及课后作业
openGauss每日一练第5天 | 用户管理及课后作业
openGauss每日一练第6天 | 模式管理及课后作业
openGauss每日一练第7天 | 表空间管理及课后作业
openGauss每日一练第8天 | 分区表管理及课后作业
openGauss每日一练第9天 | 普通表索引管理及课后作业

课程学习

索引是对数据库表中一列或多列的值进行排序的一种结构，使用索引可快速访问数据库表中的特定信息。

注意：在启动 gsql 的命令行中加上“-E”参数，就可以把 gsql 中各种以“\”开头的命令执行的实际 SQL 语句打印出来.如果在已运行的 gsql 中显示了某个命令实际执行的 SQL 语句后又想关闭此功能，该怎么办？这时可以使用“\set ECHO_HIDDEN on|off”命令。

连接 openGauss

su - omm
gsql -r

gsql -r -h localhost -U user1 -W JiekeXu12 -d postgres

1.创建分区表索引

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 schema tpcds;

CREATE TABLE tpcds.customer_address_p1
(
CA_ADDRESS_SK INTEGER NOT NULL,
CA_ADDRESS_ID CHAR(16) NOT NULL,
CA_STREET_NUMBER CHAR(10) ,
CA_STREET_NAME VARCHAR(60) ,
CA_STREET_TYPE CHAR(15) ,
CA_SUITE_NUMBER CHAR(10) ,
CA_CITY VARCHAR(60) ,
CA_COUNTY VARCHAR(30) ,
CA_STATE CHAR(2) ,
CA_ZIP CHAR(10) ,
CA_COUNTRY VARCHAR(20) ,
CA_GMT_OFFSET DECIMAL(5,2) ,
CA_LOCATION_TYPE CHAR(20)
)
PARTITION BY RANGE(CA_ADDRESS_SK)
(
PARTITION p1 VALUES LESS THAN (3000),
PARTITION p2 VALUES LESS THAN (5000) TABLESPACE example1,
PARTITION p3 VALUES LESS THAN (MAXVALUE) TABLESPACE example2
);

图片.png

–创建分区表索引 ds_customer_address_p1_index1，不指定索引分区的名称

CREATE INDEX ds_customer_address_p1_index1 ON tpcds.customer_address_p1(CA_ADDRESS_SK) LOCAL;

图片.png

–创建分区表索引 ds_customer_address_p1_index2，并指定索引分区的名称。

CREATE INDEX ds_customer_address_p1_index2 ON
tpcds.customer_address_p1(CA_ADDRESS_SK) LOCAL
(
PARTITION CA_ADDRESS_SK_index1,
PARTITION CA_ADDRESS_SK_index2 TABLESPACE example3,
PARTITION CA_ADDRESS_SK_index3 TABLESPACE example4
);

图片.png

–创建 GLOBAL 分区索引

CREATE INDEX ds_customer_address_p1_index3 ON tpcds.customer_address_p1(CA_ADDRESS_ID) GLOBAL;

图片.png

–不指定关键字，默认创建 GLOBAL 分区索引

CREATE INDEX ds_customer_address_p1_index4 ON tpcds.customer_address_p1(CA_ADDRESS_ID);

图片.png

–查看索引信息

\d+ tpcds.customer_address_p1;
select * from pg_indexes where tablename = 'customer_address_p1';
select * from pg_partition;

jiekexu=# \d+ tpcds.customer_address_p1
                              Table "tpcds.customer_address_p1"
      Column      |         Type          | Modifiers | Storage  | Stats target | Description 
------------------+-----------------------+-----------+----------+--------------+-------------
 ca_address_sk    | integer               | not null  | plain    |              | 
 ca_address_id    | character(16)         | not null  | extended |              | 
 ca_street_number | character(10)         |           | extended |              | 
 ca_street_name   | character varying(60) |           | extended |              | 
 ca_street_type   | character(15)         |           | extended |              | 
 ca_suite_number  | character(10)         |           | extended |              | 
 ca_city          | character varying(60) |           | extended |              | 
 ca_county        | character varying(30) |           | extended |              | 
 ca_state         | character(2)          |           | extended |              | 
 ca_zip           | character(10)         |           | extended |              | 
 ca_country       | character varying(20) |           | extended |              | 
 ca_gmt_offset    | numeric(5,2)          |           | main     |              | 
 ca_location_type | character(20)         |           | extended |              | 
Indexes:
    "ds_customer_address_p1_index1" btree (ca_address_sk) LOCAL(PARTITION p1_ca_address_sk_idx, PARTITION p2_ca_address_sk_idx, PARTITION p3_ca_address_sk_idx)  TABLESP
ACE pg_default
    "ds_customer_address_p1_index2" btree (ca_address_sk) LOCAL(PARTITION ca_address_sk_index1, PARTITION ca_address_sk_index2 TABLESPACE example3, PARTITION ca_address
_sk_index3 TABLESPACE example4)  TABLESPACE pg_default
    "ds_customer_address_p1_index3" btree (ca_address_id) TABLESPACE pg_default
    "ds_customer_address_p1_index4" btree (ca_address_id) TABLESPACE pg_default
Range partition by(ca_address_sk)
Number of partition: 3 (View pg_partition to check each partition range.)
Has OIDs: no
Options: orientation=row, compression=no

select schemaname,tablename,indexname,tablespace from pg_indexes where tablename = 'customer_address_p1';

select relname,parttype,parentid,rangenum,reltablespace from pg_partition;

图片.png

2.修改分区表索引定义

–修改分区表索引 CA_ADDRESS_SK_index2 的表空间为 example1

ALTER INDEX tpcds.ds_customer_address_p1_index2 MOVE PARTITION
CA_ADDRESS_SK_index2 TABLESPACE example1;

图片.png

–修改分区表索引 CA_ADDRESS_SK_index3 的表空间为example2

ALTER INDEX tpcds.ds_customer_address_p1_index2 MOVE PARTITION
CA_ADDRESS_SK_index3 TABLESPACE example2;

图片.png

–重命名分区表索引

ALTER INDEX tpcds.ds_customer_address_p1_index2 RENAME PARTITION
CA_ADDRESS_SK_index1 TO CA_ADDRESS_SK_index4;

图片.png

3.重建索引分区

图片.png

–重建单个索引分区

reindex index tpcds.ds_customer_address_p1_index1 PARTITION p1_ca_address_sk_idx;

–重建分区上的所有索引

reindex table tpcds.customer_address_p1 PARTITION p1;

图片.png

4.删除索引

DROP INDEX tpcds.ds_customer_address_p1_index1;
DROP INDEX tpcds.ds_customer_address_p1_index2;
DROP INDEX tpcds.ds_customer_address_p1_index3;
DROP INDEX tpcds.ds_customer_address_p1_index4;

图片.png

课程作业

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

CREATE TABLESPACE tbs_test1 RELATIVE LOCATION 'tablespace1/tbs_test1';
CREATE TABLESPACE tbs_test2 RELATIVE LOCATION 'tablespace2/tbs_test2';
CREATE TABLESPACE tbs_test3 RELATIVE LOCATION 'tablespace3/tbs_test3';
CREATE TABLESPACE tbs_test4 RELATIVE LOCATION 'tablespace4/tbs_test4';


CREATE TABLE products
(
CA_ADDRESS_SK INTEGER NOT NULL,
CA_ADDRESS_ID CHAR(16) NOT NULL,
CA_STREET_NUMBER CHAR(10) ,
CA_STREET_NAME VARCHAR(60) ,
CA_STREET_TYPE CHAR(15) ,
CA_SUITE_NUMBER CHAR(10) ,
CA_CITY VARCHAR(60) ,
CA_COUNTY VARCHAR(30) ,
CA_STATE CHAR(2) ,
CA_ZIP CHAR(10) ,
CA_COUNTRY VARCHAR(20) ,
CA_GMT_OFFSET DECIMAL(5,2) ,
CA_LOCATION_TYPE CHAR(20)
)
PARTITION BY RANGE(CA_ADDRESS_SK)
(
PARTITION p1 VALUES LESS THAN (3000),
PARTITION p2 VALUES LESS THAN (5000) TABLESPACE tbs_test1,
PARTITION p3 VALUES LESS THAN (MAXVALUE) TABLESPACE tbs_test2
);

CREATE INDEX pidx1_products_caaddrsk ON products(CA_ADDRESS_SK) LOCAL;

CREATE INDEX pidx2_products_caaddrsk ON
products(CA_ADDRESS_SK) LOCAL
(
PARTITION pidx1_ca_address_sk,
PARTITION pidx2_ca_address_sk TABLESPACE tbs_test3,
PARTITION pidx3_ca_address_sk TABLESPACE tbs_test4
);

CREATE INDEX pidx3_products_caaddrsk  ON products(CA_ADDRESS_ID) GLOBAL;

jiekexu=# \d+ products
                                   Table "public.products"
      Column      |         Type          | Modifiers | Storage  | Stats target | Description 
------------------+-----------------------+-----------+----------+--------------+-------------
 ca_address_sk    | integer               | not null  | plain    |              | 
 ca_address_id    | character(16)         | not null  | extended |              | 
 ca_street_number | character(10)         |           | extended |              | 
 ca_street_name   | character varying(60) |           | extended |              | 
 ca_street_type   | character(15)         |           | extended |              | 
 ca_suite_number  | character(10)         |           | extended |              | 
 ca_city          | character varying(60) |           | extended |              | 
 ca_county        | character varying(30) |           | extended |              | 
 ca_state         | character(2)          |           | extended |              | 
 ca_zip           | character(10)         |           | extended |              | 
 ca_country       | character varying(20) |           | extended |              | 
 ca_gmt_offset    | numeric(5,2)          |           | main     |              | 
 ca_location_type | character(20)         |           | extended |              | 
Indexes:
    "pidx1_products_caaddrsk" btree (ca_address_sk) LOCAL(PARTITION p1_ca_address_sk_idx, PARTITION p2_ca_address_sk_idx, PARTITION p3_ca_address_sk_idx)  TABLESPACE pg_default
    "pidx2_products_caaddrsk" btree (ca_address_sk) LOCAL(PARTITION pidx1_ca_address_sk, PARTITION pidx2_ca_address_sk TABLESPACE tbs_test3, PARTITION pidx3_ca_address_sk TABLESPACE tbs_test4)  TABLESPACE pg_default
    "pidx3_products_caaddrsk" btree (ca_address_id) TABLESPACE pg_default
Range partition by(ca_address_sk)
Number of partition: 3 (View pg_partition to check each partition range.)
Has OIDs: no
Options: orientation=row, compression=no

图片.png

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

ALTER INDEX pidx2_products_caaddrsk MOVE PARTITION
pidx1_ca_address_sk TABLESPACE tbs_test1;

jiekexu=# \d+ products
                                   Table "public.products"
      Column      |         Type          | Modifiers | Storage  | Stats target | Description 
------------------+-----------------------+-----------+----------+--------------+-------------
 ca_address_sk    | integer               | not null  | plain    |              | 
 ca_address_id    | character(16)         | not null  | extended |              | 
 ca_street_number | character(10)         |           | extended |              | 
 ca_street_name   | character varying(60) |           | extended |              | 
 ca_street_type   | character(15)         |           | extended |              | 
 ca_suite_number  | character(10)         |           | extended |              | 
 ca_city          | character varying(60) |           | extended |              | 
 ca_county        | character varying(30) |           | extended |              | 
 ca_state         | character(2)          |           | extended |              | 
 ca_zip           | character(10)         |           | extended |              | 
 ca_country       | character varying(20) |           | extended |              | 
 ca_gmt_offset    | numeric(5,2)          |           | main     |              | 
 ca_location_type | character(20)         |           | extended |              | 
Indexes:
    "pidx1_products_caaddrsk" btree (ca_address_sk) LOCAL(PARTITION p1_ca_address_sk_idx, PARTITION p2_ca_address_sk_idx, PARTITION p3_ca_address_sk_idx)  TABLESPACE pg
_default
    "pidx2_products_caaddrsk" btree (ca_address_sk) LOCAL(PARTITION pidx1_ca_address_sk TABLESPACE tbs_test1, PARTITION pidx2_ca_address_sk TABLESPACE tbs_test3, PARTIT
ION pidx3_ca_address_sk TABLESPACE tbs_test4)  TABLESPACE pg_default
    "pidx3_products_caaddrsk" btree (ca_address_id) TABLESPACE pg_default
Range partition by(ca_address_sk)
Number of partition: 3 (View pg_partition to check each partition range.)
Has OIDs: no
Options: orientation=row, compression=no

图片.png

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

reindex index pidx2_products_caaddrsk PARTITION pidx2_ca_address_sk;

reindex table products  PARTITION p1;

图片.png

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

\d+ products
select schemaname,tablename,indexname,tablespace from pg_indexes where tablename = 'products';

select relname,parttype,parentid,rangenum,reltablespace from pg_partition;

jiekexu=# select schemaname,tablename,indexname,tablespace from pg_indexes where tablename = 'products';
 schemaname | tablename |        indexname        | tablespace 
------------+-----------+-------------------------+------------
 public     | products  | pidx1_products_caaddrsk | 
 public     | products  | pidx2_products_caaddrsk | 
 public     | products  | pidx3_products_caaddrsk | 
(3 rows)

jiekexu=# select relname,parttype,parentid,rangenum,reltablespace from pg_partition;
       relname        | parttype | parentid | rangenum | reltablespace 
----------------------+----------+----------+----------+---------------
 customer_address_p1  | r        |    16411 |        0 |             0
 p1                   | p        |    16411 |        0 |             0
 p2                   | p        |    16411 |        0 |         16406
 p3                   | p        |    16411 |        0 |         16407
 products             | r        |    16437 |        0 |             0
 p1                   | p        |    16437 |        0 |             0
 p2                   | p        |    16437 |        0 |         16433
 p3                   | p        |    16437 |        0 |         16434
 p2_ca_address_sk_idx | x        |    16444 |        0 |             0
 p3_ca_address_sk_idx | x        |    16444 |        0 |             0
 pidx3_ca_address_sk  | x        |    16448 |        0 |         16436
 pidx2_ca_address_sk  | x        |    16448 |        0 |         16435
 p1_ca_address_sk_idx | x        |    16444 |        0 |             0
 pidx1_ca_address_sk  | x        |    16448 |        0 |         16433
(14 rows)

图片.png

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

DROP INDEX pidx1_products_caaddrsk;
DROP INDEX pidx2_products_caaddrsk;
DROP INDEX pidx3_products_caaddrsk;
DROP table products;
DROP tablespace tbs_test1;
DROP tablespace tbs_test2;
DROP tablespace tbs_test3;
DROP tablespace tbs_test4;

图片.png

图片.png

欧耶，第九课普通表索引管理实操及课后作业练习题完成啦！

墨力计划opengauss
最后修改时间：2021-12-23 13:07:54
「喜欢这篇文章，您的关注和赞赏是给作者最好的鼓励」
关注作者
【版权声明】本文为墨天轮用户原创内容，转载时必须标注文章的来源（墨天轮），文章链接，文章作者等基本信息，否则作者和墨天轮有权追究责任。如果您发现墨天轮中有涉嫌抄袭或者侵权的内容，欢迎发送邮件至：contact@modb.pro进行举报，并提供相关证据，一经查实，墨天轮将立刻删除相关内容。

文章被以下合辑收录

JiekeXu_openGauss 学习笔记（共39篇）
JiekeXu 的 openGauss 学习笔记，国产数据库之路，学习华为 openGauss，记录 openGauss 路上的点点滴滴。openGauss 每日一练，openGauss 训练营。

评论