openGauss每日一练第9天课程笔记和作业

原创云散月明 All China Database Union 2021-12-14

844

数据库环境

openGauss:2.0.0 - 数据库实训平台

学习目标

索引是对数据库表中一列或多列的值进行排序的一种结构，使用索引可快速访问数据库表中的特定信息

学习笔记

唯一索引、B-tree索引、表达式索引、部分索引

omm=# CREATE UNIQUE INDEX ds_ship_mode_t1_index1 ON tpcds.ship_mode_t1(SM_SHIP_MODE_SK);
CREATE INDEX
omm=# CREATE INDEX ds_ship_mode_t1_index4 ON tpcds.ship_mode_t1 USING btree(SM_SHIP_MODE_SK);
CREATE INDEX
omm=# CREATE INDEX ds_ship_mode_t1_index2 ON tpcds.ship_mode_t1(SUBSTR(SM_CODE,1 ,4));
CREATE INDEX
omm=# CREATE UNIQUE INDEX ds_ship_mode_t1_index3 ON tpcds.ship_mode_t1(SM_SHIP_MODE_SK) WHERE SM_SHIP_MODE_SK>10;
CREATE INDEX
omm=# \d+ tpcds.ship_mode_t1
                             Table "tpcds.ship_mode_t1"
     Column      |     Type      | Modifiers | Storage  | Stats target | Description 
-----------------+---------------+-----------+----------+--------------+-------------
 sm_ship_mode_sk | integer       | not null  | plain    |              | 
 sm_ship_mode_id | character(16) | not null  | extended |              | 
 sm_type         | character(30) |           | extended |              | 
 sm_code         | character(10) |           | extended |              | 
 sm_carrier      | character(20) |           | extended |              | 
 sm_contract     | character(20) |           | extended |              | 
Indexes:
    "ds_ship_mode_t1_index1" UNIQUE, btree (sm_ship_mode_sk) TABLESPACE pg_default
    "ds_ship_mode_t1_index3" UNIQUE, btree (sm_ship_mode_sk) TABLESPACE pg_default WHERE sm_ship_mode_sk > 10
    "ds_ship_mode_t1_index2" btree (substr(sm_code::text, 1, 4)) TABLESPACE pg_default
    "ds_ship_mode_t1_index4" btree (sm_ship_mode_sk) TABLESPACE pg_default
Has OIDs: no
Options: orientation=row, compression=no

omm=# select * from pg_indexes where tablename = 'ship_mode_t1';
 schemaname |  tablename   |       indexname        | tablespace |                                                                     indexdef               
------------+--------------+------------------------+------------+---------------------------------------------------------------------------------------------------------------------------------------------------
 tpcds      | ship_mode_t1 | ds_ship_mode_t1_index1 |            | CREATE UNIQUE INDEX ds_ship_mode_t1_index1 ON tpcds.ship_mode_t1 USING btree (sm_ship_mode_sk) TABLESPACE pg_default
 tpcds      | ship_mode_t1 | ds_ship_mode_t1_index4 |            | CREATE INDEX ds_ship_mode_t1_index4 ON tpcds.ship_mode_t1 USING btree (sm_ship_mode_sk) TABLESPACE pg_default
 tpcds      | ship_mode_t1 | ds_ship_mode_t1_index2 |            | CREATE INDEX ds_ship_mode_t1_index2 ON tpcds.ship_mode_t1 USING btree (substr((sm_code)::text, 1, 4)) TABLESPACE pg_default
 tpcds      | ship_mode_t1 | ds_ship_mode_t1_index3 |            | CREATE UNIQUE INDEX ds_ship_mode_t1_index3 ON tpcds.ship_mode_t1 USING btree (sm_ship_mode_sk) TABLESPACE pg_default WHERE (sm_ship_mode_sk > 10)
(4 rows)

修改索引表空间

omm=# alter index tpcds.ds_ship_mode_t1_index4 set tablespace example0;
ALTER INDEX
omm=# \d+ tpcds.ship_mode_t1;
                             Table "tpcds.ship_mode_t1"
     Column      |     Type      | Modifiers | Storage  | Stats target | Description 
