暂无图片
暂无图片
暂无图片
暂无图片
暂无图片
首页 / openGauss每日一练第10天 | openGauss分区表索引

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

原创 lxs_data 2021-12-10
454

在上一篇文章中，学习了openGauss普通表索引，这一篇文章进行openGauss分区表索引的学习与实践操作。

在上一篇文章写道了，openGauss 分区表索引分为LOCAL索引与GLOBAL索引，LOCAL索引与某个具体分区绑定，而GLOBAL索引则对应整个分区表。

分区索引的创建语法如下：

CREATE [ UNIQUE ] INDEX [ [schema_name.]index_name ] ON table_name [ USING method ]
    ( {{ column_name | ( expression ) } [ COLLATE collation ] [ opclass ] [ ASC | DESC ] [ NULLS LAST ] }[, ...] )
    [ LOCAL [ ( { PARTITION index_partition_name [ TABLESPACE index_partition_tablespace ] } [, ...] ) ] | GLOBAL ]
    [ WITH ( { storage_parameter = value } [, ...] ) ]
    [ TABLESPACE tablespace_name ];

  • LOCAL

    指定创建的分区索引为LOCAL索引。

  • GLOBAL

    指定创建的分区索引为GLOBAL索引，当不指定LOCAL、GLOBAL关键字时，默认创建GLOBAL索引。

连接openGauss数据库

root@modb:~# su - omm
omm@modb:~$ gsql -r
gsql ((openGauss 2.0.0 build 78689da9) compiled at 2021-03-31 21:03:52 commit 0 last mr )
Non-SSL connection (SSL connection is recommended when requiring high-security)
Type "help" for help.

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

创建schema ：lxs_data，范围分区表products，语句如下：

首先创建存放不同分区的表空间，语句如下：

omm=# CREATE TABLESPACE lxs_data1 RELATIVE LOCATION 'tablespacelxs_data1/tablespace_lxs_data1';
omm=# CREATE TABLESPACE
omm=# CREATE TABLESPACE lxs_data2 RELATIVE LOCATION 'tablespacelxs_data2/tablespace_lxs_data2';
CREATE TABLESPACE
omm=# CREATE TABLESPACE lxs_data3 RELATIVE LOCATION 'tablespacelxs_data3/tablespace_lxs_data3';
CREATE TABLESPACE
omm=# CREATE TABLESPACE lxs_data4 RELATIVE LOCATION 'tablespacelxs_data4/tablespace_lxs_data4';
CREATE TABLESPACE

创建schema 为 lxs_data，语句如下：

omm=# create schema lxs_data;
CREATE SCHEMA

创建范围分区表products，语句如下：

