学习内容

第10天的内容为在分区表上建立索引，是对前几天的建立索引，建立分区和建立表空间的结合。

课程学习

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_

课后作业

1.创建范围分区表products, 为表创建分区表索引1，不指定索引分区的名称，创建分区表索引2，并指定索引分区的名称，创建GLOBAL分区索引3

创建表空间

CREATE TABLESPACE pct_1 RELATIVE LOCATION 'tablespace1/pct_1';

CREATE TABLESPACE pct_2 RELATIVE LOCATION 'tablespace2/pct_2';

CREATE TABLESPACE pct_3 RELATIVE LOCATION 'tablespace3/pct_3';

CREATE TABLESPACE pct_4 RELATIVE LOCATION 'tablespace4/pct_4';

创建视图

create schema sc_pct;

创建分区表

productCREATE TABLE sc_pct.products
( pct_id1 INTEGER NOT NULL, 
pct_id2 CHAR(16) NOT NULL, 
pct_name1 CHAR(10) ,
pct_name2 VARCHAR(60) ) 
PARTITION BY RANGE(pct_id1) 
( PARTITION p1 VALUES LESS THAN (3000),
PARTITION p2 VALUES LESS THAN (5000) TABLESPACE tbs_1, 
PARTITION p3 VALUES LESS THAN (MAXVALUE) TABLESPACE tbs_2 ); 

创建分区表索引1，不指定索引分区的名称

CREATE INDEX ds_porducts_pct_id1_index1 ON sc_pct.products(pct_id1) LOCAL;

创建分区表索引2,并指定索引分区的名称

创建GLOBAL分区索引3

CREATE INDEX ds_products_pct_id2_index3 ON sc_pct.products(pct_id2) GLOBAL;

2.在分区表索引1上，修改分区表索引的表空间，重命名分区表索引

--查询表信息

 \d+ sc_pct.products

修改分区表索引的表空间

ALTER INDEX sc_pct.ds_porducts_pct_id1_index1 MOVE PARTITION p1_pct_id1_idx TABLESPACE tbs_1;

重命名分区表索引

ALTER INDEX sc_pct.ds_porducts_pct_id1_index1 RENAME PARTITION p2_pct_id1_idx TO p2_pct_id1_idx_new;

3.在分区表索引2上，重建单个索引分区和分区上的所有索引重建单个索引

reindex index sc_pct.ds_products_pct_id1_index2 PARTITION pct_id1_index1;

重建分区上的所有索引

reindex table sc_pct.products PARTITION p1;

4.使用\d+、系统视图pg_indexes和pg_partition查看索引信息

\d+ sc_pct.products; 

select * from pg_indexes where tablename = 'products' ; 

select * from pg_partition;

5.删除索引、表和表空间删除索引

DROP INDEX sc_pct.ds_porducts_pct_id1_index1;

DROP INDEX sc_pct.ds_products_pct_id1_index2; 

DROP INDEX sc_pct.ds_products_pct_id2_index3;

删除表

drop table sc_pct.products

删除表空间

drop tablespace tbs_1;

drop tablespace tbs_2;

drop tablespace tbs_3;

drop tablespace tbs_4;