学习目标
学习openGauss分区表索引
1.创建范围分区表products, 为表创建分区表索引1，不指定索引分区的名称，创建分区表索引2，并指定索引分区的名称，创建GLOBAL分区索引3
2.在分区表索引1上，修改分区表索引的表空间，重命名分区表索引
3.在分区表索引2上，重建单个索引分区和分区上的所有索引
4.使用\d+、系统视图pg_indexes和pg_partition查看索引信息
5.删除索引、表和表空间
omm=# CREATE TABLE products
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(# omm(# CA_COUNTY VARCHAR(30) ,
CA_STATE CHAR(2) ,
CA_COUNTRY VARCHAR(20) ,
omm(# CA_ZIP CHAR(10) ,
omm(# 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
omm=# omm=#
omm=# omm=#
omm=#
omm=#
omm=# omm=#
omm=#
omm=# ;
omm=#
omm=#
omm=# CREATE INDEX ds_products_address_p1_index1 ON tpcds.customer_address_p1(CA_ADDRESS_SK) LOCAL;
omm=# CREATE INDEX
omm=# omm-# CREATE INDEX ds_products_address_p1_index2 ON
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
omm=#
omm=# CREATE INDEX ds_products_address_p1_index3 ON tpcds.customer_address_p1(CA_ADDRESS_ID) GLOBAL;
CREATE INDEX
omm=#
omm=# ALTER INDEX s_products_address_p1_index1 MOVE PARTITION
omm-# CA_ADDRESS_SK_index2 TABLESPACE example1;
omm=# ERROR: relation "s_products_address_p1_index1" does not exist
omm=# reindex index ds_products_address_p1_index2 PARTITION p1_ca_address_sk_idx;
ERROR: relation "ds_products_address_p1_index2" does not exist
omm=#
omm=#
omm=#
omm=#
omm=# reindex table products PARTITION p1;
REINDEX
omm=# omm=#
omm=#
omm=#
omm=#
omm=# \d+
List of relations
Schema | Name | Type | Owner | Size | Storage | Description
--------+--------------+-------+-------+---------+----------------------------------+-------------
public | ds_t1 | table | omm | 0 bytes | {orientation=row,compression=no} |
public | products | table | omm | 0 bytes | {orientation=row,compression=no} |
public | update_table | table | omm | 0 bytes | {orientation=row,compression=no} |
(3 rows)
omm=# select * from pg_indexes;
------------+-------------------------------+-----------------------------------------------+--------
----+------------------------------------------------------------------------------------------------
schemaname | tablename | indexname | tablesp
ace |
indexdef
tpcds | customer_address_p1 | ds_products_address_p1_index3 |
| CREATE INDEX ds_products_address_p1_index3 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_products_address_p1_index2 |
| CREATE INDEX ds_products_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 example3, PARTIT
ION ca_address_sk_index3 TABLESPACE example4) TABLESPACE pg_default
_id) TABLESPACE pg_default
pg_catalog | statement_history | statement_history_time_idx |
-----------------------------------------------------------------------------------------------------
---------------------------------------------------------------------
tpcds | customer_address_p1 | ds_products_address_p1_index1 |
| CREATE INDEX ds_products_address_p1_index1 ON tpcds.customer_address_p1 USING btree (ca_address
--More-- pg_catalog | pg_type | pg_type_typname_nsp_index |
| CREATE INDEX statement_history_time_idx ON statement_history USING btree (start_time, is_slow_s
ql) TABLESPACE pg_default
Cancel request sent
omm=#
omm=#
omm=# select * from pg_partitions;
ERROR: relation "pg_partitions" does not exist on gaussdb
LINE 1: select * from pg_partitions;
^
omm=# select * from pg_partition;
relname | parttype | parentid | rangenum | intervalnum | partstrategy | relfilenode | r
eltablespace | relpages | reltuples | relallvisible | reltoastrelid | reltoastidxid | indextblid | in
disusable | reldeltarelid | reldeltaidx | relcudescrelid | relcudescidx | relfrozenxid | intspnum | p
artkey | intervaltablespace | interval | boundaries | transit | reloptions
| relfrozenxid64
----------------------+----------+----------+----------+-------------+--------------+-------------+--
-------------+----------+-----------+---------------+---------------+---------------+------------+---
----------+---------------+-------------+----------------+--------------+--------------+----------+--
-------+--------------------+----------+------------+---------+--------------------------------------
-------------+----------------
update_table | r | 16492 | 0 | 0 | r | 0 |
0 | 0 | 0 | 0 | 0 | 0 | 0 | t
| 0 | 0 | 0 | 0 | 0 | | 1
| | 0 | 0 | 0 | 0 | 0 | 0 | 0 | t
| 0 | 0 | 0 | 0 | 9415 | |
| | | {50} | | {orientation=row,compression=no}
| 9415
update_table_p1 | p | 16492 | 0 | 0 | r | 16497 |
| | | {orientation=row,compression=no,wait_
clean_gpi=n} | 0
update_table_p0 | p | 16492 | 0 | 0 | r | 16496 |
0 | 0 | 0 | 0 | 0 | 0 | 0 | t
| 0 | 0 | 0 | 0 | 9415 | |
| | | {100} | | {orientation=row,compression=no}
Cancel request sent
omm=#
omm=# drop table products;
omm=# DROP TABLE
omm=#
