第十课
10.1 打卡第10天 | 分区表索引
👉openGauss SQL学习参考资料
https://opengauss.org/zh/docs/2.1.0/docs/Developerguide/SQL%E8%AF%AD%E6%B3%95.html
学习目标
学习openGauss分区表索引
课程学习
连接数据库
#第一次进入等待15秒
#数据库启动中...
su - omm
gsql -r
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
);
–创建分区表索引ds_customer_address_p1_index1，不指定索引分区的名称
CREATE INDEX ds_customer_address_p1_index1 ON tpcds.customer_address_p1(CA_ADDRESS_SK) LOCAL;
–创建分区表索引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
);
–创建GLOBAL分区索引
CREATE INDEX ds_customer_address_p1_index3 ON tpcds.customer_address_p1(CA_ADDRESS_ID) GLOBAL;
–不指定关键字，默认创建GLOBAL分区索引
CREATE INDEX ds_customer_address_p1_index4 ON tpcds.customer_address_p1(CA_ADDRESS_ID);
–查看索引信息
\d+ tpcds.customer_address_p1;
select * from pg_indexes where tablename = 'customer_address_p1';
select * from pg_partition;
2.修改分区表索引定义
–修改分区表索引CA_ADDRESS_SK_index2的表空间为example1
ALTER INDEX tpcds.ds_customer_address_p1_index2 MOVE PARTITION
CA_ADDRESS_SK_index2 TABLESPACE example1;
–修改分区表索引CA_ADDRESS_SK_index3的表空间为example2
ALTER INDEX tpcds.ds_customer_address_p1_index2 MOVE PARTITION
CA_ADDRESS_SK_index3 TABLESPACE example2;
–重命名分区表索引
ALTER INDEX tpcds.ds_customer_address_p1_index2 RENAME PARTITION
CA_ADDRESS_SK_index1 TO CA_ADDRESS_SK_index4;
3.重建索引分区
–重建单个索引分区
reindex index tpcds.ds_customer_address_p1_index1 PARTITION p1_ca_address_sk_idx;
–重建分区上的所有索引
reindex table tpcds.customer_address_p1 PARTITION p1;
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;
课程作业
1.创建范围分区表products, 为表创建分区表索引1，不指定索引分区的名称，创建分区表索引2，并指定索引分区的名称，创建GLOBAL分区索引3
CREATE TABLE tpcds.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 example1,
PARTITION p3 VALUES LESS THAN (MAXVALUE) TABLESPACE example2
);
CREATE INDEX ds_products_index1 ON tpcds.products(CA_ADDRESS_SK) LOCAL;
CREATE INDEX ds_products_index2 ON
tpcds.products(CA_ADDRESS_SK) LOCAL
(
PARTITION CA_ADDRESS_SK_index1,
PARTITION CA_ADDRESS_SK_index2 TABLESPACE example3,
PARTITION CA_ADDRESS_SK_index3 TABLESPACE example4
);
CREATE INDEX ds_products_index3 ON tpcds.products(CA_ADDRESS_ID) GLOBAL;
2.在分区表索引1上，修改分区表索引的表空间，重命名分区表索引
ALTER INDEX tpcds.ds_products_index1 MOVE PARTITION
p1_ca_address_sk_idx TABLESPACE example1;
ALTER INDEX tpcds.ds_products_index1 RENAME PARTITION
p1_ca_address_sk_idx TO p1_ca_address_sk_idx4;
3.在分区表索引2上，重建单个索引分区和分区上的所有索引
reindex index tpcds.ds_products_index2 PARTITION ca_address_sk_index1;
reindex table tpcds.products PARTITION p2;
4.使用\d+、系统视图pg_indexes和pg_partition查看索引信息
\d+ tpcds.products;
select * from pg_indexes where tablename = 'products';
select * from pg_partition;
5.删除索引、表和表空间
DROP INDEX tpcds.ds_products_index1;
DROP INDEX tpcds.ds_products_index2;
DROP INDEX tpcds.ds_products_index3;
DROP TABLE tpcds.products;
DROP TABLESPACE example1;
DROP TABLESPACE example2;
DROP TABLESPACE example3;
DROP TABLESPACE example4;
omm=# omm-# (
omm(# CREATE TABLE tpcds.products
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(# omm(# CA_CITY VARCHAR(60) ,
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
omm=# CREATE INDEX ds_products_index1 ON tpcds.products(CA_ADDRESS_SK) LOCAL;
CREATE INDEX
omm=# CREATE INDEX ds_products_index2 ON
omm-# (
omm(# tpcds.products(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_products_index3 ON tpcds.products(CA_ADDRESS_ID) GLOBAL;
CREATE INDEX
omm=# ALTER INDEX tpcds.ds_products_index1 MOVE PARTITION
omm-# p1_ca_address_sk_idx TABLESPACE example1;
omm=# ALTER INDEX
omm=# ALTER INDEX tpcds.ds_products_index1 RENAME PARTITION
omm-# p1_ca_address_sk_idx TO p1_ca_address_sk_idx4;
ALTER INDEX
omm=#
omm=# reindex index tpcds.ds_products_index2 PARTITION ca_address_sk_index1;
REINDEX
omm=# reindex table tpcds.products PARTITION p2;
REINDEX
omm=# \d+ tpcds.products;
Table "tpcds.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:
"ds_products_index1" btree (ca_address_sk) LOCAL(PARTITION p1_ca_address_sk_idx4 TABLESPACE example1, P
k_index2 TABLESPACE example3, PARTITION ca_address_sk_index3 TABLESPACE example4) TABLESPACE pg_default
"ds_products_index3" btree (ca_address_id) TABLESPACE pg_default
Range partition by(ca_address_sk)
--More--ARTITION p2_ca_address_sk_idx, PARTITION p3_ca_address_sk_idx) TABLESPACE pg_default
"ds_products_index2" btree (ca_address_sk) LOCAL(PARTITION ca_address_sk_index1, PARTITION ca_address_s
Number of partition: 3 (View pg_partition to check each partition range.)
Has OIDs: no
Options: orientation=row, compression=no
omm=#
omm=# select * from pg_indexes where tablename = 'products';
schemaname | tablename | indexname | tablespace |
indexdef
------------+-----------+--------------------+------------+------------------------------------------------
-----------------------------------------------------------------------------------------------------------
-----------------------------------------------------------------------------------------
tpcds | products | ds_products_index1 | | CREATE INDEX ds_products_index1 ON tpcds.produc
ts USING btree (ca_address_sk) LOCAL(PARTITION p1_ca_address_sk_idx4 TABLESPACE example1, PARTITION p2_ca_a
ddress_sk_idx, PARTITION p3_ca_address_sk_idx) TABLESPACE pg_default
tpcds | products | ds_products_index2 | | CREATE INDEX ds_products_index2 ON tpcds.produc
ts USING btree (ca_address_sk) LOCAL(PARTITION ca_address_sk_index1, PARTITION ca_address_sk_index2 TABLESP
ts USING btree (ca_address_id) TABLESPACE pg_default
(3 rows)
ACE example3, PARTITION ca_address_sk_index3 TABLESPACE example4) TABLESPACE pg_default
tpcds | products | ds_products_index3 | | CREATE INDEX ds_products_index3 ON tpcds.produc
omm=# select * from pg_partition;
relname | parttype | parentid | rangenum | intervalnum | partstrategy | relfilenode | reltab
lespace | relpages | reltuples | relallvisible | reltoastrelid | reltoastidxid | indextblid | indisusable |
reldeltarelid | reldeltaidx | relcudescrelid | relcudescidx | relfrozenxid | intspnum | partkey | interval
tablespace | interval | boundaries | transit | reloptions | relfroze
nxid64
-----------------------+----------+----------+----------+-------------+--------------+-------------+-------
--------+----------+-----------+---------------+---------------+---------------+------------+-------------+
---------------+-------------+----------------+--------------+--------------+----------+---------+---------
-----------+----------+------------+---------+---------------------------------------------------+---------
-------
0 | 0 | 0 | 0 | 0 | | 1 |
| | | | {orientation=row,compression=no,wait_clean_gpi=n} |
0
customer_address_p1 | r | 16394 | 0 | 0 | r | 0 |
0 | 0 | 0 | 0 | 0 | 0 | 0 | t |
p1 | p | 16394 | 0 | 0 | r | 16398 |
p2 | p | 16394 | 0 | 0 | r | 16399 |
0 | 0 | 0 | 0 | 0 | 0 | 0 | t |
0 | 0 | 0 | 0 | 9320 | | |
| | {3000} | | {orientation=row,compression=no} |
9320
16389 | 0 | 0 | 0 | 0 | 0 | 0 | t |
0 | 0 | 0 | 0 | 9320 | | |
| | {5000} | | {orientation=row,compression=no} |
9320
p3 | p | 16394 | 0 | 0 | r | 16400 |
16390 | 0 | 0 | 0 | 0 | 0 | 0 | t |
0 | 0 | 0 | 0 | 9320 | | |
| | {NULL} | | {orientation=row,compression=no} |
9320
p3_ca_address_sk_idx | x | 16443 | 0 | 0 | n | 16446 |
0 | 1 | 0 | 0 | 0 | 0 | 16442 | t |
0 | 0 | 0 | 0 | 0 | | |
| | | | |
0
ca_address_sk_index3 | x | 16447 | 0 | 0 | n | 16450 |
16392 | 1 | 0 | 0 | 0 | 0 | 16442 | t |
| | | | |
0
ca_address_sk_index1 | x | 16447 | 0 | 0 | n | 16453 |
0 | 0 | 0 | 0 | 0 | | |
| | | | |
0 p1_ca_address_sk_idx4 | x | 16443 | 0 | 0 | n | 16452 |
16389 | 1 | 0 | 0 | 0 | 0 | 16440 | t |
0 | 0 | 0 | 0 | 0 | | |
0 | 1 | 0 | 0 | 0 | 0 | 16440 | t |
0 | 0 | 0 | 0 | 0 | | |
| | | | |
0
products | r | 16436 | 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 | 16436 | 0 | 0 | r | 16440 |
0 | 0 | 0 | 0 | 0 | 0 | 0 | t |
0 | 0 | 0 | 0 | 9351 | | |
p3 | p | 16436 | 0 | 0 | r | 16442 |
16390 | 0 | 0 | 0 | 0 | 0 | 0 | t |
| | {3000} | | {orientation=row,compression=no} |
9351
p2 | p | 16436 | 0 | 0 | r | 16441 |
16389 | 0 | 0 | 0 | 0 | 0 | 0 | t |
0 | 0 | 0 | 0 | 9351 | | |
| | {5000} | | {orientation=row,compression=no} |
9351
0 | 0 | 0 | 0 | 9351 | | |
| | {NULL} | | {orientation=row,compression=no} |
9351
p2_ca_address_sk_idx | x | 16443 | 0 | 0 | n | 16454 |
0 | 1 | 0 | 0 | 0 | 0 | 16441 | t |
0 | 0 | 0 | 0 | 0 | | |
| | | | |
0
ca_address_sk_index2 | x | 16447 | 0 | 0 | n | 16455 |
16391 | 1 | 0 | 0 | 0 | 0 | 16441 | t |
0 | 0 | 0 | 0 | 0 | | |
| | | | |
0
(14 rows)
omm=#
omm=# DROP INDEX tpcds.ds_products_index1;
DROP INDEX
omm=# DROP INDEX tpcds.ds_products_index2;
DROP INDEX
omm=# DROP INDEX tpcds.ds_products_index3;
DROP INDEX
omm=# DROP TABLE tpcds.products;
DROP TABLE
omm=# DROP TABLE tpcds.customer_address_p1;
DROP TABLE
omm=# omm=# DROP TABLESPACE example1;
DROP TABLESPACE
omm=#
omm=# DROP TABLESPACE example2;
DROP TABLESPACE
omm=#
omm=# DROP TABLESPACE example3;
DROP TABLESPACE
omm=#
omm=# DROP TABLESPACE example4;
DROP TABLESPACE
omm=#
