作业：
1.创建范围分区表products, 为表创建分区表索引1，不指定索引分区的名称，创建分区表索引2，并指定索引分区的名称，创建GLOBAL分区索引3
omm=# create table store
omm-# (
omm(# c1 int,
omm(# c3 name
c2 CHAR(2),
omm(# omm-# omm(# )
partition by range (c1)
omm-# (
omm(# partition p1 values less than (50),
omm(# partition p2 values less than (100),
omm(# partition p3 values less than (150)
);omm(#
CREATE TABLE
omm=#
omm=# create index store_idx1 on store(c1) local;
CREATE INDEX
omm=# create index store_idx2 on store(c2) local (partition idx2_p1,partition idx_p2,partition idx_p3);
CREATE INDEX
omm=# create index store_idx3 on store(c3) global;
CREATE INDEX
omm=#
omm=# \d+ store
Table "public.store"
Column | Type | Modifiers | Storage | Stats target | Description
--------+--------------+-----------+----------+--------------+-------------
c1 | integer | | plain | |
c2 | character(2) | | extended | |
c3 | name | | plain | |
Indexes:
"store_idx1" btree (c1) LOCAL(PARTITION p1_c1_idx, PARTITION p2_c1_idx, PARTITION p3_c1_idx) TABLESPACE pg_default
"store_idx2" btree (c2) LOCAL(PARTITION idx2_p1, PARTITION idx_p2, PARTITION idx_p3) TABLESPACE pg_default
"store_idx3" btree (c3) TABLESPACE pg_default
Range partition by(c1)
Number of partition: 3 (View pg_partition to check each partition range.)
Has OIDs: no
Options: orientation=row, compression=no
2.在分区表索引1上，修改分区表索引的表空间，重命名分区表索引
omm=# alter index store_index1 move partition p1_c1_idx tablespace example1;
ERROR: relation "store_index1" does not exist
omm=# alter index store_idx1 move partition p1_c1_idx tablespace example1;
ALTER INDEX
omm=# \d+ store
Table "public.store"
Column | Type | Modifiers | Storage | Stats target | Description
--------+--------------+-----------+----------+--------------+-------------
c1 | integer | | plain | |
c2 | character(2) | | extended | |
c3 | name | | plain | |
Indexes:
"store_idx1" btree (c1) LOCAL(PARTITION p1_c1_idx TABLESPACE example1, PARTITION p2_c1_idx, PARTITION p3_c1_idx) TABLESPACE pg_default
"store_idx2" btree (c2) LOCAL(PARTITION idx2_p1, PARTITION idx_p2, PARTITION idx_p3) TABLESPACE pg_default
"store_idx3" btree (c3) TABLESPACE pg_default
Range partition by(c1)
Number of partition: 3 (View pg_partition to check each partition range.)
Has OIDs: no
Options: orientation=row, compression=no
omm=# alter index store_idx1 rename partition p2_c1_idx to p22222_c1_idx;
ALTER INDEX
omm=# \d+ store
Table "public.store"
Column | Type | Modifiers | Storage | Stats target | Description
--------+--------------+-----------+----------+--------------+-------------
c1 | integer | | plain | |
c2 | character(2) | | extended | |
c3 | name | | plain | |
Indexes:
"store_idx1" btree (c1) LOCAL(PARTITION p1_c1_idx TABLESPACE example1, PARTITION p22222_c1_idx, PARTITION p3_c1_idx) TABLESPACE pg_default
"store_idx2" btree (c2) LOCAL(PARTITION idx2_p1, PARTITION idx_p2, PARTITION idx_p3) TABLESPACE pg_default
"store_idx3" btree (c3) TABLESPACE pg_default
Range partition by(c1)
Number of partition: 3 (View pg_partition to check each partition range.)
Has OIDs: no
Options: orientation=row, compression=no
3.在分区表索引2上，重建单个索引分区和分区上的所有索引
omm=# reindex index store_idx2 partition idx2_p1;
REINDEX
omm=# reindex table store partition p1;
REINDEX
4.使用\d+、系统视图pg_indexes和pg_partition查看索引信息
omm=# \d+ store
Table "public.store"
Column | Type | Modifiers | Storage | Stats target | Description
--------+--------------+-----------+----------+--------------+-------------
c1 | integer | | plain | |
c2 | character(2) | | extended | |
c3 | name | | plain | |
Indexes:
omm=# "store_idx1" btree (c1) LOCAL(PARTITION p1_c1_idx TABLESPACE example1, PARTITION p22222_c1_idx, PARTITION p3_c1_idx) TABLESPACE pg_default
"store_idx2" btree (c2) LOCAL(PARTITION idx2_p1, PARTITION idx_p2, PARTITION idx_p3) TABLESPACE pg_default
"store_idx3" btree (c3) TABLESPACE pg_default
Range partition by(c1)
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='store';
schemaname | tablename | indexname | tablespace |
indexdef
public | store | store_idx3 | | CREATE INDEX store_idx3 ON store USING btree (c3) TABLESPACE pg_default
(3 rows)
------------+-----------+------------+------------+---------------------------------------------------------------------------
--------------------------------------------------------------------------------------------
public | store | store_idx1 | | CREATE INDEX store_idx1 ON store USING btree (c1) LOCAL(PARTITION p1_c1_id
x TABLESPACE example1, PARTITION p22222_c1_idx, PARTITION p3_c1_idx) TABLESPACE pg_default
public | store | store_idx2 | | CREATE INDEX store_idx2 ON store USING btree (c2) LOCAL(PARTITION idx2_p1,
PARTITION idx_p2, PARTITION idx_p3) TABLESPACE pg_default
omm=# select * from pg_partition;
relname | parttype | parentid | rangenum | intervalnum | partstrategy | relfilenode | reltablespace | relpages |
reltuples | relallvisible | reltoastrelid | reltoastidxid | indextblid | indisusable | reldeltarelid | reldeltaidx | relcudesc
relid | relcudescidx | relfrozenxid | intspnum | partkey | intervaltablespace | interval | boundaries | transit |
reloptions | relfrozenxid64
---------------------+----------+----------+----------+-------------+--------------+-------------+---------------+----------+-
----------+---------------+---------------+---------------+------------+-------------+---------------+-------------+----------
------+--------------+--------------+----------+---------+--------------------+----------+------------+---------+-------------
--------------------------------------+----------------
customer_address_p1 | r | 16394 | 0 | 0 | r | 0 | 0 | 0 |
0 | 0 | 0 | 0 | 0 | t | 0 | 0 |
=row,compression=no,wait_clean_gpi=n} | 0
0 | 0 | 0 | | 1 | | | | | {orientation
p1 | p | 16394 | 0 | 0 | r | 16398 | 0 | 0 |
=row,compression=no} | 9320
p2 | p | 16394 | 0 | 0 | r | 16399 | 16389 | 0 |
0 | 0 | 0 | 0 | 0 | t | 0 | 0 |
=row,compression=no} | 9320
p3 | p | 16394 | 0 | 0 | r | 16400 | 16390 | 0 |
0 | 0 | 0 | 0 | 0 | t | 0 | 0 |
0 | 0 | 9320 | | | | | {3000} | | {orientation
0 | 0 | 9320 | | | | | {5000} | | {orientation
0 | 0 | 0 | 0 | 0 | t | 0 | 0 |
0 | 0 | 9320 | | | | | {NULL} | | {orientation
=row,compression=no} | 9320
store | r | 16456 | 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 | 16456 | 0 | 0 | r | 16460 | 0 | 0 |
0 | 0 | 0 | 0 | 0 | t | 0 | 0 |
0 | 0 | 9354 | | | | | {50} | | {orientation
=row,compression=no} | 9354
p2 | p | 16456 | 0 | 0 | r | 16461 | 0 | 0 |
0 | 0 | 0 | 0 | 0 | t | 0 | 0 |
0 | 0 | 9354 | | | | | {100} | | {orientation
=row,compression=no} | 9354
p3 | p | 16456 | 0 | 0 | r | 16462 | 0 | 0 |
0 | 0 | 0 | 0 | 0 | t | 0 | 0 |
0 | 0 | 9354 | | | | | {150} | | {orientation
=row,compression=no} | 9354
p3_c1_idx | x | 16463 | 0 | 0 | n | 16466 | 0 | 1 |
0 | 0 | 0 | 0 | 16462 | t | 0 | 0 |
0 | 0 | 0 | | | | | | |
| 0
idx_p2 | x | 16467 | 0 | 0 | n | 16469 | 0 | 1 |
0 | 0 | 0 | 0 | 16461 | t | 0 | 0 |
0 | 0 | 0 | | | | | | |
| 0
idx_p3 | x | 16467 | 0 | 0 | n | 16470 | 0 | 1 |
0 | 0 | 0 | 0 | 16462 | t | 0 | 0 |
0 | 0 | 0 | | | | | | |
| 0
p22222_c1_idx | x | 16463 | 0 | 0 | n | 16465 | 0 | 1 |
0 | 0 | 0 | 0 | 16461 | t | 0 | 0 |
0 | 0 | 0 | | | | | | |
| 0
p1_c1_idx | x | 16463 | 0 | 0 | n | 16474 | 16389 | 1 |
0 | 0 | 0 | 0 | 16460 | t | 0 | 0 |
0 | 0 | 0 | | | | | | |
| 0
idx2_p1 | x | 16467 | 0 | 0 | n | 16475 | 0 | 1 |
0 | 0 | 0 | 0 | 16460 | t | 0 | 0 |
0 | 0 | 0 | | | | | | |
| 0
(14 rows)
5.删除索引、表和表空间
omm=# drop index store_idx1;
DROP INDEX
omm=# drop index store_idx2;
DROP INDEX
omm=# drop index store_idx3;
DROP INDEX
omm=# drop table store;
DROP TABLE
omm=# drop tablespace example;
DROP TABLESPACE
5.删除索引、表和表空间