omm=# CREATE TABLE lxs_data.products
omm-# (
omm(# DATA_ADDRESS_SK INTEGER NOT NULL,
omm(# DATA_ADDRESS_ID CHAR(16) NOT NULL,
omm(# DATA_STREET_NUMBER CHAR(10) ,
omm(# DATA_STREET_NAME VARCHAR(60) ,
omm(# DATA_STREET_TYPE CHAR(15) ,
omm(# DATA_SUITE_NUMBER CHAR(10) ,
omm(# DATA_CITY VARCHAR(60) ,
omm(# DATA_COUNTY VARCHAR(30) ,
omm(# DATA_STATE CHAR(2) ,
omm(# DATA_ZIP CHAR(10) ,
omm(# DATA_GMT_OFFSET DECIMAL(5,2) ,
omm(# DATA_COUNTRY VARCHAR(20) ,
omm(# DATA_LOCATION_TYPE CHAR(20)
omm(# PARTITION BY RANGE(DATA_ADDRESS_SK)
omm(# omm-# PARTITION p1 VALUES LESS THAN (1000),
omm(# PARTITION p2 VALUES LESS THAN (2000) TABLESPACE lxs_data1,
omm(# PARTITION p3 VALUES LESS THAN (MAXVALUE) TABLESPACE lxs_data2
omm(# );
CREATE TABLE

查询刚创建的表空间、schema、表等信息，语句如下：

omm=# \db

List of tablespaces
Name | Owner | Location
------------+-------+------------------------------------------
example1 | omm | tablespace1/tablespace_1
example2 | omm | tablespace2/tablespace_2
example3 | omm | tablespace3/tablespace_3
example4 | omm | tablespace4/tablespace_4
lxs_data1 | omm | tablespacelxs_data1/tablespace_lxs_data1
lxs_data2 | omm | tablespacelxs_data2/tablespace_lxs_data2
lxs_data3 | omm | tablespacelxs_data3/tablespace_lxs_data3
lxs_data4 | omm | tablespacelxs_data4/tablespace_lxs_data4
(10 rows)
omm=# pg_default | omm |
pg_global | omm | 

omm=# \d+ lxs_data.products
Table "lxs_data.products"
Column | Type | Modifiers | Storage | Stats target | Descript
ion
--------------------+-----------------------+-----------+----------+--------------+---------
----
data_address_sk | integer | not null | plain | |
data_address_id | character(16) | not null | extended | |
data_street_number | character(10) | | extended | |
data_street_name | character varying(60) | | extended | |
data_street_type | character(15) | | extended | |
data_suite_number | character(10) | | extended | |
data_city | character varying(60) | | extended | |
data_county | character varying(30) | | extended | |
data_state | character(2) | | extended | |
data_zip | character(10) | | extended | |
data_country | character varying(20) | | extended | |
data_gmt_offset | numeric(5,2) | | main | |
data_location_type | character(20) | | extended | |
Range partition by(data_address_sk)
Number of partition: 3 (View pg_partition to check each partition range.)
Has OIDs: no
Options: orientation=row, compression=no

操作截图：





基于刚创建的products表创建分区表索引1,不指定索引分区的名称，创建分区表索引2，并指定索引分区的名称，创建GLOBAL分区索引3，语句如下：

创建分区索引1，不指定索引分区的名称语句如下：

omm=# CREATE INDEX data_address_p1_index1 ON lxs_data.products(DATA_ADDRESS_SK);
CREATE INDEX

创建分区表索引2 ，指定索引分区的名称,语句如下：

omm=# CREATE INDEX data_address_p2_index2 ON
omm-# lxs_data.products(DATA_ADDRESS_ID) LOCAL
omm-# (
omm(# PARTITION DATA_ADDRESS_ID_p1_index1,
omm(# PARTITION DATA_ADDRESS_ID_p2_index2 TABLESPACE lxs_data3,
omm(# PARTITION DATA_ADDRESS_ID_p3_index3 TABLESPACE lxs_data4
omm(# );
CREATE INDEX

创建GLOBAL分区索引3，语句如下:

omm=# CREATE INDEX data_address_p3_index3 ON lxs_data.products(DATA_ADDRESS_SK) GLOBAL;
CREATE INDEX

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

omm=# \d+ lxs_data.products;
Table "lxs_data.products"
Column | Type | Modifiers | Storage | Stats target | Descript
ion
--------------------+-----------------------+-----------+----------+--------------+---------
----
data_address_sk | integer | not null | plain | |
data_address_id | character(16) | not null | extended | |
data_street_number | character(10) | | extended | |
data_street_name | character varying(60) | | extended | |
data_street_type | character(15) | | extended | |
data_suite_number | character(10) | | extended | |
data_country | character varying(20) | | extended | |
data_gmt_offset | numeric(5,2) | | main | |
data_city | character varying(60) | | extended | |
data_county | character varying(30) | | extended | |
data_state | character(2) | | extended | |
data_zip | character(10) | | extended | |
x1, PARTITION data_address_id_p2_index2 TABLESPACE lxs_data3, PARTITION data_address_id_p3_i
ndex3 TABLESPACE lxs_data4) TABLESPACE pg_default
--More-- data_location_type | character(20) | | extended | |
Indexes:
"data_address_p1_index1" btree (data_address_sk) TABLESPACE pg_default
"data_address_p2_index2" btree (data_address_id) LOCAL(PARTITION data_address_id_p1_inde
"data_address_p3_index3" btree (data_address_sk) TABLESPACE pg_default
Range partition by(data_address_sk)
Number of partition: 3 (View pg_partition to check each partition range.)
Has OIDs: no
Options: orientation=row, compression=no

修改分区表索引的表空间，语句如下：

omm=# ALTER INDEX lxs_data.data_address_p2_index2 MOVE PARTITION DATA_ADDRESS_ID_p1_index1 TABLESPACE lxs_data1;
ALTER INDEX
omm=# 

重命名分区表索引,语句如下：

omm=# ALTER INDEX lxs_data.data_address_p2_index2 RENAME PARTITION DATA_ADDRESS_ID_p1_index1 TO DATA_ADDRESS_ID_p1_index1_bk;
ALTER INDEX

再次查看索引信息，语句如下：

omm=# \d+ lxs_data.products;
Table "lxs_data.products"
Column | Type | Modifiers | Storage | Stats target | Descript
ion
--------------------+-----------------------+-----------+----------+--------------+---------
----
data_address_sk | integer | not null | plain | |
data_address_id | character(16) | not null | extended | |
data_street_number | character(10) | | extended | |
data_street_name | character varying(60) | | extended | |
data_street_type | character(15) | | extended | |
data_suite_number | character(10) | | extended | |
data_city | character varying(60) | | extended | |
data_county | character varying(30) | | extended | |
data_state | character(2) | | extended | |
data_zip | character(10) | | extended | |
data_country | character varying(20) | | extended | |
data_gmt_offset | numeric(5,2) | | main | |
data_location_type | character(20) | | extended | |
Indexes:
"data_address_p1_index1" btree (data_address_sk) TABLESPACE pg_default
"data_address_p2_index2" btree (data_address_id) LOCAL(PARTITION data_address_id_p1_inde
x1_bk TABLESPACE lxs_data1, PARTITION data_address_id_p2_index2 TABLESPACE lxs_data3, PARTIT
ION data_address_id_p3_index3 TABLESPACE lxs_data4) TABLESPACE pg_default
"data_address_p3_index3" btree (data_address_sk) TABLESPACE pg_default
Range partition by(data_address_sk)
Number of partition: 3 (View pg_partition to check each partition range.)
Has OIDs: no
Options: orientation=row, compression=no

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

在分区表索引2上，重建单个索引分区，语句如下：

omm=# reindex index lxs_data.data_address_p2_index2 PARTITION DATA_ADDRESS_ID_p2_index2;
REINDEX

在分区表索引2上重建所有索引，语句如下：

omm=# reindex table lxs_data.products PARTITION p1;
REINDEX
omm=# reindex table lxs_data.products PARTITION p2;
REINDEX
omm=# reindex table lxs_data.products PARTITION p3;
REINDEX
omm=# reindex table lxs_data.products PARTITION p4;

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

使用\d+ 查看索引信息，语句如下：

omm=# \d+ lxs_data.products;
Table "lxs_data.products"
Column | Type | Modifiers | Storage | Stats target | Descript
ion
--------------------+-----------------------+-----------+----------+--------------+---------
----
data_address_sk | integer | not null | plain | |
data_address_id | character(16) | not null | extended | |
data_street_number | character(10) | | extended | |
data_street_name | character varying(60) | | extended | |
data_street_type | character(15) | | extended | |
data_suite_number | character(10) | | extended | |
data_city | character varying(60) | | extended | |
data_county | character varying(30) | | extended | |

"data_address_p1_index1" btree (data_address_sk) TABLESPACE pg_default
"data_address_p2_index2" btree (data_address_id) LOCAL(PARTITION data_address_id_p1_inde
x1_bk TABLESPACE lxs_data1, PARTITION data_address_id_p2_index2 TABLESPACE lxs_data3, PARTIT
ION data_address_id_p3_index3 TABLESPACE lxs_data4) TABLESPACE pg_default
--More-- data_state | character(2) | | extended | |
data_zip | character(10) | | extended | |
data_country | character varying(20) | | extended | |
data_gmt_offset | numeric(5,2) | | main | |
data_location_type | character(20) | | extended | |
"data_address_p3_index3" btree (data_address_sk) TABLESPACE pg_default
Range partition by(data_address_sk)
Number of partition: 3 (View pg_partition to check each partition range.)
Has OIDs: no
Options: orientation=row, compression=no

查询系统视图pg_indexes查看索引信息,语句如下：

omm=# select * from pg_indexes where tablename = 'products';
schemaname | tablename | indexname | tablespace |  indexdef
------------+-----------+------------------------+------------+-----------------------------
-----------------------------------------------------------------------------------------
---------------------------------------------------------------------------------
lxs_data | products | data_address_p1_index1 | | CREATE INDEX data_address_p1
_index1 ON lxs_data.products USING btree (data_address_sk) TABLESPACE pg_default
lxs_data | products | data_address_p2_index2 | | CREATE INDEX data_address_p2
_index2 ON lxs_data.products USING btree (data_address_id) LOCAL(PARTITION data_address_id_p
1_index1_bk TABLESPACE lxs_data1, PARTITION data_address_id_p2_index2 TABLESPACE lxs_data3,
_index3 ON lxs_data.products USING btree (data_address_sk) TABLESPACE pg_default
(3 rows)

PARTITION data_address_id_p3_index3 TABLESPACE lxs_data4) TABLESPACE pg_default
lxs_data | products | data_address_p3_index3 | | CREATE INDEX data_address_p3

查看系统视图pg_partition查看索引信息，语句如下：

omm=# select * from pg_partition;



删除索引、表和表空间

删除索引，语句如下：

omm=# \d+ lxs_data.products;
Table "lxs_data.products"
Column | Type | Modifiers | Storage | Stats target | Descript
ion
--------------------+-----------------------+-----------+----------+--------------+---------
----
data_address_sk | integer | not null | plain | |
data_address_id | character(16) | not null | extended | |
data_street_number | character(10) | | extended | |
data_zip | character(10) | | extended | |
data_country | character varying(20) | | extended | |
data_gmt_offset | numeric(5,2) | | main | |
data_location_type | character(20) | | extended | |
Indexes:
data_street_name | character varying(60) | | extended | |
data_street_type | character(15) | | extended | |
data_suite_number | character(10) | | extended | |
data_city | character varying(60) | | extended | |
data_county | character varying(30) | | extended | |
data_state | character(2) | | extended | |
"data_address_p1_index1" btree (data_address_sk) TABLESPACE pg_default
"data_address_p2_index2" btree (data_address_id) LOCAL(PARTITION data_address_id_p1_inde
x1_bk TABLESPACE lxs_data1, PARTITION data_address_id_p2_index2 TABLESPACE lxs_data3, PARTIT
ION data_address_id_p3_index3 TABLESPACE lxs_data4) TABLESPACE pg_default
"data_address_p3_index3" btree (data_address_sk) TABLESPACE pg_default
Range partition by(data_address_sk)
Number of partition: 3 (View pg_partition to check each partition range.)
Has OIDs: no
Options: orientation=row, compression=no

删除语句:

omm=# DROP INDEX lxs_data.data_address_p1_index1;
DROP INDEX
omm=# DROP INDEX lxs_data.data_address_p2_index2;
DROP INDEX
omm=# DROP INDEX lxs_data.data_address_p3_index3;
DROP INDEX

删除索引后查看：

omm=# \d+ lxs_data.products;
Table "lxs_data.products"
Column | Type | Modifiers | Storage | Stats target | Descript
ion
--------------------+-----------------------+-----------+----------+--------------+---------
----
data_address_sk | integer | not null | plain | |
data_address_id | character(16) | not null | extended | |
data_street_number | character(10) | | extended | |
data_street_name | character varying(60) | | extended | |
data_street_type | character(15) | | extended | |
data_suite_number | character(10) | | extended | |
data_city | character varying(60) | | extended | |
data_county | character varying(30) | | extended | |
data_state | character(2) | | extended | |
data_zip | character(10) | | extended | |
data_country | character varying(20) | | extended | |
data_gmt_offset | numeric(5,2) | | main | |
data_location_type | character(20) | | extended | |
Range partition by(data_address_sk)
Number of partition: 3 (View pg_partition to check each partition range.)
Has OIDs: no
Options: orientation=row, compression=no

删除表，语句如下：

omm=# drop table lxs_data.products;
DROP TABLE
删除表空间语句：

查看表空间：

omm=# \db+
List of tablespaces
Name | Owner | Location | Access privileges | Descrip
tion
------------+-------+------------------------------------------+-------------------+--------
-----
lxs_data1 | omm | tablespacelxs_data1/tablespace_lxs_data1 | |
lxs_data2 | omm | tablespacelxs_data2/tablespace_lxs_data2 | |
lxs_data3 | omm | tablespacelxs_data3/tablespace_lxs_data3 | |
lxs_data4 | omm | tablespacelxs_data4/tablespace_lxs_data4 | |
pg_default | omm | | |
pg_global | omm | | |
(6 rows)

删除表空间：

omm=# drop tablespace lxs_data1;
DROP TABLESPACE
omm=# drop tablespace lxs_data2;
DROP TABLESPACE
omm=# drop tablespace lxs_data3;
DROP TABLESPACE
omm=# drop tablespace lxs_data4;
DROP TABLESPACE

再次查看表空间：

omm=# \db+
List of tablespaces
Name | Owner | Location | Access privileges | Description
------------+-------+----------+-------------------+-------------
pg_default | omm | | |
pg_global | omm | | |
(2 rows)


持续打卡第十天，继续！！！！1







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

评论