第10天学习分区索引了，这里面的步骤比较复杂，需要细心，废话不多说，直接开干！
1.登录数据库环境
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.
2.创建表空间
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
3.创建分区表
omm=# create schema tpcds;
ERROR: schema "tpcds" already exists
omm=# CREATE TABLE tpcds.customer_address_p1
(
omm-# omm(# omm(# CA_ADDRESS_SK INTEGER NOT NULL,
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(# omm(# )
CA_LOCATION_TYPE CHAR(20)
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
4.创建索引，分区索引，全局索引
omm=# CREATE INDEX ds_customer_address_p1_index1 ON tpcds.customer_address_p1(CA_ADDRESS_SK) LOCAL;
CREATE INDEX
omm=# CREATE INDEX ds_customer_address_p1_index2 ON
omm-# (
omm(# tpcds.customer_address_p1(CA_ADDRESS_SK) LOCAL
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
omm=#
omm=# CREATE INDEX ds_customer_address_p1_index3 ON tpcds.customer_address_p1(CA_ADDRESS_ID) GLOBAL;
CREATE INDEX
5.查看表相关信息
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, PART
ITION p3_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 TABLE
SPACE 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
...skipping 1 line
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_index1 | | CREATE INDEX ds_customer_address_p1_index1 ON tpcds
.customer_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_index2 | | CREATE INDEX ds_customer_address_p1_index2 ON tpcds
.customer_address_p1 USING btree (ca_address_sk) LOCAL(PARTITION ca_address_sk_index1, PARTITION ca_address_sk_index2 TABLESPACE exa
mple3, PARTITION ca_address_sk_index3 TABLESPACE example4) TABLESPACE pg_default
tpcds | customer_address_p1 | ds_customer_address_p1_index3 | | CREATE INDEX ds_customer_address_p1_index3 ON tpcds
.customer_address_p1 USING btree (ca_address_id) TABLESPACE pg_default
tpcds | customer_address_p1 | ds_customer_address_p1_index4 | | CREATE INDEX ds_customer_address_p1_index4 ON tpcds
.customer_address_p1 USING btree (ca_address_id) TABLESPACE pg_default
(4 rows)
6.查看分区信息
omm=# select * from pg_partition;
relname | parttype | parentid | rangenum | intervalnum | partstrategy | relfilenode | reltablespace | relpages | reltu
ples | relallvisible | reltoastrelid | reltoastidxid | indextblid | indisusable | reldeltarelid | reldeltaidx | relcudescrelid | rel
cudescidx | relfrozenxid | intspnum | partkey | intervaltablespace | interval | boundaries | transit | reloptions
| relfrozenxid64
----------------------+----------+----------+----------+-------------+--------------+-------------+---------------+----------+------
-----+---------------+---------------+---------------+------------+-------------+---------------+-------------+----------------+----
----------+--------------+----------+---------+--------------------+----------+------------+---------+------------------------------
---------------------+----------------
customer_address_p1 | r | 16421 | 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 | 16421 | 0 | 0 | r | 16425 | 0 | 0 |
0 | 0 | 0 | 0 | 0 | t | 0 | 0 | 0 |
0 | 9352 | | | | | {3000} | | {orientation=row,compression=
no} | 9352
p2 | p | 16421 | 0 | 0 | r | 16426 | 16417 | 0 |
0 | 0 | 0 | 0 | 0 | t | 0 | 0 | 0 |
0 | 0 | 0 | 0 | 0 | t | 0 | 0 | 0 |
0 | 9352 | | | | | {5000} | | {orientation=row,compression=
no} | 9352
p3 | p | 16421 | 0 | 0 | r | 16427 | 16418 | 0 |
0 | 9352 | | | | | {NULL} | | {orientation=row,compression=
no} | 9352
p1_ca_address_sk_idx | x | 16428 | 0 | 0 | n | 16429 | 0 | 1 |
0 | 0 | 0 | 0 | 16425 | t | 0 | 0 | 0 |
0 | 0 | | | | | | |
| 0
p2_ca_address_sk_idx | x | 16428 | 0 | 0 | n | 16430 | 0 | 1 |
0 | 0 | 0 | 0 | 16426 | t | 0 | 0 | 0 |
0 | 0 | | | | | | |
| 0
p3_ca_address_sk_idx | x | 16428 | 0 | 0 | n | 16431 | 0 | 1 |
0 | 0 | 0 | 0 | 16427 | t | 0 | 0 | 0 |
0 | 0 | | | | | | |
| 0
ca_address_sk_index1 | x | 16432 | 0 | 0 | n | 16433 | 0 | 1 |
0 | 0 | 0 | 0 | 16425 | t | 0 | 0 | 0 |
0 | 0 | | | | | | |
| 0
ca_address_sk_index2 | x | 16432 | 0 | 0 | n | 16434 | 16419 | 1 |
0 | 0 | 0 | 0 | 16426 | t | 0 | 0 | 0 |
0 | 0 | | | | | | |
| 0
ca_address_sk_index3 | x | 16432 | 0 | 0 | n | 16435 | 16420 | 1 |
0 | 0 | 0 | 0 | 16427 | t | 0 | 0 | 0 |
0 | 0 | | | | | | |
| 0
(10 rows)
omm=#
7.修改分区索引表空间
omm=# ALTER INDEX tpcds.ds_customer_address_p1_index2 MOVE PARTITION
omm-# CA_ADDRESS_SK_index2 TABLESPACE example1;
ALTER INDEX
omm=# ALTER INDEX tpcds.ds_customer_address_p1_index2 MOVE PARTITION
omm-# CA_ADDRESS_SK_index3 TABLESPACE example2;
ALTER INDEX
8.修改分区索引名称
omm=# ALTER INDEX tpcds.ds_customer_address_p1_index2 RENAME PARTITION
omm-# CA_ADDRESS_SK_index1 TO CA_ADDRESS_SK_index4;
ALTER INDEX
9.重建索引
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
10.删除索引
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
omm=# CREATE TABLESPACE example10 RELATIVE LOCATION 'tablespace1/tablespace_10';
CREATE TABLESPACE
omm=# CREATE TABLESPACE example20 RELATIVE LOCATION 'tablespace2/tablespace_20';
CREATE TABLESPACE
omm=# CREATE TABLESPACE example30 RELATIVE LOCATION 'tablespace3/tablespace_30';
CREATE TABLESPACE
omm=# CREATE TABLESPACE example40 RELATIVE LOCATION 'tablespace4/tablespace_40';
CREATE TABLESPACE
omm=# create schema alex;
ERROR: schema "alex" already exists
创建分区表products
omm=# CREATE TABLE alex.products
omm-# (
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-# omm-# PARTITION BY RANGE(CA_ADDRESS_SK)
(
omm(# PARTITION p1 VALUES LESS THAN (3000),
omm(# PARTITION p2 VALUES LESS THAN (5000) TABLESPACE example10,
omm(# PARTITION p3 VALUES LESS THAN (MAXVALUE) TABLESPACE example20
omm(# );
CREATE TABLE
omm=#
为表创建分区索引1，不指定索引分区的名称
omm=# CREATE INDEX idx_1 ON alex.products(CA_ADDRESS_SK) LOCAL;
CREATE INDEX
创建分区过引2，并指定过引分区的名称
omm=# CREATE INDEX idx_2 ON alex.products(CA_ADDRESS_SK) LOCAL
omm-# (
omm(# PARTITION CA_ADDRESS_SK_index1,
omm(# PARTITION CA_ADDRESS_SK_index2 TABLESPACE example30,
omm(# PARTITION CA_ADDRESS_SK_index3 TABLESPACE example40
omm(# );
CREATE INDEX
创建global分区索引3
omm=# CREATE INDEX idx_3 ON alex.products(CA_ADDRESS_ID) GLOBAL;
CREATE INDEX
2.在分区表索引1上，修改分区表索引的表空间，重命名分区表索引
omm=# alter index alex.idx_2 move partition CA_ADDRESS_SK_index3 tablespace example2;
ALTER INDEX
omm=#
omm=# ALTER INDEX alex.idx_2 RENAME PARTITION CA_ADDRESS_SK_index1 TO CA_ADDRESS_SK_index1_new;
ALTER INDEX
3.在分区表索引2上，重建单个索引分区和分区上的所有索引
omm=# reindex index alex.idx_2 PARTITION CA_ADDRESS_SK_index1_new;
REINDEX
omm=# reindex table alex.products PARTITION p1;
REINDEX
4.使用\d+、系统视图pg_indexes和pg_partition查看索引信息
omm=# \d+ alex.products;
Table "alex.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:
"idx_1" btree (ca_address_sk) LOCAL(PARTITION p1_ca_address_sk_idx, PARTITION p2_ca_address_sk_idx, PARTITION p3_ca_address_sk_i
dx) TABLESPACE pg_default
"idx_2" btree (ca_address_sk) LOCAL(PARTITION ca_address_sk_index1_new, PARTITION ca_address_sk_index2 TABLESPACE example30, PAR
TITION ca_address_sk_index3 TABLESPACE example2) TABLESPACE pg_default
"idx_3" 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 = 'products';
schemaname | tablename | indexname | tablespace |
indexdef
------------+-----------+-----------+------------+----------------------------------------------------------------------------------
------------------------------------------------------------------------------------------------------------------------------------
---------------------
alex | products | idx_2 | | CREATE INDEX idx_2 ON alex.products USING btree (ca_address_sk) LOCAL(PARTITION c
a_address_sk_index1_new, PARTITION ca_address_sk_index2 TABLESPACE example30, PARTITION ca_address_sk_index3 TABLESPACE example2) T
ABLESPACE pg_default
alex | products | idx_3 | | CREATE INDEX idx_3 ON alex.products USING btree (ca_address_id) TABLESPACE pg_def
ault
alex | products | idx_1 | | CREATE INDEX idx_1 ON alex.products USING btree (ca_address_sk) LOCAL(PARTITION p
1_ca_address_sk_idx, PARTITION p2_ca_address_sk_idx, PARTITION p3_ca_address_sk_idx) TABLESPACE pg_default
(3 rows)
omm=# select * from pg_partition;
relname | parttype | parentid | rangenum | intervalnum | partstrategy | relfilenode | reltablespace | relpages | r
eltuples | relallvisible | reltoastrelid | reltoastidxid | indextblid | indisusable | reldeltarelid | reldeltaidx | relcudescrelid |
relcudescidx | relfrozenxid | intspnum | partkey | intervaltablespace | interval | boundaries | transit | relopt
ions | relfrozenxid64
--------------------------+----------+----------+----------+-------------+--------------+-------------+---------------+----------+--
---------+---------------+---------------+---------------+------------+-------------+---------------+-------------+----------------+
--------------+--------------+----------+---------+--------------------+----------+------------+---------+--------------------------
-------------------------+----------------
customer_address_p1 | r | 16421 | 0 | 0 | r | 0 | 0 | 0 |
0 | 0 | 0 | 0 | 0 | t | 0 | 0 | 0 |
0 | 0 | | 1 | | | | | {orientation=row,compress
ion=no,wait_clean_gpi=n} | 0
p1 | p | 16421 | 0 | 0 | r | 16425 | 0 | 0 |
0 | 0 | 0 | 0 | 0 | t | 0 | 0 | 0 |
0 | 9352 | | | | | {3000} | | {orientation=row,compress
ion=no} | 9352
p2 | p | 16421 | 0 | 0 | r | 16426 | 16417 | 0 |
0 | 0 | 0 | 0 | 0 | t | 0 | 0 | 0 |
0 | 9352 | | | | | {5000} | | {orientation=row,compress
ion=no} | 9352
p3 | p | 16421 | 0 | 0 | r | 16427 | 16418 | 0 |
0 | 0 | 0 | 0 | 0 | t | 0 | 0 | 0 |
0 | 9352 | | | | | {NULL} | | {orientation=row,compress
ion=no} | 9352
products | r | 16447 | 0 | 0 | r | 0 | 0 | 0 |
0 | 0 | 0 | 0 | 0 | t | 0 | 0 | 0 |
0 | 0 | | 1 | | | | | {orientation=row,compress
ion=no,wait_clean_gpi=n} | 0
p1 | p | 16447 | 0 | 0 | r | 16451 | 0 | 0 |
0 | 0 | 0 | 0 | 0 | t | 0 | 0 | 0 |
0 | 9374 | | | | | {3000} | | {orientation=row,compress
ion=no} | 9374
p2 | p | 16447 | 0 | 0 | r | 16452 | 16443 | 0 |
0 | 0 | 0 | 0 | 0 | t | 0 | 0 | 0 |
0 | 9374 | | | | | {5000} | | {orientation=row,compress
ion=no} | 9374
p3 | p | 16447 | 0 | 0 | r | 16453 | 16444 | 0 |
0 | 0 | 0 | 0 | 0 | t | 0 | 0 | 0 |
0 | 9374 | | | | | {NULL} | | {orientation=row,compress
ion=no} | 9374
ca_address_sk_index2 | x | 16454 | 0 | 0 | n | 16456 | 16445 | 1 |
0 | 0 | 0 | 0 | 16452 | t | 0 | 0 | 0 |
0 | 0 | | | | | | |
| 0
p2_ca_address_sk_idx | x | 16459 | 0 | 0 | n | 16461 | 0 | 1 |
0 | 0 | 0 | 0 | 16452 | t | 0 | 0 | 0 |
0 | 0 | | | | | | |
| 0
p3_ca_address_sk_idx | x | 16459 | 0 | 0 | n | 16462 | 0 | 1 |
0 | 0 | 0 | 0 | 16453 | t | 0 | 0 | 0 |
0 | 0 | | | | | | |
| 0
ca_address_sk_index3 | x | 16454 | 0 | 0 | n | 16463 | 16418 | 1 |
0 | 0 | 0 | 0 | 16453 | t | 0 | 0 | 0 |
0 | 0 | | | | | | |
| 0
ca_address_sk_index1_new | x | 16454 | 0 | 0 | n | 16465 | 0 | 1 |
0 | 0 | 0 | 0 | 16451 | t | 0 | 0 | 0 |
0 | 0 | | | | | | |
| 0
p1_ca_address_sk_idx | x | 16459 | 0 | 0 | n | 16466 | 0 | 1 |
0 | 0 | 0 | 0 | 16451 | t | 0 | 0 | 0 |
0 | 0 | | | | | | |
| 0
(14 rows)
5.删除索引，表和表空间
omm=# select indexname,tablename,tablespace from pg_indexes where tablename='products';
indexname | tablename | tablespace
-----------+-----------+------------
idx_2 | products |
idx_3 | products |
idx_1 | products |
(3 rows)
omm=# drop table alex.products;
DROP TABLE
drop index alex.idx_1;
drop index alex.idx_2;
drop index alex.idx_3;
omm=# drop tablespace example10;
DROP TABLESPACE
omm=# drop tablespace example20;
DROP TABLESPACE
omm=# drop tablespace example30;
DROP TABLESPACE
omm=# drop tablespace example40;
DROP TABLESPACE