-----------------+---------------+-----------+----------+--------------+-------------
 sm_ship_mode_sk | integer       | not null  | plain    |              | 
 sm_ship_mode_id | character(16) | not null  | extended |              | 
 sm_type         | character(30) |           | extended |              | 
 sm_code         | character(10) |           | extended |              | 
 sm_carrier      | character(20) |           | extended |              | 
 sm_contract     | character(20) |           | extended |              | 
Indexes:
    "ds_ship_mode_t1_index3" UNIQUE, btree (sm_ship_mode_sk) TABLESPACE pg_default WHERE sm_ship_mode_sk > 10
    "ds_ship_mode_t1_index5" UNIQUE, btree (sm_ship_mode_sk) TABLESPACE pg_default
    "ds_ship_mode_t1_index2" btree (substr(sm_code::text, 1, 4)) TABLESPACE pg_default
    "ds_ship_mode_t1_index4" btree (sm_ship_mode_sk) TABLESPACE example0, tablespace "example0"
Has OIDs: no
Options: orientation=row, compression=no

课后作业

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

omm=# create schema product_schema;
CREATE SCHEMA
omm=# create table product_schema.products
omm-# (
omm(#     product_sk integer not null,
omm(#     product_id char(20) not null,
omm(#     product_name char(30)
omm(# );
CREATE TABLE
omm=# \d+ product_schema.products;
                         Table "product_schema.products"
    Column    |     Type      | Modifiers | Storage  | Stats target | Description 
--------------+---------------+-----------+----------+--------------+-------------
 product_sk   | integer       | not null  | plain    |              | 
 product_id   | character(20) | not null  | extended |              | 
 product_name | character(30) |           | extended |              | 
Has OIDs: no
Options: orientation=row, compression=no

omm=# select * from pg_indexes where tablename = 'products';    
 schemaname | tablename | indexname | tablespace | indexdef 
------------+-----------+-----------+------------+----------
(0 rows)

omm=# create unique index products_index1 on product_schema.products(product_sk);
CREATE INDEX
omm=# create index products_index2 on product_schema.products using btree(product_sk);
CREATE INDEX
omm=# create index products_index3 on product_schema.products(substr(product_name,1 ,4));
CREATE INDEX
omm=# \d+ product_schema.products;
omm=#                          Table "product_schema.products"
    Column    |     Type      | Modifiers | Storage  | Stats target | Description 
--------------+---------------+-----------+----------+--------------+-------------
 product_sk   | integer       | not null  | plain    |              | 
 product_id   | character(20) | not null  | extended |              | 
 product_name | character(30) |           | extended |              | 
Indexes:
    "products_index1" UNIQUE, btree (product_sk) TABLESPACE pg_default
    "products_index2" btree (product_sk) TABLESPACE pg_default
    "products_index3" btree (substr(product_name::text, 1, 4)) TABLESPACE pg_default
Has OIDs: no
Options: orientation=row, compression=no


omm=# select * from pg_indexes where tablename = 'products';
   schemaname   | tablename |    indexname    | tablespace |                                                            indexdef                              
----------------+-----------+-----------------+------------+--------------------------------------------------------------------------------------------------------------------------------
 product_schema | products  | products_index1 |            | CREATE UNIQUE INDEX products_index1 ON product_schema.products USING btree (product_sk) TABLESPACE pg_default
 product_schema | products  | products_index2 |            | CREATE INDEX products_index2 ON product_schema.products USING btree (product_sk) TABLESPACE pg_default
 product_schema | products  | products_index3 |            | CREATE INDEX products_index3 ON product_schema.products USING btree (substr((product_name)::text, 1, 4)) TABLESPACE pg_default
(3 rows)

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

omm=# alter index product_schema.products_index1 unusable;
ALTER INDEX
omm=# create tablespace products_ts1 relative location 'tablespace/products_ts1';
CREATE TABLESPACE
omm=# alter index product_schema.products_index2 set tablespace products_ts1;
ALTER INDEX
omm=# alter index product_schema.products_index3 rename to products_index3_new;
ALTER INDEX
omm=# \d+ product_schema.products;
                         Table "product_schema.products"
    Column    |     Type      | Modifiers | Storage  | Stats target | Description 
--------------+---------------+-----------+----------+--------------+-------------
 product_sk   | integer       | not null  | plain    |              | 
 product_id   | character(20) | not null  | extended |              | 
 product_name | character(30) |           | extended |              | 
Indexes:
    "products_index1" UNIQUE, btree (product_sk) TABLESPACE pg_default
    "products_index2" btree (product_sk) TABLESPACE products_ts1, tablespace "products_ts1"
    "products_index3_new" btree (substr(product_name::text, 1, 4)) TABLESPACE pg_default
Has OIDs: no
Options: orientation=row, compression=no
omm=# select * from pg_indexes where tablename = 'products';
   schemaname   | tablename |      indexname      |  tablespace  |                                                              indexdef                      
----------------+-----------+---------------------+--------------+------------------------------------------------------------------------------------------------------------------------------------
 product_schema | products  | products_index1     |              | CREATE UNIQUE INDEX products_index1 ON product_schema.products USING btree (product_sk) TABLESPACE pg_default
 product_schema | products  | products_index2     | products_ts1 | CREATE INDEX products_index2 ON product_schema.products USING btree (product_sk) TABLESPACE products_ts1
 product_schema | products  | products_index3_new |              | CREATE INDEX products_index3_new ON product_schema.products USING btree (substr((product_name)::text, 1, 4)) TABLESPACE pg_default
(3 rows)

omm=#

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

omm=# reindex index product_schema.products_index2;
REINDEX
omm=# reindex table product_schema.products;
REINDEX

两种重建索引方法的帮助信息
REINDEX
ALTER INDEX

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

omm=# \d+ product_schema.products;
                         Table "product_schema.products"
    Column    |     Type      | Modifiers | Storage  | Stats target | Description 
--------------+---------------+-----------+----------+--------------+-------------
 product_sk   | integer       | not null  | plain    |              | 
 product_id   | character(20) | not null  | extended |              | 
 product_name | character(30) |           | extended |              | 
Indexes:
    "products_index1" UNIQUE, btree (product_sk) TABLESPACE pg_default
    "products_index2" btree (product_sk) TABLESPACE products_ts1, tablespace "products_ts1"
    "products_index3_new" btree (substr(product_name::text, 1, 4)) TABLESPACE pg_default
Has OIDs: no
Options: orientation=row, compression=no

omm=# select * from pg_indexes where tablename='products';
   schemaname   | tablename |      indexname      |  tablespace  |                                                              indexdef                      
----------------+-----------+---------------------+--------------+------------------------------------------------------------------------------------------------------------------------------------
 product_schema | products  | products_index1     |              | CREATE UNIQUE INDEX products_index1 ON product_schema.products USING btree (product_sk) TABLESPACE pg_default
 product_schema | products  | products_index2     | products_ts1 | CREATE INDEX products_index2 ON product_schema.products USING btree (product_sk) TABLESPACE products_ts1
 product_schema | products  | products_index3_new |              | CREATE INDEX products_index3_new ON product_schema.products USING btree (substr((product_name)::text, 1, 4)) TABLESPACE pg_default
(3 rows)
omm=#

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

omm=# drop index product_schema.products_index1;    
DROP INDEX
omm=# drop index product_schema.products_index2;
DROP INDEX
omm=# drop index product_schema.products_index3;
ERROR:  index "products_index3" does not exist
omm=# drop index product_schema.products_index3_new;
DROP INDEX
omm=# drop table product_schema.products;
DROP TABLE
omm=# drop schema product_schema;
DROP SCHEMA
omm=# drop tablespace products_ts1;
DROP TABLESPACE

学习资源

欢迎各位同学一起来交流学习心得！

墨力计划 opengauss

最后修改时间：2021-12-16 18:37:42

「喜欢这篇文章，您的关注和赞赏是给作者最好的鼓励」

关注作者

文章被以下合辑收录

课程笔记每日一练：openGauss数据库在线实训课程（共24篇）

《每日一练：openGauss数据库在线实训课程》的课程笔记（2021年12月）

openGauss每日一练第9天 课程笔记和作业