openGauss 普通表索引 index

openGauss每日一练第9天 | 普通表索引

学习内容

作业内容

1.创建表products, 分别为表创建一个unique索引1，指定b-tree索引2和表达式索引3

create schema tpcds;
create table tpcds.products(
	product_id int not null,
	product_name char(20) not null,
	product_type char(20),
	product_category char(20)
);
create unique index index1 on tpcds.products(product_id);
create index index2 on tpcds.products using btree(product_name);
create index index3 on tpcds.products(substr(product_name,2,5));

效果

create schema tpcds;
CREATE SCHEMA
omm=# create table tpcds.products(
omm(# product_id int not null,
omm(# product_name char(20) not null,
omm(# product_type char(20),
omm(# product_category char(20)
omm(# );
CREATE TABLE
omm=# create unique index index1 on tpcds.products(product_id);
CREATE INDEX
omm=# create index index2 on tpcds.products using btree(product_name);
CREATE INDEX
omm=# create index index3 on tpcds.products(substr(product_name,2,5));
CREATE INDEX
omm=# 

2.设置索引1不可用，修改索引2的表空间，重命名索引3

alter index tpcds.index1 unusable;

create tablespace tablespace0 relative location 'tablespace/tablespace0';
alter index tpcds.index2 set tablespace tablespace0;
\d+ tpcds.products;

alter index tpcds.index3 rename to index3new;

效果

omm=# alter index tpcds.index1 unusable;
ALTER INDEX
omm=# create tablespace tablespace0 relative location 'tablespace/tablespace0';
CREATE TABLESPACE
omm=# alter index tpcds.index2 set tablespace tablespace0;
ALTER INDEX
omm=# \d+ tpcds.products;
                                Table "tpcds.products"
      Column      |     Type      | Modifiers | Storage  | Stats target | Description 
------------------+---------------+-----------+----------+--------------+-------------
 product_id       | integer       | not null  | plain    |              | 
 product_name     | character(20) | not null  | extended |              | 
 product_type     | character(20) |           | extended |              | 
 product_category | character(20) |           | extended |              | 
Indexes:
    "index1" UNIQUE, btree (product_id) TABLESPACE pg_default
    "index2" btree (product_name) TABLESPACE tablespace0, tablespace "tablespace0"
    "index3" btree (substr(product_name::text, 2, 5)) TABLESPACE pg_default
Has OIDs: no
Options: orientation=row, compression=no

omm=# alter index tpcds.index3 rename to index3new;
ALTER INDEX
omm=# \d+ tpcds.products;
                                Table "tpcds.products"
      Column      |     Type      | Modifiers | Storage  | Stats target | Description 
------------------+---------------+-----------+----------+--------------+-------------
 product_id       | integer       | not null  | plain    |              | 
 product_name     | character(20) | not null  | extended |              | 
 product_type     | character(20) |           | extended |              | 
 product_category | character(20) |           | extended |              | 
Indexes:
    "index1" UNIQUE, btree (product_id) TABLESPACE pg_default
    "index2" btree (product_name) TABLESPACE tablespace0, tablespace "tablespace0"
    "index3new" btree (substr(product_name::text, 2, 5)) TABLESPACE pg_default
Has OIDs: no
Options: orientation=row, compression=no

3.重建索引2和products的所有索引

重建索引2
alter index tpcds.index2 rebuild;
或
reindex index tpcds.index2;

重建products的所有索引
reindex table tpcds.products;

效果

omm=# alter index tpcds.index2 rebuild;
REINDEX
omm=# reindex table tpcds.products;
REINDEX

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

\d+ tpcds.products

select * from pg_indexes;

select * from pg_indexes where schemaname='tpcds';

效果

omm=# \d+ tpcds.products
                                Table "tpcds.products"
      Column      |     Type      | Modifiers | Storage  | Stats target | Description 
------------------+---------------+-----------+----------+--------------+-------------
 product_id       | integer       | not null  | plain    |              | 
 product_name     | character(20) | not null  | extended |              | 
 product_type     | character(20) |           | extended |              | 
 product_category | character(20) |           | extended |              | 
Indexes:
    "index1" UNIQUE, btree (product_id) TABLESPACE pg_default
    "index2" btree (product_name) TABLESPACE tablespace0, tablespace "tablespace0"
    "index3new" btree (substr(product_name::text, 2, 5)) TABLESPACE pg_default
Has OIDs: no
Options: orientation=row, compression=no

omm=# select * from pg_indexes;
 schemaname |           tablename           |                   indexname                   | tablespace  |                                                             
                  indexdef                                                                               
------------+-------------------------------+-----------------------------------------------+-------------+-------------------------------------------------------------
---------------------------------------------------------------------------------------------------------
 tpcds      | products                      | index1                                        |             | CREATE UNIQUE INDEX index1 ON tpcds.products USING btree (pr
oduct_id) TABLESPACE pg_default
 tpcds      | products                      | index2                                        | tablespace0 | CREATE INDEX index2 ON tpcds.products USING btree (product_n
ame) TABLESPACE tablespace0
 tpcds      | products                      | index3new                                     |             | CREATE INDEX index3new ON tpcds.products USING btree (substr
((product_name)::text, 2, 5)) TABLESPACE pg_default
 pg_catalog | statement_history             | statement_history_time_idx                    |             | CREATE INDEX statement_history_time_idx ON statement_history
 USING btree (start_time, is_slow_sql) TABLESPACE pg_default
 pg_catalog | pg_type                       | pg_type_typname_nsp_index                     |             | CREATE UNIQUE INDEX pg_type_typname_nsp_index ON pg_type USI
NG btree (typname, typnamespace) TABLESPACE pg_default
 pg_catalog | pg_ts_dict                    | pg_ts_dict_dictname_index                     |             | CREATE UNIQUE INDEX pg_ts_dict_dictname_index ON pg_ts_dict 
USING btree (dictname, dictnamespace) TABLESPACE pg_default
（此处省略很多行）

omm=# select * from pg_indexes where schemaname='tpcds';
 tpcds      | products  | index1    |             | CREATE UNIQUE INDEX index1 ON tpcds.products USING btree (product_id) TABLESPACE pg_default
 tpcds      | products  | index2    | tablespace0 | CREATE INDEX index2 ON tpcds.products USING btree (product_name) TABLESPACE tablespace0
 tpcds      | products  | index3new |             | CREATE INDEX index3new ON tpcds.products USING btree (substr((product_name)::text, 2, 5)) TABLESPACE pg_default
(3 rows)

omm=#  schemaname | tablename | indexname | tablespace  |                                                    indexdef                                                     
------------+-----------+-----------+-------------+-----------------------------------------------------------------------------------------------------------------

omm=# 

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

/* 按顺序“从子到父”操作？ */
drop index index1;
drop index index2;
drop index index3;
drop tablespace tablespace0;
drop table products;

如果跳过drop index，直接drop table，会把相关index都删除

omm=# drop table tpcds.products;
DROP TABLE
omm=# drop index index1;
ERROR:  index "index1" does not exist
omm=# drop index index2;
ERROR:  index "index2" does not exist
omm=# drop index index3;
ERROR:  index "index3" does not exist
omm=# drop tablespace tablespace0;
DROP TABLESPACE

最后删掉模式

omm=# drop schema tpcds;
DROP SCHEMA
omm=# \dn
   List of schemas
    Name     | Owner 
-------------+-------
 cstore      | omm
 dbe_perf    | omm
 pkg_service | omm
 public      | omm
 snapshot    | omm
(5 rows)