openGauss
案例复制
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;
学习内容
为表创建分区表索引，不指定索引分区的名称
create index indexname on tablename(字段名) local;
为表创建分区表索引，指定索引分区的名称
create index indexname on tablename(字段名) local(
partition 自己写第1个分区的索引名 (后面内容可以用于指定tablespace),
partition 自己写第2个分区的索引名 (后面内容可以用于指定tablespace),
partition 自己写第3个分区的索引名 (后面内容可以用于指定tablespace)
/*分区数和创建时写的数目一样*/
);
为表创建分区表global索引
CREATE INDEX indexname ON n tablename(字段名) global;
注意ERROR: Global and local partition index should not be on same column
–查看索引信息
\d+ indexname;
select * from pg_indexes where tablename = 'tablename';
select * from pg_partition;
alter index indexname move partition partition_indexname tablespace tablespace_name;
作业内容
1.创建范围分区表products, 为表创建分区表索引1，不指定索引分区的名称，创建分区表索引2，并指定索引分区的名称，创建GLOBAL分区索引3
1、先创建分区表
2、创建索引
create table products(
id int,
name char(20),
key1 char(10)
)partition by range(id)(
partition p1 values less than(300),
partition p2 values less than(500),
partition p3 values less than(maxvalue)
);
create index index1 on products(key1) local;
create index index2 on products(key1) local(
partition myindex1,
partition myindex2,
partition myindex3
);
create index index3 on products(name) global;
omm=# create table products(
omm(# id int,
omm(# name char(20),
omm(# key1 char(10)
omm(# )partition by range(id)(
omm(# partition p1 values less than(300),
omm(# partition p2 values less than(500),
omm(# partition p3 values less than(maxvalue)
omm(# );
CREATE TABLE
omm=# create index index1 on products(key1) local;
CREATE INDEX
omm=# create index index2 on products(key1) local(
omm(# partition myindex1,
omm(# partition myindex2,
omm(# partition myindex3
omm(# );
CREATE INDEX
omm=# create index index3 on products(key1) global;
ERROR: Global and local partition index should not be on same column
omm=# create index index3 on products(name) global;
CREATE INDEX
2.在分区表索引1上，修改分区表索引的表空间，重命名分区表索引
- 应该是索引2吧? 答：应该是修改索引1，直接改整个分区表，而非部分
create tablespace mytbspace1 relative location 'tablespace/mytbspace1';
alter index index2 move partition myindex1 tablespace mytbspace1;
alter index index2 rename partition myindex2 to mynewindex2;
create tablespace mytbspace2 relative location 'tablespace/mytbspace2';
alter index index1 tablespace mytbspace2; /*bug*/
alter index index1 rename to newindex1;
omm=# create tablespace mytbspace2 relative location 'tablespace/mytbspace2';
CREATE TABLESPACE
omm=# alter index index1 tablespace mytbspace2;
ERROR: syntax error at or near "tablespace"
LINE 1: alter index index1 tablespace mytbspace2;
^
omm=# alter index index1 rename to newindex1;
ALTER INDEX
omm=# ^C
omm=# \d+ index1;
Index "public.index1"
Column | Type | Definition | Storage
--------+---------------+------------+----------
key1 | character(10) | key1 | extended
btree, for table "public.products"
omm=# create tablespace mytbspace1 relative location 'tablespace/mytbspace1';
CREATE TABLESPACE
omm=# alter index index1 move partition myindex1 tablespace mytbspace1;
ERROR: partition "myindex1" does not exist
omm=# alter index index2 move partition myindex1 tablespace mytbspace1;
ALTER INDEX
omm=# alter index index2 rename partition myindex2 to mynewindex2;
ALTER INDEX
3.在分区表索引2上，重建单个索引分区和分区上的所有索引
alter index newindex1 rename to index1;
reindex index index1 partition p1_partkeyindex; /*bug*/
omm=# reindex index index1 partition p1keyindex;
ERROR: partition "p1keyindex" does not exist
omm=# reindex index index1 partition p1_partkeyindex;
ERROR: partition "p1_partkeyindex" does not exist
(尚未解决)
4.使用\d+、系统视图pg_indexes和pg_partition查看索引信息
omm=# \d+ index1;
Index "public.index1"
Column | Type | Definition | Storage
--------+---------------+------------+----------
key1 | character(10) | key1 | extended
btree, for table "public.products"
omm=# select * from pg_indexes where tablename = 'products';
public | products | index1 | | CREATE INDEX index1 ON products USING btree (key1) LOCAL(PARTITION p1_key1_idx, PARTITION p2_key1_idx, PARTITION p3_k
ey1_idx) TABLESPACE pg_default
public | products | index2 | | CREATE INDEX index2 ON products USING btree (key1) LOCAL(PARTITION myindex1 TABLESPACE mytbspace1, PARTITION mynewind
ex2, PARTITION myindex3) TABLESPACE pg_default
public | products | index3 | | CREATE INDEX index3 ON products USING btree (name) TABLESPACE pg_default
(3 rows)
schemaname | tablename | indexname | tablespace | indexdef
------------+-----------+-----------+------------+----------------------------------------------------------------------------------------------------------------------
------------------------------------------------
omm=# select * from pg_partition
omm-# ;
relname | parttype | parentid | rangenum | intervalnum | partstrategy | relfilenode | reltablespace | relpages | reltuples | relallvisible | reltoastrelid | reltoa
stidxid | indextblid | indisusable | reldeltarelid | reldeltaidx | relcudescrelid | relcudescidx | relfrozenxid | intspnum | partkey | intervaltablespace | interval | b
oundaries | transit | reloptions | relfrozenxid64
-------------+----------+----------+----------+-------------+--------------+-------------+---------------+----------+-----------+---------------+---------------+-------
--------+------------+-------------+---------------+-------------+----------------+--------------+--------------+----------+---------+--------------------+----------+--
----------+---------+---------------------------------------------------+----------------
products | r | 16389 | 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 | 16389 | 0 | 0 | r | 16393 | 0 | 0 | 0 | 0 | 0 |
0 | 0 | t | 0 | 0 | 0 | 0 | 9034 | | | | | {
300} | | {orientation=row,compression=no} | 9034
500} | | {orientation=row,compression=no} | 9034
p3 | p | 16389 | 0 | 0 | r | 16395 | 0 | 0 | 0 | 0 | 0 |
p2 | p | 16389 | 0 | 0 | r | 16394 | 0 | 0 | 0 | 0 | 0 |
0 | 0 | t | 0 | 0 | 0 | 0 | 9034 | | | | | {
0 | 0 | t | 0 | 0 | 0 | 0 | 9034 | | | | | {
NULL} | | {orientation=row,compression=no} | 9034
| | | 0
p1_key1_idx | x | 16396 | 0 | 0 | n | 16397 | 0 | 1 | 0 | 0 | 0 |
0 | 16393 | t | 0 | 0 | 0 | 0 | 0 | | | | |
p2_key1_idx | x | 16396 | 0 | 0 | n | 16398 | 0 | 1 | 0 | 0 | 0 |
0 | 16394 | t | 0 | 0 | 0 | 0 | 0 | | | | |
| | | 0
p3_key1_idx | x | 16396 | 0 | 0 | n | 16399 | 0 | 1 | 0 | 0 | 0 |
0 | 16395 | t | 0 | 0 | 0 | 0 | 0 | | | | |
| | | 0
myindex3 | x | 16400 | 0 | 0 | n | 16403 | 0 | 1 | 0 | 0 | 0 |
0 | 16395 | t | 0 | 0 | 0 | 0 | 0 | | | | |
| | | 0
myindex1 | x | 16400 | 0 | 0 | n | 16406 | 16405 | 1 | 0 | 0 | 0 |
0 | 16393 | t | 0 | 0 | 0 | 0 | 0 | | | | |
| | | 0
mynewindex2 | x | 16400 | 0 | 0 | n | 16402 | 0 | 1 | 0 | 0 | 0 |
0 | 16394 | t | 0 | 0 | 0 | 0 | 0 | | | | |
| | | 0
(10 rows)
5.删除索引、表和表空间
DROP INDEX index1;
drop index index2;
drop table products;
drop tablespace mytbspace1;
drop tablespace mytbspace2;
omm=# DROP INDEX index1;
DROP INDEX
omm=# drop index index2;
DROP INDEX
omm=# drop table products;
DROP TABLE
omm=# drop tablespace mytbspace1;
DROP TABLESPACE
omm=# drop tablespace mytbspace2;
DROP TABLESPACE
omm=#
「喜欢这篇文章，您的关注和赞赏是给作者最好的鼓励」
关注作者
【版权声明】本文为墨天轮用户原创内容，转载时必须标注文章的来源（墨天轮），文章链接，文章作者等基本信息，否则作者和墨天轮有权追究责任。如果您发现墨天轮中有涉嫌抄袭或者侵权的内容，欢迎发送邮件至：contact@modb.pro进行举报，并提供相关证据，一经查实，墨天轮将立刻删除相关内容。
