openGauss每日一练第9天 | 学习心得体会

索引

索引是对数据库中的一列进行排序的一种结构，使用索引可以快速的对数据库特定的列进行访问。

1.练习

create schema tpcds;
CREATE TABLE tpcds.ship_mode_t1
(
SM_SHIP_MODE_SK INTEGER NOT NULL,
SM_SHIP_MODE_ID CHAR(16) NOT NULL,
SM_TYPE CHAR(30),
SM_CODE CHAR(10),
SM_CARRIER CHAR(20),
SM_CONTRACT CHAR(20)
);
CREATE UNIQUE INDEX ds_ship_mode_t1_index1 ON tpcds.ship_mode_t1(SM_SHIP_MODE_SK);

CREATE INDEX ds_ship_mode_t1_index4 ON tpcds.ship_mode_t1 USING btree(SM_SHIP_MODE_SK);

CREATE INDEX ds_ship_mode_t1_index2 ON tpcds.ship_mode_t1(SUBSTR(SM_CODE,1 ,4));

CREATE UNIQUE INDEX ds_ship_mode_t1_index3 ON tpcds.ship_mode_t1(SM_SHIP_MODE_SK) WHERE SM_SHIP_MODE_SK>10;

\d+ tpcds.ship_mode_t1

select * from pg_indexes where tablename = 'ship_mode_t1';

ALTER INDEX tpcds.ds_ship_mode_t1_index1 RENAME TO ds_ship_mode_t1_index5;

ALTER INDEX tpcds.ds_ship_mode_t1_index2 UNUSABLE;

CREATE TABLESPACE example0 RELATIVE LOCATION 'tablespace1/tablespace_0';
alter index tpcds.ds_ship_mode_t1_index4 set tablespace example0;
\d+ tpcds.ship_mode_t1;

ALTER INDEX tpcds.ds_ship_mode_t1_index2 REBUILD;
REINDEX INDEX tpcds.ds_ship_mode_t1_index4;

reindex table tpcds.ship_mode_t1;

DROP INDEX tpcds.ds_ship_mode_t1_index2;
DROP INDEX tpcds.ds_ship_mode_t1_index3;
DROP INDEX tpcds.ds_ship_mode_t1_index4;
DROP INDEX tpcds.ds_ship_mode_t1_index5;

2.作业

CREATE TABLE products
( id INTEGER NOT NULL,
price INTEGER NOT NULL,
name CHAR(10));
CREATE UNIQUE INDEX products_index1 ON products(id);
CREATE INDEX products_index2 ON products USING btree(price);
CREATE INDEX products_index3 ON products( SUBSTR(name,1,5));

ALTER INDEX products_index1 UNUSABLE;
ALTER INDEX products_index2 SET TABLESPACE example0;
ALTER INDEX products_index3 RENAME TO products_index33;

ALTER INDEX products_index2 REBUILD;
REINDEX TABLE products;

\d+ products;
select * from pg_indexes;

DROP TABLE products;