openGauss每日一练第10天 | 学习分区表索引-学习笔记

学习目标
学习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.

1.创建分区表索引
omm=# CREATE TABLESPACE example1 RELATIVE LOCATION 'tablespace1/tablespace_1';
CREATE TABLESPACE
omm=# CREATE TABLESPACE example2 RELATIVE LOCATION 'tablespace2/tablespace_2';
CREATE TABLESPACE
omm=# CREATE TABLESPACE example3 RELATIVE LOCATION 'tablespace3/tablespace_3';
CREATE TABLESPACE
omm=# CREATE TABLESPACE example4 RELATIVE LOCATION 'tablespace4/tablespace_4';
CREATE TABLESPACE
omm=# create schema tpcds;
CREATE SCHEMA
omm=# CREATE TABLE tpcds.customer_address_p1
omm(# omm-# (
CA_ADDRESS_SK INTEGER NOT NULL,
omm(# CA_ADDRESS_ID CHAR(16) NOT NULL,
omm(# CA_STREET_NUMBER CHAR(10) ,
omm(# CA_STREET_NAME VARCHAR(60) ,
omm(# CA_STREET_TYPE CHAR(15) ,
omm(# CA_SUITE_NUMBER CHAR(10) ,
omm(# CA_CITY VARCHAR(60) ,
omm(# CA_COUNTY VARCHAR(30) ,
omm(# CA_STATE CHAR(2) ,
omm(# CA_ZIP CHAR(10) ,
omm(# CA_COUNTRY VARCHAR(20) ,
omm(# CA_GMT_OFFSET DECIMAL(5,2) ,
omm(# CA_LOCATION_TYPE CHAR(20)
omm(# )
omm-# PARTITION BY RANGE(CA_ADDRESS_SK)
omm-# (
omm(# PARTITION p1 VALUES LESS THAN (3000),
omm(# PARTITION p2 VALUES LESS THAN (5000) TABLESPACE example1,
omm(# PARTITION p3 VALUES LESS THAN (MAXVALUE) TABLESPACE example2
omm(# );
CREATE TABLE
–创建分区表索引ds_customer_address_p1_index1，不指定索引分区的名称
omm=# CREATE INDEX ds_customer_address_p1_index1 ON tpcds.customer_address_p1(CA_ADDRESS_SK) LOCAL;
CREATE INDEX
–创建分区表索引ds_customer_address_p1_index2，并指定索引分区的名称。
omm=# CREATE INDEX ds_customer_address_p1_index2 ON
omm-# tpcds.customer_address_p1(CA_ADDRESS_SK) LOCAL
omm-# (
omm(# PARTITION CA_ADDRESS_SK_index1,
omm(# PARTITION CA_ADDRESS_SK_index2 TABLESPACE example3,
omm(# PARTITION CA_ADDRESS_SK_index3 TABLESPACE example4
omm(# );
CREATE INDEX
–创建GLOBAL分区索引
omm=# CREATE INDEX ds_customer_address_p1_index3 ON tpcds.customer_address_p1(CA_ADDRESS_ID) GLOBAL;
CREATE INDEX
–不指定关键字，默认创建GLOBAL分区索引
omm=# CREATE INDEX ds_customer_address_p1_index4 ON tpcds.customer_address_p1(CA_ADDRESS_ID);
CREATE INDEX
–查看索引信息
omm=# \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 p
3_ca_address_sk_idx) TABLESPACE pg_default
"ds_customer_address_p1_index2" btree (ca_address_sk) LOCAL(PARTITION ca_address_sk_index1, PARTITION ca_address_sk_index2 TABLESPACE e
"ds_customer_address_p1_index4" btree (ca_address_id) TABLESPACE pg_default
--More--xample3, PARTITION ca_address_sk_index3 TABLESPACE example4) TABLESPACE pg_default
"ds_customer_address_p1_index3" 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

omm=# select * from pg_indexes where tablename = 'customer_address_p1';
schemaname | tablename | indexname | tablespace |
indexdef

------------+---------------------+-------------------------------+------------+-----------------------------------------------------------
-------------------------------------------------------------------------------------------------------------------------------------------
--------------------------------------------------------------------
tpcds | customer_address_p1 | ds_customer_address_p1_index2 | | CREATE INDEX ds_customer_address_p1_index2 ON tpcds.custom
er_address_p1 USING btree (ca_address_sk) LOCAL(PARTITION ca_address_sk_index1, PARTITION ca_address_sk_index2 TABLESPACE example3, PARTITI
ON ca_address_sk_index3 TABLESPACE example4) TABLESPACE pg_default
tpcds | customer_address_p1 | ds_customer_address_p1_index1 | | CREATE INDEX ds_customer_address_p1_index1 ON tpcds.custom
er_address_p1 USING 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
tpcds | customer_address_p1 | ds_customer_address_p1_index4 | | CREATE INDEX ds_customer_address_p1_index4 ON tpcds.custom
er_address_p1 USING btree (ca_address_id) TABLESPACE pg_default
(4 rows)

tpcds | customer_address_p1 | ds_customer_address_p1_index3 | | CREATE INDEX ds_customer_address_p1_index3 ON tpcds.custom
er_address_p1 USING btree (ca_address_id) TABLESPACE pg_default

omm=# select * from pg_partition;

2.修改分区表索引定义
–修改分区表索引CA_ADDRESS_SK_index2的表空间为example1

omm=# ALTER INDEX tpcds.ds_customer_address_p1_index2 MOVE PARTITION
omm-# CA_ADDRESS_SK_index2 TABLESPACE example1;
ALTER INDEX
–修改分区表索引CA_ADDRESS_SK_index3的表空间为example2
omm=# ALTER INDEX tpcds.ds_customer_address_p1_index2 MOVE PARTITION
omm-# CA_ADDRESS_SK_index3 TABLESPACE example2;
ALTER INDEX
–重命名分区表索引
omm=# ALTER INDEX tpcds.ds_customer_address_p1_index2 RENAME PARTITION
omm-# CA_ADDRESS_SK_index1 TO CA_ADDRESS_SK_index4;
ALTER INDEX
3.重建索引分区
–重建单个索引分区
omm=# reindex index tpcds.ds_customer_address_p1_index1 PARTITION p1_ca_address_sk_idx;
REINDEX
–重建分区上的所有索引
omm=# reindex table tpcds.customer_address_p1 PARTITION p1;
REINDEX
4.删除索引
omm=# DROP INDEX tpcds.ds_customer_address_p1_index1;
DROP INDEX
omm=# DROP INDEX tpcds.ds_customer_address_p1_index2;
DROP INDEX
omm=# DROP INDEX tpcds.ds_customer_address_p1_index3;
DROP INDEX
omm=# DROP INDEX tpcds.ds_customer_address_p1_index4;
DROP INDEX

课程作业
1.创建范围分区表products, 为表创建分区表索引1，不指定索引分区的名称，创建分区表索引2，并指定索引分区的名称，创建GLOBAL分区索引3
--创建数据表空间
CREATE TABLESPACE par_data_1 RELATIVE LOCATION 'tablespace1/par_data_1';
CREATE TABLESPACE par_data_2 RELATIVE LOCATION 'tablespace1/par_data_2';
CREATE TABLESPACE par_data_3 RELATIVE LOCATION 'tablespace1/par_data_3';

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.
omm=#
omm=# CREATE TABLESPACE par_data_1 RELATIVE LOCATION 'tablespace1/par_data_1';
CREATE TABLESPACE
omm=# CREATE TABLESPACE par_data_2 RELATIVE LOCATION 'tablespace1/par_data_2';
CREATE TABLESPACE
omm=# CREATE TABLESPACE par_data_3 RELATIVE LOCATION 'tablespace1/par_data_3';
omm=# CREATE TABLESPACE


--创建索引表空间
CREATE TABLESPACE index_data_1 RELATIVE LOCATION 'tablespace1/index_data_1';

omm=# CREATE TABLESPACE index_data_1 RELATIVE LOCATION 'tablespace1/index_data_1';
CREATE TABLESPACE


--创建表

CREATE TABLE products
( product_id integer,
product_name char(30),
category char(20)
)
PARTITION BY RANGE(product_id )
(
PARTITION p1 VALUES LESS THAN (1000) TABLESPACE par_data_1,
PARTITION p2 VALUES LESS THAN (2000) TABLESPACE par_data_2,
PARTITION p3 VALUES LESS THAN (MAXVALUE) TABLESPACE par_data_3
);

omm=# CREATE TABLE products
omm-# ( product_id integer,
omm(# omm(# product_name char(30),
category char(20)
omm(# omm-# (
)
omm-# PARTITION BY RANGE(product_id )
omm(# omm(# PARTITION p1 VALUES LESS THAN (1000) TABLESPACE par_data_1,
PARTITION p2 VALUES LESS THAN (2000) TABLESPACE par_data_2,
omm(# PARTITION p3 VALUES LESS THAN (MAXVALUE) TABLESPACE par_data_3
omm(# );
CREATE TABLE
omm=#



--创建分表表索引1，不指定索引分区的名称

CREATE INDEX proid_idx1 ON products(product_id) LOCAL;

omm=# CREATE INDEX proid_idx1 ON products(product_id) LOCAL;
CREATE INDEX

--创建分区表索引2，并指定索引分区的名称

CREATE INDEX proid_idx2 ON
products(product_id) LOCAL
(
PARTITION proid_idx2_p1,
PARTITION proid_idx2_p2 TABLESPACE index_data_1,
PARTITION proid_idx2_p3 TABLESPACE index_data_1
);
omm=# CREATE INDEX proid_idx2 ON
omm-# products(product_id) LOCAL
omm-# (
omm(# PARTITION proid_idx2_p1,
omm(# PARTITION proid_idx2_p2 TABLESPACE index_data_1,
omm(# PARTITION proid_idx2_p3 TABLESPACE index_data_1
omm(# );
CREATE INDEX
omm=#

--创建GLOBAL分区表索引3

CREATE INDEX proid_idx3 ON products(category) GLOBAL;

omm=# CREATE INDEX proid_idx3 ON products(category) GLOBAL;
CREATE INDEX

omm=# \d+ products;
Table "public.products"
Column | Type | Modifiers | Storage | Stats target | Description
--------------+---------------+-----------+----------+--------------+-------------
product_id | integer | | plain | |
product_name | character(30) | | extended | |
category | character(20) | | extended | |
Indexes:
"proid_idx1" btree (product_id) LOCAL(PARTITION p1_product_id_idx, PARTITION p2_product_id_idx, PARTITION p3_product_id_idx) TABLESPACE pg_default
"proid_idx2" btree (product_id) LOCAL(PARTITION proid_idx2_p1, PARTITION proid_idx2_p2 TABLESPACE index_data_1, PARTITION proid_idx2_p3 TABLESPACE index_data_1) TABLESPACE pg_default
"proid_idx3" btree (category) TABLESPACE pg_default
Range partition by(product_id)
Number of partition: 3 (View pg_partition to check each partition range.)
Has OIDs: no
Options: orientation=row, compression=no




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

ALTER INDEX proid_idx1 MOVE PARTITION
p1_product_id_idx TABLESPACE index_data_1;
omm=# ALTER INDEX proid_idx1 MOVE PARTITION
omm-# p1_product_id_idx TABLESPACE index_data_1;
ALTER INDEX
omm=#
--重命名分区表索引

ALTER INDEX proid_idx1 RENAME PARTITION
p1_product_id_idx TO p1_product_id_idx＿new;
omm=# ALTER INDEX proid_idx1 RENAME PARTITION
omm-# p1_product_id_idx TO p1_product_id_idx＿new;
ALTER INDEX

3.在分区表索引2上，重建单个索引分区和分区上的所有索引
reindex index proid_idx2 PARTITION proid_idx2_p2;
reindex table products PARTITION p2;
omm=# reindex index proid_idx2 PARTITION proid_idx2_p2;
REINDEX

omm=# reindex table products PARTITION p2;
REINDEX

4.使用\d+、系统视图pg_indexes和pg_partition查看索引信息
\d+ products;
select * from pg_indexes where tablename = 'products';
select * from pg_partition;

omm=# \d+ products;
Table "public.products"
Column | Type | Modifiers | Storage | Stats target | Description
--------------+---------------+-----------+----------+--------------+-------------
product_id | integer | | plain | |
product_name | character(30) | | extended | |
category | character(20) | | extended | |
Indexes:
"proid_idx1" btree (product_id) LOCAL(PARTITION "p1_product_id_idx＿new" TABLESPACE index_data_1, PARTITION p2_product_id_idx, PARTITION p3_product_id_idx) TABLESPACE pg_default
"proid_idx2" btree (product_id) LOCAL(PARTITION proid_idx2_p1, PARTITION proid_idx2_p2 TABLESPACE index_data_1, PARTITION proid_idx2_p3 TABLESPACE index_data_1) TABLESPACE pg_default
"proid_idx3" btree (category) TABLESPACE pg_default
Range partition by(product_id)
Number of partition: 3 (View pg_partition to check each partition range.)
Has OIDs: no
Options: orientation=row, compression=no

omm=# select * from pg_indexes where tablename = 'products';
_data_1, PARTITION p2_product_id_idx, PARTITION p3_product_id_idx) TABLESPACE pg_default
public | products | proid_idx2 | | CREATE INDEX proid_idx2 ON products USING btree (product_id) LOCAL(PARTITION proid_idx2_p1, PARTITION proid_idx2_p2
TABLESPACE index_data_1, PARTITION proid_idx2_p3 TABLESPACE index_data_1) TABLESPACE pg_default
public | products | proid_idx3 | | CREATE INDEX proid_idx3 ON products USING btree (category) TABLESPACE pg_default
(3 rows)

schemaname | tablename | indexname | tablespace | indexdef

------------+-----------+------------+------------+---------------------------------------------------------------------------------------------------------------------
-------------------------------------------------------------------------------------------------
public | products | proid_idx1 | | CREATE INDEX proid_idx1 ON products USING btree (product_id) LOCAL(PARTITION "p1_product_id_idx＿new" TABLESPACE index
omm=# select * from pg_partition;
relname | parttype | parentid | rangenum | intervalnum | partstrategy | relfilenode | reltablespace | relpages | reltuples | relallvisible | reltoastre
lid | reltoastidxid | indextblid | indisusable | reldeltarelid | reldeltaidx | relcudescrelid | relcudescidx | relfrozenxid | intspnum | partkey | intervaltablespace |
interval | boundaries | transit | reloptions | relfrozenxid64
-------------------------+----------+----------+----------+-------------+--------------+-------------+---------------+----------+-----------+---------------+-----------
----+---------------+------------+-------------+---------------+-------------+----------------+--------------+--------------+----------+---------+--------------------+-
0 | 0 | 0 | t | 0 | 0 | 0 | 0 | 9323 | | | |
| {1000} | | {orientation=row,compression=no} | 9323
p2 | p | 16393 | 0 | 0 | r | 16398 | 16390 | 0 | 0 | 0 |
0 | 0 | 0 | t | 0 | 0 | 0 | 0 | 9323 | | | |
| {2000} | | {orientation=row,compression=no} | 9323
---------+------------+---------+---------------------------------------------------+----------------
products | r | 16393 | 0 | 0 | r | 0 | 0 | 0 | 0 | 0 |
0 | 0 | 0 | t | 0 | 0 | 0 | 0 | 0 | | 1 | |
| | | {orientation=row,compression=no,wait_clean_gpi=n} | 0
p1 | p | 16393 | 0 | 0 | r | 16397 | 16389 | 0 | 0 | 0 |
0 | 0 | 0 | t | 0 | 0 | 0 | 0 | 9323 | | | |
| {NULL} | | {orientation=row,compression=no} | 9323
p3_product_id_idx | x | 16400 | 0 | 0 | n | 16403 | 0 | 1 | 0 | 0 |
0 | 0 | 16399 | t | 0 | 0 | 0 | 0 | 0 | | | |
| | | | 0
proid_idx2_p1 | x | 16404 | 0 | 0 | n | 16405 | 0 | 1 | 0 | 0 |
0 | 0 | 16397 | t | 0 | 0 | 0 | 0 | 0 | | | |
--More-- p3 | p | 16393 | 0 | 0 | r | 16399 | 16391 | 0 | 0 | 0 |
| | | | 0
proid_idx2_p3 | x | 16404 | 0 | 0 | n | 16407 | 16392 | 1 | 0 | 0 |
| |
| | | | 0
proid_idx2_p2 | x | 16404 | 0 | 0 | n | 16412 | 16392 | 1 | 0 | 0 |
0 | 0 | 16398 | t | 0 | 0 | 0 | 0 | 0 | | | |
| | | | 0
(10 rows)

0 | 0 | 16399 | t | 0 | 0 | 0 | 0 | 0 | | | |
| | | | 0
p1_product_id_idx＿new | x | 16400 | 0 | 0 | n | 16409 | 16392 | 1 | 0 | 0 | 0
| 0 | 16397 | t | 0 | 0 | 0 | 0 | 0 | | | |
| | | | 0
p2_product_id_idx | x | 16400 | 0 | 0 | n | 16411 | 0 | 1 | 0 | 0 |
0 | 0 | 16398 | t | 0 | 0 | 0 | 0 | 0 | | omm=#


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

drop index proid_idx1;
drop index proid_idx2;
drop index proid_idx3;

omm=# drop index proid_idx1;
DROP INDEX
omm=# drop index proid_idx2;
DROP INDEX
omm=# drop index proid_idx3;
DROP INDEX


--删除表

drop table products;
omm=# drop table products;
DROP TABLE
--删除表空间

drop tablespace par_data_1;
drop tablespace par_data_2;
drop tablespace par_data_3;

drop tablespace index_data_1;


omm=# drop tablespace par_data_1;
DROP TABLESPACE
omm=# drop tablespace par_data_2;
DROP TABLESPACE
omm=# drop tablespace par_data_3;
DROP TABLESPACE
omm=# drop tablespace index_data_1;
DROP TABLESPACE