openGauss每日一练第10天|《学习openGauss分区表索引》学习心得体会和课后练习
学习目标
学习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 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 table product(pno number, price number)
partition by range(price) (
partition p1 values less than(1000),
partition p2 values less than(2000) TABLESPACE example1,
partition p3 values less than(maxvalue) TABLESPACE example2
);
create index idx1 on product(price) local;
CREATE INDEX idx2 ON
product(price) LOCAL
(
PARTITION pro_price_index1,
PARTITION pro_price_index2 TABLESPACE example3,
PARTITION pro_price_index3 TABLESPACE example4
);
create index idx3 on product(pno);
\d+ product;
omm=# \d+ product;
Table "public.product"
Column | Type | Modifiers | Storage | Stats target | Description
--------+---------+-----------+---------+--------------+-------------
pno | numeric | | main | |
price | numeric | | main | |
Indexes:
"idx1" btree (price) LOCAL(PARTITION p1_price_idx, PARTITION p2_price_idx, PARTITION p3_price_idx) TABLESPACE pg_default
"idx2" btree (price) LOCAL(PARTITION pro_price_index1, PARTITION pro_price_index2 TABLESPACE example3, PARTITION pro_price_index3 TABLESPACE example4) TABLESPACE pg_default
"idx3" btree (pno) TABLESPACE pg_default
Range partition by(price)
Number of partition: 3 (View pg_partition to check each partition range.)
Has OIDs: no
Options: orientation=row, compression=no
select * from pg_indexes where tablename = 'product';
omm=# select * from pg_indexes where tablename = 'product';
schemaname | tablename | indexname | tablespace |
indexdef
------------+-----------+-----------+------------+--------------------------------------------
----------------------------------------------------------------------------------------------
-----------------------------------------------------------------
public | product | idx1 | | CREATE INDEX idx1 ON product USING btree (p
rice) LOCAL(PARTITION p1_price_idx, PARTITION p2_price_idx, PARTITION p3_price_idx) TABLESPAC
E pg_default
public | product | idx2 | | CREATE INDEX idx2 ON product USING btree (p
rice) LOCAL(PARTITION pro_price_index1, PARTITION pro_price_index2 TABLESPACE example3, PARTIT
ION pro_price_index3 TABLESPACE example4) TABLESPACE pg_default
public | product | idx3 | | CREATE INDEX idx3 ON product USING btree (p
no) TABLESPACE pg_default
(3 rows)
select * from pg_partition where relname='product';
omm=# select * from pg_partition where relname='product';
relname | parttype | parentid | rangenum | intervalnum | partstrategy | relfilenode | reltabl
espace | relpages | reltuples | relallvisible | reltoastrelid | reltoastidxid | indextblid | i
ndisusable | reldeltarelid | reldeltaidx | relcudescrelid | relcudescidx | relfrozenxid | ints
pnum | partkey | intervaltablespace | interval | boundaries | transit | rel
options | relfrozenxid64
---------+----------+----------+----------+-------------+--------------+-------------+--------
-------+----------+-----------+---------------+---------------+---------------+------------+--
-----------+---------------+-------------+----------------+--------------+--------------+-----
-----+---------+--------------------+----------+------------+---------+-----------------------
----------------------------+----------------
product | r | 16416 | 0 | 0 | r | 0 |
0 | 0 | 0 | 0 | 0 | 0 | 0 | t
| 0 | 0 | 0 | 0 | 0 |
| 2 | | | | | {orientation=row,compr
ession=no,wait_clean_gpi=n} | 0
(1 row)
2.在分区表索引1上，修改分区表索引的表空间，重命名分区表索引
alter index idx1 move PARTITION p1_price_idx TABLESPACE example4;
alter index idx1 rename partition p1_price_idx to p1_price_new_idx;
omm=# select * from pg_indexes where tablename = 'product';
schemaname | tablename | indexname | tablespace |
indexdef
------------+-----------+-----------+------------+--------------------------------------------
----------------------------------------------------------------------------------------------
-----------------------------------------------------------------
public | product | idx1 | | CREATE INDEX idx1 ON product USING btree (p
rice) LOCAL(PARTITION p1_price_new_idx TABLESPACE example4, PARTITION p2_price_idx, PARTITION
p3_price_idx) TABLESPACE pg_default
public | product | idx2 | | CREATE INDEX idx2 ON product USING btree (p
rice) LOCAL(PARTITION pro_price_index1, PARTITION pro_price_index2 TABLESPACE example3, PARTIT
ION pro_price_index3 TABLESPACE example4) TABLESPACE pg_default
public | product | idx3 | | CREATE INDEX idx3 ON product USING btree (p
no) TABLESPACE pg_default
(3 rows)
omm=#
3.在分区表索引2上，重建单个索引分区和分区上的所有索引
reindex index idx2 PARTITION pro_price_index1;
reindex table product PARTITION p1;
4.使用\d+、系统视图pg_indexes和pg_partition查看索引信息
\d+ product;
omm=# \d+ product;
Table "public.product"
Column | Type | Modifiers | Storage | Stats target | Description
--------+---------+-----------+---------+--------------+-------------
pno | numeric | | main | |
price | numeric | | main | |
Indexes:
"idx1" btree (price) LOCAL(PARTITION p1_price_idx, PARTITION p2_price_idx, PARTITION p3_price_idx) TABLESPACE pg_default
"idx2" btree (price) LOCAL(PARTITION pro_price_index1, PARTITION pro_price_index2 TABLESPACE example3, PARTITION pro_price_index3 TABLESPACE example4) TABLESPACE pg_default
"idx3" btree (pno) TABLESPACE pg_default
Range partition by(price)
Number of partition: 3 (View pg_partition to check each partition range.)
Has OIDs: no
Options: orientation=row, compression=no
select * from pg_indexes where tablename = 'product';
omm=# select * from pg_indexes where tablename = 'product';
schemaname | tablename | indexname | tablespace |
indexdef
------------+-----------+-----------+------------+--------------------------------------------
----------------------------------------------------------------------------------------------
-----------------------------------------------------------------
public | product | idx1 | | CREATE INDEX idx1 ON product USING btree (p
rice) LOCAL(PARTITION p1_price_idx, PARTITION p2_price_idx, PARTITION p3_price_idx) TABLESPAC
E pg_default
public | product | idx2 | | CREATE INDEX idx2 ON product USING btree (p
rice) LOCAL(PARTITION pro_price_index1, PARTITION pro_price_index2 TABLESPACE example3, PARTIT
ION pro_price_index3 TABLESPACE example4) TABLESPACE pg_default
public | product | idx3 | | CREATE INDEX idx3 ON product USING btree (p
no) TABLESPACE pg_default
(3 rows)
select * from pg_partition where relname='product';
omm=# select * from pg_partition where relname='product';
relname | parttype | parentid | rangenum | intervalnum | partstrategy | relfilenode | reltabl
espace | relpages | reltuples | relallvisible | reltoastrelid | reltoastidxid | indextblid | i
ndisusable | reldeltarelid | reldeltaidx | relcudescrelid | relcudescidx | relfrozenxid | ints
pnum | partkey | intervaltablespace | interval | boundaries | transit | rel
options | relfrozenxid64
---------+----------+----------+----------+-------------+--------------+-------------+--------
-------+----------+-----------+---------------+---------------+---------------+------------+--
-----------+---------------+-------------+----------------+--------------+--------------+-----
-----+---------+--------------------+----------+------------+---------+-----------------------
----------------------------+----------------
product | r | 16416 | 0 | 0 | r | 0 |
0 | 0 | 0 | 0 | 0 | 0 | 0 | t
| 0 | 0 | 0 | 0 | 0 |
| 2 | | | | | {orientation=row,compr
ession=no,wait_clean_gpi=n} | 0
(1 row)
5.删除索引、表和表空间
drop index idx1;
drop index idx2;
drop index idx3;
drop index idx4;
drop table product;
drop tablespace example1;
drop tablespace example2;
drop tablespace example3;
drop tablespace example4;
「喜欢这篇文章，您的关注和赞赏是给作者最好的鼓励」
关注作者
【版权声明】本文为墨天轮用户原创内容，转载时必须标注文章的来源（墨天轮），文章链接，文章作者等基本信息，否则作者和墨天轮有权追究责任。如果您发现墨天轮中有涉嫌抄袭或者侵权的内容，欢迎发送邮件至：contact@modb.pro进行举报，并提供相关证据，一经查实，墨天轮将立刻删除相关内容。
